Discover whether PostGraphile is worth it

[gligneul]

📚 Context

The Rollups Node must provide an interface for querying the Rollups data (inputs, outputs, and proofs). The most agreed way is to use GraphQL because it is easy to use and very flexible. We have two options when using GraphQL. The first is implementing the API by hand by fetching the data from a database. The other option is using a tool that automatically generates this API from the database.

Currently, the node implements the first option with a few thousand lines of Rust code. If we could use a tool to generate this API instead, we might avoid having to rewrite this code in Go.

One of those tools is PostGraphile. Since we already use PostgreSQL, we can evaluate how this tool behaves in the current database and how the generated API compares to our manually built one. If we like the Postgraphile API, we will create another issue to migrate to it.

[endersonmaia]

We used it for a while to make some aggregations in the graph-node database that weren't yet implemented in the indexing code. Let me know if you want some help evaluating this.

Sharing a little bit about the approach we took, we created a view to map what we wanted in the output to be used by Cartesi Explorer.

The first thing you'd need to check is it's possible to keep the same GraphQL schema with the one generated by postgraphile, I know it's very configurable but we didn't used those features on our case. Or you should take care about the database schema to match what postgraphile needs.

Another thing is that since there are plans to make the node work with sqlite, postgraphile won't be a fit for sqlite.

[endersonmaia]

Since we're on this, in case we want to offer a REST API directly from the database:

• https://postgrest.org/en/stable/ (Haskell) I already used and approved
• https://docs.prestd.com/ (Golang) I never used

[gligneul]

Thanks for the feedback, @endersonmaia!

Sharing a little bit about the approach we took, we created a view to map what we wanted in the output to be used by Cartesi Explorer.

I was thinking about creating a completely separate database for the GraphQL API. Then, we could make the schema for this separate database stable and have the freedom to change anything we want in the internal node database.

Your approach might be a good option as well.

The first thing you'd need to check is it's possible to keep the same GraphQL schema with the one generated by postgraphile, I know it's very configurable but we didn't used those features on our case. Or you should take care about the database schema to match what postgraphile needs.

We don't need it to be 100% compatible. We can align this change with a major version and break compatibility slightly. In particular, the pagination/filter queries will probably be better in Postgraphile, so we could use this moment to improve the API.

Another thing is that since there are plans to make the node work with sqlite, postgraphile won't be a fit for sqlite.

This is not part of the roadmap anymore. I want to focus on a single environment, which is the one that has Postgres.

Since we're on this, in case we want to offer a REST API directly from the database:

If define a good database schema, this can be plugged from the outside.

[endersonmaia]

Just found this other option that's Rust and it's a Postgres extension, so no other service needs to be deployed since it will be inside the database itself.

It has its own scaling challenges since it will run within the database, but I thought it was good to bookmark it.

https://github.com/supabase/pg_graphql