You can take the raw SQL string and pass it--at compile time, treating the database itself as part of the product you are linking against (and it doesn't have to be the full production one: it can be a local one built from the artifacts in the repository)--to the database server along with the types of the input placeholders and it should be able to tell you the types of the output columns in the result set. I've implemented this before as a macro for Clojure and it was a godsend.

With tests? Or just a schema definition?

Tests are a type of runtime validation, they require literal execution of your code to perform the validation.

This supports static verification of queries, your code doesn't need to run to validate the type is correct.

I don't care to argue either way for tests vs static types (I write both frankly), just want the distinction to be clear.