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.
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_10insert into warehouse.analytics_10select * from public.analytics_10where ts > (now() - interval '1 DAY');
This approach provides several benefits:
- Simplicity: the Wrappers API is just SQL, so data engineers don't need to learn new tools and languages.
- Save on time: avoid setting up additional data pipelines.
- 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:_10select_10 auth.users.id as user_id,_10 warehouse.orders.id as order_id_10from_10 warehouse.orders_10join _10 auth.users on auth.users.id = warehouse.orders.user_id_10where _10 auth.users.id = '<some_user_id>';
This approach has several benefits:
- On-demand: analytical data is immediately available within your application with no additional infrastructure.
- Always in sync: since the data is queried directly from the remote server, it's always up-to-date.
- Integrated: large datasets are available within your application, and can be joined with your operational/transactional data.
- 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:_11select 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);
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.
WebAssembly Wrappers
WebAssembly (Wasm) is a binary instruction format that enables high-performance execution of code on the web. Wrappers now includes a Wasm runtime, which provides a sandboxed execution environment, to run Wasm foreign data wrappers. Combined Wrappers with Wasm, developing and distributing new FDW becomes much easier and you can even build your own Wasm FDW and use it on Supabase platform.
To learn more about Wasm FDW, visit Wrappers official documentation.
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,
-
Create a Stripe Products foreign table:
_15create foreign table stripe.stripe_products (_15id text,_15name text,_15active bool,_15default_price text,_15description text,_15created timestamp,_15updated timestamp,_15attrs jsonb_15)_15server stripe_fdw_server_15options (_15object 'products',_15rowid_column 'id'_15); -
Create a security definer function that queries the foreign table and filters on the name prefix parameter:
_26create function public.get_stripe_products(name_prefix text)_26returns table (_26id text,_26name text,_26active boolean,_26default_price text,_26description text_26)_26language plpgsql_26security definer set search_path = ''_26as $$_26begin_26return query_26select_26t.id,_26t.name,_26t.active,_26t.default_price,_26t.description_26from_26stripe.stripe_products t_26where_26t.name like name_prefix || '%'_26;_26end;_26$$; -
Restrict the function execution to a specific role only, for example, the authenticated users:
By default, the function created can be executed by any roles like
anon
, that means the foreign table is public accessible. Always limit the function execution permission to appropriate roles._10-- revoke public execute permission_10revoke execute on function public.get_stripe_products from public;_10revoke execute on function public.get_stripe_products from anon;_10_10-- grant execute permission to a specific role only_10grant 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:
_10const { data, error } = await supabase_10 .rpc('get_stripe_products', { name_prefix: 'Test' })_10 .select('*')_10if (error) console.error(error)_10else console.log(data)
Resources
- Official
supabase/wrappers
documentation