Sequin

Airtable Reference

Querying Airtable with SQL

Below is how your Airtable data will be represented in your Sequin database.

Airtable base β†’ database

Each Airtable base maps one-to-one with a Sequin database.

Your Sequin database instance runs on a shared AWS Relational Database Service (RDS). By default, this database runs in the AWS region `us-west-2` (Oregon).

If you would like your database located closer to you, please set up a self-hosted database.

Your database is a read-only, follower database.

The name of your database is randomly assigned. Your database name does not match the name of your Airtable base.

You'll see two schemas in your database:

  • `public`: where all your tables are located. This is the one you use.
  • `public_swap`: a schema that we use for our syncing operation. (Ignore it.)

If you would like multiple bases to be synced as different schemas within one database, send us a quick note in intercom ↙

Airtable table β†’ table

Each table in your Airtable base maps one-to-one with a table in your Sequin database.

We sync all the data from your table including the record's `id`. We don't sync any of your Airtable views.

You'll see that we convert your table names to `snake_case` (i.e lowercase words delineated by an underscore).

So what was `Product Inventory` in Airtable is now `product_inventory` in your Sequin database.

If you used emojis πŸ™ˆ or punctuation in your table names, we'll remove them from the table names in your Sequin database so your queries are easy to write.

You'll also see a table called `_sync_meta` in your schema. Here's what it looks like:

dbsyncinc=# select * from _sync_meta;
started_at | completed_at | duration_last
-------------------------------+------------------------------+-----------------
2021-02-11 13:56:16.951233-08 | 2021-02-11 13:56:16.94848-08 | 00:00:00.634816

`started_at` is a timestamp for when the last sync was started. `completed_at` is a timestamp for when the last sync was completed. In the example above, because `started_at` is slightly more recent than `completed_at`, we know a sync is currently running. `duration_last` is the duration of the last sync, which in this example was 0.63 seconds.

For more information about our sync process, scroll down to "The syncing process".

Airtable field types β†’ Postgres column types

Below is a complete reference for how your Airtable data will be typed in your Sequin Postgres database.

Airtable Field TypeSequin Database Type
Attachment`text[]`
Autonumber`numeric`
Barcode`numeric`
Button`text`
Checkbox`bool`
Collaborator`text[]`
Count`numeric`
Created time`timestamp`
Currency`numeric`
Date`date` or `timestamp`
Duration`text`
Email`text`
Formula`text`
Last modified by`text`
Link to another record`text`
Long text`text`
Lookup^`text[]` or `text`
Multiple select`text[]`
Number`numeric`
Percent`numeric`
Phone number`text`
Rating`numeric`
Rollup`numeric`
Single line text`text`
Single select`text`
URL`text`

Note: You can toggle whether a Lookup will be stored as a `text[]` or `text` in your Postgres database using the Advanced settings for your resource. See the linked records and lookups section to learn how.

`id` and `created_time`

Every table will contain an `id` and `created_time` column:

  • `id` - this is the Airtable `record_id` for the that row.
  • `created_time` - this is the timestamp for when the record was created.

If your table has another column named `id` or `created_time` then we will append an underscore (`_`) to the end of the columns name like so:

  • `id` β†’ `id_`
  • `created_time` β†’ `created_time_`

Linked records and lookups

Linked records and lookups in your Airtable base can refer to one or more records. So these fields will appear in your Sequin database as an array (`text[]`) by default:

Lookup

For linked records specifically, if you only every link to single records, you can use the Advanced settings for your Sequin resource to toggle the linked records to be type `text`. All the linked records in your base will now be stored as type `text` in your Postgres database:

Advanced Settings

This can make your subsequent SQL queries less verbose and improve how your Sequin database integrates with BI tools.

You should only toggle this setting on if all the linked records in your base only link to single records. Otherwise, if more than one linked records is present in your Airtable base and this setting is turned on, your Sequin Postgres database will only store the first record.

A common query with linked records and lookups is to `JOIN` the two corresponding tables by matching a record's `id` with the `id` from the origination table (the table being looked up). Check out our cheat sheet for a complete example.

Arrays

Any field type that can contain more than one item will be represented in your Postgres database as an array of `text` values.

Here are some great resources for using PostgreSQL arrays:

Our cheat sheet has some great examples and tricks for working with arrays. But here are three tips:

  1. PostgreSQL uses 1-base index arrays. To select the first value in the array, start with `[1]`.
SELECT product_inventory[1] FROM warehouse_locations;
  1. `unnest()` function will expand any array into a set of rows
SELECT unnest(product_inventory) FROM warehouse_locations;
  1. The `ANY` operator makes `JOINS` easy:
SELECT warehouse_locations.name, product_inventory.product_name[1]
FROM warehouse_locations JOIN product_inventory
ON product_inventory.id = ANY (warehouse_locations.product_inventory);

The syncing process

Always syncing

Sequin is constantly syncing your Postgres database with Airtable. Your database's "sync time" is how long it takes us to complete a full sync of your base. Because we're always syncing, you can think of this time as the maximum replication lag.

Here's how long you can expect bases to sync at different sizes:

SizeEst sync time
1,0003.33s
5,00016.66s
10,00033.33s
20,0001min 6s
50,0002min 46s
100,0005min 33s

We display your base's current sync time in the console:

base with sync status

If your sync time is slower than usual, or if we're having trouble syncing, your sync indicator will turn yellow or red. Hover over it for details:

base with degraded performance

We automatically detect changes to your Airtable schema and migrate your database tables for you. After making a change to your Airtable schema, it may take a few minutes for those changes to appear in your database. These rebuild syncs take however long your sync time takes, plus about one minute.

Turbo

In your console, you'll see a toggle for "Turbo":

the turbo button

Your Sequin sync works by continuously pulling data about your base from Airtable's API. Airtable's API allows for 5 requests per second per base. By default, we configure your sync to use up 3 requests per second, leaving room for any other API activity you may have setup for your base. API activity includes using your own code to interface with your base as well as other third-party services like Zapier or Integromat.

When you turn Turbo "on" for a base, our sync process bumps up from 3 requests per second to the fully allotted 5 requests per second.

If you're mainly interfacing with your base using your own code, we recommend that you transition all your reads to your Sequin database and use our proxy for all your writes. Then, you can safely turn on Turbo to maximize your sync throughput and minimize how far your database lags behind Airtable. Our proxy will take care of "sharing" your base's API quota between your code and our sync.

If you're using a lot of third-party services like Zapier or Integromat, it's probably safest to leave Turbo off. If you want to experiment with turning it on, send us a note via Intercom or email and we can help you find the right configuration.

Writes

One-way data flow

Sequin provisions a read-only replica of your Airtable data. This is to promote a one-way data flow architecture.

Data flows from Airtable to your Postgres database. Your code or SQL client then reads from the database. To mutate your data, you write to the Airtable API through Sequin. Those mutations are applied simultaneously to both your Airtable base and your Sequin database so that they show up in subsequent reads by your code or SQL client:

Diagram of using Sequin Proxy

With this architecture, your code is structured so that you're using SQL for reads but API calls for writes. This gives you the best of both worlds while avoiding the conflict resolution that two-way syncing entails. We think you'll really enjoy building this way.

How to write through the proxy

To use the Sequin Proxy, you craft HTTP requests to the Airtable API like you normally would. Except, you prepend `proxy.sequin.io/` to the beginning of the hostname.

For example, here's a request that creates a new sales order in Airtable's example "Inventory Tracking" base:

curl -v -X POST https://proxy.sequin.io/api.airtable.com/v0/appβ–’β–’β–’β–’β–’β–’β–’β–’β–’/Sales%20Orders \
-H "Authorization: Bearer keyβ–’β–’β–’β–’β–’β–’β–’β–’β–’" \
-H "Content-Type: application/json" \
--data '{
"records": [
{
"fields": {
"Date": "2021-04-17",
"Product": [
"rec5zFZu80EN0QyJT"
],
"Quantity": 4,
"Sale Platform": "πŸ–₯ Online"
}
}
]
}'

Note the request looks exactly the same as what you'd find in the Airtable API docs, except for the URL. The base of the URL is `https://proxy.sequin.io/api.airtable.com` instead of `https://api.airtable.com`. This sends the request through the Sequin Proxy so that mutations are applied immediately to your Postgres database as well as your Airtable base.

You can also easily use the Sequin Proxy with the Airtable.js client (`airtable` on npm). You just need to set the `endpointUrl` to `https://proxy.sequin.io/api.airtable.com`, like this:

var Airtable = require("airtable");
var base = new Airtable({
apiKey: "keyβ–’β–’β–’β–’β–’β–’β–’β–’β–’",
endpointUrl: "https://proxy.sequin.io/api.airtable.com",
}).base("appβ–’β–’β–’β–’β–’β–’β–’β–’β–’");

The proxy works with every Airtable API procedure available: List requests (GET), updates (PATCH), creates (POST), and deletes (DELETE). All fields – including computed and lookup fields – will be written to your database immediately.

Write responses

The proxy acts as a reverse proxy to Airtable. Therefore, the response's status code, headers, and body are all set by Airtable.

The noteworthy exceptions are in the case of these two status codes:

  • `504`: This is sent by the Sequin proxy if we timed out before reaching Airtable. This may happen in certain instances where Airtable is overloaded and not serving requests in time.
  • `502`: A `502` may be sent by either the Proxy or by Airtable. The body will indicate whether the `502` was sent by the Proxy or by Airtable.

Secondary benefit: increased syncing speed

Airtable's API has a rate limit of 5 requests per second. By default, Sequin uses 3 requests per second to keep your database in-sync. This leaves room for you to make Airtable API requests without hitting Airtable's rate limit.

However, if you use the Sequin Proxy for all your Airtable API requests, the Proxy will intelligently manage your base's Airtable API quota between our sync process and your requests. The Proxy ensures we don't hit 429s, and gives priority to your API requests over our sync process.

Therefore, if you use the Proxy, we can bump up your sync process by 66% to use all 5 requests per second safely.

We'll be adding a toggle to the console to use this increased rate soon. In the meantime, please send us a note via Intercom or email if you're using the Proxy and would like a faster sync process.

How updates via the Proxy work

The Proxy forwards requests to Airtable's API. In the case of creates, updates, and deletes, when Airtable's API responds successfully, those changes are written to your Sequin database before the response is sent to you.

The order of operations is therefore expressed in the following example, sequentially:

  1. You make a POST request to create a new entry in the "Orders" table on Airtable, via the Sequin Proxy.
  2. The Sequin Proxy forwards the request to Airtable.
  3. Airtable responds with a `200`. The body contains the new order.
  4. The Sequin Proxy writes the new order to the `orders` table in your Sequin database.
  5. The Sequin Proxy forwards Airtable's response to you.

Given this order of operations, after your API request completes you can immediately read your Sequin database and expect that change to be present.

Note: immediate updates are only for the record that was created or modified. If you have another record dependent on this one – ie via a Linked Record field or Lookup field – that record will be updated according to your sync process at the rate shown in the Sequin console.

Cheat sheet
Airtable App

Was this helpful?