Foreign Data Wrappers (FDWs) allow Postgres to interact with externally hosted data. To operate a FDW, the user creates a foreign table. When queried, the foreign table reaches out to the 3rd party service, collects the requested data, and returns it to the query in the shape defined by the foreign table. This allows seamless querying and data manipulation across different tools as if they were local tables from within Postgres.
Wrappers is a Rust framework for creating Postgres Foreign Data Wrappers. Today we're releasing support for Wasm (WebAssembly) wrappers.
With this feature, anyone can create a Wasm wrapper to an external service and run it directly from e.g. GitHub:
_14-- An Example Google Sheets Wasm Wrapper:_14_14create server google_sheets_14 foreign data wrapper wasm_wrapper_14 options (_14 -- Install from GitHub_14 fdw_package_url 'https://github.com/<ORG>/<REPO>/releases/download/v0.2.0/google_sheets_fdw.wasm',_14 fdw_package_name 'my-company:google-sheets-fdw',_14 fdw_package_version '0.2.0',_14 fdw_package_checksum '338674c4c983aa6dbc2b6e63659076fe86d847ca0da6d57a61372b44e0fe4ac9',_14_14 -- Provide custom options_14 base_url 'https://docs.google.com/spreadsheets/d'_14 );
This feature is available today in public alpha for all new projects.
What are Foreign Data Wrappers?
Foreign Data Wrappers (FDW) are a powerful feature of Postgres that allows you to connect to and query external data sources as if they were regular tables.
Wrappers is an open source project that simplifies the creation of Postgres Foreign Data Wrappers using Rust.
Why WebAssembly?
WebAssembly (Wasm) is a binary instruction format that enables secure and high-performance execution of code on the web. It is originally designed for web browsers, but now can also be used in server-side environments like Postgres.
Here's how the Wasm FDW benefits us:
- Improved Security: Wasm's sandboxed execution runtime with minimum interfaces enhances the security of FDW.
- Simplified Development: Developers can use Rust to create complex FDWs without diving deep into Postgres internal API.
- Simplified Distribution: Easily distribute your Wasm FDW through any URL-accessible storage (such as GitHub or S3).
- Enhanced Performance: Wasm's near-native speed ensures FDWs operate with minimal overhead.
- Increased Modularity: Each Wasm FDW is an isolated package which is dynamically loaded and executed by Wrappers individually.
Architecture
To better understand how the Wasm FDW works, let's take a look at the architecture:
The above diagram illustrates the key components and how they interact:
- Supabase Wrappers Extension (Host): This is the core component that runs within Postgres. It includes below modules:
- Wasm Runtime: Provides runtime environment to executes the Wasm FDW package.
- HTTP Interface: Manages communication with external data sources through HTTP.
- Utilities: Helper tools and functions to support FDW operations.
- Other modules providing specific functionalities, such like JWT, stats and etc.
- Wasm FDWs (Guests): Isolated, dynamically-loaded Wasm packages that perform data fetching and processing. They execute in a sandboxed environment to ensure security and performance. For example:
- Web Storage: Represents external storage services like GitHub or S3, where Wasm packages can be publicly stored and downloaded from.
- External Data Source: Various external systems which data is fetched from or pushed to, such as Snowflake and Paddle. Data is accessed using RESTful APIs.
Data fetching
Wasm FDWs are loaded dynamically when the first request is made. The interaction flow is:
- Wasm download: The Wasm FDWs are dynamically downloaded from web storage services, like GitHub or S3, and cached locally. This happens the first time the
SELECTstatement is initiated. - Initialization and Execution: Once downloaded, the Wasm FDWs are initialized and executed within the embedded Wasm runtime environment. This provides a secure, sandboxed execution environment that isolates the packages from the main Postgres system.
- Data Fetching via RESTful API: The Wasm FDWs interact with their respective external data sources via RESTful APIs.
- Query Handling and Data Integration: When a query is executed against a foreign table in Postgres, the Supabase Wrappers extension invokes the appropriate Wasm FDW, fetches data from the external source, processes it, and returns it to the Supabase Wrappers, which integrates it back into the Postgres query execution pipeline.
The Wasm FDW currently only supports data sources which have HTTP(s) based JSON API, other sources such like TCP/IP based DBMS or local files are not supported yet.
Developing your own Wasm FDW
A major benefit of Wasm FDW is that you can build your own FDW and use it on Supabase. To get started, clone the Postgres Wasm FDW [Template]. Building your own Wasm FDWs opens up a world of possibilities for integrating diverse data sources into Postgres.
Visit Wrappers docs and guides to learn more about how to develop a Wasm FDW.
As the Wasm FDW can access external data sources, you should never install Wasm Wrappers from untrusted source. Always use official Supabase FDWs, or use sources which you have full visibility and control.
Try it now on Supabase
The Wasm FDW feature is available today on the Supabase platform. We have 2 new built-in Wasm FDWs: Snowflake and Paddle.
To get started, follow below steps:
- Create a new Supabase project: database.new
- Navigate to the Database -> Wrappers section and enable Wrappers.
- Add
SnowflakeorPaddlewrapper, follow the instructions and create foreign tables.
We can also use SQL. Let's try, using the Paddle FDW as an example.
Enable Wasm Wrappers
Inside the SQL editor, enable the Wasm Wrapper feature:
_10-- install Wrappers extension_10create extension if not exists wrappers with schema extensions;_10_10-- create Wasm foreign data wrapper_10create foreign data wrapper wasm_wrapper_10 handler wasm_fdw_handler_10 validator wasm_fdw_validator;
Get your Paddle credentials
Sign up for a sandbox account and get API key with Paddle.
Save your Paddle credentials
Create a Paddle server in Postgres using the Wasm FDW created above:
_15-- create Paddle foreign server_15create server paddle_server_15 foreign data wrapper wasm_wrapper_15 options (_15 -- check all available versions at_15 -- https://fdw.dev/catalog/paddle/#available-versions_15 fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_paddle_fdw_v0.1.1/paddle_fdw.wasm',_15 fdw_package_name 'supabase:paddle-fdw',_15 fdw_package_version '0.1.1',_15 fdw_package_checksum 'c5ac70bb2eef33693787b7d4efce9a83cde8d4fa40889d2037403a51263ba657',_15_15 -- save your Paddle credentials_15 api_url 'https://sandbox-api.paddle.com',_15 api_key '<your Paddle sandbox API key>'_15 );
Set up your Foreign Tables
Create a table for Paddle data:
_19-- create dedicated schema for Paddle foreign tables_19create schema if not exists paddle;_19_19-- create foreign table_19create foreign table paddle.customers (_19 id text,_19 name text,_19 email text,_19 status text,_19 custom_data jsonb,_19 created_at timestamp,_19 updated_at timestamp,_19 attrs jsonb_19)_19server paddle_server_19options (_19 object 'customers',_19 rowid_column 'id'_19);
Query Paddle from Postgres
Now let's query the foreign table and check the result:
_10select id, name, email, status_10from paddle.customers;
That's it. Head over to the Supabase Wrappers documentation to find more detailed guides on setting up and using Wasm FDWs.
Thanks to our community contributors
None of this innovation would have been possible without the relentless efforts and contributions of our vibrant community. We'd like to thank all the following developers for their contributions:
Aayushya Vajpayee, Romain Graux
Want to join the Supabase Wrappers community contributors? Check out our contribution docs.