Supabase Wrappers v0.2 is out and now available on the Supabase platform. Wrappers is a Postgres extension that provides integrations with external sources so you can interact with third-party data using SQL.
_12-- Connect Postgres to Stripe_12create foreign table products (_12 id text,_12 name text,_12 description text,_12 default_price text_12)_12server my_stripe_server_12options ( object 'products' );_12_12-- Fetch all your Stripe products in Postgres_12select * from products limit 10;
Key Features and Improvements in Wrappers v0.2:
To start using Wrappers on the Supabase platform, check out the Wrappers docs.
New Wrappers and Improvements
- Airtable (Read-only): query your Airtable bases with support for jsonb
- AWS S3 (Read-only): with support for CSV, JSON, and Parquet
- Clerk (from the team at Tembo). Not yet available on our platform since it is a community wrapper, see below for details.
- And a WIP Wrapper for Auth0
We cannot support community Wrappers inside the Supabase Dashboard until the Wrappers API is stabilized. You can vote your favorite Wrapper if you'd like it to be added to Supabase in the future. If you have developed a Wrapper that you want inside the Supabase Dashboard, please contribute it as a PR in the wrappers repo. Once we release Wrappers 1.0, we will support community Wrappers within the Supabase Dashboard.
More improvements and updates can be found on Wrappers release page, including support for Query Pushdown, Remote Subqueries, and Usage Statistics which we’ll explore below.
Support for Query Pushdown
The Wrappers v0.2 framework now supports Query Pushdown.
What is Query Pushdown?
Query pushdown is a technique that enhances query performance by executing parts of the query directly on the data source. It reduces data transfer between the database and the application, enabling faster execution and improved performance.
How to Use Query Pushdown in Wrappers
In Wrappers, the pushdown logic is integrated into each extension. You don’t need to modify your queries to benefit from this feature. For example, the Stripe FDW automatically applies query pushdown for id
within the customer
object:
_10select *_10from stripe.customers_10where id = 'cus_N5WMk7pvQPkY3B';
This approach contrasts with fetching and filtering all customers locally, which is less efficient. Query pushdown translates this into a single API call, significantly speeding up the process:
_10https://api.stripe.com/v1/customers/cus_N5WMk7pvQPkY3B
We can use push down criteria and other query parameters too. For example, ClickHouse FDW supports order by
and limit
pushdown:
_10select *_10from clickhouse.people_10order by name_10limit 20;
This query executes order by name limit 20
on ClickHouse before transferring the result to Postgres.
For details on where pushdown is supported, consult each FDW's documentation in the Wrappers Documentation.
Remote Subqueries
Remote subqueries enable the use of prepared data on a remote server, which is beneficial for complex queries or sensitive data protection.
Static Subqueries
In its most basic form, you can map a query on the remote server into a foreign table in Postgres. For instance:
_10create foreign table clickhouse.people (_10 id bigint,_10 name text,_10 age bigint_10)_10server clickhouse_server_10options (_10 table '(select * from people where age < 25)'_10);
In this example, the foreign table clickhouse.people
data is read from the result of the subquery select * from people where age < 25
which runs on ClickHouse server.
Dynamic Subqueries
What if the query is not fixed and needs to be dynamic? For example, ClickHouse provides Parameterized Views which can accept parameters for a view. Wrappers v0.2 supports this by defining a column for each parameter. Let's take a look at an example:
_11create foreign table clickhouse.my_table (_11 id bigint,_11 col1 text,_11 col2 bigint,_11 _param1 text,_11 _param2 bigint_11)_11server clickhouse_server_11options (_11 table '(select * from my_view(column1=${_param1}, column2=${_param2}))'_11);
You can then pass values to these parameters in your query:
_10select id, col1, col2_10from clickhouse.my_table_10where _param1 = 'abc' and _param2 = 42;
Currently, this feature is supported by ClickHouse FDW and BigQuery FDW, with plans to expand support in the future.
FDW Usage Statistics
Quantitative metrics play a pivotal role when working with Postgres FDWs because of their impact on performance optimisation, monitoring, and query planning across distributed databases. We introduced a FDW usage statistics table wrappers_fdw_stats
in Wrappers v0.2, storing:
create_times
- number of times the FDW instance has been createdrows_in
- number of rows transferred from sourcerows_out
- number of rows transferred to sourcebytes_in
- number of bytes transferred from sourcebytes_out
- number of bytes transferred to sourcemetadata
- additional usage statistics specific to a FDW
We can use these to identify bottlenecks, latency issues, and inefficiencies in data retrieval. Access this table on the Supabase platform using the following:
_10select *_10from extensions.wrappers_fdw_stats;
Thanks to Our Community Contributors
We couldn't build Wrappers v0.2 without our community and we'd like to thank all the following people for their contributions:
Dom Jocubeit, 0xflotus, Glitch, Tobias Florek, Jubilee, Germán Larraín, Kavanaugh Latiolais, tedverse.
A separate shout-out belongs to the pgrx team, which allows us to write Wrappers with Rust.
Want to join the Supabase Wrappers community contributors? Check out our contribution docs. We'd love to add you to the list next time.