PostGraphile Instant GraphQL API for PostgreSQL Database | Hacker News Reader

PostGraphile Instant GraphQL API for PostgreSQL Database

graphile.org

183 pointsjdnier50 comments

I absolutely love the idea of these "thin layer over the DB" systems that turn a database into a "mostly-fledged" API, but my biggest problem with them is the edge cases.

When I need to drop out of the system and do something more, most of them require very specific "plugins" or "tools" to integrate with the system, locking you to that technology pretty hard, and often being difficult to achieve exactly what you need in anything resembling "ideal". Sometimes I need a unique authentication model which is difficult to model with these systems, or I need to do some data conversion or processing on top of what is being stored/retrieved, but only in one small part.

I'd love one of these kinds of things that is flipped around. Let me create the server, let me write the authentication and user system in the application language, then let me define which endpoints I want the "automatic" system to do the heavy lifting on.

Not only would this alleviate my fears of lockin to a technology that might die in a few years, but it would also allow me to start adding it to current projects easily.

Hi, PostGraphile maintainer here. Excellent points! This is actually where the powerful GraphQL ecosystem comes in really handy - with functionality such as GraphQL schema stitching (e.g. graphql-weaver) you can use something like PostGraphile to rapidly build your MVP and easily augment it, extend it or combine it with other GraphQL schemas to build exactly what you need. You could also use something like graphql-binding from graph.cool to build your dream GraphQL schema, and then just call out to PostGraphile under the hood - there's no need to expose the PostGraphile schema directly to the public if you don't want to!

With version 4 of PostGraphile I've been really concentrating on the ways that people might want to customise or extend the system, whilst also giving it a welcome speed boost (and reduced memory usage). We're pretty close to a v4 beta now :)

That's actually fantastic news!

I haven't really used GraphQL all that much outside of playing with it a few times, but it's something I really would love to use in a few projects as it solves some very real problems in a really elegant way.

And having something like this layered on top might make a switch over to GraphQL possible in a timely manner.

I 100% agree with what Benjie said. With things like schema stitching (see https://blog.graph.cool/graphql-schema-stitching-explained-s...) it's getting super simple to compose GraphQL APIs and therefore use PostGraphile as a "GraphQL database".

Static GraphQL bindings even allow to take this even a step further by mapping the GraphQL API capabilities to your (typed) programming language. This kind of gives you a typed "GraphQL ORM" without the typical downsides of a ORM (performance, API limitations etc).

If you're interested, this blog post further describes these concepts: https://blog.graph.cool/reusing-composing-graphql-apis-with-...

Seems like you could do exactly that, by running your own GraphQL server and, default proxying requests to the PostGraphile server, and handling more specific endpoints yourself.

In theory, however that's a lot of added complexity. Maintaining 2 systems, managing upgrades, schema updates, scaling, and more all becomes much more complicated with that.

A large part of the "value add" of something like this is that it's simple and easy to maintain, and doing a 2-stage system like that kind of removes that benefit somewhat.

From my chat with graph.cool the other day it seems like graphql-bindings would be a very thin wrapper that also can be auto-generated from a GraphQL schema (e.g. PostGraphile), so there shouldn't be too much additional complexity, and I don't think there's any major reasons they couldn't run in literally the same Node.js process. DISCLAIMER: I've not got around to trying this yet.

Yes, this simply runs in the same Node.js process (or any other programming language really) and doesn't add any complexity. Under the hood it's simply delegating your GraphQL queries to another GraphQL API (e.g. PostGraphile or Graphcool) – think of this as a smart HTTP request forwarder.

In terms of the API you're using, you can either use a "dynamic" GraphQL binding which uses method introspection (e.g. via JS proxies) or a generated "static" GraphQL binding which maps the GraphQL type system to your programming language. This way you can catch errors at build/compile time and get auto-completion functionality like in GraphiQL/Playground.

Check out this article for some more details: https://blog.graph.cool/reusing-composing-graphql-apis-with-...

Cool project. But potentially dangerous if used as a CRUD ORM layer.

I'd only recommend using something like this for the read model of a CQRS/Event Sourced system.

Capture the commands and send them to business logic, that then updates the database. If you let a generic framework handle writes, it is like putting the business logic in the client. In that case SQL would be simpler.

You can manage that with mutations which correspond with PostgreSQL functions to do the writing in the Postgraphile system.

    create or replace function addFrobnoz (fooId uuid, frobnoz uuid)
    returns void as $$
    declare
        foo row;
    begin
        -- fetch and validate foo wrt. frobnoz
        select insertIntoEventStore(
               "frobnozAdded",
               json_build_object('frobnoz', frobnoz));
    end;
    $$ language plpgsql;

Where `insertIntoEventStore` does what it says, maintains a monotonic sequence, etc, etc. Postgraphile can discover the _command_ functions as mutations and map them the GraphQL mutations spec for you. Then your projections just fill in the read tables for you and that's what Postgraphile will read from.

And if you need side-effects like sending email or stream more complicated aggregates there are lots of options in PostgreSQL to use the `notify/subscribe` system or use extensions to publish off to a message queue directly.

PostGraphile is super cool. Especially if you like centralising logic at the database level. There are pros and cons to this approach.

Personally I prefer keeping my database layer as simple as possible - no stored procedures, no queues and certainly no communicating to external systems.

But I still see great value in generating a GraphQL data API and using that as the only way to interact with the database. GraphQL Bindings is a new technology that allows you write a server that acts as an advanced proxy in front of another GraphQL API. It allows you to recompose the schema, and intercept requests for specific queries or mutations.

Using an approach like this you can pass through queries to PostGraphile almost unmodified while taking full control over mutations.

It's early days, but we have a pretty cool setup that generates TypeScript type definitions based on the underlying GraphQL API. And it is all open source :-) https://blog.graph.cool/reusing-composing-graphql-apis-with-...

What's its authorization story?

More generally, how good actually is authorization in graphql?

Official docs seem to essentially say "not our problem!", while various tutorials say "here's the best hack we could come up with".

(just my impression. I really want to believe in graphql, but often the topic appears mostly bypassed)

Last I looked for postgraphile, they suggest you hand all that off to row-level authorization mechanisms, which admittedly got a lot more performant in postgres 10.

Interesting, I've been working on something similar but the other way around. You define your GraphQL schema and the framework provisions tables. Right now I have a very alpha dynamodb backend, working on Google Cloud Datastore right now. Postgres was going to be next. I don't feel comfortable with it yet to share here.

You should consider talking to Johannes Schickling of graph.cool; they seem to be very interested in that sort of thing and they've got some really cool open source going on right now. Johannes is a really nice person - I'd definitely recommend you have a chat.

Thanks a lot for the mention, Benjie!

optimuspaul, I'd be very interested in learning more about your project, as this is something we're looking into as part of our roadmap. Would be great to have a chat in our Slack: https://slack.graph.cool/

I have some skunkworks projects that attempt the same on postgres. If you ever get to the point of wanting contributions, let me know.

Hi jmandzik, I'd be very interested to learn more about your project. What's the best way to get in touch?

The email in my profile would be best. Thanks!

In case anyone is curious, this is a rename of PostGraphQL.

"NOTICE: PostGraphQL will be renaming to PostGraphile with the release of v4. You can replace postgraphile in the documentation with postgraphql if you're running postgraphql@next"

> https://www.graphile.org/postgraphile/introduction/

I have been deep-diving into graphql lately, as it seems like it very completely fixes the client-side N+1 query problem (you can specify everything, including children of children in hierarchical data, that you'll need to render a page in a single fetch request), and the return-to-rpc feel of mutations is also kind of nice after feeling a bit over-constrained by REST conventions for some edge-case mutation actions.

That said, when I looked last postgraphile didn't handle has many and belongs to many relationships out of the box. I do this a lot, with a mostly-standard pattern of naming and so on, with a join table in postgres. This lets me do stuff like "sort this list by the timestamp of when the member of the community was added" (which is an extra column in the join table because it belongs to the relationship between the two entities and not either entity in particular), or put other (often role-based) metadata on a relationship - the community_profile_membership table has a level column noting whether the member is also a moderator, for example.

So far, I've been building stuff manually using apollo tools and with facebook's dataloader library to reduce the number of queries needed to fulfill the request (most common query scenarios end up with three total queries, one for the root object, one for the relationships, and one to inflate the relationship values). Naturally, this could be fewer if I took postgraphile's approach.

Are there plans for building hmabtm style relationships into postgraphile?

I see this as a very positive direction for back-end persistence services. There are similar projects underway for .Net and SQL Server. Also, there are compatible front-end libraries that follow suit. What I find most compelling is to finally be reunited with the RAD (Rapid Application Development) approaches that we had 30 years ago.

GraphQL is finally an API standard that strikes the right balance between simplicity and flexibility and has the potential to be the only query language most developers have to learn – and could therefore fulfil the idea of ODBC.

In regards to other SQL databases, we're currently working on a GraphQL database proxy that turns your database into a GraphQL API. We're starting with MySQL but have other databases like PostgraphQL, SQL Server, Oracle, DynamoDB on the way.

Whenever I see GraphQL implementations I'm interested in two things:

  1. How do you secure it?
  2. How do you make sure that queries are performant?

Typically these are left "up to the implementation" and are probably both harder than the problem graphql proposes to solve itself. Took at look at this and here is what I could figure out:

1. Use postgresql's row based access control. This is not a bad solution at all.

2. No clue. I see that you can write your own custom queries that could be used to optimize special cases, but the point of GraphQL is to be able to do composition and having to use custom queries that do a bunch of things in one query defeats the purpose. Is there any other way that this is handled? Or is just hoping that naive composition of SQL fragments will result in reasonable performance for a majority of cases?

Great questions! You're right with number 1: RBAC + RLS (+ custom mutation functions if you need them). For number 2, the solution is currently naive composition of SQL fragments, CTEs, and trusting PostgreSQL to find optimal solutions to admittedly quite complex queries, but this is just the initial implementation. I can confirm that v4 is already significantly faster than v3 at complex queries, but there's still a lot of optimisations left to throw at it (e.g. not converting everything to JSON in the database (which seems to be very expensive), and instead passing down the raw types that PostgreSQL can handle in a significantly more performant manner), and that's before we even get around to writing a query engine that can perform query transforms and optimisations. I hope that PostGraphile can already give better performance than a traditional naive GraphQL SQL + DataLoader solution, but I'm not concentrating on perf right now - correctness first (and tests to back it up), THEN perf optimisations :)

Does anyone have experience with long-term usage of these automatic DB to API systems?

I always worry about change management and inefficient designs.

I can't talk about PostGraphile, but that's my experience using anything automated. Even your run of the mill ORM's drive me nuts. Abstracting out schemas is fine, but when it comes to query creation, I need to be in COMPLETE control of what is happening.

with PostGraphile you pretty much are in complete control. You're free to write your own postgres functions/views which postgraphile will expose as endpoints. you can do so such that they are standalone endpoints or sub-queries to the auto-generated table queries. you can also basically bypass all the auto-generated table queries by creating them in a schema that postgraphile doesn't introspect, and then write all the functionality you want in a schema that it will, thus getting only endpoints/queries that you wrote yourself exposed in the api.

This isn't an ORM, is it? I poked at it in an earlier (PostGraphQL) version and it feels like a query translator from GraphQL to SQL and back, to me, with no munging onto or into objects, at all. ORMs are for people who love their programming language but don't want to touch the database.

This is kinda the opposite of an ORM. Nearly everything is in the database and this is a wrapper that allows you to build your app in your database.

You're thinking of code-first ORMs (to use the EF terminology), where you write classes and the tool maps them to tables and queries.

These GraphQL tools, however, act a lot like a database-first ORM, where you provide a database and the tool generates code in a different paradigm (OO for ORMs, Graph QL here). It is indeed the opposite direction, but ORMs can work that way too.

Yeah, I guess GraphQL is delivering JSON "objects". Nonetheless, developing for it is a completely different experience from any other ORM I've ever poked at (not saying it's better, just very different).

Hmm, isn't that nearly exactly the same what PostgREST has been doing?

Very similar! The project was originally inspired by PostgREST - the main difference being that it uses GraphQL as the API layer rather than REST.

Edit: Apparently this is based on https://github.com/postgraphql/postgraphql which makes sense!

This is PostGraphQL, we're renaming to PostGraphile with the official release of v4 which should happen in the coming months.

Yep, my bad! Thanks for PostGraphQL, we are using it in production with lots of success :)

It's similar, but written in JavaScript instead of Haskell. As someone who prefers the type safety and performance of Haskell, I'll be sticking with PostgREST.

To get both GraphQL and standard PostgREST endpoints (as well as a few other tools), this starter kit is really nice and fires right up with docker-compose:

https://github.com/subzerocloud/subzero-starter-kit

I know that these (https://www.nrecosite.com/graphql_to_sql_database.aspx) guys are working on such component and have adapter for PostgreSQL (based on description)

I've played around with Postgraphile extensively and it's a wonderfully refreshing way to build apps.

That said, you are programming in Postgres instead of your language of choice so the learning curve is real. And refactoring code felt more complicated because you have to delete a constraint and then add one back in, you can't just modify a constraint in Postgres.

So, if you have your app mapped out it definitely is faster than most other app generators. But, for ongoing maintenance and custom functionality like image uploads it feels more complicated to me.

Fascinating. Anyone using this for their side project or in production?

I was confused how this compares to PostGraphQL at first, until I read in the footer:

> “PostGraphile was originally authored as PostGraphQL by Caleb Meredith.”

This should probably be made more obvious from the get-go.

Yeah, renaming things is always painful. We're renaming the project because "GraphQL" is owned by Facebook and we don't want any trademark disputes down the line. We've mentioned the rename in a number of places but I suppose we could always add it to more places - where would you suggest?

It's worth noting that the entire GraphQL schema generation in v4 is a ground-up re-write (that's where the "graphile" comes from - it was originally a separate project), we've kept the top level APIs from v3 though (e.g. the web layer). This has enabled the performance improvements, plugin system, and greater customisability.

I'm curious if this is compatible with AWS Aurora, and if anyone has tried it

Anyone else find it kind of funny that this feel like an ORM? We come full circle!

graphql on postgres is literally orm. objects are graphs. and graphql inherits all the same problems as the last 1000 attempts to do this. unless you're facebook who uses an eventually consistent graph datastore!

Developers run into all kinds of trouble when they use an orm without understanding the underlying execution model. Before starting Graphcool I lead the team that scaled a consumer site into the top 1000 visited sites globally. A well tuned SQL server and Hibernate took us a long way :-)

One of the biggest foot guns when working with ORMs is lazy data loading. This is a feature implemented by most advanced ORMs that allow the developer to pretend that the entire data model is in memory without having to actually fetch all data on every request. The reason this is so dangerous is that you end up passing objects around that other developers then misuse to fetch "just a little more data". Over time this can lead to tens or hundreds of roundtrips to the database for a single request.

GraphQL tackles this issue head on by encouraging the developer to specify all data dependencies up front. Client side tools like Apollo and Relay even help you collect data requirements from multiple related components and construct a minimal covering query. This means that there is only one request, and the underlying query engine has all information available.

I'm pretty excited to bring these same concepts to the server with GraphQL Bindings: https://github.com/graphcool/graphql-binding

Declaring data dependencies is interesting, but unfortunately the vision you describe is not possible in the general case. Just in contrived toy cases. You show me a Facebook scale application hydrating its data in one request, and I'll show you that it isn't built on RDBMS. Case in point: Facebook. Case #2: GraphQL has a shitty programming language built into it, to make decisions about data fetching closer to the database.

Perhaps we should code the entire app in stored procedures? (which might even actually work)

Absolutely you should code the entire app in stored procedures. The arguments for doing so have long been stronger than the arguments against doing so.

Where can I learn more about this?

Reply on news.ycombinator.com