35 ms·

Pg_GraphQL: A GraphQL Extension for PostgreSQL

431 points by samwillis 5y ago

IceDane 5y ago
While this is super neat, this and the amplify thread can't help but make me wonder: Is everyone really this okay with their API always being 1-to-1 with their DB models? In my experience, that kind of setup is only viable for the smaller, simpler projects and otherwise you always run into something where you'd really prefer to have a layer between you and your database. I am currently using graphql at work and this is a very hard requirement for us. Our database schema is not translated literally into graphql(or the other way around) and this is very intentional. The whole idea of the API layer is to be able to make changes to your internals without breaking all your consumers. This was a problem with amplify(though the least of the problems we had) and it seems to me this is also a problem here, as it is with Hasura and postgrest.
- deleted 5y ago
  [deleted]
- steve-chavez 5y ago
  > 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-isolation https://postgrest.org/en/v9.0/schema_structure.html#schema-i...
Ataraxy 5y ago
I use Hasura heavily and the thing I wish it had out of the box is groupby aggregates. This doesn't have aggregates yet but when that conversation happens hopefully it's considered.
luke_j 5y ago
Can I also install such extension in a Google Cloud SQL Instance?
cdaringe 5y ago
Heck ya! I can’t wait to try it in whatever my next project is. I’d like to learn more about the security of the functionality. I’m not talking about how to apply pg roles/privs/RLS, but rather my perceived risk of how hard it is to write stuff safely in C. That is, who is handling inbound http requests, pg or the extension? What confidence do I have that my db mem is safe from imperfect extension logic?
nikivi 5y ago
Curious how it compares to https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/
- samwillis 5y ago
  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.
  - gregplaysguitar 5y ago
    Just noting that Graphile also uses transactions - that’s a given for this sort of tool I’d say.
- oliverrice 5y ago
  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/ 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
- underbluewaters 5y ago
  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...
  - gardnr 5y ago
ancharm 5y ago
How long before someone just goes into the Postgres source code and creates true native support for GraphQL
- whazor 5y ago
  An extension is the best next thing to native support and in my opinion a better place for such functionality.
brap 5y ago
That’s interesting. How does it deal with authentication and authorization? It it handles those well, it could be a real “serverless” solution for most CRUD apps.
- dljsjr 5y ago
  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`).
- oliverrice 5y ago
  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!
- clessg 5y ago
  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 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"`
  - majkinetor 5y ago
oliverrice 5y ago
author here! happy to answer questions
- config_yml 5y ago
  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?
  - oliverrice 5y ago
    PostgREST does solves a few of the core challenges that GraphQL is intended to address like over/under-selection and resource embedding (relationships). Even so, there has still been a lot of interest in GraphQL so users can leverage the growing ecosystem for things reflecting the data model/types for client usage, offline caching, etc
    - config_yml 5y ago
      Thanks, this was missing for me in the Motivation section on the blog post. It was a genuine question, not sure why it‘s downvoted.
- thelastbender12 5y ago
  > 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.
  - oliverrice 5y ago
    sure, if we're talking free-tier: Postgres runs in a 1 GB VM all by itself with some optimizations to get the most that limited hardware All other services are in a second 1 GB VM. You can see a list of those services here https://github.com/supabase/supabase/blob/master/docker/docker-compose.yml https://github.com/supabase/supabase/blob/master/docker/dock... The memory use per process can differs by use-case, so its important to leave a bit of headroom. For example, PostgREST's memory consumption can grow if the amount of data being returned from its queries is large. By the time you include: - supabase studio - kong - auth - storage - meta if they each take only 100MB, its pretty snug on a 1 GB VM!
xrd 5y ago
I've been using Hasura extensively and love it. I'm curious what the delta is between the two. It's so simple to run Hasura on dokku and instantly get an amazing UI for postgres with the bonus of graphql. It would take a lot for me to switch.
- piaste 5y ago
  > 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)
  - kall 5y ago
    Pretty recently, as of 2.0, hasura has added support for multiple databases.
    - piaste 5y ago
      It's a VERY fragile support: https://github.com/hasura/graphql-engine/issues/6648 https://github.com/hasura/graphql-engine/issues/6648 Basically, if two entities in the databases have the same names, Hasura fails unless you manually define a unique `custom_name` for each such entity. Given that the most common multiple database scenario involves different databases with either the exact same schema (one-db-per-tenant) or similar schemas (staging vs. production database), it forces you to painstakingly set a custom_name for basically every single entity in your db. Thankfully there is an API so in theory you could set this programmatically, but it still means that your client code needs to be manually kept in-sync with whatever custom name generator rule you used.
      - kall 5y ago
        For what it's worth, this is addressed in 2.1, which is only out in beta. Maybe should have been there from the start.
akulkarni 5y ago
As co-founder of another database company in the PostgreSQL ecosystem, I have to say that I'm really impressed with the quality and velocity of launches from the Supabase team. Nothing else to add, except please keep up the great work!
- kiwicopple 5y ago
  thanks Ajay, we're also big fans of yours here at Supabase :)
  - nextaccountic 5y ago
    Hey it's unrelated but, what's the relationship between Supabase and Postgrest? My understanding is that Postgrest is a separate project that began development in an unrelated way to Supabase, and Supabase is just an user. Is that correct? But, Supabase is also a contributor to Postgrest, right? Does Supabase employs engineers dedicated to it?
    - oliverrice 5y ago
      > What's the relationship between Supabase and Postgrest? Supabase uses PostgREST for its automatic/reflected REST API > Supabase is just an user. Is that correct? yep! > Does Supabase employs engineers dedicated to it? https://supabase.com/blog/2020/06/15/supabase-steve-chavez https://supabase.com/blog/2020/06/15/supabase-steve-chavez
      - nextaccountic 5y ago
        Cool, thanks!
- nextaccountic 5y ago
  What's your company?
  - AdriaanvRossum 5y ago
cmrajan 5y ago
I've explored multiple Graphql solutions for PostgreSQL in the past and couldn't convince of the resolvers based approach and always felt that an extension would be the right solution. The closest I've come across and used so far is Graphjin https://github.com/dosco/graphjin https://github.com/dosco/graphjin . Thanks Supabase for the fantastic extension!
mmargerum 5y ago
Thanks for giving this to the community. Will definitely be looking at you for a SASS app i'm building.
- mritchie712 5y ago
  They just released a free course you might like: https://egghead.io/courses/build-a-saas-product-with-next-js-supabase-and-stripe-61f2bc20 https://egghead.io/courses/build-a-saas-product-with-next-js...
the_arun 5y ago
What are the use cases we could use a direct plugin to database for GraphQL? I mean where do we write business logic before data becomes GraphQL?
- warent 5y ago
  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.
halfmatthalfcat 5y ago
Wow, with Relay built-in. Looks great!
andrewingram 5y ago
Whilst I don't like the paradigm of generating a GraphQL schema from your database (or vice-versa!), I appreciate this one for having a specific goal of being able to run as part of a small database VM. So congrats on the release!
- nick__m 5y ago
  Ideally you don't expose the schema directly, you make views in another schema and expose that instead of exposing your real tables.
  - andrewingram 5y ago
    Even so, the patterns I really enjoy using with GraphQL make heavy use of interfaces and unions; and I’ve yet to see any automatic “data model to schema” tool make use of them.
supa-osum 5y ago
How does it compare to Apache Age?
- oliverrice 5y ago
  Apache Age is based around the openCypher graph query language rather than GraphQL
Apaec 5y ago
How does it compare with https://github.com/solidsnack/GraphpostgresQL https://github.com/solidsnack/GraphpostgresQL? It's like 7 years older and it takes the same approach, it seems.
- oliverrice 5y ago
  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
  - deleted 5y ago
    [deleted]
- solidsnack9000 5y ago
  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.
  - mritchie712 5y ago
    Never ceases to amaze me when someone pops up on HN with a "yeah, I made that, ask me anything". Nice work, looks like an awesome project.
lucasyvas 5y ago
There is no way every cloud provider won't jump on this as soon as it's stable for their Postgres offerings.
- darksaints 5y ago
  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.
  - yurisagalov 5y ago
    Supabase is open source. I believe the hosted version is on AWS, but there's no reason you couldn't run it on your company's Azure/AWS directly? :)
agilob 5y ago
PostgreSQL has the great shiny features, but still doesn't support zero downtime upgrades?
wiradikusuma 5y ago
I use Dgraph, a "native" GraphQL DB. But I still transform the incoming/outgoing JSON to Java object and transforming the object again to/from the DB. Why? 2 things: Business Logic and Security/Access Control. Yes I can put (some) Business Logic in Dgraph DB, but it feels "leaky" like Stored Procedure (maybe because I come from Java). I feel it's tiring and stupid, but I don't know a better solution. I reckon for this extension, the business logic uses Stored Procedure and Access Control uses PG's user role? Many apps I know simply have 1 user "myappuser" (or even default user) to access its DB.
- Twisol 5y ago
  > 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.
darksaints 5y ago
This is awesome! Really creative approach to the problem. Does this also support custom datatypes that can be marshalled to json? For example, PostGIS geometries?
- oliverrice 5y ago
  Currently, any datatypes that are not in the allow list https://supabase.github.io/pg_graphql/reflection/#type-conversion 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
  - darksaints 5y ago
    Personally I like the idea of allowing the user to create a cast: https://www.postgresql.org/docs/current/sql-createcast.html https://www.postgresql.org/docs/current/sql-createcast.html There are some weird permissions issues to work out IIRC, but this allows the user to specify how the json should be marshalled, and then can be implicitly or explicitly used by any sql statements. This is how PostgREST solves this problem, and I currently have a few custom casts created for range and multirange datatypes through my PostgREST server.
  - oreilles 5y ago
    Don't know how your JSON generation works but when using `row_to_json()`, all PostGIS geometries are automatically casted to GeoJSON objects by default which is very convenient.
pictur 5y ago
supabase look like so helpful tool. which has anyone use in production? does it have a hasura like event trigger mechanism?
- oliverrice 5y ago
  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
sporkland 5y ago
I enjoyed Sam Newman's take on this capability [1] > Great to see AWS providing direct data coupling as a service. /s > This service allows you to directly map a GraphQL endpoint to a database table. It’s like putting getters and setters on an object and claiming your encapsulating private variables. The end result is coupling between GraphQL clients and the underlying datasource. > Information hiding is a key concept in independent change. Can I change the provider (of the GraphQL) endpoint independent of the clients? Directly exposing internal data structure makes this very difficult. And [2]: > So a few people have asked why I have this snarky response. What is my problem with this service? Well, to be clear, it’s not an issue with GraphQL, it’s an issue with direct coupling with underlying datasources #thread > The service as advertised makes it simple to map a GraphQL definition against a database. Now, what’s the problem with this? Well, the devil here is in the detail. But fundamentally it comes down to how important information hiding is to you. > ... see the thread for more ... [1] https://twitter.com/samnewman/status/1346541251617828877 https://twitter.com/samnewman/status/1346541251617828877 [2] https://twitter.com/samnewman/status/1346749556583780352 https://twitter.com/samnewman/status/1346749556583780352
- Twisol 5y ago
  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.
ndejaco 5y ago
I see this as a neat way of exposing a graphql interface but a pattern with a number of limitations that are traded off against the goal of reducing the network latency. Definitely useful for users who want a single datasource and only want to expose access through GraphQL (cannot scale db resources indepedently of the api access layer). Wondering if the graphql engine could make use of the postgres stats collector for optimizing graphql resolver execution ex) resolver dispatch scheduling or pre-execution query optimization?
PeterZaitsev 5y ago
Wow, Interesting - many extensions are coming up allowing different interfaces to PostgreSQL! Other "Protocol Convertors" launched recently MS SQL - https://babelfishpg.org/ https://babelfishpg.org/ MongoDB - https://www.ferretdb.io/ https://www.ferretdb.io/
intrasight 5y ago
This runs within the database engine? Doesn't that adversely affect scalability?
- a-priori 5y ago
  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.
- syastrov 5y ago
  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.
n0w 5y ago
As someone that has used Hasura very heavily in the past, I can see the operational benefit of not needing to manage one more service. The talk about memory constraints seems a bit odd though. The extension is still going to require memory and without profiling the alternatives it seems odd to say: "we won't use any more memory with an extension". Especially when you say "these established/stable alternatives fulfill all our feature requirements". In practice I always found Hasura to be reasonably lightweight (I can't speak for PostGraphile). I think there's an advantage to running this API layer as an additional service in front of the DB though. Then it can act as an API gateway to more than just your database. Also, the generation of a "Relay style" schema is off putting for me. I'm really not a fan of the style and was one of the biggest advantages for me in using Hasura.
- halfmatthalfcat 5y ago
  It is nice Hasura offers it as well via the /v1beta1/relay endpoint[1]. [1] https://hasura.io/docs/latest/graphql/core/databases/postgres/schema/relay-schema.html https://hasura.io/docs/latest/graphql/core/databases/postgre...
foldr 5y ago
Naive question: If your entire DB schema is automatically reflected into a GraphQL schema, then how is this better than just having the client send SQL queries directly to the backend? Does GraphQL make it easier to do this securely?
Winsaucerer 5y ago
Looks excellent, and I'm pleased to see you've provided the relay connection style pagination, using keyset pagination under the hood. That's awesome!

431 points by samwillis 5y ago

While this is super neat, this and the amplify thread can't help but make me wonder: Is everyone really this okay with their API always being 1-to-1 with their DB models? In my experience, that kind of setup is only viable for the smaller, simpler projects and otherwise you always run into something where you'd really prefer to have a layer between you and your database. I am currently using graphql at work and this is a very hard requirement for us. Our database schema is not translated literally into graphql(or the other way around) and this is very intentional. The whole idea of the API layer is to be able to make changes to your internals without breaking all your consumers. This was a problem with amplify(though the least of the problems we had) and it seems to me this is also a problem here, as it is with Hasura and postgrest.

deleted 5y ago
[deleted]
steve-chavez 5y ago
> 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-isolation https://postgrest.org/en/v9.0/schema_structure.html#schema-i...

Ataraxy 5y ago

I use Hasura heavily and the thing I wish it had out of the box is groupby aggregates. This doesn't have aggregates yet but when that conversation happens hopefully it's considered.

luke_j 5y ago

Can I also install such extension in a Google Cloud SQL Instance?

cdaringe 5y ago

Heck ya! I can’t wait to try it in whatever my next project is. I’d like to learn more about the security of the functionality. I’m not talking about how to apply pg roles/privs/RLS, but rather my perceived risk of how hard it is to write stuff safely in C. That is, who is handling inbound http requests, pg or the extension? What confidence do I have that my db mem is safe from imperfect extension logic?

nikivi 5y ago

Curious how it compares to https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/

samwillis 5y ago
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.
- gregplaysguitar 5y ago
  Just noting that Graphile also uses transactions - that’s a given for this sort of tool I’d say.
oliverrice 5y ago
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/ 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
underbluewaters 5y ago
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...
- gardnr 5y ago

ancharm 5y ago

How long before someone just goes into the Postgres source code and creates true native support for GraphQL

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

brap 5y ago

That’s interesting. How does it deal with authentication and authorization? It it handles those well, it could be a real “serverless” solution for most CRUD apps.

dljsjr 5y ago
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`).
oliverrice 5y ago
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!
clessg 5y ago
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 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"`
- majkinetor 5y ago

oliverrice 5y ago

author here! happy to answer questions

config_yml 5y ago
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?
- oliverrice 5y ago
  PostgREST does solves a few of the core challenges that GraphQL is intended to address like over/under-selection and resource embedding (relationships). Even so, there has still been a lot of interest in GraphQL so users can leverage the growing ecosystem for things reflecting the data model/types for client usage, offline caching, etc
  - config_yml 5y ago
    Thanks, this was missing for me in the Motivation section on the blog post. It was a genuine question, not sure why it‘s downvoted.
thelastbender12 5y ago
> 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.
- oliverrice 5y ago
  sure, if we're talking free-tier: Postgres runs in a 1 GB VM all by itself with some optimizations to get the most that limited hardware All other services are in a second 1 GB VM. You can see a list of those services here https://github.com/supabase/supabase/blob/master/docker/docker-compose.yml https://github.com/supabase/supabase/blob/master/docker/dock... The memory use per process can differs by use-case, so its important to leave a bit of headroom. For example, PostgREST's memory consumption can grow if the amount of data being returned from its queries is large. By the time you include: - supabase studio - kong - auth - storage - meta if they each take only 100MB, its pretty snug on a 1 GB VM!

xrd 5y ago

I've been using Hasura extensively and love it. I'm curious what the delta is between the two. It's so simple to run Hasura on dokku and instantly get an amazing UI for postgres with the bonus of graphql. It would take a lot for me to switch.

piaste 5y ago
> 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)
- kall 5y ago
  Pretty recently, as of 2.0, hasura has added support for multiple databases.
  - piaste 5y ago
    It's a VERY fragile support: https://github.com/hasura/graphql-engine/issues/6648 https://github.com/hasura/graphql-engine/issues/6648 Basically, if two entities in the databases have the same names, Hasura fails unless you manually define a unique `custom_name` for each such entity. Given that the most common multiple database scenario involves different databases with either the exact same schema (one-db-per-tenant) or similar schemas (staging vs. production database), it forces you to painstakingly set a custom_name for basically every single entity in your db. Thankfully there is an API so in theory you could set this programmatically, but it still means that your client code needs to be manually kept in-sync with whatever custom name generator rule you used.
    - kall 5y ago
      For what it's worth, this is addressed in 2.1, which is only out in beta. Maybe should have been there from the start.

akulkarni 5y ago

As co-founder of another database company in the PostgreSQL ecosystem, I have to say that I'm really impressed with the quality and velocity of launches from the Supabase team. Nothing else to add, except please keep up the great work!

kiwicopple 5y ago
thanks Ajay, we're also big fans of yours here at Supabase :)
- nextaccountic 5y ago
  Hey it's unrelated but, what's the relationship between Supabase and Postgrest? My understanding is that Postgrest is a separate project that began development in an unrelated way to Supabase, and Supabase is just an user. Is that correct? But, Supabase is also a contributor to Postgrest, right? Does Supabase employs engineers dedicated to it?
  - oliverrice 5y ago
    > What's the relationship between Supabase and Postgrest? Supabase uses PostgREST for its automatic/reflected REST API > Supabase is just an user. Is that correct? yep! > Does Supabase employs engineers dedicated to it? https://supabase.com/blog/2020/06/15/supabase-steve-chavez https://supabase.com/blog/2020/06/15/supabase-steve-chavez
    - nextaccountic 5y ago
      Cool, thanks!
nextaccountic 5y ago
What's your company?
- AdriaanvRossum 5y ago

cmrajan 5y ago

I've explored multiple Graphql solutions for PostgreSQL in the past and couldn't convince of the resolvers based approach and always felt that an extension would be the right solution. The closest I've come across and used so far is Graphjin https://github.com/dosco/graphjin https://github.com/dosco/graphjin . Thanks Supabase for the fantastic extension!

mmargerum 5y ago

Thanks for giving this to the community. Will definitely be looking at you for a SASS app i'm building.

mritchie712 5y ago
They just released a free course you might like: https://egghead.io/courses/build-a-saas-product-with-next-js-supabase-and-stripe-61f2bc20 https://egghead.io/courses/build-a-saas-product-with-next-js...

the_arun 5y ago

What are the use cases we could use a direct plugin to database for GraphQL? I mean where do we write business logic before data becomes GraphQL?

warent 5y ago
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.

halfmatthalfcat 5y ago

Wow, with Relay built-in. Looks great!

andrewingram 5y ago

Whilst I don't like the paradigm of generating a GraphQL schema from your database (or vice-versa!), I appreciate this one for having a specific goal of being able to run as part of a small database VM. So congrats on the release!

nick__m 5y ago
Ideally you don't expose the schema directly, you make views in another schema and expose that instead of exposing your real tables.
- andrewingram 5y ago
  Even so, the patterns I really enjoy using with GraphQL make heavy use of interfaces and unions; and I’ve yet to see any automatic “data model to schema” tool make use of them.

supa-osum 5y ago

How does it compare to Apache Age?

oliverrice 5y ago
Apache Age is based around the openCypher graph query language rather than GraphQL

Apaec 5y ago

How does it compare with https://github.com/solidsnack/GraphpostgresQL https://github.com/solidsnack/GraphpostgresQL? It's like 7 years older and it takes the same approach, it seems.

oliverrice 5y ago
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
- deleted 5y ago
  [deleted]
solidsnack9000 5y ago
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.
- mritchie712 5y ago
  Never ceases to amaze me when someone pops up on HN with a "yeah, I made that, ask me anything". Nice work, looks like an awesome project.

lucasyvas 5y ago

There is no way every cloud provider won't jump on this as soon as it's stable for their Postgres offerings.

darksaints 5y ago
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.
- yurisagalov 5y ago
  Supabase is open source. I believe the hosted version is on AWS, but there's no reason you couldn't run it on your company's Azure/AWS directly? :)

agilob 5y ago

PostgreSQL has the great shiny features, but still doesn't support zero downtime upgrades?

wiradikusuma 5y ago

I use Dgraph, a "native" GraphQL DB. But I still transform the incoming/outgoing JSON to Java object and transforming the object again to/from the DB. Why? 2 things: Business Logic and Security/Access Control. Yes I can put (some) Business Logic in Dgraph DB, but it feels "leaky" like Stored Procedure (maybe because I come from Java). I feel it's tiring and stupid, but I don't know a better solution. I reckon for this extension, the business logic uses Stored Procedure and Access Control uses PG's user role? Many apps I know simply have 1 user "myappuser" (or even default user) to access its DB.

Twisol 5y ago
> 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.

darksaints 5y ago

This is awesome! Really creative approach to the problem. Does this also support custom datatypes that can be marshalled to json? For example, PostGIS geometries?

oliverrice 5y ago
Currently, any datatypes that are not in the allow list https://supabase.github.io/pg_graphql/reflection/#type-conversion 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
- darksaints 5y ago
  Personally I like the idea of allowing the user to create a cast: https://www.postgresql.org/docs/current/sql-createcast.html https://www.postgresql.org/docs/current/sql-createcast.html There are some weird permissions issues to work out IIRC, but this allows the user to specify how the json should be marshalled, and then can be implicitly or explicitly used by any sql statements. This is how PostgREST solves this problem, and I currently have a few custom casts created for range and multirange datatypes through my PostgREST server.
- oreilles 5y ago
  Don't know how your JSON generation works but when using `row_to_json()`, all PostGIS geometries are automatically casted to GeoJSON objects by default which is very convenient.

pictur 5y ago

supabase look like so helpful tool. which has anyone use in production? does it have a hasura like event trigger mechanism?

oliverrice 5y ago
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

sporkland 5y ago

I enjoyed Sam Newman's take on this capability [1] > Great to see AWS providing direct data coupling as a service. /s > This service allows you to directly map a GraphQL endpoint to a database table. It’s like putting getters and setters on an object and claiming your encapsulating private variables. The end result is coupling between GraphQL clients and the underlying datasource. > Information hiding is a key concept in independent change. Can I change the provider (of the GraphQL) endpoint independent of the clients? Directly exposing internal data structure makes this very difficult. And [2]: > So a few people have asked why I have this snarky response. What is my problem with this service? Well, to be clear, it’s not an issue with GraphQL, it’s an issue with direct coupling with underlying datasources #thread > The service as advertised makes it simple to map a GraphQL definition against a database. Now, what’s the problem with this? Well, the devil here is in the detail. But fundamentally it comes down to how important information hiding is to you. > ... see the thread for more ... [1] https://twitter.com/samnewman/status/1346541251617828877 https://twitter.com/samnewman/status/1346541251617828877 [2] https://twitter.com/samnewman/status/1346749556583780352 https://twitter.com/samnewman/status/1346749556583780352

Twisol 5y ago
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.

ndejaco 5y ago

I see this as a neat way of exposing a graphql interface but a pattern with a number of limitations that are traded off against the goal of reducing the network latency. Definitely useful for users who want a single datasource and only want to expose access through GraphQL (cannot scale db resources indepedently of the api access layer). Wondering if the graphql engine could make use of the postgres stats collector for optimizing graphql resolver execution ex) resolver dispatch scheduling or pre-execution query optimization?

PeterZaitsev 5y ago

Wow, Interesting - many extensions are coming up allowing different interfaces to PostgreSQL! Other "Protocol Convertors" launched recently MS SQL - https://babelfishpg.org/ https://babelfishpg.org/ MongoDB - https://www.ferretdb.io/ https://www.ferretdb.io/

intrasight 5y ago

This runs within the database engine? Doesn't that adversely affect scalability?

a-priori 5y ago
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.
syastrov 5y ago
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.

n0w 5y ago

As someone that has used Hasura very heavily in the past, I can see the operational benefit of not needing to manage one more service. The talk about memory constraints seems a bit odd though. The extension is still going to require memory and without profiling the alternatives it seems odd to say: "we won't use any more memory with an extension". Especially when you say "these established/stable alternatives fulfill all our feature requirements". In practice I always found Hasura to be reasonably lightweight (I can't speak for PostGraphile). I think there's an advantage to running this API layer as an additional service in front of the DB though. Then it can act as an API gateway to more than just your database. Also, the generation of a "Relay style" schema is off putting for me. I'm really not a fan of the style and was one of the biggest advantages for me in using Hasura.

halfmatthalfcat 5y ago
It is nice Hasura offers it as well via the /v1beta1/relay endpoint[1]. [1] https://hasura.io/docs/latest/graphql/core/databases/postgres/schema/relay-schema.html https://hasura.io/docs/latest/graphql/core/databases/postgre...

foldr 5y ago

Naive question: If your entire DB schema is automatically reflected into a GraphQL schema, then how is this better than just having the client send SQL queries directly to the backend? Does GraphQL make it easier to do this securely?

Winsaucerer 5y ago

Looks excellent, and I'm pleased to see you've provided the relay connection style pagination, using keyset pagination under the hood. That's awesome!