Database

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.

Supported Data Types

Postgres Data TypePaddle Data Type
booleanBoolean
smallintMoney
integerMoney
bigintMoney
realMoney
double precisionMoney
numericMoney
textText
dateDates and time
timestampDates and time
timestamptzDates and time

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

Available Versions

VersionWasm Package URLChecksum
0.1.1https://github.com/supabase/wrappers/releases/download/wasm_paddle_fdw_v0.1.1/paddle_fdw.wasmc5ac70bb2eef33693787b7d4efce9a83cde8d4fa40889d2037403a51263ba657
0.1.0https://github.com/supabase/wrappers/releases/download/wasm_paddle_fdw_v0.1.0/paddle_fdw.wasm7d0b902440ac2ef1af85d09807145247f14d1d8fd4d700227e5a4d84c8145409

Preparation

Before you get started, make sure the wrappers extension is installed on your database:


_10
create extension if not exists wrappers with schema extensions;

and then create the Wasm foreign data wrapper:


_10
create foreign data wrapper wasm_wrapper
_10
handler wasm_fdw_handler
_10
validator wasm_fdw_validator;

Secure 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`
_10
insert into vault.secrets (name, secret)
_10
values (
_10
'paddle',
_10
'bb4e69088ea07a98a90565ac610c63654423f8f1e2d48b39b5'
_10
)
_10
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:


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


_10
create schema if not exists paddle;

Creating Foreign Tables

The Paddle Wrapper supports data reads and writes from Paddle.

IntegrationSelectInsertUpdateDeleteTruncate
Paddle✅✅✅❌❌

For example:


_15
create 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
);

Foreign table options

The full list of foreign table options are below:

  • object - Object name in Paddle, required.

    Supported objects are listed below:

    Object name
    products
    prices
    discounts
    customers
    transactions
    reports
    notification-settings
    notifications

  • rowid_column - Primary key column name, optional for data scan, required for data modify

Query Pushdown Support

This FDW supports where clause pushdown with id as the filter. For example,


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

Examples

Below are some examples on how to use Paddle foreign tables.

Basic example

This example will create a "foreign table" inside your Postgres database and query its data. First, we can create a schema to hold all the Paddle foreign tables.


_10
create schema if not exists paddle;

Then create the foreign table and query it, for example:


_17
create 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
_17
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


_36
create 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
_36
select id, attrs->>'type' as type
_36
from paddle.products where id = 'pro_01hymwj50rfavry9kqsf2vk6sy';
_36
_36
create 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
_36
select id, attrs#>'{items,status}' as item_status
_36
from 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
_10
insert into paddle.products (name, tax_category)
_10
values ('my prod', 'standard');
_10
_10
-- update existing data
_10
update paddle.products
_10
set name = 'my prod'
_10
where id = 'pro_01hzrr95qz1g0cys1f9sgj4t3h';