My vision for the future of PostGraphQL

[calebmer]

Hi, I’m @calebmer, author of PostGraphQL a framework which turns your PostgreSQL database into a GraphQL server with minimal configuration. In this issue, I want to share my vision for the future PostGraphQL.

As you may know, PostGraphQL currently does one thing fairly well: turns a PostgreSQL database into a GraphQL API. You also may have heard of PostgREST which I contributed to in the past, it’s written in Haskell you should check it out. Similarly PostgREST also does one thing fairly well: turns a PostgreSQL database into a REST API.

Tools like these have a very high utility for certain use cases, but at the same time they are inflexible. What if I wanted a GraphQL API for my MySQL database using PostGraphQL? Well that wouldn’t be too easy. Every time we create another one of these tools we are repeating a lot of logic, because every API is generally just doing the same four things: creates, reads, updates, and deletes. CRUD.

It’s weird that we standardize on front-end frameworks like React or Angular, but our backends which are often just glorified CRUD interfaces get written from scratch.

I want PostGraphQL to be an opinionated tool that’s part of a more powerful ecosystem. An ecosystem that is focused on abstracting the data layer from the data interface. So instead of having one tightly coupled tool that look like:

PostgreSQL → GraphQL

We could have two loosely coupled tools that look like:

PostgreSQL → Interface → GraphQL

One tool that turns our PostgreSQL database into an abstract interface, and one that turns our abstract interface into a GraphQL API.

When we add that extra layer, we can start interchanging parts. Instead of PostgreSQL → Interface → GraphQL, we could have MySQL → Interface → GraphQL for half the effort. We also get the advantage of when our Interface → GraphQL tool improves, both toolchains benefit.

This is an ambitious goal, but it wouldn’t be the first time I have tried it. I’ve had this vision for a long time. It’s been about two years in the making. It started when I was contributing to PostgREST and contributors were talking about adding GraphQL support (at the time I was opposed, isn’t that ironic :blush:). After I stopped contributing to PostgREST I tried building this very idea, you can see my efforts in the Ardite organization. This approach failed for a couple of reasons which I won’t go into, but when it failed I decided to narrow my scope and just focus on PostgreSQL and GraphQL. That’s where PostGraphQL was born. I’m returning to this idea now because we just finished talking to a client who would have benefitted greatly if this technology existed.

Now that you’ve hezard the vision, let’s talk implementation.

Timeline

Getting to the point where we have MySQL to GraphQL tooling is going to take a lot of effort, so we’re going to focus on making PostGraphQL the best possible product it can be for now. Here’s my timeline to get that done:

1. Implement authentication. In order to do this in the best way possible we will need the PostgreSQL to GraphQL interface built, this might take a bit. I have the core code already written but it will take a while to reach feature parity with PostGraphQL. Once the interface is built, layering authentication on top will be much easier than it is today.
2. Documentation binge. We need a website for PostGraphQL with some really good documentation. In this website I’d also like to write a tutorial for how to design a good PostgreSQL database schema.
3. Create a CMS on top of the abstract interface we’ve built. At this point we’ll start to break up all the pieces that compose PostGraphQL and allow them to be iterated on separately. I’ve thought about building a CMS on top of GraphQL, but it turns out GraphQL has too few opinions to make that tech really work.

---

When we are done, I believe we will have created software that will make the lives of many engineers much easier. Developers will be able to iterate faster and build apps quicker. Development shops will be able to take on more clients for lower prices which means more businesses can be created with a strong software core. But it’s a lot of work, and I’m going to need help.

I’ve posted the interface I have so far in this Gist if you want to start taking a look. I don’t know when I will be comfortable sharing the entire new PostGraphQL codebase, but once I’m done with that, I’ll need a lot of help with steps 2 and 3.

If anyone is willing to start designing and building a website, I would be greatly appreciative. If anyone wants to help write examples like the Relay example @ferdinandsalis has been working on in #66, that would also be awesome.

If anyone wants to reach out to me personally, my email is calebmeredith8@gmail.com

[vamaq]

Question: could it be RDBMS -> ORM -> GraphQL interface? If that's the case and if you are willing to move to python there are several libs that makes the road easier. Sqlalchemy -> graphene + Flask + SQLA/Flask plugins. A Node SQLA would be grate btw.

[prevostc]

I admire and encourage this vision and goals. I will gladly contribute examples, documentation and new code to support this aim for the crud killer.

I am a bit worried though that PostGraphQl falls into the ORM trap where what you end up with is the least common feature set amongst all supported rdbms.

[pvdvreede]

It is certainly a good idea, but like @prevostc I am interested in how differences in RDBMs will be handled. I love that postgraphql uses the the native postgresql descriptions from tables and columns for the graphql descriptions. I am not familiar with many RDBMs but do they all have the ability to add a description for tables and columns? Will the standard interface still support it if they dont? Same with JSON data types or PostGIS data?

Or will this new project just support SELECT, INSERT, UPDATE and DELETE only?

I do like the interface idea so that from the other end, I can add a REST API as well as a Graphql API on the same interface with no extra coding.

[calebmer]

@prevostc, @pvdvreede:

I am a bit worried though that PostGraphQl falls into the ORM trap where what you end up with is the least common feature set amongst all supported rdbms.

We have an advantage over other ORM-ish systems in that we started with the most practical implementation, PostGraphQL, and I want to keep that practical (opinionated) implementation as the focus so we always have a product that only increases in utility. Ultimately I want to see these tools not only provide out of the box PostgreSQL → Interface → GraphQL support, but do that plus some use case specific PostGraphQL code on top. For example, authorization/authentication. I’m not sure if we can lowest common denominator that feature and frankly I don’t care to because I’m not a MySQL expert. Same goes for descriptions, JSON types, and PostGIS data. The interface can just return undefined at that point. Hopefully, by starting with PostGraphQL and focusing on PostGraphQL no matter what happens we are still in a position to assert practical lower level opinions.

I love that postgraphql uses the the native postgresql descriptions from tables and columns for the graphql descriptions.

As for using native PostgreSQL description, we actually don’t currently use the verbatim descriptions. We abstract away the native descriptions anyway because the native descriptions are very tough to work with. In thinking about adding authentication, domain types, composite types, and on it becomes very difficult to scale the system we currently have in place (especially because “table,” “schema,” and “column” is actually the incorrect nomenclature for the objects we’re working with :wink:). Evolving this interface to fully separate concerns not only means we can build interesting tech on top of it, but it also cleans up the PostGraphQL codebase.

Or will this new project just support SELECT, INSERT, UPDATE and DELETE only?

If there are more operations that may prove to be useful, of course we can add more higher level operations :blush:. For example, procedures. I want this interface to be a platform which means it needs to be sufficiently flexible and allow for extensibility.

[ferdinandsalis]

@calebmer this sounds very exciting. I am sure its ambitious but I am not really able to judge since I don’t have much background with backend work. Anyways I would love to contribute!

PS. I am curious to see what you already have besides the gist.

[calebmer]

If you want to follow my work, I’ll be pushing to the next branch: https://github.com/calebmer/postgraphql/tree/next

I’ve tried to document as much as possible in the code and as you navigate the directories. Hopefully I’ll be able to document more as time goes on. Please feel free to ask me questions and if you want to contribute, let me know!

Currently what’s missing is connections, mutations, procedures, and most of the PostgreSQL implementation. One interesting note though is that I’m trying to fit the entire PostgreSQL introspection into a single query this time around: https://github.com/calebmer/postgraphql/blob/next/queries/introspection-query.sql

[baloodevil]

The link above to the introspection sql is broken. Here is the correct link... https://github.com/calebmer/postgraphql/blob/next/src/postgres/sql/introspection.sql

[calebmer]

Ah yes, I moved it recently 😊

The original link really should have pinned to a commit…

[dts]

I recently started working on a koa-based project similar to PostgREST (https://github.com/thehumaneffort/koa-resteasy), that I created because of simple needs I had that postgrest simply did not cover. I'd like to share a few of the things that I think are important and/or learned. PostgREST and Postgraphql are both incredible tools that offer an amazing launching-off point. However, I personally find the Postgres system of authorization and authentication to be less than ideal (also, JWTs aren't always the right choice, authorization in the schema is fine in some cases, not ideal in others), and the long-term flexibility of the systems isn't enough for me.

If you were to ask me, building postgraphql as a standalone thing AND and as a configurable koa/express middleware with authorization and lifecycle hooks would make this tool useful much more long-term for me (use it as-is while developing the frontend, then customize with hooks as it nears production). That way, if I want to mount it as an API endpoint for a webapp with session auth, I can do that. If I want to use totally external authentication, or rule-based authorization, etc.

Edit: I just found https://github.com/calebmer/postgraphql/blob/master/docs/library.md, so the library thing is in the works - but I want to be able to replace logical components with hooks. Maybe I'll dive in and see if I can't do that myself 👍 .

[calebmer]

@dts a goal of this migration is to allow extensibility in JS land (this would be useful for PostGraphQL) and further flexibility when it comes to other databases (which may or may not have good DB auth capabilities like Postgres). I'd love to see what ideas for hooks you have in mind 😊

I'd also like to know if there was more documentation around auth in Postgres might this need be (at least somewhat) alleviated?

[dts]

Perhaps it would be, the two big problems are OAuth and Session-based auth (obviously the second is possible, it is unclear if the first one is). A good how-to would go a long way. Can you specify rules like users with ROLE=blah are allowed to access records in table Y as long as table.user_id = user.id?

The hooks that I added in koa-resteasy are an "authorize" hook (authentication was handled by a separate koa middleware), and pre-query and post-query hooks. Those hooks are given abstract information about the goals of the request (action/resource/data), so that they can perform other actions, like upload an image to S3, create a related model in an external service, etc.

[calebmer]

Can you specify rules like users with ROLE=blah are allowed to access records in table Y as long as table.user_id = user.id?

Yes actually, with row level security in Postgres 9.5 :blush:

If you want to hop into the Gitter chat we can talk more about RLS :+1:

[matthewmueller]

@calebmer really good read, thanks for sharing your vision. I also see the benefit of having a single source of truth and generating the other from it. I was originally thinking about it from GraphQL schema => Postgres tables + queries, but your forum example in this repo showed me what's possible with Postgres and I'm sold on Postgres being the source of truth.

One implication of this is that I'm wondering about is database migrations. Typically you organize migrations as something like this: 00-setup.sql, 01-create-user.sql, ... it seems like your functions would be spread over these migrations. I think this may get confusing, like where your functions actually live in the code. Do you see any issues with this?

The other thing I'm wondering is: have you tried moving the GraphQL queries themselves into Postgres? Something like this: https://github.com/solidsnack/GraphpostgresQL. It sort of goes against your idea of a universal interface, but it does allow the changes to be live immediately, instead of having to recompile your GraphQL API every time you make a change to your schema.

[calebmer]

@matthewmueller I have worked on another project in the past named accelerate aimed at making database migrations easier. Likely sometime soon I’ll come back to that project and add features to do stuff like this. My first thought is to support a templating language like handle bars inside migrations code so you can import files, add loops, etc. Exploring how we can better manage functions in a database migration framework might also be interesting. I currently don’t think its a problem but I’d love to hear solutions!

Postgres can do a lot inside the database (like the link you presented will do), but sometimes it could be bad for performance. I haven’t done too much research into this but I have heard warnings that complicated logic in Postgres does run the risk of blocking the database. In addition, part of my goal for PostGraphQL is to integrate with more data sources then just Postgres. This allows you to keep the majority of state in Postgres but then maybe have some data in another source you maintain like Contentful. I’ve definitely thought about it, but unfortunately it can limit options if you’re not careful :blush:

[lacker]

I'm glad to hear that extensibility in JS land is on the roadmap. In practice it seems like as node.js projects get larger, you eventually need to integrate some third party npm modules for something, and so you need logic in an application server that has a full node environment (so not just the postgres js environment). Things like image processing, hitting APIs like Stripe or Twilio, or handling social media login are tough to do unless you have hooks into a node environment. Do you have an API in mind for how it might work?

[calebmer]

PostGraphQL 2 beta is out (#145), try it out now and tell me what you guys think, thanks for coming along for the ride :wink:

To try it out just run the following commands (make sure you have a Postgres database listening on postgres://localhost:5432):

npm install -g postgraphql@next
postgraphql

[tomberek]

I've played a bit with https://github.com/sarkistlt/graphql-auto-generating-cms and https://github.com/marmelab/admin-on-rest for generation of a CMS. The main issues slowing this down is naming conventions.

[ungerik]

I think before having a complete CMS that is autogenerated, it would be benefitial to collect the generic front end components (for instance: autogenerate a table with delete/add entry buttons) that would be the building blocks of a CMS or admin interface.

This way PostGraphQL users are not locked into one flavour of CMS but are free to build their own out of the components.

[revolunet]

i heard that https://github.com/marmelab/admin-on-rest can be used with GraphQL too

[benjie]

Closing this issue because it's pretty old now and a lot has changed!