Syncing to your database

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

1. Sync to a Sequin-hosted demo database
2. Sync to your self-hosted database

We provide Sequin-hosted demo 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 Sequin hosted database from any standard SQL client. But since your database is on a shared instance, permissions and performance are limited.

After you've gotten a feel for Sequin and are ready to use your sync in production, configure us to sync right to your production database.

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.sequin.io/signup.

Step 2: Select the Source (e.g. Salesforce, HubSpot, etc) you want to sync to your database and connect it to Sequin.

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:

Step 4: Enter the hosting details for your database. Optionally setup a SSH Tunnel to connect to a Postgres instance in a private network.

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

• Database: The name of the database you are connecting to (sometimes called the "dbname").
• Schema to sync to: The name of the Postgres schema (or namespace) Sequin 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. Sequin never stores these credentials. We temporarily use these credentials to create a Sequin user in your database with the proper permissions to create and update schemas for your sync. The Sequin 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, read the permissions section 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 Sequin and granting it the proper permissions.

Step 6: Once you've entered the details for your database, click Setup. Sequin will verify your database connection.

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

(If you need to grant a new user access to a Sequin schema, read the permissions section below.)

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

Step 9: Your self-hosted database is now connected to Sequin. 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.

SSL Certificates

If you require client SSL certificates to connect to your database, send us a note. This is often the case if you're using Google Cloud as your database provider.

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 Sequin.

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

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

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

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

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:

Step 2: Expand the Destination section and click Change:

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:

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

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;

Permissions

PostgreSQL permissions define what a user can do. You can define what a user can access (like CONNECT to your database) and what actions they can take (like SELECT and CREATE). These permissions can be defined at the database, schema, table, and view level.

A role is a set of permissions. A user is a role that has login permissions (includes a password that someone can use to login to the database).

In Postgres, a group is the same thing as a role. So we'll just use the role syntax everywhere.

When creating a sync to your self-hosted database, Sequin creates a new user and a new role:

• The sequin_▒▒▒▒ user (e.g. sequin_mta5mzk): This is the user Sequin uses to connect to your database. This user only has permission to connect to your database and create the schemas, tables, and views associated to your sync. This user will need owner privileges on any table or view related to your sync.
• The sequin_read_▒▒▒▒ role (e.g. sequin_read_mta5mzk): This role has the privilege to read Sequin tables and views in your database. Any user in your database that needs to read synced data should be granted the sequin_read_▒▒▒▒ role. It is provided for convenience and is not used by the Sequin sync process.

Note that your specific user and role names start with sequin_ and sequin_read_, but include a random alphanumeric appendix. The names are provided during the provisioning process of your database and available on the console at any time.

The Sequin user

The sequin_▒▒▒▒ user creates and updates the data we are syncing for you. When syncing to your database we first create the Sequin user:

create user sequin_mta5mzk with encrypted password '▀▀▀▀';

Then we grant that user permissions to:

1. Connect to your database
2. Create new schemas

grant connect, create on database YOUR_DBNAME to sequin_mta5mzk;

Note: the permissions granted to the Sequin user do not grant the user access to the rest of your database. The Sequin user will only be able to read and write to the schemas it creates.

The Sequin read role

The sequin_read_▒▒▒▒ role is provided for your convenience. The role has permission to read all the Sequin data in your database. You can give or revoke the read role permission to your database users at any time.

If you used our automated database setup, your read role was automatically created and granted to the users you selected.

If you used our manual database setup, you can manually create this read role. If we detect a role named sequin_read, we'll grant that role the appropriate permissions.

To find your read role:

• You can check your Sequin console.
• You can just let your SQL client auto-complete it after typing sequin_read + "tab."
• You can run this query:

select * from pg_roles where rolname ilike 'sequin_read%';

You can then grant users the ability to read from Sequin-managed schemas via the Sequin read role:

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

To revoke the read role from a user:

revoke sequin_read_qbQ2g from some_user;

Additional tables and views

Tables

You may want to create new tables in your Sequin schema. As a best practice, we encourage you to create any additional tables in a different schema.

But, in instances where the table needs to be in a Sequin-owned schema, make sure that the Sequin user does not own the table. Sequin won't drop tables it doesn't own. To verify the owner of a table, use this query:

select tableowner from pg_tables where tablename = 'my_table';

To set the table owner to your current user, run this command:

alter table stripe._sync_event owner to current_user;

Views

Postgres' views let you turn a query into a table. This lets you customize the schema and format of the data that Sequin syncs. Because views are just queries under the hood, they'll always return the latest data.

Treat views just like tables (above): make sure that Sequin is not the owner of the view.

Common setup issues

Create privileges

In order to successfully connect your database to Sequin, you will need a PostgreSQL admin user with both CREATE and CONNECT permissions.

To check if your PostgreSQL role has the necessary CREATE and CONNECT permissions, you can follow these steps:

• Connect to your PostgreSQL database using psql.

• Run the following command to display all the privileges granted to the role:

\du <role_name>

• Look for the lines that correspond to Create role, Create DB. If these lines are present, the role has the necessary permissions.

For example, a role with the necessary permissions might look like this:

 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 myrole    | Create role, Create DB                         | {}

• If the role does not have these privileges, you can grant them by using the following commands:

GRANT CREATE ON DATABASE mydatabase TO myrole;

Replace mydatabase and myrole with the appropriate values for your setup.

Please note that these commands require sufficient privileges on the database, so you may need to connect as a superuser to execute them.