Database

Stripe


Stripe is an API driven online payment processing utility. supabase/wrappers exposes below endpoints.

Preparation

Before you can query Stripe, 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:


_10
create extension if not exists wrappers with schema extensions;

Enable the Stripe Wrapper

Enable the stripe_wrapper FDW:


_10
create foreign data wrapper stripe_wrapper
_10
handler stripe_fdw_handler
_10
validator stripe_fdw_validator;

Store your credentials

We need to provide Postgres with the credentials to connect to Stripe, and any additional options. We can do this using the create server command:

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 Stripe API key in Vault and retrieve the `key_id`
_10
insert into vault.secrets (name, secret)
_10
values (
_10
'stripe',
_10
'YOUR_SECRET'
_10
)
_10
returning key_id;

Reference the credentials using the Key ID or Key Name:


_10
create server stripe_server
_10
foreign data wrapper stripe_wrapper
_10
options (
_10
api_key_id '<key_ID>', -- The Key ID from above, required if api_key_name is not specified.
_10
api_key_name '<key_Name>', -- The Key Name from above, required if api_key_id is not specified.
_10
api_url 'https://api.stripe.com/v1/', -- Stripe API base URL, optional. Default is 'https://api.stripe.com/v1/'
_10
api_version '2024-06-20' -- Stripe API version, optional. Default is your Stripe account’s default API version.
_10
);

Create a schema

We recommend creating a schema to hold all the foreign tables:


_10
create schema stripe;

Creating Foreign Tables

The Stripe Wrapper supports data read and modify from Stripe API.

ObjectSelectInsertUpdateDeleteTruncate
Accounts
Balance
Balance Transactions
Charges
Checkout Sessions
Customers
Disputes
Events
Files
File Links
Invoices
Mandates
Meters
PaymentIntents
Payouts
Prices
Products
Refunds
SetupAttempts
SetupIntents
Subscriptions
Tokens
Topups
Transfers

The Stripe foreign tables mirror Stripe's API.

We can then create the foreign table, for example:


_13
create foreign table stripe.accounts (
_13
id text,
_13
business_type text,
_13
country text,
_13
email text,
_13
type text,
_13
created timestamp,
_13
attrs jsonb
_13
)
_13
server stripe_server
_13
options (
_13
object 'accounts'
_13
);

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.

Accounts

This is an object representing a Stripe account.

Ref: Stripe docs

Operations

ObjectSelectInsertUpdateDeleteTruncate
Accounts

Usage


_13
create foreign table stripe.accounts (
_13
id text,
_13
business_type text,
_13
country text,
_13
email text,
_13
type text,
_13
created timestamp,
_13
attrs jsonb
_13
)
_13
server stripe_server
_13
options (
_13
object 'accounts'
_13
);

Notes

  • While any column is allowed in a where clause, it is most efficient to filter by id.

Balance

read only

Shows the balance currently on your Stripe account.

Ref: Stripe docs


_10
create foreign table stripe.balance (
_10
balance_type text,
_10
amount bigint,
_10
currency text,
_10
attrs jsonb
_10
)
_10
server stripe_server
_10
options (
_10
object 'balance'
_10
);

Balance Transactions

read only

Balance transactions represent funds moving through your Stripe account. They're created for every type of transaction that comes into or flows out of your Stripe account balance.

Ref: Stripe docs


_16
create foreign table stripe.balance_transactions (
_16
id text,
_16
amount bigint,
_16
currency text,
_16
description text,
_16
fee bigint,
_16
net bigint,
_16
status text,
_16
type text,
_16
created timestamp,
_16
attrs jsonb
_16
)
_16
server stripe_server
_16
options (
_16
object 'balance_transactions'
_16
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • type

Charges

read only

To charge a credit or a debit card, you create a Charge object. You can retrieve and refund individual charges as well as list all charges. Charges are identified by a unique, random ID.

Ref: Stripe docs


_16
create foreign table stripe.charges (
_16
id text,
_16
amount bigint,
_16
currency text,
_16
customer text,
_16
description text,
_16
invoice text,
_16
payment_intent text,
_16
status text,
_16
created timestamp,
_16
attrs jsonb
_16
)
_16
server stripe_server
_16
options (
_16
object 'charges'
_16
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • customer

Checkout Sessions

read only

A Checkout Session represents your customer's session as they pay for one-time purchases or subscriptions through Checkout or Payment Links. We recommend creating a new Session each time your customer attempts to pay.

Ref: Stripe docs


_12
create foreign table stripe.checkout_sessions (
_12
id text,
_12
customer text,
_12
payment_intent text,
_12
subscription text,
_12
attrs jsonb
_12
)
_12
server stripe_server
_12
options (
_12
object 'checkout/sessions',
_12
rowid_column 'id'
_12
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • customer
  • payment_intent
  • subscription

Customers

read and modify

Contains customers known to Stripe.

Ref: Stripe docs


_13
create foreign table stripe.customers (
_13
id text,
_13
email text,
_13
name text,
_13
description text,
_13
created timestamp,
_13
attrs jsonb
_13
)
_13
server stripe_server
_13
options (
_13
object 'customers',
_13
rowid_column 'id'
_13
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • email

Disputes

read only

A dispute occurs when a customer questions your charge with their card issuer.

Ref: Stripe docs


_15
create foreign table stripe.disputes (
_15
id text,
_15
amount bigint,
_15
currency text,
_15
charge text,
_15
payment_intent text,
_15
reason text,
_15
status text,
_15
created timestamp,
_15
attrs jsonb
_15
)
_15
server stripe_server
_15
options (
_15
object 'disputes'
_15
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • charge
  • payment_intent

Events

read only

Events are our way of letting you know when something interesting happens in your account.

Ref: Stripe docs


_11
create foreign table stripe.events (
_11
id text,
_11
type text,
_11
api_version text,
_11
created timestamp,
_11
attrs jsonb
_11
)
_11
server stripe_server
_11
options (
_11
object 'events'
_11
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • type

Files

read only

This is an object representing a file hosted on Stripe's servers.

Ref: Stripe docs


_16
create foreign table stripe.files (
_16
id text,
_16
filename text,
_16
purpose text,
_16
title text,
_16
size bigint,
_16
type text,
_16
url text,
_16
created timestamp,
_16
expires_at timestamp,
_16
attrs jsonb
_16
)
_16
server stripe_server
_16
options (
_16
object 'files'
_16
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • purpose

read only

To share the contents of a File object with non-Stripe users, you can create a FileLink.

Ref: Stripe docs


_13
create foreign table stripe.file_links (
_13
id text,
_13
file text,
_13
url text,
_13
created timestamp,
_13
expired bool,
_13
expires_at timestamp,
_13
attrs jsonb
_13
)
_13
server stripe_server
_13
options (
_13
object 'file_links'
_13
);

Invoices

read only

Invoices are statements of amounts owed by a customer, and are either generated one-off, or generated periodically from a subscription.

Ref: Stripe docs


_15
create foreign table stripe.invoices (
_15
id text,
_15
customer text,
_15
subscription text,
_15
status text,
_15
total bigint,
_15
currency text,
_15
period_start timestamp,
_15
period_end timestamp,
_15
attrs jsonb
_15
)
_15
server stripe_server
_15
options (
_15
object 'invoices'
_15
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • customer
  • status
  • subscription

Mandates

read only

A Mandate is a record of the permission a customer has given you to debit their payment method.

Ref: Stripe docs


_11
create foreign table stripe.mandates (
_11
id text,
_11
payment_method text,
_11
status text,
_11
type text,
_11
attrs jsonb
_11
)
_11
server stripe_server
_11
options (
_11
object 'mandates'
_11
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id

Meters

read only

A billing meter is a resource that allows you to track usage of a particular event.

Ref: Stripe docs


_12
create foreign table stripe.meter (
_12
id text,
_12
display_name text,
_12
event_name text,
_12
event_time_window text,
_12
status text,
_12
attrs jsonb
_12
)
_12
server stripe_server
_12
options (
_12
object 'billing/meters'
_12
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id

Payment Intents

read only

A payment intent guides you through the process of collecting a payment from your customer.

Ref: Stripe docs


_13
create foreign table stripe.payment_intents (
_13
id text,
_13
customer text,
_13
amount bigint,
_13
currency text,
_13
payment_method text,
_13
created timestamp,
_13
attrs jsonb
_13
)
_13
server stripe_server
_13
options (
_13
object 'payment_intents'
_13
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • customer

Payouts

read only

A Payout object is created when you receive funds from Stripe, or when you initiate a payout to either a bank account or debit card of a connected Stripe account.

Ref: Stripe docs


_15
create foreign table stripe.payouts (
_15
id text,
_15
amount bigint,
_15
currency text,
_15
arrival_date timestamp,
_15
description text,
_15
statement_descriptor text,
_15
status text,
_15
created timestamp,
_15
attrs jsonb
_15
)
_15
server stripe_server
_15
options (
_15
object 'payouts'
_15
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • status

Prices

read only

A Price object is needed for all of your products to facilitate multiple currencies and pricing options.

Ref: Stripe docs


_14
create foreign table stripe.prices (
_14
id text,
_14
active bool,
_14
currency text,
_14
product text,
_14
unit_amount bigint,
_14
type text,
_14
created timestamp,
_14
attrs jsonb
_14
)
_14
server stripe_server
_14
options (
_14
object 'prices'
_14
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • active

Products

read and modify

All products available in Stripe.

Ref: Stripe docs


_15
create foreign table stripe.products (
_15
id text,
_15
name text,
_15
active bool,
_15
default_price text,
_15
description text,
_15
created timestamp,
_15
updated timestamp,
_15
attrs jsonb
_15
)
_15
server stripe_server
_15
options (
_15
object 'products',
_15
rowid_column 'id'
_15
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • active

Refunds

read only

Refund objects allow you to refund a charge that has previously been created but not yet refunded.

Ref: Stripe docs


_15
create foreign table stripe.refunds (
_15
id text,
_15
amount bigint,
_15
currency text,
_15
charge text,
_15
payment_intent text,
_15
reason text,
_15
status text,
_15
created timestamp,
_15
attrs jsonb
_15
)
_15
server stripe_server
_15
options (
_15
object 'refunds'
_15
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • charge
  • payment_intent

SetupAttempts

read only

A SetupAttempt describes one attempted confirmation of a SetupIntent, whether that confirmation was successful or unsuccessful.

Ref: Stripe docs


_16
create foreign table stripe.setup_attempts (
_16
id text,
_16
application text,
_16
customer text,
_16
on_behalf_of text,
_16
payment_method text,
_16
setup_intent text,
_16
status text,
_16
usage text,
_16
created timestamp,
_16
attrs jsonb
_16
)
_16
server stripe_server
_16
options (
_16
object 'setup_attempts'
_16
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • setup_intent

SetupIntents

read only

A SetupIntent guides you through the process of setting up and saving a customer's payment credentials for future payments.

Ref: Stripe docs


_15
create foreign table stripe.setup_intents (
_15
id text,
_15
client_secret text,
_15
customer text,
_15
description text,
_15
payment_method text,
_15
status text,
_15
usage text,
_15
created timestamp,
_15
attrs jsonb
_15
)
_15
server stripe_server
_15
options (
_15
object 'setup_intents'
_15
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • customer
  • payment_method

Subscriptions

read and modify

Customer recurring payment schedules.

Ref: Stripe docs


_13
create foreign table stripe.subscriptions (
_13
id text,
_13
customer text,
_13
currency text,
_13
current_period_start timestamp,
_13
current_period_end timestamp,
_13
attrs jsonb
_13
)
_13
server stripe_server
_13
options (
_13
object 'subscriptions',
_13
rowid_column 'id'
_13
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • customer
  • price
  • status

Tokens

read only

Tokenization is the process Stripe uses to collect sensitive card or bank account details, or personally identifiable information (PII), directly from your customers in a secure manner.

Ref: Stripe docs


_12
create foreign table stripe.tokens (
_12
id text,
_12
customer text,
_12
currency text,
_12
current_period_start timestamp,
_12
current_period_end timestamp,
_12
attrs jsonb
_12
)
_12
server stripe_server
_12
options (
_12
object 'tokens'
_12
);

Top-ups

read only

To top up your Stripe balance, you create a top-up object.

Ref: Stripe docs


_13
create foreign table stripe.topups (
_13
id text,
_13
amount bigint,
_13
currency text,
_13
description text,
_13
status text,
_13
created timestamp,
_13
attrs jsonb
_13
)
_13
server stripe_server
_13
options (
_13
object 'topups'
_13
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • status

Transfers

read only

A Transfer object is created when you move funds between Stripe accounts as part of Connect.

Ref: Stripe docs


_13
create foreign table stripe.transfers (
_13
id text,
_13
amount bigint,
_13
currency text,
_13
description text,
_13
destination text,
_13
created timestamp,
_13
attrs jsonb
_13
)
_13
server stripe_server
_13
options (
_13
object 'transfers'
_13
);

While any column is allowed in a where clause, it is most efficient to filter by:

  • id
  • destination

Query Pushdown Support

This FDW supports where clause pushdown. You can specify a filter in where clause and it will be passed to Stripe API call.

For example, this query


_10
select * from stripe.customers where id = 'cus_xxx';

will be translated Stripe API call: https://api.stripe.com/v1/customers/cus_xxx.

For supported filter columns for each object, please check out foreign table documents above.

Examples

Some examples on how to use Stripe foreign tables.

Basic example


_10
-- always limit records to reduce API calls to Stripe
_10
select * from stripe.customers limit 10;
_10
select * from stripe.invoices limit 10;
_10
select * from stripe.subscriptions limit 10;

Query JSON attributes


_11
-- extract account name for an invoice
_11
select id, attrs->>'account_name' as account_name
_11
from stripe.invoices where id = 'in_xxx';
_11
_11
-- extract invoice line items for an invoice
_11
select id, attrs#>'{lines,data}' as line_items
_11
from stripe.invoices where id = 'in_xxx';
_11
_11
-- extract subscription items for a subscription
_11
select id, attrs#>'{items,data}' as items
_11
from stripe.subscriptions where id = 'sub_xxx';

Data modify


_10
insert into stripe.customers(email,name,description) values ('test@test.com', 'test name', null);
_10
update stripe.customers set description='hello fdw' where id ='cus_xxx';
_10
update stripe.customers set attrs='{"metadata[foo]": "bar"}' where id ='cus_xxx';
_10
delete from stripe.customers where id ='cus_xxx';

To insert into an object with sub-fields, we need to create the foreign table with column name exactly same as the API required. For example, to insert a subscription object we can define the foreign table following the Stripe API docs:


_12
-- create the subscription table for data insertion, the 'customer'
_12
-- and 'items[0][price]' fields are required.
_12
create foreign table stripe.subscriptions (
_12
id text,
_12
customer text,
_12
"items[0][price]" text -- column name will be used in API Post request
_12
)
_12
server stripe_server
_12
options (
_12
object 'subscriptions',
_12
rowid_column 'id'
_12
);

And then we can insert a subscription like below:


_10
insert into stripe.subscriptions (customer, "items[0][price]")
_10
values ('cus_Na6dX7aXxi11N4', 'price_1MowQULkdIwHu7ixraBm864M');

Note this foreign table is only for data insertion, it cannot be used in select statement.