PostGraphQL

A GraphQL schema created by reflection over a PostgreSQL schema.

The strongly typed GraphQL data querying language is a revolutionary new way to interact with your server. Similar to how JSON very quickly overtook XML, GraphQL will likely take over REST. Why? Because GraphQL allows us to express our data in the exact same way we think about it.

The PostgreSQL database is the self acclaimed “world’s most advanced open source database” and even after 15 years that still rings true. PostgreSQL is the most feature rich SQL database available and provides an excellent public reflection API giving its users a lot of power over their data. And despite being 15 years old, the database still has frequent releases.

With PostGraphQL, you can access the power of PostgreSQL through a well designed GraphQL server. PostGraphQL uses PostgreSQL reflection APIs to automatically detect primary keys, relationships, types, comments, and more providing a GraphQL server that is highly intelligent about your data.

PostGraphQL holds a fundamental belief that a well designed database schema should be all you need to serve well thought out APIs. PostgreSQL already has amazing user management and relationship infrastructure, why duplicate that logic in a custom API? PostGraphQL is likely to provide a more performant and standards compliant GraphQL API then any created in house. Focus on your product and let PostGraphQL manage how the data gets to the product.

For a critical evaluation of PostGraphQL to determine if it fits in your tech stack, read the evaluating PostGraphQL for your project section.

Usage

First install using npm:

npm install -g postgraphql

and then just run it!

postgraphql postgres://localhost:5432/mydb --schema forum --development

For more information run:

postgraphql --help

Benefits

PostGraphQL uses the joint benefits of PostgreSQL and GraphQL to provide a number of key benefits.

Automatic Relation Detection

Does your table’s authorId column reference another table? PostGraphQL knows and will give you a field for easily querying that reference.

A schema like:

create table post (
  id serial primary key,
  author_id int non null references user(id),
  headline text,
  body text,
  …
);

Can query relations like so:

{
  postNodes {
    nodes {
      headline
      body
      author: userByAuthorId {
        name
      }
    }
  }
}

Fully Documented APIs

Introspection of a GraphQL schema is powerful for developer tooling and one element of introspection is that every type in GraphQL has an associated description field. As PostgreSQL allows you to document your database objects, naturally PostGraphQL exposes these documentation comments through GraphQL.

Documenting PostgreSQL objects with the COMMENT command like so:

create table user (
  username text non null unique,
  …
);

comment on table user is 'A human user of the forum.';
comment on column user.username is 'A unique name selected by the user to represent them on our site.';

Will let you reflect on the schema and get the JSON below:

{
  __type(name: "User") { … }
}

{
  "__type": {
    "name": "User",
    "description": "A human user of the forum.",
    "fields": {
      "username": {
        "name": "username",
        "description": "A unique name selected by the user to represent them on our site."
      }
    }
  }
}

UI For Development Comes Standard

GraphiQL is a great tool by Facebook to let you interactively explore your data. When development mode is enabled in PostGraphQL, the GraphiQL interface will be automatically displayed at your GraphQL endpoint.

Just navigate with your browser to the URL printed to your console after starting PostGraphQL and use GraphiQL with your data! Even if you don’t want to use GraphQL in your app, this is a great interface for working with any PostgreSQL database.

Just remember to use the --development when starting PostGraphQL!

Cursor Based Pagination For Free

There are some problems with traditional limit/offset pagination and realtime data. For more information on such problems, read this article.

PostGraphQL not only provides limit/offset pagination, but it also provides cursor based pagination ordering on the column of your choice. Never again skip an item with free cursor based pagination!

Relay Specification Compliant

You don’t have to use GraphQL with React and Relay, but if you are PostGraphQL implements the brilliant Relay specifications for GraphQL. Even if you are not using Relay your project will benefit from having these strong, well thought out specifications implemented by PostGraphQL.

The specific specs PostGraphQL implements are:

• Global Object Identification Specification.
• Cursor Connections Specification.
• Input Object Mutations Specification.

---

Roadmap

In the future, things PostGraphQL will be able to do will include:

• Authentication using PostgreSQL roles.
• Whitelisted queries in production.
• Subscriptions using the PostgreSQL NOTIFY feature.

Evaluating PostGraphQL For Your Project

Hopefully you’ve been convinced that PostGraphQL serves an awesome GraphQL API, but now let’s take a more critical look at whether or not you should adopt PostGraphQL for your project.

PostGraphQL’s audience is for people who’s core business is not the API and want to prioritize their product. PostGraphQL allows you to define your content model in the database as you normally would, however instead of building the bindings to the database (your API) PostGraphQL takes care of it.

This takes a huge maintenance burden of your shoulders. Now you don’t have to worry about optimizing the API and the database, instead you can focus on just optimizing your database.

No Lock In

PostGraphQL does not lock you into using PostGraphQL forever. Its purpose is to help your business in a transitory period. When you feel comfortable with the cost of building your API PostGraphQL is simple to switch with a custom solution.

How is it simple? Well first of all, your PostgreSQL schema is still your PostgreSQL schema. PostGraphQL does not ask you to do anything too divergent with your schema allowing you to take your schema (and all your data) to whatever solution you build next. GraphQL itself provides a simple and clear deprecation path if you want to start using different fields.

Ideally PostGraphQL scales with your company and we would appreciate your contributions to help it scale, however there is a simple exit path even years into the business.

Schema Driven APIs

If you fundamentally disagree with a one-to-one mapping of a SQL schema to an API (GraphQL or otherwise) this section is for you. First of all, PostGraphQL is not necessarily meant to be a permanent solution. PostGraphQL was created to allow you to focus on your product and not the API. If you want a custom API there is a simple transition path (read no lock in). If you still can’t get over the one-to-one nature of PostGraphQL consider the following arguments why putting your business logic in PostgreSQL is a good idea:

1. PostgreSQL already has a powerful user management system with fine grained row level security. A custom API would mean you have to build your own user management and security.
2. PostgreSQL allows you to hide implementation details with views of your data. Simple views can even be auto-updatable. This provides you with the same freedom and flexibility as you might want from a custom API except more performant.
3. PostgreSQL gives you automatic relations with the REFERENCES constraint and PostGraphQL automatically detects them. With a custom API, you’d need to hardcode these relationships.
4. For what it’s worth, you can write in your favorite scripting language in PostgreSQL including JavaScript and Ruby.
5. And if you don’t want to write your Ruby in PostgreSQL, you could also use PostgreSQL’s NOTIFY feature to fire events to a listening Ruby or JavaScript microservice can listen and respond to PostgreSQL events (this could include email transactions and event reporting).

Still worried about a certain aspect of a schema driven API? Open an issue, confident we can convince you otherwise 😉

---

Enjoy this server? Want updates and seeing what I’m up to next? Follow me on Twitter @calebmer.

Thanks and enjoy 👍