Six Easy Ways to add a GraphQL API to your Postgres Database, comparing Hasura, Prisma and others

K - May 22 '19 - - Dev Community

Cover image by Seth Dennon on Flickr.

PostgreSQL is one of the most popular opensource SQL databases in the world and GraphQL is an emerging API specification growing in popularity every day.

Wouldn't it be nice to integrate the proven and well known PostgreSQL with the new way of API creation that GraphQL brings to the table?

In this article, we talk about six different projects that try to merge the SQL with the GraphQL world. Some of them even allow automatic schema creation based on the database structure.

Database Focused Approaches

The database focused solutions try to eliminate most configuration and schema setup. They will check how our DB looks and provide types and endpoint for us.

Since they know how the database is structured, they can generate high-performing resolvers for us, so we don't run in the N+1 query problem.

1. Hasura

Instant Real-time GraphQL on Postgres

Hasura is probably the most exciting player on the field right now. It's PHPMyAdmin on steroids.

It runs in a Docker container as its server in front of the database and provides us with a management UI for the DB and the API a bit as PHPMyAdmin does.

It comes with its own authentication and authorization features and even integrates with other auth providers.

It's as BaaS as it gets without being hosted service, so if things like AWS AppSync aren't your thing, but you like some its the convenience, go for Hasura.

It's open source under Apache 2.0 license and mostly written in Haskell.

Also, the creators offer paid support plans.

2. Postgraphile

Instantly spin-up a GraphQL API server by pointing PostGraphile at your existing PostgreSQL database

Postgraphile is like Hasura in the way that it allows generating a GraphQL API from a PostgreSQL schema and runs as a server in front of our DB. It just goes into a different direction in how it accomplishes this goal.

It doesn't use a Docker container and tries to re-use as much of the Postgres functionality as possible. For example user management, authorization via RLS and auto-updatable views.

So it's well suited for Postgres pros that have years of experience in setting up and configuring such a DB. They can use all their skills and let Postgraphile do the API work for them.

Postgraphiles also focuses mainly on a CLI to do all the interaction, which is what DB admins probably prefer anyway.

It's an open source product released under MIT license and mainly written in TypeSCript.

The creators also offer a paid pro-version with extra features and paid support.

3. Prisma & GraphQL Nexus

[Updated 2019-05-02]

Prisma replaces traditional ORMs

[Nexus is a] Declarative, Code-First GraphQL Schemas for JavaScript/TypeScript

Prisma is an open-source suite of database tools for the data access (similar to traditional ORMs), migrations and data management.

Developers can use a subset of SDL to define a data model which Prisma maps to their database, simplifying the process of database migrations.

Prisma then generates a type-safe database client that can be used inside your API server. When paired with GraphQL Nexus (a code-first GraphQL schema construction library) and the nexus-prisma integration, developers can take advantage of auto-generated CRUD operations for the database models. This makes it possible to spin up a full GraphQL CRUD API within just a few lines of code!

It is then possible to customize and extend the generated API according to the use cases of your application.

It's open source software licensed under Apache 2.0 and written in Scala.

Prisma also offers a paid enterprise version.

What is Moesif? Moesif is the most advanced REST and GraphQL analytics platform used by over 2000 organizations to measure how your queries are performing and understand what your most loyal customers are doing with your APIs.

Schema Focused Approaches

The next three solutions are a bit more classical in their approaches, they require manual schema creation and don't come with many extras, but they try to help with common pitfalls.

They also require the usage of Node.js since they are regular Node.js libraries.

4. Node.js API Starter Kit

Boilerplate and tooling for authoring data API backends with Node.js and GraphQL

The Node.js API Starter Kit is probably the most basic approach to getting a GraphQL API up and running.

It's a boilerplate project that comes with all the Node.js libraries needed to connect to a Postgres DB, running an HTTP server and create GraphQL schema and resolvers.

It's a good start for greenfield projects that need full control in every part of their API server.

There is no paid support, only free community support.

It's open source with an MIT license and written in JavaScript.

5. graphql-sequelize

GraphQL & Relay for MySQL & Postgres via Sequelize

This is a library that generates GraphQL resolver functions from Sequelize models. We still have to create our schema, but don't have to worry about the resolvers anymore.

It's the right solution for people who already have a vast knowledge of Sequelize and don't want to throw it away.

It's an open source library written in JavaScript and is released under MIT license.

6. Join Monster

It's a function that takes a GraphQL query and dynamically translates GraphQL to SQL for efficient, batched data retrieval before resolution.

Join Monster helps with the GraphQL schema modeling by providing a way to use the full SQL power of Postgres. It allows to tell every GraphQL type which table it belongs to, so it can generate the best SQL query from every GraphQL query.

It's an excellent solution for people who want to build most of the API server on their own but don't want to mess with SQL directly.

Join Monster is open source an released under MIT license. It's written in JavaScript.

Conclusion

There are many different solutions to make a Postgres database accessible via a GraphQL API. Each one with their pros and cons.

If we can't go all in on cloud solutions, the systems listed here allow us to choose how much "hand holding" we want in our API and how much we want to do on our own.

With Hasura and Postgraphile we finally have language-agnostic ways to get things done, which will make many of the non-Node.js developers happy.


Moesif is the most advanced GraphQL Analytics platform. Over 2000 organizations use Moesif to track what their most loyal customers do with their APIs. Learn More


Originally published at www.moesif.com

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .