Static analysis of queries?

[karlhorky]

Hi @porsager ! 👋 Again, thanks for the continued effort on this library by yourself and team, really great!!

One thing that I've seen recently is squid, which promises, among other things:

Validate SQL queries in JavaScript and TypeScript code against your schema at build time 🚀

This is achieved by leveraging another library by Andy Wermke, postguard

I wanted to test the waters as to how open the Postgres.js project is to some kind of built-in static analysis of queries, to make sure that queries that get built can also run. I suppose the easiest would be TypeScript here.

cc @Minigugus

Alternatives considered:

One alternative that I've also considered here is to "only" improve the error messaging when things go wrong at runtime with a query: #450

[Minigugus]

Do you know ts-sql-plugin? It's more efficient (real-time in IDEs), less intrusive than squid (no extra code required) and should work with postgres out of the box. Also, since it is a typescript plugin, the tsc command even runs plugin validation so even build scripts could validate sql at no extra cost. (I haven't tested it thought)

There are other plugins more focused on postgres like postgres-ts-plugin that could even validate parameters types using .describe() but they are pretty recent and not well maintained (help welcomed 😁)

[karlhorky]

Do you know ts-sql-plugin? ...
...
There are other plugins more focused on postgres like postgres-ts-plugin

Nice, these look cool. However, couldn't get ts-sql-plugin set up on this demo project - there are not very many instructions other than npm install and adding to tsconfig.json, and after following these two steps, the plugin is not working. So it doesn't seem like these are a real solution until they are a bit more robust / documented.

Even if they were to become more robust, not ideal to have to set up and configure some external tool for this. So I think there is still value in having static analysis built in to Postgres.js out of the box (without having to install any other TypeScript plugins), so that there are no extra setup steps.

[porsager]

Hi Karl. Is this maybe what you've been looking for? https://twitter.com/CoEliya/status/1564036318229073921

[karlhorky]

This looks great! I've reached out to Eliya to see if I can give it a try!

[Pyrolistical]

It would still be useful to do static analysis to distinguish sql queries from fragments.

Right now the returned type of the fragment

sql`where x is not null`

is a PendingQuery.

If the template literal could be parsed unambiguously with https://www.typescriptlang.org/docs/handbook/2/template-literal-types.html, then the fragment can have its own type and can never be confused with a query.