You can use a Postgres GUI client like TablePlus or Postico to connect to your database and get a sense of the tables, columns, and their values. GUI clients are also great ways to perform one-off workflows on your API data (“how many Salesforce leads did we add in the last 30 days?”).

When you’re ready to start building your integration, you’ll need to configure the Postgres adapter you’re using. While you can use a simple Postgres adapter and build your integration with raw SQL, ORMs often provide a superior interface.

​
Configuring your ORM

Object–relational mapping libraries (ORMs) are the best way to bring your Sequin-synced data into your application. ORMs let us write expressive queries for our data using our programming language. This ORM query for Prisma generates a complex SQL query under the hood:

let charges = await prisma.charge.findMany({
  where: { customer_id: customer_id, receipt_url: { not: null } },
  orderBy: { created: "desc" },
  include: { payment_method: true },
});

​
Migrations

Most ORMs come with a migration tool. The migration tool lets you define a database migration in a file. That migration might add a new table or drop a column.

When using Sequin-synced tables, Sequin is the “owner” of those tables. That means that all migrations are taken care of for you. If you want to add a table, add the table in Sequin’s console and Sequin will add the table to your database.

Most developers connect Sequin to the databases that they use in both production and development. This means managing migrations is not necessary for those environments.

However, if you want to write automated tests against your Sequin tables, you’ll likely need to add migrations to make Sequin’s tables and columns available to your test code.

​
Defining ORM models

In an ORM, a model is a class that represents a table in the database. It serves as the link between your code and your relational database.

A model typically corresponds to a single table in the database. The attributes or properties of the model class correspond to the columns of the table. An instance of the model represents a single row in the database table.

Each ORM requires some effort to setup these models. Some, like Prisma, require a full schema definition which describes the database structure in an ORM-specific format (the ORM’s DSL or domain-specific language). Others, like ActiveRecord, only require that you setup the relationships between tables.

In general, the process for setting up an ORM is:

  1. For each table, list the schema
  2. Paste each table’s schema into your ORM schema definitions, translating to your ORM’s DSL
  3. Setup the relationships between tables
  4. Set the primary key type for your ORM to the right type

For ORMs like ActiveRecord that don’t require a schema definition, you can skip steps 1 & 2. Compiled or statically-typed languages typically require a schema definition.

1. List the schema for your tables

For each table you want to create an ORM schema for, you can use the schema in Postgres to bootstrap your schema definition. For example, to list the schema for the table customer, you can run:

select
    column_name,
    data_type
from information_schema.columns
where table_name = 'customer';

2. Paste and translate

In your codebase, create the schema definition file. Paste in the schema definition from Postgres. Then tweak it into a valid declaration according to your language’s DSL.

For example, if setting up an Elixir/Ecto schema for a Stripe customer, you might paste in a schema definition that looks like this:

defmodule MyApp.Synced.Stripe.Customer do
  use MyApp.SyncedSchema

  schema "customer" do
    id  text
    address_city    text
    address_country text
    address_line1   text
    address_line2   text
    address_postal_code text
    address_state   text
    balance bigint
    created timestamp without time zone
  end
end

Using some text editor wizardry, you can quickly:

  • Reformat each line into a valid declaration
  • Swap out data types as necessary

Like so:

defmodule MyApp.Synced.Stripe.Customer do
  use MyApp.SyncedSchema

  schema "customer" do
    field :address_city, :string
    field :address_country, :string
    field :address_line1, :string
    field :address_line2, :string
    field :address_postal_code, :string
    field :address_state, :string
    field :balance, :integer
    field :created, :naive_datetime
  end
end

Delete any fields that are references, as you’ll handle those in the next step.

3. Setup relationships

Next you need to declare relationships between entities, such as “has one” or “belongs to” relationships. Again, in Elixir, that would look like this:

schema "subscription" do
  belongs_to :customer
end

That means there’s a customer_id on subscription which references a customer. With this field in place, we can now call subscription.customer on any subscription to retrieve its corresponding customer. This is called a forward reference and is a key value that ORMs provide.

The easiest approach is to setup the relationship on the child first, as the field for the relationship lives there. Then you can add the corresponding field on the parent later. All forward references end in _id, so an easy way to see which tables a given table is the child of is to run this query:

select
    column_name,
    data_type
from information_schema.columns
where table_name = 'subscription'
and column_name ilike '%_id';

4. Set the primary key type for your ORM

The last step required by many ORMs is to set the type for the primary key of the IDs on your synced tables. Sometimes IDs are declared along with other fields, other times they’re declared separately. You’ll need to set the primary key type to text, as that’s the ID type used by all of Sequin’s sources.

info

You may be able to skip some of these steps if your ORM has a schema generator. While it’s worth a Google search to see if your ORM has one, you’ll usually still want to take a manual pass of the auto-generated files. Some of these generators miss relationships between tables or give odd names to back-refs or forward-refs.

​
Notes for specific ORMs

Below are setup notes for a few different ORMs. Even if you don’t see your ORM below, many of the configuration notes apply across ORMs:

​
Ecto (Elixir)

Ecto does not have schema generator. But fortunately Ecto declarations are concise and easy to generate (see above).

You’ll want to set three module attributes for each one of your Ecto schemas:

@primary_key {:id, :string, []}
@foreign_key_type :string
@schema_prefix "stripe"
schema "subscription" do
  # ...
end

In Ecto, you need to specify the type for both the primary key and foreign keys as types string. If you’re syncing to a Postgres schema other than public, you’ll also want to set the @schema_prefix.

If you’re generating many schemas, you can roll some of these module attributes up to a shared module like this:

# synced_schema.ex
defmodule MyApp.SyncedSchema do
  defmacro __using__(_) do
    quote do
      use Ecto.Schema
      @primary_key {:id, :string, []}
      @foreign_key_type :string
    end
  end
end

# stripe/customer.ex
defmodule MyApp.Synced.Stripe.Customer do
  use MyApp.SyncedSchema

  @schema_prefix "stripe"
  schema "customer" do
    # ...
  end
end

​
ActiveRecord (Ruby)

ActiveRecord does not require schema definitions. So, for each table, you just need to:

  1. Declare a model
  2. Specify the table’s name (with the schema)
  3. Establish relationships between tables

Here’s an example:

class Stripe::Subscription < ApplicationRecord
  # set the table name with the schema
  self.table_name = "stripe.subscription"

  # setup the relationships
  belongs_to :customer
  belongs_to :latest_invoice
end

​
Prisma (JavaScript)

While Prisma has a schema generator, there are two considerations:

  1. Verbose default names for forward-refs and back-refs.
  2. No support for querying across schemas

1. Verbose default names for forward-refs and back-refs.

Prisma has a schema generator. But for our more complex syncs like Stripe, its default names for forward-refs and back-refs aren’t ideal. For example, you’ll see some crazy field names like this:

payment_method_payment_intent_payment_method_idTopayment_method

We have some scripts we use to clean these up. We’re happy to provide Prisma schemas on demand. Just reach out.

2. No support for querying across schemas

Prisma does not support querying across schemas. This is a bummer, but we’re hoping this will change soon.

In the meantime, we can sync to an existing schema in your database, but with a prefix. For example, if you’re syncing Stripe to your database, we’d sync all tables with the stripe_ prefix, like stripe_customer and stripe_subscription. This feature is not exposed in our UI at the moment, so just get in touch if you need it.

Getting startedWriting data