Paddle
Paddle is a merchant of record that acts to provide a payment infrastructure to thousands of software companies around the world.
The Paddle Wrapper is a WebAssembly(Wasm) foreign data wrapper which allows you to read and write data from Paddle within your Postgres database.
Restoring a logical backup of a database with a materialized view using a foreign table can fail. For this reason, either do not use foreign tables in materialized views or use them in databases with physical backups enabled.
Supported Data Types
| Postgres Data Type | Paddle Data Type |
|---|---|
| boolean | Boolean |
| smallint | Money |
| integer | Money |
| bigint | Money |
| real | Money |
| double precision | Money |
| numeric | Money |
| text | Text |
| date | Dates and time |
| timestamp | Dates and time |
| timestamptz | Dates and time |
The Paddle API uses JSON formatted data, please refer to Paddle docs for more details.
Available Versions
| Version | Wasm Package URL | Checksum |
|---|---|---|
| 0.1.1 | https://github.com/supabase/wrappers/releases/download/wasm_paddle_fdw_v0.1.1/paddle_fdw.wasm | c5ac70bb2eef33693787b7d4efce9a83cde8d4fa40889d2037403a51263ba657 |
| 0.1.0 | https://github.com/supabase/wrappers/releases/download/wasm_paddle_fdw_v0.1.0/paddle_fdw.wasm | 7d0b902440ac2ef1af85d09807145247f14d1d8fd4d700227e5a4d84c8145409 |
Preparation
Before you can query Paddle, you need to enable the Wrappers extension and store your credentials in Postgres.
Enable Wrappers
Make sure the wrappers extension is installed on your database:
_10create extension if not exists wrappers with schema extensions;
Enable the Paddle Wrapper
Enable the Wasm foreign data wrapper:
_10create foreign data wrapper wasm_wrapper_10 handler wasm_fdw_handler_10 validator wasm_fdw_validator;
Store your credentials (optional)
By default, Postgres stores FDW credentials inside pg_catalog.pg_foreign_server in plain text. Anyone with access to this table will be able to view these credentials. Wrappers is designed to work with Vault, which provides an additional level of security for storing credentials. We recommend using Vault to store your credentials.
_10-- Save your Paddle API key in Vault and retrieve the `key_id`_10insert into vault.secrets (name, secret)_10values (_10 'paddle',_10 '<Paddle API key>' -- Paddle API key_10)_10returning key_id;
Connecting to Paddle
We need to provide Postgres with the credentials to access Paddle, and any additional options. We can do this using the create server command:
_10create server paddle_server_10 foreign data wrapper wasm_wrapper_10 options (_10 fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_paddle_fdw_v0.1.1/paddle_fdw.wasm',_10 fdw_package_name 'supabase:paddle-fdw',_10 fdw_package_version '0.1.1',_10 fdw_package_checksum 'c5ac70bb2eef33693787b7d4efce9a83cde8d4fa40889d2037403a51263ba657',_10 api_url 'https://sandbox-api.paddle.com', -- Use https://api.paddle.com for live account_10 api_key_id '<key_ID>' -- The Key ID from above._10 );
Note the fdw_package_* options are required, which specify the Wasm package metadata. You can get the available package version list from above.
Create a schema
We recommend creating a schema to hold all the foreign tables:
_10create schema if not exists paddle;
Options
The full list of foreign table options are below:
object- Object name in Paddle, required.
Supported objects are listed below:
| Object |
|---|
| products |
| prices |
| discounts |
| customers |
| transactions |
| reports |
| notification-settings |
| notifications |
rowid_column- Primary key column name, optional for data scan, required for data modify
Entities
Products
This is an object representing Paddle Products.
Ref: Paddle API docs
Operations
| Object | Select | Insert | Update | Delete | Truncate |
|---|---|---|---|---|---|
| Products | ✅ | ✅ | ✅ | ❌ | ❌ |
Usage
_15create foreign table paddle.products (_15 id text,_15 name text,_15 tax_category text,_15 status text,_15 description text,_15 created_at timestamp,_15 updated_at timestamp,_15 attrs jsonb_15)_15 server paddle_server_15 options (_15 object 'products',_15 rowid_column 'id'_15 );
Notes
- Requires
rowid_columnoption for data modification operations - Query pushdown supported for
idcolumn - Product type can be extracted using:
attrs->>'type'
Customers
This is an object representing Paddle Customers.
Ref: Paddle API docs
Operations
| Object | Select | Insert | Update | Delete | Truncate |
|---|---|---|---|---|---|
| Customers | ✅ | ✅ | ✅ | ❌ | ❌ |
Usage
_15create foreign table paddle.customers (_15 id text,_15 name text,_15 email text,_15 status text,_15 custom_data jsonb,_15 created_at timestamp,_15 updated_at timestamp,_15 attrs jsonb_15)_15 server paddle_server_15 options (_15 object 'customers',_15 rowid_column 'id'_15 );
Notes
- Requires
rowid_columnoption for data modification operations - Query pushdown supported for
idcolumn - Custom data stored in dedicated
custom_datacolumn
Subscriptions
This is an object representing Paddle Subscriptions.
Ref: Paddle API docs
Operations
| Object | Select | Insert | Update | Delete | Truncate |
|---|---|---|---|---|---|
| Subscriptions | ✅ | ✅ | ✅ | ❌ | ❌ |
Usage
_12create foreign table paddle.subscriptions (_12 id text,_12 status text,_12 created_at timestamp,_12 updated_at timestamp,_12 attrs jsonb_12)_12 server paddle_server_12 options (_12 object 'subscriptions',_12 rowid_column 'id'_12 );
Notes
- Requires
rowid_columnoption for data modification operations - Query pushdown supported for
idcolumn - Subscription items status can be extracted using:
attrs#>'{items,status}'
Query Pushdown Support
This FDW supports where clause pushdown with id as the filter. For example,
_10select * from paddle.customers where id = 'ctm_01hymwgpkx639a6mkvg99563sp';
Examples
Basic Example
This example will create a "foreign table" inside your Postgres database and query its data.
_17create foreign table paddle.customers (_17 id text,_17 name text,_17 email text,_17 status text,_17 custom_data jsonb,_17 created_at timestamp,_17 updated_at timestamp,_17 attrs jsonb_17)_17 server paddle_server_17 options (_17 object 'customers',_17 rowid_column 'id'_17 );_17_17select * from paddle.customers;
attrs is a special column which stores all the object attributes in JSON format, you can extract any attributes needed or its associated sub objects from it. See more examples below.
Query JSON Attributes
_36create foreign table paddle.products (_36 id text,_36 name text,_36 tax_category text,_36 status text,_36 description text,_36 created_at timestamp,_36 updated_at timestamp,_36 attrs jsonb_36)_36 server paddle_server_36 options (_36 object 'products',_36 rowid_column 'id'_36 );_36_36-- extract product type for a product_36select id, attrs->>'type' as type_36from paddle.products where id = 'pro_01hymwj50rfavry9kqsf2vk6sy';_36_36create foreign table paddle.subscriptions (_36 id text,_36 status text,_36 created_at timestamp,_36 updated_at timestamp,_36 attrs jsonb_36)_36 server paddle_server_36 options (_36 object 'subscriptions',_36 rowid_column 'id'_36 );_36_36-- extract subscription items for a subscription_36select id, attrs#>'{items,status}' as item_status_36from paddle.subscriptions where id = 'sub_01hv959anj4zrw503h2acawb3p';
Data Modify Example
This example will modify data in a "foreign table" inside your Postgres database, note that rowid_column option is mandatory for data modify:
_10-- insert new data_10insert into paddle.products(name, tax_category)_10values ('my prod', 'standard');_10_10-- update existing data_10update paddle.products_10set name = 'my prod'_10where id = 'pro_01hzrr95qz1g0cys1f9sgj4t3h';