Sequin

Syncing to your database

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

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

We provide Sequin-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 Sequin hosted database from any standard SQL client. But 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 Sequin 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.sequin.io/signup.

Step 2: Select the Source (e.g. Stripe, Airtable, 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:

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 Sequin (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) 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 5: Once you've entered the details for your database, click Setup. Sequin will verify your database connection.

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

Sync complete

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

Step 7: 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.

Sync complete

Step 8: 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:

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;

Permissions

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

  • The `sequin` user: This user only has permission to connect to your database and create the schemas, tables, and views associated to your sync. The `sequin` user will keep run the transactions that keep all your data in sync in real-time. This user will need admin permissions on any table or view that is in sync. It should not have permissions to anything else!
  • The `sequin_read` role: This role includes the permissions to read all the sequin data in your database. Any user in your database that needs to read synced data should be granted the `sequin_read` role.

Depending on how you work with your Sequin data, you may need to extend the permissions of the sequin user and read role. Here is how.

PostgreSQL permissions overview

First, a quick refresher on how PostgreSQL permissions work.

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` amd `CREATE`). These permissions can be defined at the database, schema, table and view level.

A role is simply a set of permissions.

A user is technically a role that has login permissions. And a group is simply a collection of users. In fact, under the hood, a user, group and role are the same thing, with the only difference being that users have permissions to log in by default.

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 with encrypted password 'β–€β–€β–€β–€';

Then we grant that user permissions to:

  1. Connect to your database
  2. Create the schemas for each source we're syncing
grant connect, create on database YOUR_DBNAME to sequin;

Note: the permissions granted to `sequin` do not grant `sequin` access to the rest of your database. `sequin` will only be able to read and write to the schemas it manages.

The `sequin_read` role

The `sequin_read` role has permissions 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.

The read role created for you takes the form `sequin_read_XXXX`, where `XXXX` is a randomly-generated string, like `qbQ2g`.

Note: if you choose to manually connect your database, then you created this read role and there is likely no appended, random string.

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 to ensure that the `sequin` user does not inadvertently drop your table (since this table is not being synced).

But, in instances where the table needs to be in a `sequin` owned schema, make sure that the `sequin` user does not have access to the table to ensure it's not dropped during the syncing process (i.e. when Sequin detects that this table is not present in the source):

revoke all on YOUR_TABLE_NAME from sequin;

Views

You may want to create a new view in your Sequin schema. When creating a view, ensure that the `sequin` user is a co-owner of the view so that during the sync process the view can be recreated when tables referenced in the view change.

Why sequin needs to co-own views: `sequin` needs to be able to recreate views when performing a rebuild sync. This is because during a rebuild, all the tables in your Sequin schema are technically dropped and then replaced with the freshly synced tables in the `swap` the schema. When this happens, your views will break unless the `sequin` user has permission to grab the view definitions, drop the views during the swap, and then recreate the views referencing the new tables.

First, create a new `view_owner` role and grant it ownership privileges to the view.

create role view_owner;
alter view YOUR_VIEW owner to view_owner

Then, make `sequin` a co-owner of the view so it can drop and recreate the view during the sync process:

grant role view_owner to sequin

Deleting a self-hosted database

If you would like to delete your self-hosted database from Sequin, 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 Sequin.

Welcome
ORMs

Was this helpful?