Well, if you can't use languages that make it possible/feasible, then code generation might indeed be the best option. It's just that your claim sounded quite general, so I had to jump in. :)
TypeScript can do this. It was done already halfway-well 6 years ago with way fewer features in TypeScript (https://github.com/brianc/node-sql/blob/master/lib/types.d.t...) - today we can do much better.
Its technically codegen but only from the schema; queries are fully typed on-the-fly.
You will also need to use a tuntime dsl to describe the schema, and then derive both the model classes and the model types from it (just because runtime parts cannot be derived from types)
Other than that, the rest is very possible.
This is kind of the whole ballgame right? How would you do any of this with types if the schema isn't defined until runtime?
The popular choice here is to use classes plus decorators, but its not the only choice
https://typegraphql.com/docs/introduction.html#what
The convenient bit here is that classes already have both a type and a runtime representation, and decorators are also available to attach any extra metadata necessary to that runtime representation
Interesting reading along the same lines is Language Oriented Programming (1994) http://www.gkc.org.uk/martin/papers/middle-out-t.pdf
Often in Java/Kotlin projects people end up writing compiler plugins / annotation processors which is like moving the codegen to build time and making it harder to inspect/understand, while simultaneously spending more developer time on it since those systems may need to run for every compiler invocation. The same goes for Rust; rust’s various macro systems target the problem I have, but Rust users report long compile times due to macro magic. More esoteric languages like Scala and Haskell might have the expressive power I want, but don’t seem practical to implement for other reasons.
Of course there’s always the runtime only approach in Ruby/ActiveRecord but that runs into correctness problems at practical scales.
Codegen is usually brittle and is often ugly, but is guaranteed to have more metaprogramming power than in-language typelevel features. Ultimately any such system can be complex, I just hope to build one where the leverage I payed for with complexity is well worth it.