A "proper" solution to this problem requires language changes (as Microsoft did with C# 3.0).
For example, ActiveRecord doesn't have support for window functions, but its underlying AST (a library called ARel) allows you to extend it, which at one point I did, allowing me to write something like this:
things = Arel::Table.new('things')
things.project(Arel::Nodes::NamedFunction.new(:sum, [things.count]).over('my_window')).
window('my_window').
order(things[:name]).
range(Arel::Nodes::Preceding.new)
select sum(count(*)) over my_window
from things
window my_window as (order by name rows unbounded preceding)
In other words, I could continue to express whatever I wanted of SQL, at the language level — no type mismatches.
The SQL builder code isn't pretty, to be sure, but it's something you typically encapsulate into a lower-level module rather than writing in application code. And as it's highly analogous to the SQL code it generates, it's not like it's that much more complicated. (ARel isn't a shining example of how to do it right, either. I expect Diesel implements the AST aspect more cleanly.)
Edit: I see what you're saying about typed record types. I was thinking more about the grammar, not the shape of the data.
I'd like to point out that strong static-typing does not preclude powerful metaprogramming! Nim's macro system is extremely powerful because of it's great interplay between it and the type system.
Look at what Microsoft did with LINQ. You can't pull that off in Rust no matter how much you want to.
The main reasons I like this approach are:
* it makes way easier (and safer) to reuse query parts compared to string composition/interpolation
* the ORM takes care of differences between the backends, so it is easier if I have to port the application to another RDBMS.
1. http://www.sandimetz.com/blog/2016/1/20/the-wrong-abstractio...
For example, in ActiveRecord (the Ruby library that this looks very much influenced by), given:
...you can now do things like: or and then you can extract data: or do mutate it: or ...And so on. By encapsulating a result set as something that can be augmented with new query clauses (where, limit, select) and so on, you can incrementally build queries, pass them to other functions, store them as member variables so that they be reused as "scopes" across multiple calls, and so on.If the query were just a string, this sort of thing becomes awkward, verbose, brittle, and generally not type-safe.