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.
Restoring a logical backup of a database with a materialized view using a foreign table can fail. For this reason, either do not use foreign tables in materialized views or use them in databases with physical backups enabled.
Preparation
Before you can query Airtable, you need to enable the Wrappers extension and store your credentials in Postgres.
Enable Wrappers
Make sure the wrappers extension is installed on your database:
_10create extension if not exists wrappers with schema extensions;
Enable the Airtable Wrapper
Enable the airtable_wrapper FDW:
_10create foreign data wrapper airtable_wrapper_10 handler airtable_fdw_handler_10 validator airtable_fdw_validator;
Store your credentials (optional)
By default, Postgres stores FDW credentials inside 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`_10insert into vault.secrets (name, secret)_10values (_10 'airtable',_10 '<Airtable API Key or PAT>' -- Airtable API key or Personal Access Token (PAT)_10)_10returning 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:
_10create server airtable_server_10 foreign data wrapper airtable_wrapper_10 options (_10 api_key_id '<key_ID>' -- The Key ID from above._10 );
Create a schema
We recommend creating a schema to hold all the foreign tables:
_10create schema if not exists airtable;
Entities
The Airtable Wrapper supports data reads from the Airtable API.
Records
The Airtable Wrapper supports data reads from Airtable's Records endpoint (read only).
Operations
| Object | Select | Insert | Update | Delete | Truncate |
|---|---|---|---|---|---|
| Records | ✅ | ❌ | ❌ | ❌ | ❌ |
Usage
_10create foreign table airtable.my_foreign_table (_10 name text_10 -- other fields_10)_10server airtable_server_10options (_10 base_id 'appXXXX',_10 table_id 'tblXXXX'_10);
Notes
- The table requires both
base_idandtable_idoptions - Optional
view_idcan be specified to query a specific view
Query Pushdown Support
This FDW doesn't support query pushdown.
Examples
Query an Airtable table
This will create a "foreign table" inside your Postgres database called airtable_table:
_12create foreign table airtable.airtable_table (_12 name text,_12 notes text,_12 content text,_12 amount numeric,_12 updated_at timestamp_12)_12server airtable_server_12options (_12 base_id 'appTc3yI68KN6ukZc',_12 table_id 'tbltiLinE56l3YKfn'_12);
You can now fetch your Airtable data from within your Postgres database:
_10select * from airtable.airtable_table;
Query an Airtable view
We can also create a foreign table from an Airtable View called airtable_view:
_13create foreign table airtable.airtable_view (_13 name text,_13 notes text,_13 content text,_13 amount numeric,_13 updated_at timestamp_13)_13server airtable_server_13options (_13 base_id 'appTc3yI68KN6ukZc',_13 table_id 'tbltiLinE56l3YKfn',_13 view_id 'viwY8si0zcEzw3ntZ'_13);
You can now fetch your Airtable data from within your Postgres database:
_10select * from airtable.airtable_view;