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.
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.
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…
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.