GraphQL + PostgreSQL: Need an ORM or just query builder?

Question

We are about to start a big project, with several tables and relationships, we have to expose an API to consult the data. GraphQL seems like a great idea and we want to use it with PostgreSQL, my main question is whether we should use an ORM (like wetland) or just a query builder/helper (like knex). What do you think?

Answer 1

I'd recommend looking at PostGraphile (formerly PostGraphQL) - it's an entirely plugin-based solution which automatically builds a GraphQL API based on your PostgreSQL database by inspecting the tables, columns, relations, functions, etc. It supports GraphQL best practices such as connections for pagination, globally unique object identifiers and Relay mutations. It has a lot of features out of the box, and also allows easy extension by defining computed columns, custom queries and custom mutations inside your SQL database or by adding plugins.

PostGraphile solves the N+1 problem that's common in naïve SQL-based GraphQL APIs by looking ahead at the query AST. Instead of generating hundreds or even thousands of individual SQL queries to serve complex GraphQL requests, PostGraphile compiles the required SQL fragments down to just one query for each root-level field requested.

You can try it out to see what you think; even if you don't end up using it, maybe it can help you to figure out how to structure your GraphQL API 👍

With npx (bundled with Node) you can just run this one command for a temporary install:

npx postgraphile -c postgres://user:pass@host/dbname

or if you prefer you could install it globally and run it like this:

npm install -g postgraphile
postgraphile -c postgres://user:pass@host/dbname

(If you're not using the public schema in PostgreSQL, specify the schema name(s) you want to use via --schema schema_name1,schema_name2)

NOTE: I'm the current maintainer of PostGraphile.

Answer 2

ORM vs Query builder

I would recommend using a query builder and not an ORM because with GraphQL it's easy for clients to query for nested information. The effort spent in optimising the ORM portion of your code will not be required if you were using a query builder based approach from the get go.

massive.js is probably an even better fit that knex for this kind of use case.

• ORM pros: Migration tooling, easy to write code
• ORM cons: Painful when optimising for nested queries, custom SQL
• Query builder pros: Easy to map to efficient SQL queries
• Query builder cons: No "objects" modelling that might make writing code easier

Instant GraphQL on Postgres

We also recently open-sourced our GraphQL engine which gives you instant GraphQL on a Postgres database as-is. The GraphQL endpoint is safe to be queried from apps directly because of an access control layer. This access control layer can integrate with any auth system (auth0, custom, forgerock etc.).

It is also performance optimised for making real-life nested queries fetching non-trivial amounts of data because we compile GraphQL queries to a single SQL query (instead of resolving them) which results in you being able to get a throughput of over a 1000 req/s at around 50-60MB of memory usage.

Hasura comes with optional rails-inspired migration tooling and an admin UI as well in case you don't have an existing migration system.

Answer 3

My recommendation is to use Nexus.

In combination with Prisma to manage database operations, it's a very nice way to build GraphQL servers in a fully type-safe way.

Disclaimer: I work at Prisma

Answer 4

I'm dealing with the same question at the moment. I think graphql is itself some kind of ORM in terms of defining relationships between data models. The second part of the ORM, building SQL queries behind the scenes, is something which I want to have under my control when I provide to frontend developer a freedom for constructing potentially complex graphql queries which can be resource heavy. The last building block of an ORM (broadly speeking), is data model validation. Graphql already has some validation rules included (i.e. if a query is valid against the schema). For more complex data validation it is possible to use any javascript object validation library.

So, I think using ORM with graphql is totally viable option and I assume lots of developers would go on this way. But for me graphql presents a vector which can easily tear down the server if it's not designed properly. And for this you need to know what is going on behind the execution of a beautiful graphql query without the catchy ORM fog :)