graph,graphql,server,store: Subgraph Sql Service

[gusinacio]

Semiotic Labs is thrilled to present the Subgraph SQL Service in partnership with TheGuild and Edge & Node. This exposes a GraphQL type to any graph-node user, providing a secure SQL interface to directly query a subgraph's entities.

SQL Service

We introduce a new sql field on the "Query" type that exposes inputs so graph-node can receive raw SQL.

We also implemented a lot of safe sql rewrite to remove functions that we think may break the postgres instance along with some schema safety.

We also expose each of the tables via Common Table Expressions where we can have granular control over what is exposed or not and some workaround exposing only the latest block data.

What we cover on this PR

• New sql query type
• Defined API for inputs for backward compatibility with future work
• Environment variable/Config to enable or disable the SQL service
• Safety measures for SQL threats via complete AST SQL scan.
• Latest block data
• Single subgraph query
• JSON and CSV union output

What we do not cover on this PR

• Cross subgraph joins
• Query on specific block/snapshot
• Query optimizations
• Query parameters binding
• Query/Join between two different blocks/snapshots

[lutter]

Wow .. that's a really nice addition!

Before I start reviewing this in detail, I noticed a few things from a quick look at the PR:

• there is no end-user documentation. Can you add something to docs/ that explains how to run queries? Obvious questions that should answer are: how do I send a query (example)? how do I use bind params? how does the GraphQL schema relate to the tables that this can query? What are the attributes/columns that can be queried? How much of SQL does this cover? I haven't looked but does this allow e.g., aggregations, window functions, CTEs etc.?
• for operators: since SQL queries can be arbitrarily complex and take an arbitrary amount of time, how do they guard against long-running queries?
• a minor nit is that commit messages should follow the guidelines here This won't cause us to not approve the PR, but it would be nice to keep with the current scheme.
• this might be too much work, but I see that a sql crate appears and is then folded into the store - would it be possible to squash various commits so that there are fewer commits that fix up previous ones, not just for that crate but other fix commits, too? It'll make reviewing a lot easier

[gusinacio]

Wow .. that's a really nice addition!

Before I start reviewing this in detail, I noticed a few things from a quick look at the PR:

• there is no end-user documentation. Can you add something to docs/ that explains how to run queries? Obvious questions that should answer are: how do I send a query (example)? how do I use bind params? how does the GraphQL schema relate to the tables that this can query? What are the attributes/columns that can be queried? How much of SQL does this cover? I haven't looked but does this allow e.g., aggregations, window functions, CTEs etc.?
• for operators: since SQL queries can be arbitrarily complex and take an arbitrary amount of time, how do they guard against long-running queries?
• a minor nit is that commit messages should follow the guidelines here This won't cause us to not approve the PR, but it would be nice to keep with the current scheme.
• this might be too much work, but I see that a sql crate appears and is then folded into the store - would it be possible to squash various commits so that there are fewer commits that fix up previous ones, not just for that crate but other fix commits, too? It'll make reviewing a lot easier

Hey @lutter, thank you for the information. Here are some notes:

• we added end-user documentation about the SQL-service to /docs as requested
• graph-node has some build-in mechanisms to cancel long-running queries and we are relying on that
• thanks for pointing out the guidelines, we rebased and reworded all the commits to make it easier to review
• while rebasing, we removed how it was made and created commits of the "happy-path". We removed this appearance and fold of the SQL crate and fix up some commits.