Stripe
Stripe is an API driven online payment processing utility. supabase/wrappers exposes below endpoints.
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.
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:
_10create extension if not exists wrappers with schema extensions;
Enable the Stripe Wrapper
Enable the stripe_wrapper FDW:
_10create 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`_10insert into vault.secrets (name, secret)_10values (_10 'stripe',_10 'YOUR_SECRET'_10)_10returning key_id;
Reference the credentials using the Key ID or Key Name:
_10create 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:
_10create schema stripe;
Creating Foreign Tables
The Stripe Wrapper supports data read and modify from Stripe API.
| Object | Select | Insert | Update | Delete | Truncate |
|---|---|---|---|---|---|
| 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:
_13create 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
| Object | Select | Insert | Update | Delete | Truncate |
|---|---|---|---|---|---|
| Accounts | ✅ | ❌ | ❌ | ❌ | ❌ |
Usage
_13create 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
_10create 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
_16create 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
_16create 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
_12create 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
_13create 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
Disputes
read only
A dispute occurs when a customer questions your charge with their card issuer.
Ref: Stripe docs
_15create 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
_11create 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
_16create 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
File Links
read only
To share the contents of a File object with non-Stripe users, you can create a FileLink.
Ref: Stripe docs
_13create 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
_15create 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
_11create 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
_12create 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
_13create 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
_15create 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
_14create 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
_15create 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
_15create 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
_16create 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
_15create 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
_13create 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
_12create 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
_13create 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
_13create 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
_10select * 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_10select * from stripe.customers limit 10;_10select * from stripe.invoices limit 10;_10select * from stripe.subscriptions limit 10;
Query JSON attributes
_11-- extract account name for an invoice_11select id, attrs->>'account_name' as account_name_11from stripe.invoices where id = 'in_xxx';_11_11-- extract invoice line items for an invoice_11select id, attrs#>'{lines,data}' as line_items_11from stripe.invoices where id = 'in_xxx';_11_11-- extract subscription items for a subscription_11select id, attrs#>'{items,data}' as items_11from stripe.subscriptions where id = 'sub_xxx';
Data modify
_10insert into stripe.customers(email,name,description) values ('test@test.com', 'test name', null);_10update stripe.customers set description='hello fdw' where id ='cus_xxx';_10update stripe.customers set attrs='{"metadata[foo]": "bar"}' where id ='cus_xxx';_10delete 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._12create 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:
_10insert into stripe.subscriptions (customer, "items[0][price]")_10values ('cus_Na6dX7aXxi11N4', 'price_1MowQULkdIwHu7ixraBm864M');
Note this foreign table is only for data insertion, it cannot be used in select statement.