The Quickest Way to Build PostgreSQL GraphQL APIs—PostGraphile
PostGraphile generates powerful, secure and fast GraphQL APIs very rapidly.
In previous iterations, PostGraphQL by Caleb Meredith was one of the most popular library for connecting GraphQL APIs and PostgreSQL Databases. This library has seen major changes while transitioning from version 3 to Graphile in v4. This article shows you how to turn your PostgreSQL database schema into an GraphQL API automatically with the updated library - PostGraphile.
With PostGraphile just coming out of its beta period - v4 Release Candidate 1
We will first build a GraphQL API automatically with this library and then a brief overview of what has changed in the new v4.0.0-rc.1 and then finish things off with a brief overview of similar offerings from Prisma and Hasura.
How to Connect GraphQL and PostgreSQL Automatically using PostGraphile (Example)
Let us take an example of a PostgreSQL database for a blog.
To keep things simple, our database schema has three tables:
- Users
- Posts
- Comments
First make sure Postgres is installed and running locally and then connect to the default db.
psql --version
psql
You should be able to connect to the default database:
psql (9.6.x)
Type "help" for help.
postgres=#
The following creates a database called blog and switches to it.
Type "help" for help.
psql (9.6.7, server 9.4.13)
postgres=# CREATE DATABASE blog;
CREATE DATABASE
postgres=# \q
Switch to the new blog database.
$ sudo -u postgres psql -d blog
Type "help" for help.
psql (9.6.7, server 9.4.13)
blog=#
Before firing up Postgraphile, you will need a new role (I am creating a blog admin but feel free to create whatever you want), exit the blog database and restart PostgreSQL once the role is created. See \h CREATE USER for options.
blog=# CREATE ROLE SUPERUSER WITH PASSWORD 'admin';
CREATE ROLE
blog=# GRANT ALL PRIVILEGES ON DATABASE blog TO admin;
GRANT
blog=# ALTER ROLE ADMIN WITH LOGIN;
ALTER ROLE
blog=# \q
$ sudo systemctl restart postgresql
The following SQL code builds the PostgreSQL schema:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name text NOT NULL,
email text NOT NULL UNIQUE,
password text NOT NULL
);
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
title text NOT NULL,
content text NOT NULL,
published BOOLEAN NOT NULL DEFAULT FALSE,
author_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE
);
CREATE TABLE comments (
user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
post_id INTEGER NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
content text NOT NULL,
PRIMARY KEY (user_id, post_id)
);
If you had build the schema before creating a new role, you will get ERROR: permission denied for relation.
GRANT on the database is NOT what you need. GRANT on the tables directly, and the sequences etc.
$ sudo -u postgres psql -d blog
[sudo] password for user:
psql (9.6.7, server 9.4.13)
Type "help" for help.
blog=# grant all privileges on table users to admin;
GRANT
blog=# grant all privileges on table posts to admin;
GRANT
blog=# grant all privileges on table comments to admin;
GRANT
blog=# \q
$ sudo systemctl restart postgresql
Next we need to install Postgraphile.
$ sudo npm install -g postgraphile
/usr/bin/postgraphile -> /usr/lib/node_modules/postgraphile/build/postgraphile/cli.js
+ postgraphile@4.0.0-rc.1
added 94 packages from 52 contributors in 8.398s
The following command checks if Postgraphile is correctly installed (and gives you connection details to get started at the end).
$ postgraphile --help
# You may need to set the environment variables as described in your PostgreSQL version
# https://www.postgresql.org/docs/9/static/libpq-envars.html
Mine was 9.x
$ psql --version
psql (PostgreSQL) 9.6.7
You can now connect postgraphile to your database with postgraphile -c postgres://user:pass@localhost/db.
$ npx postgraphile -c postgres://admin:admin@localhost/blog
PostGraphile server listening on port 5000
‣ Connected to Postgres instance postgres://localhost:5432/blog
‣ Introspected Postgres schema(s) public
‣ GraphQL endpoint served at http://localhost:5000/graphql
‣ GraphiQL endpoint served at http://localhost:5000/graphiql
Our GraphQL API is now ready at http://localhost:5000/graphiql. Have a look see!.
Alas! All the Queries and Mutations have been automatically generated for us.
PostGraphile uses your PostgreSQL schema and Introspection to automatically generate your GraphQL schema.
You can inspect the docs to see what Queries and Mutations that have been automatically generated. We can try something from the schema.
The following query tries to get the total count of all users.
query {
allUsers {
totalCount
}
}
...which we haven't created none yet so you'll get back.
{
"data": {
"allUsers": {
"totalCount": 0
}
}
}
Let's try to create a user.
mutation {
createUser (
input: {
user: {
name: "Foo",
email: "foo@example.com",
password: "123456",
}
}) {
user {
id
name
email
password
}
}
}
{
"data": {
"createUser": {
"user": {
"id": 1,
"name": "Jack",
"email": "jack@example.com",
"password": "123456"
}
}
}
}
You might need a GRANT sequence is you created your db role after building your schema. This following fixes Error: permission denied for sequence users_id_seq when posting user mutations - same for comments.
$ sudo -u postgres psql -d blog
[sudo] password for user:
psql (9.6.7, server 9.4.13)
Type "help" for help.
$ sudo -u postgres psql -d blog
blog=# grant all on sequence users_id_seq to admin;
GRANT
blog=# grant all on sequence posts_id_seq to admin;
GRANT
blog=# \q
Note that if we try to create a duplicate,the PostgreSQL Primary Key Unique Constraint kicks in.
We can now query our users again.
query {
allUsers {
totalCount
}
userByEmail (
email: "jack@example.com"
) {
id
name
email
}
}
We get back the following, as expected:
{
"data": {
"allUsers": {
"totalCount": 2
},
"userByEmail": {
"id": 1,
"name": "Foo",
"email": "jack@example.com"
}
}
}
Neat!
PostGraphile Authentication with JWT: Securing your GraphQL Express Server
PostGraphile recommends using PostgreSQL 9.5+. This is because at the beginning of 2016, v9.5 introduced much more granular permissions with Row-Level Security (RLS) policies.
RLS permissions together with role-based permissions in table and columns create the much needed granular constraints which are critical for security at the lowest level—the data.
To use PostGraphile with Javascript Web Tokens, we MUST supply a --jwt-secret on the CLI (or jwtsecret to the library option).
We issue PostGraphile a secret key and custom PostgreSQL data type, PostGraphile in turn encodes the content as a JWT token and signs it before returning it. We also need to supply a --default-role that is used for requests that don't specify one.
JWT for Sessions: A Quick Side Note
There is strong sentiment for using JWT for session management—as opposed to cookies.
More accurately, a lot of people think JWT tokens are more secure because they use cryptography and because 'they are not sent as cookies'. Cookies are just HTTP headers and there is nothing inherently insecure about them. Every session implementation, including JWTs, can be easily intercepted without TLS. PostGraphile does not impose JWT for session management.
PostGraphile JWT Authentication Example
$ sudo -u postgres psql blog
[sudo] password for user:
psql (9.6.7, server 9.4.13)
Type "help" for help.
blog=# create role guest;
CREATE ROLE
Currently, my database has only two users.
The following creates a guest role.
blog=# CREATE ROLE guest;
CREATE ROLE
blog=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
admin | Superuser | {}
guest | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication | {}
The following creates a new data type for our JWT tokens. See \h CREATE TYPE for options.
CREATE TYPE jwt_token AS (
role TEXT,
user_id INTEGER,
name TEXT
);
The role in the jwt_token is for setting the PostgreSQL roles.
Then we need two PL/pgSQL functions: SIGNUP and SIGNIN. PL/pgSQL groups block computations for a series of queries inside the database server reducing extra roundtrips and eliminating multiple rounds of query parsing.
These two functions return the jwt_token type which PostGraphile will translate into a JWT.
The following creates the PL/pgSQL SIGNUP function. See \h CREATE FUNCTION for options.
CREATE FUNCTION SIGNUP(username TEXT, email TEXT, password TEXT) RETURNS jwt_token AS
$$
DECLARE
token_information jwt_token;
BEGIN
INSERT INTO users (name, email, password) VALUES ($1, $2, crypt($3, gen_salt('bf', 8)));
SELECT 'admin', id, name
INTO token_information
FROM users
WHERE users.email = $2;
RETURN token_information::jwt_token;
END;
$$ LANGUAGE PLPGSQL VOLATILE SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION SIGNUP(username TEXT, email TEXT, password TEXT) TO guest;
And here is my CLI output:
blog=# CREATE FUNCTION SIGNUP(username TEXT, email TEXT, password TEXT) RETURNS jwt_token AS
blog-# $$
blog$# DECLARE
blog$# token_information jwt_token;
blog$# BEGIN
blog$# INSERT INTO users (name, email, password) VALUES ($1, $2, crypt($3, gen_salt('bf', 8)));
blog$# SELECT 'admin', id, name
blog$# INTO token_information
blog$# FROM users
blog$# WHERE users.email = $2;
blog$# RETURN token_information::jwt_token;
blog$# END;
blog$# $$ LANGUAGE PLPGSQL VOLATILE SECURITY DEFINER;
CREATE FUNCTION
blog=# GRANT EXECUTE ON FUNCTION SIGNUP(username TEXT, email TEXT, password TEXT) TO guest;
GRANT
The following creates the PL/pgSQL SIGNUP function:
CREATE FUNCTION SIGNIN(email TEXT, password TEXT) RETURNS jwt_token AS
$$
DECLARE
token_information jwt_token;
BEGIN
SELECT 'admin', id, name
INTO token_information
FROM users
WHERE users.email = $1
AND users.password = crypt($2, users.password);
RETURN token_information::jwt_token;
END;
$$ LANGUAGE PLPGSQL VOLATILE STRICT SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION SIGNIN(email TEXT, password TEXT) TO guest;
And here is my CLI output:
blog=# CREATE FUNCTION SIGNIN(email TEXT, password TEXT) RETURNS jwt_token AS
blog-# $$
blog$# DECLARE
blog$# token_information jwt_token;
blog$# BEGIN
blog$# SELECT 'admin', id, name
blog$# INTO token_information
blog$# FROM users
blog$# WHERE users.email = $1
blog$# AND users.password = crypt($2, users.password);
blog$# RETURN token_information::jwt_token;
blog$# END;
blog$# $$ LANGUAGE PLPGSQL VOLATILE STRICT SECURITY DEFINER;
CREATE FUNCTION
blog=# GRANT EXECUTE ON FUNCTION SIGNIN(email TEXT, password TEXT) TO guest;
GRANT
Now that both functions are now created, we need to issue PostGraphile with a secret key and the name of the token type:
$ npx postgraphile -c postgres://admin:admin@localhost/blog \
> --watch \
> --jwt-token-identifier public.jwt_token \
> --jwt-secret [ INSERT RANDOM STRING HERE ] \
> --default-role guest \
> --show-error-stack
The following uses the signup mutation to create a new user.
mutation {
signup (
input: {
username: "Jill",
email: "jill@example.com",
password: "123456"
}) {
jwtToken
}
}
{
"data": {
"signup": {
"jwtToken": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYWRtaW4iLCJ1c2VyX2lkIjo2LCJuYW1lIjoiSmlsbCIsImlhdCI6MTUzMTU4MzUxMCwiZXhwIjoxNTMxNjY5OTEwLCJhdWQiOiJwb3N0Z3JhcGhpbGUiLCJpc3MiOiJwb3N0Z3JhcGhpbGUifQ.6aw1H2jlDUZmRxfHFM3hOGUv100L_iOHcQuVJJfVuMk"
}
}
}
If it complains that "function gen_salt(unknown, integer) does not exist", add and test PostgreSQL's pgcrypto extension as follows:
$ sudo -u postgres psql blog
[sudo] password for user:
psql (9.6.7, server 9.4.13)
Type "help" for help.
blog=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION
blog=# select gen_salt('bf');
gen_salt
-------------------------------
$2a$06$tovq59uSUjecELl/5aVCpe
(1 row)
blog=# \q
Let's try to confirm whether our new user Jill was created successfully:
query {
userByEmail (
email: "jill@example.com"
) {
id
name
email
}
}
We get back permission errors because we need to be signed to access this:
{
"errors": [
{
"message": "permission denied for relation users",
"locations": [
{
"line": 2,
"column": 3
}
],
"path": [
"userByEmail"
],
"stack": "..."
}
],
"data": {
"userByEmail": null
}
}
We get errors when we try to query anything else. We will come back to this later.
We can use the newly created token to log in as follows:
With tokens, we now how a clear way of distinguishing guests and admins.
We can now assign some permissions to guest users as follows:
$ sudo -u postgres psql blog
[sudo] password for user:
psql (9.6.7, server 9.4.13)
Type "help" for help.
blog=# GRANT SELECT ON posts TO guest;
GRANT
blog=# \q
Now guest should be able to see posts.
PostGraphile vs Prisma vs Hasura
PostGraphile, Prisma and Hasura use different approaches for implementing a GraphQL abstraction layer for databases.
A key reason behind the v4 rewrite of PostGraphQL to PostGraphile was, clearly, a performance amp. PostGraphile builds a powerful, extensible GraphQL API from PostgreSQL schema in seconds but Prisma abstracts your PostgreSQL database into a GraphQL API with realtime capabilities.
Most services requiring PostgreSQL as a database layer heavily use CRUD operations. Implementing and keeping CRUD interfaces up to date is tedious back-breaking work. This is where both Prisma and PostGraphile come in handy.
As luck would have it, Prisma and PostGraphile have been returning performance benchmarks for both simple and complex queries.
(Lower and to the right is better.) 95th precentile latency comparison of Prisma (blue), current PostGraphile (purple), and the next version of PostGraphile (postgraphile@next, pink). Prisma stays under 50ms latency up to 250 requests per second (rps); current PostGraphile reaches 900rps without breaching 50ms latency; but the next version of PostGraphile kicks the ball out of the park with an astounding 1450rps!! - Benjie GillamThis benchmark by Benjie Gillam shows that PostGraphile (beta) and PostGraphile v4.0.0-rc.1 (@next) are both way faster that Prisma for complex queries.
Prisma benchmarks on the other hand indicate that its faster than PostGraphile for simple queries (the other end of the spectrum).
In fact, both Hasura and Prisma have recently released benchmarks comparing their GraphQL offerings to PostGraphile!