> The next problem is type-checking disappears at the query boundary. This is a limitation of current tools. It doesn't have to be that way. The encoding of

> The next problem is type-checking disappears at the query boundary. This is a limitation of current tools. It doesn't have to be that way. The encoding of result row sets could elide type information and then the parser on the receiving end could just know the types from knowledge of the query that produced the result being parsed. If the application and the RDBMS were colocated then serialization could be avoided, though the price to be paid for that is that the application would have to use DB types. > Migrations should not be written in SQL but in some parseable, declarative format like JSON or YAML. > Why? Because to typecheck queries, you need to know what the schema looks like. To know what the schema looks like, you can either query the live database (it’s arguable whether this is good), or build a virtual model of that schema. I prefer static solutions. Nothing stops you from compiling a schema from a spec, or vice-versa even. I've used PostgreSQL's `COMMENT` feature to set JSON-coded comments, then I have a SQL query and jq-using script that produces a very nice JSON representation of the entire schema a) using all the metadata about the schema that PG itself makes available in the `pg_catalog`, b) enriched with the JSON `COMMENT`s embedded directly in the resulting JSON. Such `COMMENTs` in my case have UI-related metadata such that the unified schema JSON can be used to drive UI codegen. No need to live-query the DB once this JSON has been compiled unless you make schema changes. > If the database schema/migrations are defined in a declarative format, and the query language is some type-checked compile-to-SQL language, the database access tool can easily typecheck the queries separately from your codebase, and generate code to: [...] You can do it the other way around too (see above), but only if SQL's DDL is augmented. PostgreSQL's `COMMENT` allows for user-defined augmentation -- it's only not great because comments are free-form text, whereas a `JSONCOMEMNT` or alike that enforces valid JSON would be better. Everything TFA says about NoSQLs resonates. > I want strong and static types. I want queries I can typecheck statically, before executing them. Yes. > SQL is bad, for two reasons: > The syntax is bad. > Type checking is absent. SQL itself does not preclude static type-checking. PG even has the appearance of static type-checking, except it's done at run-time and is a bit leaky -- but this is an implementation problem, not a language problem. Not that SQL is a great language. Linq is clearly a superior idea, except that it dispenses with a language as such, so it's not quite a superior idea :) > > Why can’t SQLx just look at my database schema/migrations and parse the SQL itself? PG nowadays makes an AST of queries available. I don't recall what the stability of that AST's schema is, but it is possible to do this. It's still complicated, but so is writing a compiler, and writing a compiler is what we're talking about here. > Maybe someone can work out a way to do stored procedures that isn’t a huge liability with regards to migration and deployment. "Be more careful" Now for my opinion: - SQL is still evolving, and so is PostgreSQL -- we can expect better type-checking and better schema augmentation in the future, and we should demand it - PostgREST and similar make it very easy to build pure-SQL (or pure-PlPgSQL or similar extended SQLs) applications without a thought of ORMs, and without applications really having to know anything about SQL -- VIEWs, table-valued functions, JSON/whatever schema compiled from the SQL, etc. are enough to make the application SQL-unaware - therefore I think the post-ORM future is PostgREST-like front-ends for RDBMSes.