Sync Inc

Airtable Reference

Querying Airtable with SQL

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

Airtable base β†’ database

Each Airtable base maps one-to-one with a Sync Inc database.

Your Sync Inc 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, let us know.

Your database is a read-only, follower database.

Interested in a database you can write to? Let us know.

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 Sync Inc 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 Sync Inc database.

If you used emojis πŸ™ˆ or punctuation in your table names, we'll remove them from the table names in your Sync Inc 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 Sync Inc Postgres database.

Airtable Field TypeSync Inc 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 Sync Inc 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 Sync Inc 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 Sync Inc 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 Sync Inc 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

Sync Inc 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.

Writes

One-way data flow

Sync Inc 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 Sync Inc. Those mutations are applied simultaneously to both your Airtable base and your Sync Inc database so that they show up in subsequent reads by your code or SQL client:

Diagram of using Sync Inc 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 Sync Inc Proxy, you craft HTTP requests to the Airtable API like you normally would. Except, you prepend `proxy.syncinc.so/` 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.syncinc.so/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.syncinc.so/api.airtable.com` instead of `https://api.airtable.com`. This sends the request through the Sync Inc Proxy so that mutations are applied immediately to your Postgres database as well as your Airtable base.

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

var Airtable = require("airtable");
var base = new Airtable({
apiKey: "keyβ–’β–’β–’β–’β–’β–’β–’β–’β–’",
endpointUrl: "https://proxy.syncinc.so/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 Sync Inc 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, Sync Inc 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 Sync Inc 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 Sync Inc 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 Sync Inc Proxy.
  2. The Sync Inc Proxy forwards the request to Airtable.
  3. Airtable responds with a `200`. The body contains the new order.
  4. The Sync Inc Proxy writes the new order to the `orders` table in your Sync Inc database.
  5. The Sync Inc Proxy forwards Airtable's response to you.

Given this order of operations, after your API request completes you can immediately read your Sync Inc 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 Sync Inc console.

Cheat sheet
Airtable App

Was this helpful?