Rapid API development using PostgreSQL, GraphQL, and PostGraphile
12 Feb 2019One of the most important aspects to software development is rapid prototyping. This is espeically true for side projects and startups where you have limited time and runway to get an idea fleshed out and figure out what works and what doesn’t. But even estabilished companies can benefit from getting a working prototype or beta out. I always say that a working prototype is worth a thousand meetings.
If what you are building is an app (either web or native), it probably involves writting some kind of api layer. But lately, I’ve been asking my apis an uncomfortable question:
Now, you may be writting really advanced backend api logic with custom algorithms and lasers, but the apis I write mostly take data from a database (almost always PostgreSQL) and return it to the client as JSON. And there’s a lot of boilerplate in the middle to make it all work properly.
Enter PostGraphile. Graphile is a tool that generates a fully working GraphQL API almost entirely from a Postgres database. Postgres does all the things, and Graphile takes advantage of many built in features, many of which you would be using with a traditional ORM, along with some that you probably should be using. But it’s not just for prototyping. It is able to be extended and merged with other GraphQL APIs and extentions, or completely replaced with an ORM that reuses the same schema. Additionally, becuase it’s able to combine the entire API request into a single query, it avoids the typical N+1 query problem that most ORMs suffer from. Even a good ORM will use several queries when prefetching relations.
To get started, all you need is a Postgres database and a few tables. As long as you mark foreign keys as referencing other tables, Graphile will connect them.
For example, let’s create a blog database with users, posts and comments:
CREATE EXTENSION "uuid-ossp";
CREATE SCHEMA app;
CREATE TABLE app.user (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
first_name text NOT NULL,
last_name text NOT NULL,
created_at timestamp NOT NULL DEFAULT now()
);
CREATE TABLE app.post (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id uuid REFERENCES app.user,
title text NOT NULL,
body text,
created_at timestamp NOT NULL DEFAULT now()
);
CREATE TABLE app.comment (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
post_id uuid REFERENCES app.post,
body text NOT NULL,
created_at timestamp NOT NULL DEFAULT now()
);
If you’re on a mac, I recomment installing Postgres.app and Postico. Postico gives you a UI to create a new database, and you can execute raw queries in the “SQL Query” view. Otherwise you’ll need to use the command line.
I’m using UUIDs here because I’m cool, but auto increment (type SERIAL in Postgres) ids would work find too. For the most part this is the exact kind of thing you would create for use with an ORM. The only thing really out of the ordinary here is the schema. By default Postgres uses the implicit public schema (and you can with Graphile too), but by definining our own, we can explicitly expose only what we want to to our api.
You can run PostGraphile with the following command:
npm install -g graphile
npm install -g @graphile-contrib/pg-simplify-inflector
postgraphile --connection postgres:///blog --schema app -j --watch --append-plugins @graphile-contrib/pg-simplify-inflector --enhance-graphiql
There’s a lot of arguments on that command. You can read all about each one in the documentation. In short, you can certainly run graphile on it’s own, but the defaults are tuned to avoid issues when using an existing schema. These options are prefered when you are starting with a new schema from scratch.
You can now open up http://localhost:5000/graphiql and start poking around. Open up the explorer and documentation to see the api that has just been generated:
Let’s try some API mutations:
mutation {
user1: createUser(
input: {
user: {
id: "4BDF29E8-C8DD-489B-A4F9-1FF6DE124DCC"
firstName: "John"
lastName: "Doe"
}
}
) {
user {
id
firstName
lastName
createdAt
}
}
user2: createUser(
input: {
user: {
id: "2152A753-C5AC-4497-930B-4F83FF485F31"
firstName: "Jane"
lastName: "Doe"
}
}
) {
user {
id
firstName
lastName
createdAt
}
}
user3: createUser(
input: {
user: {
id: "2B21542C-6B5A-4C39-9D32-175AB0CB9DF2"
firstName: "Frank"
lastName: "Doe"
}
}
) {
user {
id
firstName
lastName
createdAt
}
}
post1: createPost(
input: {
post: {
title: "PostgreSQL is awesome"
body: "Everyone says so."
authorId: "4BDF29E8-C8DD-489B-A4F9-1FF6DE124DCC"
}
}
) {
post {
id
title
body
createdAt
}
}
comment1: createComment(
input: {
comment: {
body: "I agree."
authorId: "2B21542C-6B5A-4C39-9D32-175AB0CB9DF2"
}
}
) {
comment {
id
body
createdAt
}
}
comment2: createComment(
input: {
comment: {
body: "Of course it is."
authorId: "2B21542C-6B5A-4C39-9D32-175AB0CB9DF2"
}
}
) {
comment {
id
body
createdAt
}
}
post2: createPost(
input: {
post: {
title: "GraphQL is awesome"
body: "It's just obvious."
authorId: "4BDF29E8-C8DD-489B-A4F9-1FF6DE124DCC"
}
}
) {
post {
id
title
body
createdAt
}
}
comment3: createComment(
input: {
comment: {
body: "I wish I could use it."
authorId: "2B21542C-6B5A-4C39-9D32-175AB0CB9DF2"
}
}
) {
comment {
id
body
createdAt
}
}
}
For demonstration purposes I’m hardcoding in user ids, but if you omit them, like we do with posts and comments, Postgres will generate default ones for us.
Now we can start querying what we just created:
{
posts(orderBy: CREATED_AT_ASC, condition: { isPublished: true }) {
nodes {
id
title
body
createdAt
author {
id
firstName
lastName
}
comments {
totalCount
}
}
}
}
That will return all the data we need to show a list of posts, along with their author name and number of comments. All without writting a single line of code or extending our database schema past what we would normally use for our apis.
Notice how the snake case fields like created_at are automatically converted to camel case (createdAt) for the API. Graphile lets you customize these names if you need to, but out of the box it uses very reasonable names.
Notice that we can sort and filter using all of our available columns (created_at and is_published here). Later when you approach release, you can limit these (the most common and easiest way is to limit them to columns that have indexes on them) but for rapid initial developent, you can just leave them all on and trim them back later when you know for sure what you will need.
Notice that we are able to pull the count of comments (limited to those on the individual post) without any additional code.
Here’s what pagination looks like:
{
posts(first: 1, ...) {
nodes {
// ...
}
pageInfo {
hasNextPage
endCursor
}
}
}
Assuming hasNextPage retuns true (I’m using first: 1, so that it will have a second page even for our small data set), you can then call the query again with the endCursor:
{
posts(after: [endCursor], first: 1, ...) {
nodes {
// ...
}
pageInfo {
hasNextPage
endCursor
}
}
}
And keep doing that until hasNextPage is false. If you call it again after that, you will just get 0 results.
Taking it a bit further
When you’re starting a project, you shouldn’t worry too much about small details. Those can be polished out later once you know you’re on the right track. If you try to get too detailed now you’ll just end up needing to redo things. But there are a few thing that will be important early on.
Probably the first step you’ll want to take is to switch from the CLI interface to using a Node script (along with a package.json file with your dependencies):
// index.js
const express = require("express");
const { postgraphile } = require("postgraphile");
const PgSimplifyInflectorPlugin = require("@graphile-contrib/pg-simplify-inflector");
const app = express();
app.use(
postgraphile(process.env.DATABASE_URL || "postgres://localhost/blog?sslmode=disable", "app", {
dynamicJson: true,
appendPlugins: [PgSimplifyInflectorPlugin],
enhanceGraphiql: true,
watchPg: process.env.NODE_ENV !== "production",
})
);
app.listen(process.env.PORT || 5000);
Next, let’s create a computed property on the user type to get the full name. In SQL, add the following function:
CREATE OR REPLACE FUNCTION app.user_full_name(u app.user) returns text as $$
select u.first_name || ' ' || u.last_name
$$ language sql stable;
Using that format (prefixed with the table name and taking a row as an argument) will cause Graphile to treat it as a field. We can query it like so:
{
users {
nodes {
fullName
}
}
}
You’ll probably need some sort of login early on. Unfortunately this is a little complicated using Graphile. The example in the docs has a good walkthrough of how to impliment login and signup just using Graphile. Another option is to use a service like Auth0. The basic jist is that Graphile will parse JWT tokens and make them available within Postgres, as well as set a role to execute queries as.
UPDATE: I’ve written about exactly how you would integrate Auth0: Rapid API Development with PostGraphile and Auth0.
Real world example
On a project I’m working on, I added a “team” table and a “membership” join table that referenced a teamd and an existing individual table, along with a role.
Because I already had a currentIndividual function, I was able to fetch the users list of teams like so:
{
currentIndividual {
memberships(orderBy: CREATED_AT_ASC) {
nodes {
nodeId
team {
id
name
}
}
}
}
}
I’m using nodeId for memberships because they don’t have their own id and Graphile provides a compound globally unique key for free.
For admins, they need to see the entire list of teams, which they can do with this query:
{
teams(orderBy: CREATED_AT_ASC) {
nodes {
id
leaders: memberships(condition: { role: LEADER }) {
nodes {
nodeId
individual {
id
name
}
}
}
memberships {
totalCount
}
name
}
}
}
Notice that I’m able to get the list of leaders by filtering memberships by a particular role, but also get the entire count of all members.
On the team detail page, we want to list the members of the group, which we can do like so:
{
teamById(id: "7bb9db69-551c-4d84-a7f3-f0eef6fa8eda") {
id
memberships {
nodes {
nodeId
individual {
id
name
}
role
}
}
}
}
We can change a members role with a mutation (You can also do this by nodeId):
mutation {
updateMembershipByTeamIdAndIndividualId(
input: {
patch: { role: LEADER }
teamId: "7bb9db69-551c-4d84-a7f3-f0eef6fa8eda"
individualId: "a6446b2d-6f95-4cb3-b96b-b3949109e535"
}
) {
membership {
nodeId
role
}
}
}
And to add an individual to a team, we can use this:
mutation {
createMembership(
input: {
membership: {
teamId: "7bb9db69-551c-4d84-a7f3-f0eef6fa8eda"
individualId: "a6446b2d-6f95-4cb3-b96b-b3949109e535"
}
}
) {
membership {
nodeId
individual {
id
name
}
role
}
}
}
All that without any extra code or configuration.
In order to search teams (and individuals to add to a team), we need something more advanced than the default condition argument. We can add the filter plugin to get a lot more dynamic filter options.
For the UI to find individuals to add to a team, we want to search only users that aren’t in the team already. That’s a little to complicated for something out of the box (even for the filter plugin), but it’s a simple matter of adding a dedicated query function in Postgres, and we can even get the filter plugin options along with all the pagination we get for table queries.
There of course is a lot more to Graphile, especially around authorization (it uses Postgres’ row based security) but with very little code and configuration, you can get going on a project very quickly. And when it is time to release your api into the world, Graphile can scale to the demand.