ORMs

Object–relational mapping libraries (ORMs) are very useful when connecting code to a SQL database.

Take for example this query for Stripe. This query grabs all charges (along with the charge's payment card details) for a given `customer_id`:

select charge.*, payment_method.card_last4, payment_method.card_brand
from charge
inner join payment_method on payment_method.id = charge.payment_method_id
where charge.customer_id = $1
and charge.receipt_url is not null
order by created desc

This query is concise, and without a SQL database we'd need to make several calls to Stripe's API. One way to improve upon it, though, would be to represent the query in a format other than just raw text.

That's where an ORM comes in. This is the same query but represented using the ORM Prisma:

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

The advantages of using an ORM like Prisma:

  1. Robust type-ahead prompts in your text editor while writing the query.
  2. If you reference unknown tables or fields, you'll get a compiler error.
  3. The result object (`charges`) is strictly typed.

ORMs + Sequin

Each ORM requires some effort to setup. 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.

We're actively working on adding robust support for ORMs to our platform. Ideally, you'll soon be able to (a) select the platforms you're syncing and (b) select the ORM you're using, and we'll give you setup instructions and downloadable code to use.

In the meantime, we'll walk you through the high-level steps for setting up any ORM. Below that, we're including notes on the ORMs we've audited so far. We're also happy to help you get going, just send us a note in Intercom.

Setup

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.

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

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.

More coming soon!

Note for Airtable: Airtable presents especially unique challenges for ORMs, as the schema can change any time. So any ORM solution that we develop for Airtable will also need to include some sort of workflow for updating its schema definition.

Syncing to your database
Setup

Was this helpful?