Database

Foreign Data Wrappers

Connecting to external systems using Postgres Foreign Data Wrappers.


Foreign Data Wrappers (FDW) are a core feature of Postgres that allow you to access and query data stored in external data sources as if they were native Postgres tables.

Postgres includes several built-in foreign data wrappers, such as postgres_fdw for accessing other PostgreSQL databases, and file_fdw for reading data from files. Supabase extends this feature to query other databases or any other external systems. We do this with our open source Wrappers framework. In these guides we'll refer to them as "Wrappers", Foreign Data Wrappers, or FDWs. They are conceptually the same thing.

Concepts

Wrappers introduce some new terminology and different workflows.

FDW

Remote servers

A Remote Server is an external database, API, or any system containing data that you want to query from your Postgres database. Examples include:

  • An external database, like Postgres or Firebase.
  • A remote data warehouse, like ClickHouse, BigQuery, or Snowflake.
  • An API, like Stripe or GitHub.

It's possible to connect to multiple remote servers of the same type. For example, you can connect to two different Firebase projects within the same Supabase database.

Foreign tables

A table in your database which maps to some data inside a Remote Server.

Examples:

  • An analytics table which maps to a table inside your data warehouse.
  • A subscriptions table which maps to your Stripe subscriptions.
  • A collections table which maps to a Firebase collection.

Although a foreign table behaves like any other table, the data is not stored inside your database. The data remains inside the Remote Server.

ETL with Wrappers

ETL stands for Extract, Transform, Load. It's an established process for moving data from one system to another. For example, it's common to move data from a production database to a data warehouse.

There are many popular ETL tools, such as Fivetran and Airbyte.

Wrappers provide an alternative to these tools. You can use SQL to move data from one table to another:


_10
-- Copy data from your production database to your
_10
-- data warehouse for the last 24 hours:
_10
_10
insert into warehouse.analytics
_10
select * from public.analytics
_10
where ts > (now() - interval '1 DAY');

This approach provides several benefits:

  1. Simplicity: the Wrappers API is just SQL, so data engineers don't need to learn new tools and languages.
  2. Save on time: avoid setting up additional data pipelines.
  3. Save on Data Engineering costs: less infrastructure to be managed.

One disadvantage is that Wrappers are not as feature-rich as ETL tools. They also couple the ETL process to your database.

On-demand ETL with Wrappers

Supabase extends the ETL concept with real-time data access. Instead of moving gigabytes of data from one system to another before you can query it, you can instead query the data directly from the remote server. This additional option, "Query", extends the ETL process and is called QETL (pronounced "kettle"): Query, Extract, Transform, Load.


_10
-- Get all purchases for a user from your data warehouse:
_10
select
_10
auth.users.id as user_id,
_10
warehouse.orders.id as order_id
_10
from
_10
warehouse.orders
_10
join
_10
auth.users on auth.users.id = warehouse.orders.user_id
_10
where
_10
auth.users.id = '<some_user_id>';

This approach has several benefits:

  1. On-demand: analytical data is immediately available within your application with no additional infrastructure.
  2. Always in sync: since the data is queried directly from the remote server, it's always up-to-date.
  3. Integrated: large datasets are available within your application, and can be joined with your operational/transactional data.
  4. Save on bandwidth: only extract/load what you need.

Batch ETL with Wrappers

A common use case for Wrappers is to extract data from a production database and load it into a data warehouse. This can be done within your database using pg_cron. For example, you can schedule a job to run every night to extract data from your production database and load it into your data warehouse.


_11
-- Every day at 3am, copy data from your
_11
-- production database to your data warehouse:
_11
select cron.schedule(
_11
'nightly-etl',
_11
'0 3 * * *',
_11
$$
_11
insert into warehouse.analytics
_11
select * from public.analytics
_11
where ts > (now() - interval '1 DAY');
_11
$$
_11
);

FDW

This process can be taxing on your database if you are moving large amounts of data. Often, it's better to use an external tool for batch ETL, such as Fivetran or Airbyte.

Security

Foreign Data Wrappers do not provide Row Level Security, thus it is not advised to expose them via your API. Wrappers should always be stored in a private schema. For example, if you are connecting to your Stripe account, you should create a stripe schema to store all of your foreign tables inside. This schema should not be added to the “Additional Schemas” setting in the API section.

If you want to expose any of the foreign table columns to your public API, you can create a Database Function with security definer in the public schema, and then you can interact with your foreign table through API. For better access control, the function should have appropriate filters on the foreign table to apply security rules based on your business needs.

As an example, go to SQL Editor and then follow below steps,

  1. Create a Stripe Products foreign table:


    _15
    create foreign table stripe.stripe_products (
    _15
    id text,
    _15
    name text,
    _15
    active bool,
    _15
    default_price text,
    _15
    description text,
    _15
    created timestamp,
    _15
    updated timestamp,
    _15
    attrs jsonb
    _15
    )
    _15
    server stripe_fdw_server
    _15
    options (
    _15
    object 'products',
    _15
    rowid_column 'id'
    _15
    );

  2. Create a security definer function that queries the foreign table and filters on the name prefix parameter:


    _26
    create function public.get_stripe_products(name_prefix text)
    _26
    returns table (
    _26
    id text,
    _26
    name text,
    _26
    active boolean,
    _26
    default_price text,
    _26
    description text
    _26
    )
    _26
    language plpgsql
    _26
    security definer set search_path = ''
    _26
    as $$
    _26
    begin
    _26
    return query
    _26
    select
    _26
    t.id,
    _26
    t.name,
    _26
    t.active,
    _26
    t.default_price,
    _26
    t.description
    _26
    from
    _26
    stripe.stripe_products t
    _26
    where
    _26
    t.name like name_prefix || '%'
    _26
    ;
    _26
    end;
    _26
    $$;

  3. Restrict the function execution to a specific role only, for example, the authenticated users:


    _10
    -- revoke public execute permission
    _10
    revoke execute on function public.get_stripe_products from public;
    _10
    revoke execute on function public.get_stripe_products from anon;
    _10
    _10
    -- grant execute permission to a specific role only
    _10
    grant execute on function public.get_stripe_products to authenticated;

Once the preceding steps are finished, the function can be invoked from Supabase client to query the foreign table:


_10
const { data, error } = await supabase
_10
.rpc('get_stripe_products', { name_prefix: 'Test' })
_10
.select('*')
_10
if (error) console.error(error)
_10
else console.log(data)

Resources