Querying Airtable with SQL
Below is how your Airtable data will be represented in your Sequin database.
Airtable base → sync
Each Airtable base maps one-to-one with a Sequin sync.
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.
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 constraints 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:
Our cheat sheet has some great examples and tricks for working with arrays. But here are three tips:
- PostgreSQL uses 1-base index arrays. To select the first value in the array, start with
[1].
SELECT product_inventory[1] FROM warehouse_locations;
unnest() function will expand any array into a set of rows
SELECT unnest(product_inventory) FROM warehouse_locations;
- 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:
Writes
The Sequin Postgres Proxy
To create, update, or delete objects in Airtable, you can insert, update, or delete rows in your Postgres database.
When you’re connected to your database through the Sequin Proxy, the Proxy listens for changes. When you make a mutation, the Proxy applies the mutation to Airtable’s API first. If the mutation succeeds, your database is updated as well. If it fails, your database mutation will be rolled back, and you’ll receive a Postgres error.
Data flows from Airtable to your Postgres database. Your code or SQL client then reads from the database. To mutate your data, you update records in your database. Those mutations are applied to Airtable then to your database.
With this architecture, Airtable remains the source of truth and your database never gets out-of-sync.
Rate limit
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 Sequin is the only service connected to your Airtable account, you should crank Sequin’s rate limit up to 5 requests per second. When you write, the Sequin will intelligently manage your base’s Airtable API quota between our sync process and your write requests. The Proxy ensures you don’t hit 429s, and gives priority to your write requests over our sync process.
Edit your sync’s rate limit in the Settings tab on the sync’s page.
How updates via the Proxy work
The Postgres Proxy forwards changes to Airtable’s API before applying them to your database.
The order of operations is therefore expressed in the following example, sequentially:
- You make an
insert to create a new entry in the “Orders” table on Airtable.
- The Sequin Proxy forwards the request to Airtable.
- Airtable responds with a
200. The body contains the new order.
- The Sequin Proxy writes the new order to the
orders table in your Sequin database.
- Your Postgres client returns successfully.
