Set up a production-ready realtime GraphQL backend | Hacker News Reader

Set up a production-ready realtime GraphQL backend

hasura.io

98 pointspraveenweb57 comments

I don’t like working with ORMs for tons of joins and I don’t like denormalizing in tables, so I tend to write CTE-based JSON generating views for apps. (Yes, these are not high traffic apps...)

Hasura has been such a joy to work with against these dbs that I am willing to try and let the query abstraction do more of the output assembly with joins, etc via graphql.

No knock on the Prisma family of tools, and props also to subzerocloud and postgraphile/postgraphql, but I feel like hasura’s relationship with pg is more natural than anybody else’s. [edit: more compatible with the way I approach pg - YMMV and don’t hesitate to try the others they are solid too.]

> so I tend to write CTE-based JSON generating views for apps. (Yes, these are not high traffic apps...)

I enjoy using this pattern too. What kind of problems should I expect if it were to become a high traffic app?

If you use Postgres, CTEs are optimization boundaries that make temp tables - you may end up inlining the CTEs or making them into regular temp tables so you can put indexes on them.

I've heard this but I haven't heard why this is. Do you know if this is deliberate or just falls out of the implementation? I wonder if they'd consider an explicit syntax to have them inlined. I love CTEs for organizations, would love to not think about them affecting performance someday.

It's deliberate, and sometimes it's helpful to have a way to force the ordering of work in the database.

One potentially simple option is to have the database temp space stored in RAM, by registering a ramdisk. That also helps large sorts, which implicitly make temp tables.

You can also manually inline everything, but if you have a lot of joins, you may end up tuning queries by changing join order. By default, if there are >8 joins it uses the order you give it. If you set the 8 higher the planning step takes a little longer - fine if it's for ETL but not ideal for a web app.

> One potentially simple option is to have the database temp space stored in RAM, by registering a ramdisk. That also helps large sorts, which implicitly make temp tables.

PostgreSQL sorts small amounts of data in RAM by default, choosing to spill to disk only for larger amounts of data. This threshold is tunable:

https://www.postgresql.org/docs/current/runtime-config-resou...

If you want it to sort everything in memory all the time, a high value for `work_mem` in postgresql.conf should do it. Alternately, like most parameters, you can set it:

* on a per-user basis (ALTER USER _ SET work_mem='2GB')

* on a per-session basis using SQL within the app (SET work_mem='2GB')

* on a per-session basis using `libpq` environment variables (export PGOPTIONS="-c work_mem=2GB"), or

* on a per-transaction basis using SQL (BEGIN; SELECT set_config('work_mem', '2GB', true); COMMIT;)

I've used each of these mechanisms to turn various knobs over the years. For example: one database has a small-ish global `temp_file_limit`, which was preferred and worked fine for years until a certain overnight job started failing. Rather than raise the limit globally, I changed it just for the single query in question.

> You can also manually inline everything, but if you have a lot of joins, you may end up tuning queries by changing join order. By default, if there are >8 joins it uses the order you give it.

This… isn't true. Pick two tables and compare:

    EXPLAIN SELECT * FROM a JOIN b ON b.a_id=a.id;
    EXPLAIN SELECT * FROM b JOIN a ON a.id=b.a_id;

PostgreSQL chooses the same plan for both queries. It may do `a` then `b`, or `b` then `a`, but in either case the plan will be stable given the set of analyzer statistics.

https://www.postgresql.org/docs/current/geqo-intro.html

When the number of JOINs get large, the query planner does not consider every join order, since the number of possible orderings grows too large to search. This is where GEQO comes in:

https://www.postgresql.org/docs/current/geqo-pg-intro.html

> I don’t like working with ORMs for tons of joins and I don’t like denormalizing in tables, so I tend to write CTE-based JSON generating views for apps.

I'm not sure I follow. Do you mean you use database views that output json? And you prefer basing those views on select...with rather than with explicit joins? Because if they're all views on the postgres side, I'm not sure how orms affect the choice of cte vs joins?

Or are there orms that work more nimbly with ctes than with joins (or views)?

I'm currently fighting with some legacy ruby on rails apps that interface with various more-or-less legacy databases - and I've actually been somewhat pleasantly surprised by how concise some joins might be hammered out even under rails 4.

Yes, i am talking about asking pg to output json. This is mostly to make sure I get the structure I want with nested arrays etc, it is unnecessary with a single level query.

ORMs are not evil, but I like doing my queries in sql (and using pg functions). In the past I’ve seen the separate queries that an ORM would send to the db to build the data objects I wanted at the app level and I found it more comfortable to pound on queries in sql, then make a view so the app only needs to do simple selects. It was easier for me to maintain. I’m not saying it was the ORMs fault, but I liked having the ability to keep tables simple, make joined structures in views, then dump json out from there. With CTEs it is easy to nest arrays within parents etc. Then with PG we can materialize the view for data that doesn’t change frequently.

As horrible as that all sounds, hasura is the first layer that makes me want to try app directed queries, schema migrations etc. I got tempted by django way back but missed that train.

Ah, I think I see now. So it's not that you strongly prefer ctes to joins when manually writing sql and creating regular (or materialized) views - but that when writing views that output json, ctes are a natural fit?

This is a nice micro-tutorial, but it does not build you a production ready system. You don't seem to get backups, redundancy, automated updates, replicability, or anything else that most people would view as mandatory. It seems just show how to rapidly setup a one-off pet server?

If you use DO, Vultr, Heroku, or AWS, each have different ways of accomplishing backups, redundancy, etc.

GraphQL is really nice for the client. But I have an hard time believing that this flexibility given to the client (over well defined REST endpoints for example) doesn't hardly impact the complexity of the queries to make to the database!

How it is possible for a product like Prisma/Hasura to be able to generate the complex logic of the queries required to handle real world logic and support that extra client flexibility? In my experience even regular SQL ORMs are often a bottleneck in a REST application and you need to fallback to (or only use) plain SQL.

And even if you can fallback to hand written SQL in those GraphQL backends (I hope so!), the extra complexity of the queries to make is still there because of GraphQL.

With dataloading, you can turn anything that would need to be a join into two queries, using whereIns. It just takes a little extra work. I wrote a blog post on this exact issue a while back. https://zach.codes/graphql-query-batching-solutions/

> With dataloading, you can turn anything that would need to be a join into two queries

*two queries and a lot of extra memory consumption.

Great post! Very helpful thanks

I think Hasura takes care of building „good“ SQL queries that retrieve the data in a single request.

I agree that it’s often not viable to resort to the standard GraphQL resolvers that will produce n database queries when asking for a list of items with a corresponding joined field.

A while ago I built a MongoDB->SQL wrapper that would take a MongoDB query (with arbitrary many nested $include entries) and convert it to a single SQL query, which worked quite well. Of course this didn’t have any logic to check for permissions as we weren’t “crazy” enough to let arbitrary clients formulate the queries (we did it in the backend). The code is available at https://github.com/adewes/blitzdb, we used it with https://github.com/QuantifiedCode/QuantifiedCode.

You are right. Instead of writing resolvers for each graphql node, Hasura compiles the graphql query into a single SQL query. Read more here: https://blog.hasura.io/architecture-of-a-high-performance-gr...

PS: I work for Hasura.

Disclaimer upfront: I work at Prisma.io :)

It's very important to properly distinguish Hasura and Prisma (as well as AppSync & Graphcool while we're at it).

Hasura, AppSync and Graphcool are all Backends-as-a-Service (basically "Firebase for GraphQL"). You get an instant GraphQL CRUD API with a database and some ways to configure authentication/authorization. This is great for simpler applications but typically comes with limitations when requirements become more complex. Speaking from experience after 2 years of running Graphcool, we've learned from our customers that (especially larger) development teams quickly outgrow the capabilities of a BaaS and need more control and flexibility in their technology stack. This was exactly the reasoning why we've moved to Prisma which now replaces traditional ORMs and makes it easier for developers to build GraphQL servers (but also other applications like REST APIs).

Another point is that GraphQL was invented as a query language for APIs, not databases. Exposing a CRUD GraphQL API to client applications was never the purpose of GraphQL - clients should still be able to consume a domain-specific API that's tailored to their needs (you typically don't want to expose all CRUD operations to your clients in a production application).

With Prisma, we're trying to create the right abstraction™️ that helps people get started quickly but at the same time Prisma should remain the proper tool for them as their projects scale and grow in complexity.

While Hasura is a 'BaaS' (in the sense that it can be directly exposed to frontend applications), it absolutely does not come with the limitations that you have mentioned. Hasura is not an 'either or' solution like Graphcool or Parse or AppSync. Hasura is designed to scale with the complex requirements of a project over time and to get out of the developer's way when they need more power.

One of the main reasons why this is possible with Hasura and not possible with say Graphcool is the abstraction which Hasura provides. Hasura is not a black box which lets you do CRUD operations. It is a tool that adds powerful GraphQL APIs on top of Postgres, i.e, it works with any Postgres database, existing or new, no restrictions whatsoever. This means you are free to write your own code (and encouraged to) which speaks to Postgres directly with your favorite ORM and expose these APIs with "schema stitching" alongside Hasura.

Hasura therefore lessens the work of a GraphQL backend developer: use Hasura's GraphQL APIs when they fit your bill and write your own custom logic when needed as you would have done without Hasura.

In fact Hasura is a superset of Prisma when it comes to Postgres, you can also use Hasura as an ORM if you want to.

Disclosure: I work at Hasura on graphql-engine

Interesting that GraphQL popped up on the front page today. Spent a good portion yesterday digging through docs and tutorials about getting it setup in place of REST for my new project. Do the client side benefits REALLY outweigh the added complexity on the server side, particularly for light/minimal API calls? REST is certainly easier to whip up on the server side, and it's not like it adds a ton of overhead on the client side.

When the alternative is making multiple calls to gather all the data you need, then yes the benefits outweigh the added complexity. But not for minimal API calls.

For super small applications, where you have 5-10 endpoints, you can say that GraphQL is an overkill. But imagine having 100s of REST endpoints and the client making numerous round trips to the server fetching loads of unnecessary data; GraphQL certainly wins in this case as you can fetch everything and exactly everything in a single API call.

[Edit] Also, the GraphQL spec being defined, and the way it is defined, reduces unnecessary documentation, unnecessary decision-making, and unnecessary communication between teams.

So at this point it seems we are going back to how software was developed before web. Clients interact directly with database through a query language. In this case GraphQL is an equivalent of SQL and "custom GraphQL resolvers" are an equivalent of stored procedures, triggers and other business logic.

I wonder if we will see databases expose GraphQL access layers directly.

https://edgedb.com/

I think eventually that's where all of this is going. We're going to have to figure out how to bolt access control onto GraphQL in a better way than we currently do today but that's certainly a solvable problem.

About access control in context of this article, Hasura gives GraphQL APIs with a role-based-access-control layer that can be integrated with any standard auth system.

https://docs.hasura.io/1.0/graphql/manual/auth/index.html

PS: I work at Hasura

Also AppSync from AWS is a very access-control-first approach to GrapgQL hosting

I wonder if we will get to the point where servers will start exposing sql access layers directly.

I hope so. SQL is better defined, more usable, more complete, and has better access controls than GraphQL. But at the same time I both doubt it, and I think storage structure is the wrong abstraction to expose (and here data-mapped GraphQL is just as bad).

That's pretty much what we are doing at Hasura, expose Postgres to the client directly with a powerful enough query language on top of GraphQL so that developers can get away with writing as little business logic as possible.

The biggest challenge is access control which we have modeled based on Postgres's RLS but for application users and roles.

FWIW I appreciate the balance hasura is going for re: RLS. I balked at the interfaces I’d have to maintain to get strict pg-user per profile row access. It is nice to have a ramp.

I think your crystal ball is broken. That falls to shit as soon as you need things to work offline. Imagine if the gmail app worked that way - no more checking emails in tunnels.

How would you compare this to Prisma's free tier? I've been working through a tutorial series that uses that and it's pretty nice in dev. I've not skipped ahead to the deployment story for that though.

Prisma is used as an ORM for your database, so you would still need to write your own graphql server which sits in front of Prisma.

Hasura is meant to be exposed to the clients directly with declarative access control rules. When you have complex business logic you would write the logic yourselves and expose it alongside Hasura.

Disclosure: I work at Hasura on graphql-engine

Gotcha! It might just be my inexperience but that completely flew over me from your landing page. I can see how the auth bit implies it, but that sounds pretty cool and isn't really clear IMO!

Do you have something like Prisma's conversion from a very small datamodel to a very complete GraphQL types + functions file going on in the middle then?

> .. completely flew over me

Thanks for the feedback. We thought that the 'BaaS' part would convey it.

> .. small datamodel to a very complete GraphQL types ..

Given any GraphQL server, you can generate the types/functions (using introspection) to access the GraphQL server in a typesafe/idiomatic way for a language. A project which does this: https://github.com/dotansimha/graphql-code-generator.

Is there any sample app / quick start template of a full stack CRUD app (notes, todos etc) with user registration, signin? Not too clear on authn/authz seems to need to go down to Postgres level for such controls.

You can take a look at the examples in the repo. Here's one simple todo app with Auth0 integration. https://github.com/hasura/graphql-engine/tree/master/communi...

But we are coming up with a more detailed tutorial on how to build realtime full stack CRUD apps with Auth integration in the coming week.

Can you share a link to tutorial?

Sure, it's Wes Bos' Advanced React course (paid): https://advancedreact.com/

I am trying to understand the infra around using something like Hasura (or graphQL in general). If I wanted to make a site like HN, do I just use a hosted DB with hasura on top? Do I need to have HaProxy/NGinx for the reverse proxy between the client and Hasura?

What about caching of the results? All 100 viewers to my popular post will see the same page content and comments. Can I serve this from a cache insetad of the db, if I am using graphQL?

How will horizontal scaling work? replication? etc. I know Hasura may not be directly involved in these but I would appreciate any thoughts on these all the same.

If you are using Postgres as your database you can use Hasura to add GraphQL APIs on top of all the tables. Hasura can be exposed to the fronted applications with declarative permissions. You can add a reverse proxy if you want to. Hasura scales horizontally but you will have to manage Postgres on your own or use a hosted Postgres service like RDS.

> .. caching .. This is related to GraphQL in general. The support for caching is nowhere close to what is possible with RESTful APIs but it should eventually get there.

Here is my use case. I want to make a SPA. So i need to use a GraphQL client library like relay which again directly talks with Hasura. I am not sure where does the reverse proxy fits in.

Think of Hasura as a layer in between your front-end application and your Postgres database. You would have needed infra to deploy your custom GraphQL server which you can replace using Hasura which gives you CRUD operations on your database with role based access control as explained here https://docs.hasura.io/1.0/graphql/manual/auth/index.html.

Hasura doesn't make any presumptions about data caching as that is highly dependant on the app requirements. It simply tries to run the most optimal SQL query on the database whenever any data is requested as showcased here https://blog.hasura.io/architecture-of-a-high-performance-gr...

Hasura is horizontally scalable as it is inherently stateless. Each Hasura GraphQL engine instance runs independentally against the underylying database it is connected with.

For replicating the Hasura GraphQL schema across environments Hasura uses its Rails like migrations tooling as explained at https://docs.hasura.io/1.0/graphql/manual/migrations/index.h....

PS: I am part of the Hasura team

There are a LOT of technologies in this "automatic graphql schema/server" space now. I'd love to see a comparison article

Can anyone provide a small (partial) list of GraphQL servers that automatically map on top of a database?

Another example is Neo4j-GraphQL [1] - GraphQL API on top of Neo4j graph database.

[1] https://grandstack.io/docs/neo4j-graphql.html

I just tried putting the currently available tools a bit into perspective - check my comment above :) https://news.ycombinator.com/item?id=18494248

Here is one blogpost that we wrote (Hasura vs Prisma):

https://blog.hasura.io/hasura-vs-prisma-9ffc7271eda8

PS: I work at Hasura

This looks amazing.

So custom resolvers are node cloud functions, right?

It appears that you need a second Node.js server to implement your custom resolvers deployed via docker. This second server merges its schema with the automatically generated schema from PG.

This is a bit disappointing. I was expecting to just "point" parts of the automatic schema to some cloud functions hosted anywhere (Now v2, Lambda, etc) much like what AppSync does. This would bring all the benefits of going serverless and decoupling the schema from implementation. Some cloud functions could be written in Node, but others could be written in Go in a different provider.

You can write your custom resolvers in any language you want, you don't need to use node at all.

> Some cloud functions could be written in Node, but others could be written in Go in a different provider.

Yep, I write mine in Go with Hasura, works great.

So you merge the schemas on every cloud function?

Ah, are you talking about schema stitching? This is coming to Hasura I think.

Edit also see: https://news.ycombinator.com/item?id=18495971

Hey! Hasura founder here :)

Exactly this, we're working on adding remote schema merging into Hasura itself (kind of like AppSync) so this won't be a problem. This should be out very soon!

Awesome!

"... With Our Paid Service"

Reply on news.ycombinator.com