Six Easy Ways to add a GraphQL API to your Postgres Database, comparing Hasura, Prisma and others
PostgreSQL is one of the most popular open source 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 2021-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 data access (similar to traditional ORMs), migrations and data management.
Developers can use a subset of SDL to define a datamodel 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 Thousands of platformsto 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 a 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.
Related Articles
API Development
The Ultimate Guide to REST API Naming Convention: Best Practices for Clarity & Consistency
Discover the art of crafting perfect REST API endpoints, where consistency and simplicity transform complex web development into a seamless experience. Learn...
API Development
API for Dummies: Your Easy Guide to Understanding APIs
This beginner-friendly post explains the basics of API and how they create amazing experiences through the apps and services you daily use.
API Development
Step-by-Step Blueprint to Create RESTful APIs for Efficient Web Services
Learn from server setup to securing data, ensuring your APIs are scalable and efficient.Dive into the details for a comprehensive understanding of designing,...
API Development
The AI API Gold Rush: How to Turn Your Models into Revenue With Moesif
Monetize your AI model by transforming it into an AI API, creating new revenue streams by integrating your AI capabilities into other applications. This guid...