GraphQL for PostgreSQL - Why?

twitter logo github logo ・1 min read

Hi all,

what is the main advantage of using GraphQL on top of PostgreSQL as for instance Hasura.io? Instead of for instance providing a REST endpoint, which accepts POST-queries with SQL in the body. Seems to be much more straight forward and even more powerful!? Okay, type safety might be missing.

kind regards
Johannes

twitter logo DISCUSS (18)
markdown guide
 

The main advantage is ROCK SOLID schemas that map 1:1 with your database. Another advantage is less code that you need to maintain. The vast majority of backend code nowadays is basically making DB queries and passing them forward. The strategy you're talking about eliminates that need!

Most DB -> graphql engines are pretty intelligent about the kinds of queries that they make, so it's never just a 1:1 map for the query, only for the schema.

Might I recommend Postgraphile (as a great OSS alternative to hasura) + typescript + graphql-codegen?

You get auto generated apollo graphql hooks (for react) AND an auto generated graphql schema from postgraphile.

I have a little example of a "real" project architecture with this on my github

github.com/benbot/the-way-apps-sho...

 

So, it's basically that the user gets a GraphQL interface, which itself maps GraphQL queries to predefined SQL queries for performance reasons and because it might be in some cases easier to write GraphQL queries :-)

I'm thinking about adding a QraphQL endpoint for a temporal document store (Open Source) of mine, to hardcode really performant queries as I haven't had the time to add cost based optimizations or any kind of AST rewrites, which directly affect the physical aspects of my particular storage engine to the query processor (but the basic query compiler is very performant, as it's set oriented instead of tuple at a time...) :-)

I could use the Vert.x implementation, however I first want to build the front-end now.

 

Probably I would map this to storage primitives and in my case axis to navigate to the desired nodes.

So with postgraphile, specifically, it could either create a graphql schema (not queries) or provide a graphql api with that generated schema.

The dev would need to write their own graphql queries, but depending on the queries and the methods being called in them, postgraphile (the server, not the generated schema alone) would intelligently create speedy SQL queries UNLESS the dev defined specific functions for some kind of queryable data.

Postgraphile will generate query schema definitions for any tables in the schema that you give it access to. It can do that with mutations too, but you'll usually just want to write all of those yourself.

I really encourage you to give it a try with a toy DB, it's tough to explain how impressive it is (or how useful I find it) over comments on a post :(

I asked specifically because in the future I might want to build something like that for my own data store/storage engine, which however is based on semi-structured data (binary representation of XML and JSON) and which deduplicates the data naturally and stores an index into each revision... might be interesting, as I can map the fetching of the data probably much more easily to efficient storage operations as for complicated XQuery/JSONiq queries (which seems to be even more complicated regarding all the stuff which is possible in comparison with SQL).

 

At first, I didn't understand the question, because the point of graphql is to choose your data selectively... but then I realized that you want to send SQL queries to a single endpoint. And then I was confused! What is indeed the point of graphql (on a relational database, at any rate)?

It seems that the simplest reason is that graphql abstracts away the database, while SQL works only for querying the database directly. See here.

 

Hm, I'm not completely convinced that it's adding a lot. I think SQL is the main query language for relational databases, and why restricting its power? :-) Basically each relational database driver exposes a JDBC API, which more or less wraps pure SQL queries.

 

Agreed, if it is wrapping the database directly, as opposed to a service that might transform the database, I'm not sure how much it would add.

depending on the tools, you're not restricting the power of the DB, you're adding a thin and intelligent HTTP based layer on top of your DB.

Read into the docs (and code if you're into that) for postgraphile. They generate VERY efficient sql queries AND you can write query and mutation resolvers in postgresql so you maintain all the power of the database while getting a very nice graphql api to work with.

Another reason you may want a graphql layer on top of your DB would be for more control over your response payloads. You could get as little data as you need, which is a BIG win for mobile apps who's users have pretty small data caps.

Can you point me to the source where they generate the SQL queries?

I haven’t dug too deeply into postgraphile’s code, but I believe this is where they build their queries.

github.com/graphile/graphile-engin...

 

You don't want anyone to be able to execute any code on your server.
If I'm able to send you any sql query to execute, I can perform anything I want. For instance I could send "DELETE * FROM USERS;" or anything similar

 

For sure there needs to be authentication and authorization on the database beforehand. There are also REST interfaces :-)

 

Yes sure, but even if the user is authorized, I don't think we should simply let him run any query of his choice directly on the database.
RESTful services are, to me, a good way of orchestrating backend calls, and it has its pros and cons versus GraphQL.
To be honest I don't know GraphQL enough though !

Most db -> graphql layers (like hasura or postgraphile) respect and/or even take into account the security policies and roles of the db when generating the graphql schema (basically the list of types, queries, and mutations that you can use to build graphql queries).

A graphql query (basically a GET request) or mutations (basically a POST request) would never simply be mapped to a raw sql query.

 

A single REST endpoint that accepts query text sounds, to my very limited understanding of GraphQL, like all of its drawbacks with none of its advantages. Authentication and authorization won't protect you against malformed or mistaken queries, and even a read-only case with the appropriate database permissions can come up with some pretty wild query plans. The comparison is more between a REST API and the Hasura/Prisma/Postgraphile set. It's certainly more convenient to point one of those at your database if you're already using GraphQL; if you aren't, I don't think covering a single database is, on its own, a compelling argument to start. @benjie care to make a case for? :)

 

The thing is not REST vs GraphQL... more like why GraphQL if you already have SQL.

It seems to be an abstraction to limit what's possible for users, more or less. That said, when a user has the rights, it's his responsibility to write queries, which perform well (and so on) or which do what he intents to do (and the DBMSes responsibility). So basically I guess the question is more like why GraphQL if you have JDBC for instance, or something along these lines.

 

A few reasons:

Some engines can generate very efficient SQL from your graphql. Since you don't need to worry about performance while writing graphql queries, you should get generally faster queries without having to hand optimize queries.

There are countless security issues when sending raw SQL strings from an endpoint into a database (see the damage a basic SQL injection attack can cause), so that's just not really an option anyway. GraphQL is almost much more flexible than REST, so you may prefer Graphql in this case.

Graphql provides type safety.

You could possibly eliminate backend code. Why write an API by hand when you could just generate an entire api based on your database schema? That alone is a reason to use something like hasura or postgraphile IMHO

DB -> graphql gives you the power of SQL queries, but without having to worry (as much) about performance, compatibility (everything is compatible with HTTP), or HTTP level security.

Classic DEV Post from Sep 1

Working as a freelancer

What is/was your experience working as a freelancer?

Johannes Lichtenberger profile image
I'm working on an Open Source temporal NoSQL document storage system called SirixDB written in Java (and a module in Kotlin) in my spare time.