Sync Inc Reference

Connecting to your database

We sync your Airtable base to a Postgres database. Because it's a full-fledged Postgres instance, you can connect to your data from any SQL client.

Connection options

After you've added your Airtable base to Sync Inc, you'll be presented with the credentials for your database:

Database credentials modal

You can view your database credentials for any connected Airtable base at any time by clicking the Connect button:

Connect button

We provide you with three ways to connect to your database:

  • Connection URL: For many applications and programming libraries, the connection URL is all you need.
  • psql: We also include a click-to-copy command to run psql, a popular command-line client for Postgres.
  • Raw Credentials: Finally, some applications and programming libraries have you enter connection parameters individually. We include those broken out at the bottom for you.
    • Host: The URL for reaching your database.
    • Database: The name of your database.
    • User: The username for your database.
    • Port: For Sync Inc Postgres databases, this will always be port 5432.
    • Password: The password for your database.

Connection resources

Here are some great tools and libraries you can use to connect to your Sync Inc database:

Tools and applications

Development libraries & ORMs

(For a comprehensive list for languages not listed below, check out the PostgreSQL wiki)

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
Attachmenttext[]
Autonumbernumeric
Barcodenumeric
Buttontext
Checkboxbool
Collaboratortext[]
Countnumeric
Created timetimestamp
Currencynumeric
Datedate or timestamp
Durationtext
Emailtext
Formulatext
Last modified bytext
Link to another recordtext
Long texttext
Lookup^text[] or text
Multiple selecttext[]
Numbernumeric
Percentnumeric
Phone numbertext
Ratingnumeric
Rollupnumeric
Single line texttext
Single selecttext
URLtext

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:

  • idid_
  • created_timecreated_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.

Setting: Rapid creates and updates

Find this setting underneath the "Advanced" settings for a base:

where to find this settings

This setting prioritizes syncing creates and updates and makes your sync process considerably faster. You can have a 30,000 record base with 10 tables and your sync time will be just over 3 seconds.

There is a trade-off: deletes and updates to Airtable's computed fields (like Lookups and Roll-up fields) will take longer to propagate (about 10min for a 30,000 record base).

We recommend turning this setting on if you're OK with deletes being slow and don't use a lot of computed fields. You'll love the speed-up it gives you.

When this setting is off (default), all types of operations and fields propagate to your database at the same speed.