Database

Airtable


Airtable is an easy-to-use online platform for creating and sharing relational databases.

The Airtable Wrapper allows you to read data from your Airtable bases/tables within your Postgres database.

Preparation

Before you get started, make sure the wrappers extension is installed on your database:


_10
create extension if not exists wrappers with schema extensions;

and then create the foreign data wrapper:


_10
create foreign data wrapper airtable_wrapper
_10
handler airtable_fdw_handler
_10
validator airtable_fdw_validator;

Secure your credentials (optional)

By default, Postgres stores FDW credentials inide pg_catalog.pg_foreign_server in plain text. Anyone with access to this table will be able to view these credentials. Wrappers is designed to work with Vault, which provides an additional level of security for storing credentials. We recommend using Vault to store your credentials.


_10
-- Save your Airtable API key in Vault and retrieve the `key_id`
_10
insert into vault.secrets (name, secret)
_10
values (
_10
'airtable',
_10
'<Airtable API Key or PAT>' -- Airtable API key or Personal Access Token (PAT)
_10
)
_10
returning key_id;

Connecting to Airtable

We need to provide Postgres with the credentials to connect to Airtable, and any additional options. We can do this using the create server command:


_10
create server airtable_server
_10
foreign data wrapper airtable_wrapper
_10
options (
_10
api_key_id '<key_ID>' -- The Key ID from above.
_10
);

Creating Foreign Tables

The Airtable Wrapper supports data reads from Airtable's Records endpoint (read only).

AirtableSelectInsertUpdateDeleteTruncate
Records

For example:


_10
create foreign table my_foreign_table (
_10
name text
_10
-- other fields
_10
)
_10
server airtable_server
_10
options (
_10
base_id 'appXXXX',
_10
table_id 'tblXXXX'
_10
);

Foreign table options

The full list of foreign table options are below:

  • base_id - Airtable Base ID the table belongs to, required.
  • table_id - Airtable table ID, required.
  • view_id - Airtable view ID, optional.

Query Pushdown Support

This FDW doesn't support query pushdown.

Examples

Some examples on how to use Airtable foreign tables.

Basic example

This will create a "foreign table" inside your Postgres database called airtable_table:


_12
create foreign table airtable_table (
_12
name text,
_12
notes text,
_12
content text,
_12
amount numeric,
_12
updated_at timestamp
_12
)
_12
server airtable_server
_12
options (
_12
base_id 'appTc3yI68KN6ukZc',
_12
table_id 'tbltiLinE56l3YKfn'
_12
);

You can now fetch your Airtable data from within your Postgres database:


_10
select * from airtable_table;

We can also create a foreign table from an Airtable View called airtable_view:


_15
create foreign table airtable_view (
_15
name text,
_15
notes text,
_15
content text,
_15
amount numeric,
_15
updated_at timestamp
_15
)
_15
server airtable_server
_15
options (
_15
base_id 'appTc3yI68KN6ukZc',
_15
table_id 'tbltiLinE56l3YKfn',
_15
view_id 'viwY8si0zcEzw3ntZ'
_15
);
_15
_15
select * from airtable_view;