The best experience I know of in this regard is from some packages in ML languages, where the string literal query you write gets sent _at compile time_ to a database engine for parsing, using the database's API to determine the query's expected return type (column names and types).

They're most used in F#, which calls them type providers:

https://github.com/fsprojects/FSharp.Data.SqlClient

https://hackage.haskell.org/package/postgresql-typed