[Tech Post] A Serverless GraphQL API from PostgreSQL schema

Introduction

First off, here’s the repo that the following post will be referring to:

https://github.com/ryanwestby/serverless-postgraphile

This is a GraphQL API that can be built from a PostgreSQL schema using PostGraphile. It is mounted as HTTP middleware for the Express framework, and deployed to an AWS Lambda with the Serverless Framework.

Why?

This GraphQL server allows clients to query data directly from a PostgreSQL database. It removes the need to write a separate REST API for each possible client or use case, as all clients may query the same API for exactly the data they need.

It also allows for all the benefits of GraphQL without needing to write the GraphQL API. PostGraphile is used to handle the building of the GraphQL API, based on a PostgreSQL schema.

i.e. a PostgreSQL schema may be directly translated to this GraphQL API, instantly, while removing the need to duplicate any business logic with a custom API.

Implementation Details

The server is defined with the Express framework, and PostGraphile is mounted as HTTP middleware. The app will parse a PostgreSQL Connection URI from an environment variable into a config object, and then open up a connection pool for node-postgres. A schema to expose to PostGraphile is also taken from an environment var (e.g. “public”). This schema, the connection pool, and a PostGraphile config object are used to mount PostGraphile to the Express server.

Serverless

The project also leverages the power of serverless by running the Express app on AWS Lambda via the Serverless framework, using the Serverless Express Plugin.

Introspection Cache

PostGraphile has an introspection query feature where it finds all tables, functions, and relations available in a specified schema.

In order to reduce startup time on the Lambda, the introspection query can be cached. This is done through a pre-deploy hook.

The pre-deploy hook uses the PostGraphile CLI to write an introspection.cache to the root directory. For the hook to run properly, the PostGraphile package must be installed globally.

Because the GraphQL Server only reads from the introspection.cache, in order for Postgraphile to pick up changes to the PostgreSQL schema, a new cache must be generated and deployed to the server.

Security

PostGraphile leverages PostgreSQL’s Role-Based Access Control and Row-Level Security policies. It can process JWT’s, and also generate them.

IMPORTANT: You run the risk of exposing your database schema to the public with this project. Please read into Postgraphile and PostgreSQL’s suported security features before deploying this server:

https://www.graphile.org/postgraphile/security/

This project does not have these security features enabled by default. You will have to configure them yourself.

How to write custom Query SQL Procedures

PostGraphile will detect any PostgreSQL procedures you have defined in your schema. PostGraphile can use these procedures in a variety of ways. The PostGraphile docs give an overview of procedures here.

Query Procedures are a useful way to specify a procedure for PostGraphile. They essentially allow you to add custom queries to your GraphQL schema.

Custom Query Example

Let’s say we wanted to get the total count of posts from a certain author.

A type must be created in PostgreSQL for the return value:

create type public.total_post_count as (
  count bigint
);

A function for the query can then be created. The argument to this function is “author”, and the argument must also be exposed via GraphQL.

CREATE OR REPLACE FUNCTION public.author_total_post_count(author text)
	RETURNS setof public.total_post_count
	AS $$    SELECT COUNT(*)
	FROM (select * 
		FROM public.posts 
		where author = $1
	LANGUAGE sql stable

Notice also that the function returns a “setof public.total_post_count”. This is the type we defined above, and “setof” is an important detail for PostGraphile to recognize this type. More info on specifying a query procedure: https://www.graphile.org/postgraphile/procedures/#query-procedures

Now this function will appear in the GraphQL schema as a root-level Query type. It can be queried like so:

{
  authorTotalPostCount(author: "Ryan Westby") {
    nodes {
      count
    }
  }
}

This query returns:

{
  "data": {
    "authorTotalPostCount": {
      "nodes": [
        {
          "count": "5"
        }
      ]
    }
  }
}

How to query the API

To query the GraphQL API from a project, first define a method that makes a request to the POST endpoint for the API generated by the Serverless framework. Here is one way to define the request in JavaScipt:

let endpoint = 'url'; // This is the endpoint generated by the Serverless framework
let gqlRequest = (query) => {
    return new Promise((resolve, reject) => {

      const xhr = new XMLHttpRequest();
      xhr.responseType = 'json';
      xhr.open("POST", endpoint);
      xhr.setRequestHeader("Content-Type", "application/json");
      xhr.setRequestHeader("Accept", "application/json");

      xhr.onloadend = () => {
        if (xhr.status == 200) {
          resolve(xhr.response);
        } else {
          reject(xhr.status);
        }
      }

      xhr.onerror = function (err) {
        reject(err);
      };
      xhr.send(JSON.stringify({
        query
      }));

    });
  };

A query should look like:

let getAuthorPostCount = (author) => {
    return `{
      authorTotalPostCount(author: "${author}") {
        nodes {
          count
        }
      }
    }`;
  };

The count would then be available:

let response = await gqlRequest(getAuthorPostCount);
let totalPostCount = response.data.authorTotalPostCount.nodes[0].count;

Additional Reading

• PostGraphile: https://www.graphile.org/PostGraphile/
• Quick intro to PostGraphile (It is referred to as PostGraphQL here, this is what the project used to be named): https://www.youtube.com/watch?v=b3pwlCDy6vY
• PostGraphile example and tutorial: https://github.com/graphile/PostGraphile/blob/master/examples/forum/TUTORIAL.md
• Intro to GraphQL: https://graphql.org/learn/

Ryan Westby

Coder, climber, artist, 21st century ghost

Tweet Share