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.

Available Versions#

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:

1
create extension if not exists wrappers with schema extensions;

Enable the Paddle Wrapper#

Enable the Wasm foreign data wrapper:

1
2
3
create foreign data wrapper wasm_wrapper  handler wasm_fdw_handler  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.

1
2
3
4
5
6
7
-- Save your Paddle API key in Vault and retrieve the `key_id`insert into vault.secrets (name, secret)values (  'paddle',  '<Paddle API key>' -- Paddle API key)returning 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:

1
2
3
4
5
6
7
8
9
10
create server paddle_server  foreign data wrapper wasm_wrapper  options (    fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_paddle_fdw_v0.1.1/paddle_fdw.wasm',    fdw_package_name 'supabase:paddle-fdw',    fdw_package_version '0.1.1',    fdw_package_checksum 'c5ac70bb2eef33693787b7d4efce9a83cde8d4fa40889d2037403a51263ba657',    api_url 'https://sandbox-api.paddle.com', -- Use https://api.paddle.com for live account    api_key_id '<key_ID>' -- The Key ID from above.  );

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:

1
create 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:

• 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#

Usage#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create foreign table paddle.products (  id text,  name text,  tax_category text,  status text,  description text,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server paddle_server  options (    object 'products',    rowid_column 'id'  );

Notes#

• Requires rowid_column option for data modification operations
• Query pushdown supported for id column
• Product type can be extracted using: attrs->>'type'

Customers#

This is an object representing Paddle Customers.

Ref: Paddle API docs

Operations#

Usage#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create foreign table paddle.customers (  id text,  name text,  email text,  status text,  custom_data jsonb,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server paddle_server  options (    object 'customers',    rowid_column 'id'  );

Notes#

• Requires rowid_column option for data modification operations
• Query pushdown supported for id column
• Custom data stored in dedicated custom_data column

Subscriptions#

This is an object representing Paddle Subscriptions.

Ref: Paddle API docs

Operations#

Usage#

1
2
3
4
5
6
7
8
9
10
11
12
create foreign table paddle.subscriptions (  id text,  status text,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server paddle_server  options (    object 'subscriptions',    rowid_column 'id'  );

Notes#

• Requires rowid_column option for data modification operations
• Query pushdown supported for id column
• 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,

1
select * from paddle.customers where id = 'ctm_01hymwgpkx639a6mkvg99563sp';

Supported Data Types#

The Paddle API uses JSON formatted data, please refer to Paddle docs for more details.

Limitations#

This section describes important limitations and considerations when using this FDW:

• Query pushdown is only supported for the id column, resulting in full table scans for other filters
• Large result sets may experience slower performance due to full data transfer requirement
• Materialized views using these foreign tables may fail during logical backups

Examples#

Basic Example#

This example will create a "foreign table" inside your Postgres database and query its data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create foreign table paddle.customers (  id text,  name text,  email text,  status text,  custom_data jsonb,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server paddle_server  options (    object 'customers',    rowid_column 'id'  );select * 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#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
create foreign table paddle.products (  id text,  name text,  tax_category text,  status text,  description text,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server paddle_server  options (    object 'products',    rowid_column 'id'  );-- extract product type for a productselect id, attrs->>'type' as typefrom paddle.products where id = 'pro_01hymwj50rfavry9kqsf2vk6sy';create foreign table paddle.subscriptions (  id text,  status text,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server paddle_server  options (    object 'subscriptions',    rowid_column 'id'  );-- extract subscription items for a subscriptionselect id, attrs#>'{items,status}' as item_statusfrom 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:

1
2
3
4
5
6
7
8
-- insert new datainsert into paddle.products(name, tax_category)values ('my prod', 'standard');-- update existing dataupdate paddle.productsset name = 'my prod'where id = 'pro_01hzrr95qz1g0cys1f9sgj4t3h';