Indeed, that's incredible! I tend to think that the actual layout of db tables a low level concern that is mainly driven by query performance and simplicity.
> - Type mapping is surprisingly hard to get right, especially with composite types and arrays and custom type converters. For example, a query might return multiple jsonb columns but the app code wants to parse them into different structs.
In Rust there's serde for converting json into structs (or enums if the shape of the json is complicated enough). Doesn't Go have a similar serialization/deserialization library?
> - Dynamic queries don't work with prepared statements. Prepared statements only support values, not identifiers or scalar SQL sub-queries, so the codegen layer needs a mechanism to template SQL. I haven't built this out yet but would like to.
In this case, what about stored procedures?
But, generally, instead of a template language for SQL, I would like to have a real compile-to-SQL higher level language, that could also do dynamic queries. The trouble with templating is that dynamic queries are hard to do in a type-safe manner, and it's hard to prevent generating invalid SQL when you have a bad template substitution (and then you get bad error messages from the db). There's a few languages like this do this https://github.com/ajnsit/languages-that-compile-to-sql but none fits the bill