I am going to assume you already know what GraphQL is and why it’s cool. If you don’t hit up the link, watch a video, or something. I can wait. Convinced? Ok, let’s go.

Data Driven Design

If you know about GraphQL then you must also know that it has something called an, interface description language, or IDL. The specification for this language allows developers to define their data schema in a very succinct way. And many GraphQL services will allow you to specify your schema using this language.

However this is a post about PostgreSQL. This server does not, at present, understand the GraphQL IDL language. It knows about SQL99, plpgsql, and a handful of imperative languages. If you want to define a schema in this server then you already have a language for it.

So why would you ever use PostgreSQL?

Well you have to store your data somewhere. GraphQL is just a query language just like SQL is. PostgreSQL give you more than just SQL though! It’s an application server and it gives you many tools for managing data and building applications.

The important thing to grasp in any good software design is the design of data. In this article I’m going to show you some tricks for managing data design in PostgreSQL that works really well with a GraphQL API.

I’ll also share some lesser-known features of PostgreSQL that make it a great platform for developing applications.

Choosing the right abstractions

When you first fire up a new PostreSQL database you’re given a few schemas and not much else. The important one is the public schema. Like any good API design this is where you should expose your data for your consumer: your web server running your GraphQL endpoint.

The first thing you should do is create a new schema for your project. Let’s just call ours, project for lack of a better name. It’s in this schema that we will define our first schemas. Let’s start with something simple:

CREATE TABLE project.authors (
    id serial primary key,
    first_name varchar(140) NOT NULL,
    last_name varchar(140) NOT NULL,
    birth_date date
);

CREATE TABLE project.books (
    id serial primary key,
    title varchar(140) NOT NULL,
    summary text,
    author int references authors(id) NOT NULL,
    published date,
    unique(title, author)
);

In most tutorials and articles on web development you’ve probably come across some example like this. The difference here is that we’re creating our tables in the project schema. We haven’t defined anything in the public schema yet so let’s do that now:

CREATE VIEW public.authors AS
    SELECT
        id,
        first_name,
        last_name,
        birth_date
    FROM
        project.authors;

CREATE VIEW public.books AS
    SELECT
        id,
        title,
        summary,
        author,
        published
    FROM
        project.authors;

What we have done here is abstracted our public view of our library’s data. We can now add new columns to the project.authors table, for example, and not affect our public view of that data. This is perfect for adding administrative columns to tables and having the view simply not select them. Now consumers of your public schema will never see your admin flags or password fields. In fact let’s write an example:

ALTER TABLE project.books ADD COLUMN deleted boolean DEFAULT FALSE;

We’ll use the deleted flag to allow users to delete books from the library. However we don’t actually want to delete the row right away for reasons. So instead we write a trigger function and add a trigger to our view:

CREATE OR REPLACE FUNCTION soft_delete_book()
RETURNS TRIGGER AS
$$
    BEGIN
        IF TG_OP = 'DELETE' THEN
            UPDATE project.books SET deleted = true WHERE id = OLD.id;
        RETURN IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql

CREATE TRIGGER replace_delete_books
INSTEAD OF DELETE ON public.books
FOR EACH ROW EXECUTE PROCEDURE soft_delete_book();

What have we done here? We’ve allowed the consumers of our public schema to delete rows from our books view. To the users of our GraphQL API this will effectively appear like they just deleted their book record. However we replaced the delete action on our view with our own function which sets the deleted flag on our internal table to true.

This has plenty of other benefits. Priveleged users could be given access to see deleted books. We could allow users to undo a mistaken deletion. We can grant limited privileges to views in our public schema. We can add new columns or even tables and start building out the public interface to books by adding more, rich data without having to modify any of our API code.

Why is this great for a GraphQL endpoint in particular? It gives you control to evolve and develop your schema behind the scenes. You can add feature flags without updating your GraphQL server or accidentally exposing them to your users. As well you continue to benefit from that relational goodness where you need it: foreign key constraints, some of the best index types on offer, and the rest of the PostgreSQL eco-system to boot.

Which brings me to…

Little known PostgreSQL features

It’s these sorts of features that make PostgreSQL my go-to choice for building enterprise and web-services based applications. When people think of an RDBMS I think they mostly see SQL and tables. But there’s so much more than that in PostgreSQL.

One of my recent favourite features is pg_notify. This handy little function allows you to integrate an external process with your database and notify it of various events in the server. For example perhaps we want to send an email out a thank you email to someone who just created a purchase order:

CREATE OR REPLACE FUNCTION update_notify()
RETURNS TRIGGER AS
$$
    BODY
        IF NEW.success = true THEN
            PERFORM pg_notify('purchase_order_successes', json_build_object(
                'id', NEW.id, 'username', NEW.username, 'email_to', NEW.email
            ));
        END IF;
        RETURN NEW;
    END;
$$

CREATE TRIGGER notify_purchase_order_success
AFTER INSERT OR UPDATE ON purchase_orders
FOR EACH ROW EXECUTE PROCEDURE update_notify();

Here we’re using PostgreSQL’s pg_notify function to send a message, encoded as JSON, to an external process after every insert or update to the purchase_orders table. This process can use that data to perform the task of sending out our email.

Of course there’s more you can do with this feature but did you know that PostgreSQL has foreign data wrappers? An HTTP client?

There are an immense number of tools in the PostgreSQL toolkit that make it such a great companion to a GraphQL endpoint. That’s why I started postgra.ph. I love PostgreSQL and I love GraphQL. When you put them together you can finally rapidly develop APIs that will integrate well with your existing ecosystem and scale as your application grows.

If you’re interested in a low-touch, managed PostgreSQL+GraphQL solution for your next project click the link to my project and leave your email. I promise not to spam you! The list will simply notify you when I’ve launched the project so that you can get involved in its development early.

How to use PostgreSQL as a GraphQL backend

Data Driven Design

Choosing the right abstractions

Little known PostgreSQL features