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 snake_case identifiers into camelCase identifiers. The second is that we will not be implementing the card 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’ mergeSchemas.

Let’s create our PostgreSQL tables, roles, grants, and policies. Connect to a new database, create a role with bypassrls, 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 app_user role is that assumed role of a user-authenticated GraphQL request. The current_user_id 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 current_user_id 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 /graphql 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 cards edge of our User 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 Card 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 useknex internally to implement storeCard and removeCard (looking up the customerId 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 User identifiers rather than Stripe customer identifiers — hiding the implementation detail of a Customer is a simplifying design choice. But our only query is cardsByCustomerId, 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 cards array on the User type. This “overlap” between two schemas can be defined by another schema called linkTypeDefs:

extend type User {
  cards: [Card]
}

We can implement the resolver for User.cards by delegating to the cardsByCustomerId resolver and defining the information we need from our User type in the fragment 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 mergeSchemas, but in practice we’ve got one more hurdle to jump.

Simplified slightly, mergeSchemas 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 pgSettings function that takes in an express request and allows us to set the PostgreSQL variable jwt.claims.user_id 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 makeRemoteExecutableSchema. These requests will come from a front-end GraphQL server we’ll create using apollo-server-express and expose publicly.

The graphqlExpress function creates express middleware that has similar functionality to postgraphile's pgSettings: req => ({ ... }). It still takes in the express request, but instead of setting local variables in PostgreSQL it sets them in the top-level context of the GraphQL request, which is then available for us to proxy using a user-id 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.