Reference

info

We've closed our beta for Airtable. Join the waitlist and we'll notify you when we're ready.

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 ↙

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 Type	Sequin 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	text[]
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

By default, Linked Record fields on Airtable can refer to multiple records. However, you can turn this off on Airtable by editing a Linked Record field and toggling off "Allow linking to multiple records."

When Sequin's Intelligent cast setting is enabled, we'll read this toggle on Airtable to determine whether Linked Record fields should be text (single records) or text[] (multiple records). text columns are easier to work with in Postgres than text[] columns and allow us to setup foreign key constrainsts between the linked fields.

When this setting is disabled, all Linked Record fields will be cast to text[], no matter the quantity of records linked.

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:

• PostgreSQL Array Documentation
• PopSQL Postgres Arrays Guide
• Joins with PostgresQL Arrays by Gary Sieling

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;

2. unnest() function will expand any array into a set of rows

SELECT unnest(product_inventory) FROM warehouse_locations;

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

Size	Est sync time
1,000	3.33s
5,000	16.66s
10,000	33.33s
20,000	1min 6s
50,000	2min 46s
100,000	5min 33s

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

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:

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":

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:

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 and ensures your database stays in sync with your Airtable instance.

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.

Translation layer

When enabled, the translation layer lets you write and update data in your base with the same table and column names found in your Sequin database. This is a helpful feature for keeping your code consistent and easier to read.

To use it, just add the header sequin-translate: 1 to your proxy requests.

Example

Let's suppose you have a table named "Design Projects" in your Airtable base. Sequin will name this table design_projects in your database. Let's say this table has the fields "Client Name" and "Project Name." Sequin will convert these to the columns client_name and project_name.

When writing code that queries your Sequin database, you'll be using the snake-cased identifiers for everything. But then when it's time to write to the API, you need to map all the Sequin table and column names back to their Airtable representation:

Method	POST
URL	https://proxy.sequin.io/api.airtable.com/v0/appXXXXXXX/Design%20Projects

// Without the translation layer
{
  "records": [
    {
      "fields": {
        "Client Name": "João Apolinário",
        "Project Name": "Secret Project"
      }
    }
  ]
}

With the translation layer enabled, you can keep the identifiers as they are. Just add the header sequin-translate: 1 to your request, and Sequin will remap the identifiers before forwarding your request to Airtable:

Method	POST
URL	https://proxy.sequin.io/api.airtable.com/v0/appXXXXXXX/Design%20Projects
Headers	sequin-translate: 1

// With the translation layer
{
  "records": [
    {
      "fields": {
        "client_name": "João Apolinário",
        "project_name": "Secret Project"
      }
    }
  ]
}

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.