Sync Inc

Syncing to your database

When creating a new sync on Sync Inc, you'll have two options for your destination:

  1. Sync to our Sync Inc-hosted database
  2. Sync to your self-hosted database

We provide Sync Inc-hosted databases so you can get up and running quickly. You'll be provisioned a private database on a shared AWS RDS instance. You can connect to a Sync Inc hosted database from any standard SQL client. Buy since your database is on a shared instance, your permissions are limited.

We recommend syncing to a database you host โ€”ย like your production database โ€”ย when you need more control or want to be able to query across your synced data and core data (e.g. your `users` table) directly. For instance, on a database you host you can install Postgres extensions like Hasura or Timescale. You can manage users and permissions. And you can set up VIEWs that combine data in a Sync Inc source with your core data tables.

In this guide, you'll connect your self-hosted database and set up your sync in just a couple minutes.

Self-hosted database setup

Step 1: Create a new account at https://app.syncinc.so/signup.

Step 2: Select the Source (e.g. Stripe, Airtable, etc) you want to sync to your database and connect it to Sync Inc.

If you need step-by-step guidance, read the setup guide for your source.

Step 3: In the Destination section, click the Edit button and select Launch or Connect to setup your database. Then click to Connect to a new database:

Connecting on TablePlus

Step 4: Enter the hosting details, schema, and admin credentials for your database.

Sync complete

  • Name: The name for your database in Sync Inc (this does not need to be the same as your database name).
  • Host: The host for your database.
  • Database: The name of the database you are connecting to (sometimes called the "`dbname`").
  • Port: The port number โ€” typically 5432.
  • Schema to sync to: The name of the Postgres schema (or namespace) Sync Inc will create in your database. This schema will contain all the tables and data synced from your source. For example, if you're syncing Stripe to your database, you might sync that data to a schema called `stripe`.
  • Credentials: Enter the username and password for a database admin user. Sync Inc never stores these credentials. We temporarily use these credentials to create a Sync Inc user in your database with the proper permissions to create and update schemas for your sync. The Sync Inc user we create does not have permissions to read any of your existing schemas or tables.

(For more on how permissions work in your database, see below.)

Manual Setup: If you would prefer not to provide your database admin credentials, you can click the Setup manually instead link and we'll walk you through the steps of creating a Postgres user for Sync Inc and granting it the proper permissions.

Step 5: Once you've entered the details for your database, click Setup. Sync Inc will verify your database connection.

Step 6: Select which Postgres users should be granted access to your new Sync Inc schema. Select as many as you need and then click Finish:

Sync complete

Step 7: When you click Finish, Sync Inc will create the new schemas in your database, setup a Sync Inc user, create a read role with access to your Sync Inc schemas, then add the users you selected in Step 6 to that role. Click Finish.

Sync complete

Step 8: Your self-hosted database is now connected to Sync Inc. Finish configuring your source and click the Create button to start your real-time sync.

If you hit any errors please start a chat with us so we can help.

Syncing additional sources

You can sync multiple sources into unique schemas within your self-hosted database. This makes querying across your data easy. Here is how to setup multiple sources to sync to your self-hosted database:

Step 0: If you haven't already, connect your self-hosted database to Sync Inc.

Step 1: Select the Source you want to sync to your database and connect it to Sync Inc.

Step 2: In the Destination section, click the Edit button and select Choose Existing:

Sync complete

Step 3: Click the Select button next to your self-hosted database:

Sync complete

Step 4: Confirm the name for the new schema you'd like to create in your database and click the Confirm changes button:

Sync complete

Step 5: Your self-hosted database is now configured for an additional source. Finish configuring your source and click the Create button to start your real-time sync.

Changing schema names

You can change the name of the schema we sync to.

Step 1: Edit your resource by clicking the pencil icon and selecting Edit details:

Sync complete

Step 2: Expand the Destination section and click Change:

Sync complete

Step 3: Select Choose existing.

Step 4: In the modal that appears, you'll see all the databases we are actively syncing to. Click the grey Configure button next to your database:

Sync complete

Step 5: Enter the new name for your schema. Then click Confirm changes:

Sync complete

Step 6: Click the Save button on your resource.

When you change the name of the schema associated to your sync, we'll create a new schema in your database with the new name and then backfill your data into this new schema. We will not automatically drop the old schema and swap schema.

If you would like, you can delete the old schema and swap schema from your database:

drop schema {{schema_name}} cascade;

The Sync Inc read role

Oftentimes, you'll have many different database users for your database. Each database user might correspond to an employee in your organization. Only a few of them may have admin privileges. Therefore, you need some way to grant these users explicit permission to read from Sync Inc-managed schemas.

Before syncing to your database, Sync Inc creates a read role. The read role has permissions to read all the Sync Inc data in your database. You can give or revoke the read role permission for your database users at any time.

If you used our automated database setup, your read role was automatically created. The read role takes the form `syncinc_read_XXXX`, where `XXXX` is a randomly-generated string, like `qbQ2g`. To find your read role:

  • You can check your Sync Inc console.
  • You can just let your SQL client auto-complete it after typing `syncinc_read` + "tab."
  • You can run this query:
select * from pg_roles where rolname ilike 'syncinc_read%';

When a user is granted the read role, they can read from every Sync Inc schema in your database.

To grant the read role to a user:

-- sub-out the read role below with the one in your db
grant syncinc_read_qbQ2g to some_user;

To revoke the read role from a user:

revoke syncinc_read_qbQ2g from some_user;

Deleting a self-hosted database

If you would like to delete your self-hosted database from Sync Inc, send us a note and we'll delete the database from our system and send you the queries to remove the schemas and users associated to Sync Inc.

Welcome
ORMs

Was this helpful?