Stripe

1
create extension if not exists wrappers with schema extensions;

1
create foreign data wrapper stripe_wrapper
2
  handler stripe_fdw_handler
3
  validator stripe_fdw_validator;

1
-- Save your Stripe API key in Vault and retrieve the created `key_id`
2
select vault.create_secret(
3
  '<Stripe API key>',
4
  'stripe',
5
  'Stripe API key for Wrappers'
6
);

1
create schema if not exists stripe;

1
-- create all the foreign tables
2
import foreign schema stripe from server stripe_server into stripe;
3
4
-- or, create "checkout_sessions", "customers" and "balance" tables only
5
import foreign schema stripe
6
   limit to ("checkout_sessions", "customers", "balance")
7
   from server stripe_server into stripe;
8
9
-- or, create all foreign tables except "checkout_sessions" and "billing_meters"
10
import foreign schema stripe
11
   except ("checkout_sessions", "billing_meters")
12
   from server stripe_server into stripe;

1
create foreign table stripe.accounts (
2
  id text,
3
  business_type text,
4
  country text,
5
  email text,
6
  type text,
7
  created timestamp,
8
  attrs jsonb
9
)
10
  server stripe_server
11
  options (
12
    object 'accounts'
13
  );

1
create foreign table stripe.balance (
2
  balance_type text,
3
  amount bigint,
4
  currency text,
5
  attrs jsonb
6
)
7
  server stripe_server
8
  options (
9
    object 'balance'
10
  );

1
create foreign table stripe.balance_transactions (
2
  id text,
3
  amount bigint,
4
  currency text,
5
  description text,
6
  fee bigint,
7
  net bigint,
8
  status text,
9
  type text,
10
  created timestamp,
11
  attrs jsonb
12
)
13
  server stripe_server
14
  options (
15
    object 'balance_transactions'
16
  );

1
create foreign table stripe.charges (
2
  id text,
3
  amount bigint,
4
  currency text,
5
  customer text,
6
  description text,
7
  invoice text,
8
  payment_intent text,
9
  status text,
10
  created timestamp,
11
  attrs jsonb
12
)
13
  server stripe_server
14
  options (
15
    object 'charges'
16
  );

1
create foreign table stripe.checkout_sessions (
2
  id text,
3
  customer text,
4
  payment_intent text,
5
  subscription text,
6
  attrs jsonb
7
)
8
  server stripe_server
9
  options (
10
    object 'checkout/sessions',
11
    rowid_column 'id'
12
  );

1
create foreign table stripe.customers (
2
  id text,
3
  email text,
4
  name text,
5
  description text,
6
  created timestamp,
7
  attrs jsonb
8
)
9
  server stripe_server
10
  options (
11
    object 'customers',
12
    rowid_column 'id'
13
  );

1
-- create a new customer
2
insert into stripe.customers(email, name, description)
3
values ('jane@example.com', 'Jane Smith', 'Premium customer');
4
5
-- update a customer
6
update stripe.customers
7
set name = 'Jane Doe'
8
where email = 'jane@example.com';
9
10
-- delete a customer
11
delete from stripe.customers
12
where id = 'cus_xxx';

1
create foreign table stripe.disputes (
2
  id text,
3
  amount bigint,
4
  currency text,
5
  charge text,
6
  payment_intent text,
7
  reason text,
8
  status text,
9
  created timestamp,
10
  attrs jsonb
11
)
12
  server stripe_server
13
  options (
14
    object 'disputes'
15
  );

1
create foreign table stripe.events (
2
  id text,
3
  type text,
4
  api_version text,
5
  created timestamp,
6
  attrs jsonb
7
)
8
  server stripe_server
9
  options (
10
    object 'events'
11
  );

1
create foreign table stripe.files (
2
  id text,
3
  filename text,
4
  purpose text,
5
  title text,
6
  size bigint,
7
  type text,
8
  url text,
9
  created timestamp,
10
  expires_at timestamp,
11
  attrs jsonb
12
)
13
  server stripe_server
14
  options (
15
    object 'files'
16
  );

1
create foreign table stripe.file_links (
2
  id text,
3
  file text,
4
  url text,
5
  created timestamp,
6
  expired bool,
7
  expires_at timestamp,
8
  attrs jsonb
9
)
10
  server stripe_server
11
  options (
12
    object 'file_links'
13
  );

1
create foreign table stripe.invoices (
2
  id text,
3
  customer text,
4
  subscription text,
5
  status text,
6
  total bigint,
7
  currency text,
8
  period_start timestamp,
9
  period_end timestamp,
10
  attrs jsonb
11
)
12
  server stripe_server
13
  options (
14
    object 'invoices'
15
  );

1
create foreign table stripe.mandates (
2
  id text,
3
  payment_method text,
4
  status text,
5
  type text,
6
  attrs jsonb
7
)
8
  server stripe_server
9
  options (
10
    object 'mandates'
11
  );

1
create foreign table stripe.billing_meter (
2
  id text,
3
  display_name text,
4
  event_name text,
5
  event_time_window text,
6
  status text,
7
  attrs jsonb
8
)
9
  server stripe_server
10
  options (
11
    object 'billing/meters'
12
  );

1
create foreign table stripe.payment_intents (
2
  id text,
3
  customer text,
4
  amount bigint,
5
  currency text,
6
  payment_method text,
7
  created timestamp,
8
  attrs jsonb
9
)
10
  server stripe_server
11
  options (
12
    object 'payment_intents'
13
  );

1
create foreign table stripe.payouts (
2
  id text,
3
  amount bigint,
4
  currency text,
5
  arrival_date timestamp,
6
  description text,
7
  statement_descriptor text,
8
  status text,
9
  created timestamp,
10
  attrs jsonb
11
)
12
  server stripe_server
13
  options (
14
    object 'payouts'
15
  );

1
create foreign table stripe.prices (
2
  id text,
3
  active bool,
4
  currency text,
5
  product text,
6
  unit_amount bigint,
7
  type text,
8
  created timestamp,
9
  attrs jsonb
10
)
11
  server stripe_server
12
  options (
13
    object 'prices'
14
  );

1
create foreign table stripe.products (
2
  id text,
3
  name text,
4
  active bool,
5
  default_price text,
6
  description text,
7
  created timestamp,
8
  updated timestamp,
9
  attrs jsonb
10
)
11
  server stripe_server
12
  options (
13
    object 'products',
14
    rowid_column 'id'
15
  );

1
create foreign table stripe.refunds (
2
  id text,
3
  amount bigint,
4
  currency text,
5
  charge text,
6
  payment_intent text,
7
  reason text,
8
  status text,
9
  created timestamp,
10
  attrs jsonb
11
)
12
  server stripe_server
13
  options (
14
    object 'refunds'
15
  );

1
create foreign table stripe.setup_attempts (
2
  id text,
3
  application text,
4
  customer text,
5
  on_behalf_of text,
6
  payment_method text,
7
  setup_intent text,
8
  status text,
9
  usage text,
10
  created timestamp,
11
  attrs jsonb
12
)
13
  server stripe_server
14
  options (
15
    object 'setup_attempts'
16
  );

1
create foreign table stripe.setup_intents (
2
  id text,
3
  client_secret text,
4
  customer text,
5
  description text,
6
  payment_method text,
7
  status text,
8
  usage text,
9
  created timestamp,
10
  attrs jsonb
11
)
12
  server stripe_server
13
  options (
14
    object 'setup_intents'
15
  );

1
create foreign table stripe.subscriptions (
2
  id text,
3
  customer text,
4
  currency text,
5
  current_period_start timestamp,
6
  current_period_end timestamp,
7
  attrs jsonb
8
)
9
  server stripe_server
10
  options (
11
    object 'subscriptions',
12
    rowid_column 'id'
13
  );

1
create foreign table stripe.tokens (
2
  id text,
3
  type text,
4
  client_ip text,
5
  created timestamp,
6
  livemode boolean,
7
  used boolean,
8
  attrs jsonb
9
)
10
  server stripe_server
11
  options (
12
    object 'tokens'
13
  );

1
create foreign table stripe.topups (
2
  id text,
3
  amount bigint,
4
  currency text,
5
  description text,
6
  status text,
7
  created timestamp,
8
  attrs jsonb
9
)
10
  server stripe_server
11
  options (
12
    object 'topups'
13
  );

1
create foreign table stripe.transfers (
2
  id text,
3
  amount bigint,
4
  currency text,
5
  description text,
6
  destination text,
7
  created timestamp,
8
  attrs jsonb
9
)
10
  server stripe_server
11
  options (
12
    object 'transfers'
13
  );

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

1
-- always limit records to reduce API calls to Stripe
2
select * from stripe.customers limit 10;
3
select * from stripe.invoices limit 10;
4
select * from stripe.subscriptions limit 10;

1
-- extract account name for an invoice
2
select id, attrs->>'account_name' as account_name
3
from stripe.invoices where id = 'in_xxx';
4
5
-- extract invoice line items for an invoice
6
select id, attrs#>'{lines,data}' as line_items
7
from stripe.invoices where id = 'in_xxx';
8
9
-- extract subscription items for a subscription
10
select id, attrs#>'{items,data}' as items
11
from stripe.subscriptions where id = 'sub_xxx';

1
-- insert
2
insert into stripe.customers(email,name,description)
3
values ('test@test.com', 'test name', null);
4
5
-- update
6
update stripe.customers
7
set description='hello fdw'
8
where id = 'cus_xxx';
9
10
update stripe.customers
11
set attrs='{"metadata[foo]": "bar"}'
12
where id = 'cus_xxx';
13
14
-- delete
15
delete from stripe.customers
16
where id = 'cus_xxx';

1
-- create the subscription table for data insertion, the 'customer'
2
-- and 'items[0][price]' fields are required.
3
create foreign table stripe.subscriptions (
4
  id text,
5
  customer text,
6
  "items[0][price]" text  -- column name will be used in API Post request
7
)
8
  server stripe_server
9
  options (
10
    object 'subscriptions',
11
    rowid_column 'id'
12
  );

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