
PostGraphile Instant GraphQL API for PostgreSQL Database - jdnier
https://www.graphile.org/
======
Klathmon
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.

~~~
BenjieGillam
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 :)

~~~
schickling
I 100% agree with what Benjie said. With things like schema stitching (see
[https://blog.graph.cool/graphql-schema-stitching-
explained-s...](https://blog.graph.cool/graphql-schema-stitching-explained-
schema-delegation-4c6caf468405)) 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-...](https://blog.graph.cool/reusing-composing-graphql-apis-with-graphql-
bindings-80a4aa37cff5)

------
lldata
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.

~~~
agentultra
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.

------
vemv
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)

~~~
pfooti
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.

------
optimuspaul
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.

~~~
BenjieGillam
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.

~~~
schickling
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/](https://slack.graph.cool/)

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

~~~
moondowner
"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/](https://www.graphile.org/postgraphile/introduction/)

------
pfooti
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?

------
intrasight
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.

~~~
schickling
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.

------
tensor
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?

~~~
BenjieGillam
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 :)

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

I always worry about change management and inefficient designs.

~~~
overcast
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.

~~~
SwellJoe
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.

~~~
piaste
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.

~~~
SwellJoe
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).

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

~~~
petetnt
Edit: Apparently this is based on
[https://github.com/postgraphql/postgraphql](https://github.com/postgraphql/postgraphql)
which makes sense!

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

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

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

------
everdev
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.

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

------
mxstbr
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.

~~~
BenjieGillam
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.

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

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

~~~
dustingetz
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!

~~~
sorenbs
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](https://github.com/graphcool/graphql-binding)

~~~
dustingetz
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)

~~~
intrasight
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.

~~~
dustingetz
Where can I learn more about this?

