Stitched GraphQL Schemas with PostGraphile, Passport, and Stripe

One of my resolutions for 2018 is to find some way to use GraphQL at Robin. In addition to a web app that helps fans get into the most sought-after concerts, we’re building a product to help businesses and professionals level up the way they entertain.

Having recently started productising this new offering, it felt like the right time to start exploring how we could improve our developer experience and avoid some of the pain points of our previous architecture.

PostGraphile strikes an uncommon balance between pragmatism and power, and allows us to lean on our team’s existing PostgreSQL experience. It’s an opinionated framework, but malleable enough to invest in. Let’s get started.

For the purposes of this example, we’ll be building the back-end of a system to help our operations team register new clients and store payment information. Given our team makeup (JavaScript junkies), our business requirements, and the technologies we were comfortable using from our previous work, we set out to build our MVP:

  • Babel / Node / Express
  • PostgreSQL 10+ and PostGraphile
  • LinkedIn for authorisation (via. passport)
  • Stripe for handling sensitive credit card details

Bootstrapping the project might look something like this:

yarn init .
yarn add express body-parser [...]
yarn add @babel/node [...] --dev
yarn add passport passport-linkedin-oauth2 express-session
yarn add pg postgraphile
yarn add apollo-fetch apollo-server-express graphql-tools
yarn add stripe

Next, let’s define what we want our GraphQL schema to look like.

The information schema we want to read and manipulate via GraphQL

There are two important things to note here. The first is that PostGraphile will — by default — turn your identifiers into identifiers. The second is that we will not be implementing the type inside of our PostgreSQL database at all. Instead, we’ll “stitch” in a second GraphQL schema: a facade over Stripe’s node library, using graphql-tools.

Let’s create our PostgreSQL tables, roles, grants, and policies. Connect to a new database, create a role with , then assume that role. Execute the following migration:

create schema app_public;
create schema app_private;
create schema app_hidden;
create table app_public."user" (
id serial PRIMARY KEY,
email TEXT NOT NULL,
given_names TEXT,
family_name TEXT,
linkedin_id TEXT NOT NULL,
stripe_customer_id TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
modified_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (linkedin_id),
UNIQUE (email)
);
create function public.current_user_id()
returns integer as $$
select nullif(current_setting('jwt.claims.user_id', true), '')::integer;
$$ language sql stable;
create function app_public.this_user()
returns app_public."user" as $$
select * from "user" where id = current_user_id()
$$ language sql stable;
create function public.update_modified_at()
returns trigger as $$
begin
new.modified_at = now();
return new;
end;
$$ language 'plpgsql';
create role app_user nologin nobypassrls;
grant app_user to current_user;
create trigger user___modified_at___update
before update
on app_public."user"
for each row
execute procedure update_modified_at();
create policy select_if_user
on app_public."user"
for select
to app_user
using (id = current_user_id());

create policy update_if_user
on app_public."user"
for update
to app_user
using (id = current_user_id())
with check (id = current_user_id());
grant select, update on app_public."user" to app_user;
alter table app_public."user" enable row level security;

There are a few important points in the above code. The role is that assumed role of a user-authenticated GraphQL request. The function is a convenient way to determine the logged-in user ID. Finally, we use row-level security policies to restrict which rows the logged-in user can view or modify.

Let’s take a closer look at the function: it reads a local setting that we set on every pg connection, and returns it to the caller. Here’s how we pass that setting to PostgreSQL via PostGraphile:

app.use(postgraphile(process.env.DATABASE_URL, 'app_public', {
pgSettings: (req) => {
const userId = req.user && req.user.id; // from passport
return {
role: 'app_user',
...(userId ?
{ 'jwt.claims.user_id': String(userId) } : {}),
};
},
}));

At this point (assuming you’ve set up passport to correctly authenticate users and store them in your database), you can hit your OAuth flow then query for the current user’s information on using a query like:

// calls the app_public.this_user() function defined above
{ thisUser { id email givenNames familyName } }

Up to this point, we’ve mostly re-explained things that are already in the PostGraphile documentation. Let’s dig in deeper and implement the edge of our type, which will require bridging between two schemas.

If our plan is to do some schema stitching, the best way to understand what we’re trying to accomplish is to read the relevant article in the excellent Apollo documentation. Take a moment to skim it, then let’s continue on.

We’ll start high-level by creating our Stripe type definitions: the type, a way to access a Stripe customer’s cards, and the mutations we want to support:

type Card {
id: String!
brand: String!
expiryMonth: Int!
expiryYear: Int!
last4: String!
}
type Query {
cardsByCustomerId(id: String): [Card]
}
type Mutation {
storeCard(userId: Int!, token: String!): Card
removeCard(userId: Int!, cardId: String!): Boolean
}
schema {
query: Query
mutation: Mutation
}

For completeness’ sake, I should note that we’ve chosen to use internally to implement and (looking up the and any information needed to create a new Customer in Stripe if that user didn’t already have one). There might be a cleaner way to use GraphQL queries and migrations to do this without having knowledge of the merged schema; I’d love to hear from you if you have another solution for this.

We’ve defined mutations in terms of identifiers rather than Stripe customer identifiers — hiding the implementation detail of a is a simplifying design choice. But our only query is , so how will clients ever be able to query for a user’s stored payment cards?

When you want to merge two executable schemas, you need to define what happens where they overlap. In particular, we’re interested in adding a array on the type. This “overlap” between two schemas can be defined by another schema called :

extend type User {
cards: [Card]
}

We can implement the resolver for by delegating to the resolver and defining the information we need from our type in the field. We’ll call these link resolvers:

const linkResolvers = mergeInfo => ({
User: {
cards: {
/* the info we need from User */
fragment:
'fragment UserFragment on User { stripeCustomerId }',
resolve(parent, args, context, info) {
const { stripeCustomerId } = parent;
return mergeInfo.delegate(
'query',
'cardsByCustomerId',
{ id: stripeCustomerId },
context,
info,
);
},
},
}
});

In theory, we’ve just defined exactly how our Stripe schema and our automatically-generated PostGraphile schema relate. We should be able to just jam them together using , but in practice we’ve got one more hurdle to jump.

Simplified slightly, takes two arguments: a list of executable GraphQL schemas (executable meaning both type definitions and resolvers) and an object of cross-schema link resolvers. The problem is that while we can easily create an executable schema for our Stripe types…

const stripeSchema = makeExecutableSchema({
stripe.typeDefs,
stripe.resolvers,
})

… it’s a little more complicated for our PostGraphile schema. Remember the function that takes in an express request and allows us to set the PostgreSQL variable based on our currently-logged-in passport user? It’s not available in schema-only mode.

There is a work-around available. We can run a second express server that only hosts our PostGraphile schema, and proxy requests to it using . These requests will come from a front-end GraphQL server we’ll create using apollo-server-express and expose publicly.

The function creates express middleware that has similar functionality to 's . It still takes in the express request, but instead of setting local variables in PostgreSQL it sets them in the top-level of the GraphQL request, which is then available for us to proxy using a HTTP header. Take a look at the sample code:

I’m certain that there’s a way to do this with schema-only mode, but I haven’t quite figured it out yet. I’d love to hear your comments below if you know a way to make it work.

I’d like to thank Benjie Gillam and everyone on the postgraphql gitter for their support while I stumbled through this. Please let me know if you found this valuable, and let me know below if you’d find an article on testing PostGraphile useful.

Robin Media CTO, Postgres / ML / Node / React / Kubernetes

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store