Pg_GraphQL: A GraphQL Extension for PostgreSQL

thanks Ajay, we're also big fans of yours here at Supabase :)

What's your company?

I don't think Sam is wrong, exactly -- and that second thread does add some of the missing nuance -- but I do think there's a key missing piece here.

He's absolutely right that you shouldn't couple directly to the underlying representation. But Postgres lets you transparently define views that can be queried (and, with a little more elbow grease, updated) just like any other table. You can provide decoupling from within the database, and do so on-demand as your domain and your data model evolve.

I don't enjoy planting a separate bespoke API server on top of the database. Usually you end up lifting many of the same capabilities the database already has (auth, batching, ...) to your custom API, so a lot of the server is just boilerplate. Many API operations are natural consequences of your data model; there's little business or engineering value-add once you've settled on the latter, you're just writing glorified FFI bindings.

Lazy engineering will cause problems no matter what architectural stack you end up using. But a state-first architecture doesn't have to mean a complete loss of loose coupling -- it just means different techniques for achieving it.

We could use PG views or functions to make our own abstraction layer inside the database and handle security better, etc. But putting that complicated database rebuild aside, just because we can publish GraphQL APIs from the database doesn’t mean we have to: it would be interesting to use GraphQL as a communications layer from backend to database too. Just as frontend-to-backend could use GraphQL, so too could backend-to-database communication, but with different schemas (data models) between frontend and backend. Having a backend GraphQL server between the frontend and database could allow for connection pooling, schema versioning and abstraction, for proxying multiple databases, handling business logic and perhaps validating inputs, caching or managing compute tasks.

For front-end development where you already using GQL as a layer on top of content I can see the use case for this.

A lot of old systems have this tight coupling between databases and back-end code. I would advice against going down that path.

GraphQL was designed to solve the flaws in the utterly poor service design at Facebook. Do not forget that.

I think that (on paper at least) it makes perfect safe to allow graphql to be your only hammer (or turtle). I don't think federating graphql over bespoke swahger/json, sql, redis, soap is inherently better - expose graphql and federated graphql:

https://www.apollographql.com/docs/federation/v2/

Smack graphql on your postgres, and on your ldap, and your graphdb - and federated them as graphql?

Hopefully so. It is awesome that supabase is releasing this as open source, as they could have easily kept this proprietary. I probably won't ever be able to use supabase for my primary job (the company is obnoxiously invested in an Azure/AWS hybrid cloud). But I do have some profitable side projects with the ability to choose my own service providers, and this sort of developer goodwill really goes a long way.

It is nice Hasura offers it as well via the /v1beta1/relay endpoint[1].

[1] https://hasura.io/docs/latest/graphql/core/databases/postgre...

The article suggests they built it so that by running it within the DB it would have less compute overhead and they could match GraphQL requests to SQL transactions 1:1.

its very similar in goals to postgraphile

as samwillis mentioned, the memory footprint is tiny, which is a big perk for supabase's platform (or if you're self hosting) but its also fully language agnostic which opens up lots of options for extensibility:

For simple use-cases you can expose the graphql functionality over http using a PostgREST as described here https://supabase.github.io/pg_graphql/quickstart/

but, if you want more configuration like adding in middleware or wiring it up to an existing backend application, you can do that from any programming language that can connect to postgres, rather than only javascript

I had to make sure someone had plugged postgraphile here. It's a great system. What impresses me most about it is how it grows with the complexity of your application. There are so many thoughtful points of extensibility built in, I always have confidence that I can go into the docs and find a way to do what I need to.

That reminds me, I need to figure out how to get my employer to sponsor the project...

> Many apps I know simply have 1 user "myappuser" (or even default user) to access its DB.

Sure; either those apps don't need to differentiate access between their users, in which case one role is sufficient, or they reimplement their own auth system, in which case you'd use Postgres' own rather robust auth system instead. It comes down to the needs of the domain; you'll solve the problems differently depending on what approach you take, but you need to solve the same problems.

Yes -- I've found it very tiring, as you put it, to keep reimplementing the same boilerplate in every API server just to lift the operations my database can already support out to an HTTP frontend. Postgres' auth means I don't have to make or press into service a separate auth system, and there are multiple ways to handle business logic orthogonally.

Stored procedures and triggers work well, but are synchronous within the current transaction, and sometimes simply don't map well to the domain needs. You can also use the AWAIT and NOTIFY statements to set up asynchronous external workers. I find this has a positive effect on the data model, as you're forced to consider what states a system will pass through during an asynchronous flow.

Ideally you don't expose the schema directly, you make views in another schema and expose that instead of exposing your real tables.

> After tallying the resources reserved for PostgreSQL, PostgREST, Kong, GoTrue, and a handful of smaller services, we were left with a total memory budget of ... 0 MB

This was a really interesting bit to me, could you give detail on how the memory usage gets split up across these? Thank you.

Very excited about!

Do you have performance comparisons for the same datasets with Hasura / Graphile?

just want to give a big shout out to Oli - his implementation really ingeneous, leveraging multiple parts of the Supabase stack while also being agnostic enough to work natively inside Postgres (or with other tools)

{supabase team}

Congrats on release. We use Hasura but haven’t been happy with their speed of iteration, lack of communication of various large bugs, and lack of M1 support / communication.

But we do use a lot of their more advanced features like being able to use aggregates in sorts, aggregates in results, custom functions, etc. What are your plans there and will you have a public roadmap?

So you're calling Graphql via PostgREST, but I didn't ready about why you're including Graphql in the first place. Isn't it orthogonal to PostgREST which is already used in your stack?

I've been looking for something like this for a long time, so cool! Great work!

Does it work with TimescaleDB?

how do you implement search/sort against something like this?

Does this work in YugabyteDB/CockroachDB?

We use Postgraphile heavily, it drives the entirety of our API. A few feature questions; Is there any plan to support, or has there been any conversation around:

- custom directives that run custom code

- custom routes on the server that do custom things? Or perhaps proxying to another app to handle alternate/custom routes

- schema injection, custom resolver logic

Hi Oliver, Supabase team,

Love what you are doing with Supabase!

Quick question, have you considered building any kind of local mirroring system for offline mobile app/PWA, say on top of SQLite? Something like Realm for mongodb or PouchDB/Couchbase Lite for CouchDB/Couchbase.

It would obviously need devs to add some extra columns to tables for tracking, and a way to define the merge/overture characteristics for each column, but it would be awesome to have something like that!

It’s something I have thought about building for a while but never found the time. (I want to combine it with Yjs for collaborative offline rich text editing)

Am I correct in saying that for read queries, you essentially translate the GraphQL query into nested jsonb_agg calls?

They just released a free course you might like: https://egghead.io/courses/build-a-saas-product-with-next-js...

Currently, any datatypes that are not in the allow list

https://supabase.github.io/pg_graphql/reflection/#type-conve...

are cast as strings, but prioritizing a JSON conversion if one is available is a great idea that we'll look into

They mention in the article that auth is handled by reusing the existing row level security you'd already use to secure the DB (i.e. `CREATE POLICY`).

The extension is compatible with your existing row level security policies. If you connect to the database as a role like `authenticated` then those policies will be applied.

The columns and tables that are visible are also controlled by the role.

One cool thing about that approach is you could run e.g. an admin API and a user facing API all from that same endpoint by executing as different postgres roles!

As the sibling comments point out, it uses Postgres Row Level Security (RLS). For an approachable introduction to how Supabase's auth works:

https://supabase.com/docs/guides/auth

> 1. A user signs up. Supabase creates a new user in the auth.users table.

> 2. Supabase returns a new JWT, which contains the user's UUID.

> 3. Every request to your database also sends the JWT.

> 4. Postgres inspects the JWT to determine the user making the request.

> 5. The user's UID can be used in policies to restrict access to rows.

> Supabase provides a special function in Postgres, auth.uid(), which extracts the user's UID from the JWT. This is especially useful when creating policies.

For instance, say you have a `todos` table and want to make it so users can only read their own todos - you could have an RLS policy `todos.user_id = auth.uid()`. Afterward, `SELECT * FROM todos` will only return the authenticated user's todos. (Equivalent to manually issuing `SELECT * FROM todos WHERE todos.user_id = auth.uid()`.)

There's also `auth.role()` so you can easily restrict access by role: `auth.role() = "admin"`

> The whole idea of the API layer is to be able to make changes to your internals without breaking all your consumers.

You can do so by not exposing tables and instead use views and stored functions/procedures.

See https://postgrest.org/en/v9.0/schema_structure.html#schema-i...

Very cool! I was not familiar with this project. It does takes a similar approach to query building. It is described in the README as an alpha POC

GraphpostgresQL is no longer maintained and hasn't been updated for many years.

I am the person who wrote it. It was a proof-of-concept, written in PL/pgSQL. This made it fairly easy to set up and test but made maintenance and contributions very difficult.

> It would take a lot for me to switch.

We were pretty happy with Hasura but had to switch to Postgraphile due to poor multi-database support, bummer.

(Postgraphile is not as polished as Hasura in some ways, but since it can used as a library, it's easy to dynamically create N instances of it with different configurations at runtime. Hasura required our ops team to define a new instance of the service in the docker-compose.yml file for each database)

Security definers are a powerful feature of postgres that allow a lot of business logic to live in the database. With an extension like this, I reckon postgres can theoretically become a first class public api for a surprising number of use cases.

An extension is the best next thing to native support and in my opinion a better place for such functionality.

On the contrary, this could scale much better.

If the GraphQL server is a separate component, then a GraphQL query needs to do its own query planning, then the resolvers turn that into SQL queries to the database, then the database does its own query planning and execution for each query.

Since one GraphQL query will often turn into multiple SQL queries, there's likely to be duplicated work on the database side across those queries since they relate to the same data.

By integrating the GraphQL server into the DBMS, it can do query planning once for the whole GraphQL query, which means that it can reuse parts of the plan and prevent duplicated work and N+1 queries.

Either way it's going to have to query the database to get all the data, so the essential work is the same. But this way you have opportunities to reduce wasted work to process the whole GraphQL query.

Most likely the actual work done by this extension is little compared to the actual work required to perform the queries…but…

If you want to scale horizontally you can run multiple read replicas with something which distributes connections evenly to the replicas.

Yes, event triggers are built right into Postgres so they're supported out-of-the-box

Event triggers are the mechanism pg_graphql uses to keep the GraphQL schema up-to-date with the SQL schema

Apache Age is based around the openCypher graph query language rather than GraphQL