Airtable Foreign Data Wrapper

Installation

Requirements

PostgreSQL 9.1+ with Multicorn extension installed.

Loading extension and defining FDW server

Ensure multicorn is loaded and define Foreign Data Wrapper for airtable

create extension if not exists multicorn;
create server if not exists multicorn_airtable_srv foreign data wrapper multicorn options (
    wrapper 'airtable_fdw.AirtableFDW'
);

Usage

Define table as

create foreign table schema.table_name (
    "_id" varchar options (rowid 'true'),                       -- column used as rowid, may be any name, 
                                                                -- should appear only onece
    "Some text column" varchar,
    "Some numeric column" numeric,
    "Some date column" date,
    "Some complex column" json,                                 -- can be used for complex fields but see example below 
    "Some json nullable column" json options (nulljson 'true'), -- keep nulls as json ('null'::json instead of null::json)
    "Some computed column" varchar options (computed 'true')    -- column that won't be modified with update
                                                                -- may appear multiple times
) server multicorn_airtable_srv options (
    api_key '...',      -- api access key
    base_key '...',     -- database identifier
    table_name '...',   -- name of table to read from
    view_name '...',    -- optional view name, if not present raw table will be read
    rowid_column '...'  -- optional rowid column name will be used if no column has `rowid` option set 
);

If complex column - like Collaborator - appears in table it is read from AirTable API as a json and could be treated as json or as a complex, custom defined type.

create type AirtableCollaborator as
(
    id     varchar,
    email  varchar,
    "name" varchar
);
create foreign table schema.table_name (
    "_id" varchar options (rowid 'true'),
    "editor" AirtableCollaborator options (complextype_fields 'id,email,name', complextype_send 'email')
) server multicorn_airtable_srv options (
    api_key '...',
    base_key '...',
    table_name '...'
);

where:

• complextype_fields 'id,email,name' indicates how record string should be constructed from json - so {"id": "someid", "email": "[email protected]", "name":"My Name"} will be converted to (someid,[email protected],My Name) and will be correctly casted to AirtableCollaborator type.
• complextype_send 'email' means that when this field is modified only email field will be sent to API

Usage Tips

• Use AND in WHERE clause whenever possible, ORs are not handled well (at all?) by multicorn so unconditional queries are sent to Airtable (watch the quota!).
• If OR is required try to replace it with IN (...)

Features

• Configurable to read from given base / table / view
• SQL WHERE clause transformed to formula query (so number of requests to API is optimized)
• Batch INSERT/UPDATE/DELETE
• support for complex types - json is parsed to complex type on read (SELECT), and single, selected field is set on write (INSERT, UPDATE)