sqlx

Agree, I want to write raw SQL and have some tool that validate it and generate the correct return type for it, similar to sqlx[0], but AFAIK there is no such tool yet in Typescript land, there are a few that are taking that direction. [0] https://github.com/launchbadge/sqlx

I was thinking on how a library could work with the type inference, one idea that came to mind is to have some script running in the background (if you are using a bundler, you might be able to hook into it) that search for all sql tag templates, execute them against a live database to validate and get the result structure. This is how sqlx[0] works and might is worth exploring more in deep. One downside of course is that AFAIK typescript is not powerful enough to match all queries to types, so my idea was to build a .d.ts file that maps the queries with the result. ts-safeql seems to work with a lsp plugin? I didn't dig into how lsp and plugin works, so I cannot speak of if my approach will work with it, but I suppose no, because it needs to generate files. [0] https://github.com/launchbadge/sqlx

This looks like a less ergonomic version of Rust's SQLx (https://github.com/launchbadge/sqlx) but a more robust version of TypeScript's sqlx-ts (https://jasonshin.github.io/sqlx-ts/). Sqlc seems to copy the latter's unfortunate lack of inline SQL statements. Still, seems promising.

SQLx seems to do some form of this. though what you're suggesting may remove the build time dependency on "connecting" to a SQLite database. "SQLx supports compile-time checked queries. It does not, however, do this by providing a Rust API or DSL (domain-specific language) for building queries. Instead, it provides macros that take regular SQL as input and ensure that it is valid for your database. The way this works is that SQLx connects to your development DB at compile time to have the database itself verify (and return some info on) your SQL queries." https://github.com/launchbadge/sqlx

I haven’t used Postgres but is this the issue you’re talking about? https://github.com/launchbadge/sqlx/pull/1539

"A jaw-dropping amount of effort has been spent attempting to bridge the gap between the relational paradigm of SQL and the object-oriented nature of modern programming languages. EdgeDB sidesteps this problem by modeling data in an object-relational way." All the best to the team. I however truly hope this isn't the direction the industry moves toward. I thought we learned our lesson from MongoDB. I still believe data is best modeled in sets, not objects. The solution isn't for databases to become more like object stores but for general purpose programming languages to be more amenable to seamless access of set-oriented data. More stuff like this: https://github.com/porsager/postgres https://github.com/launchbadge/sqlx