4 ms·

SQLx is amazing. Can't do much better without replacing SQL itself. One remaining problem is writing dynamic queries without giving up static verification comp

SQLx is amazing. Can't do much better without replacing SQL itself. One remaining problem is writing dynamic queries without giving up static verification completely. For example, optional WHERE, ORDER BY, GROUP BY clauses are very common. Currently the solutions are: * Give up and write a fully dynamic query. * Generate every possible permutation. Combinatorial explosion quickly makes this impractical. * Write a static query without the optional clauses, then add clauses and execute a dynamic query. The best of both worlds but complicated. However the optional clauses are almost always independent from each other. It should be possible to statically verify each one with `SELECT NULL FROM <full set of JOINs> WHERE/ORDER BY/GROUP BY <optional clause>`.

Kinrany 5y ago
The list with fixed formatting: * Give up and write a fully dynamic query. * Generate every possible permutation. Combinatorial explosion quickly makes this impractical. * Write a static query without the optional clauses, then add clauses and execute a dynamic query. The best of both worlds but complicated.
nitnelave 5y ago
I'm working on a PoC static SQL query builder using generics, but it's pretty opinionated and kinda assumes that the binary is controlling the DB, instead of the DB being independently managed. And since it's static, it would generate every possible reachable permutation at compile time, so yeah :/ EDIT: Now that I think about it, you could have a partially dynamic query that's still statically checked: new WHERE conditions don't change the structure of the query, so as long as they're valid (which can be statically checked) they can be added to the query without changing the type. Same for e.g. ORDER BY or LIMIT. That would make the combinatorial explosion much more manageable, and it would actually represent different code paths (requesting a different set of fields).

Kinrany 5y ago

The list with fixed formatting: * Give up and write a fully dynamic query. * Generate every possible permutation. Combinatorial explosion quickly makes this impractical. * Write a static query without the optional clauses, then add clauses and execute a dynamic query. The best of both worlds but complicated.

nitnelave 5y ago

I'm working on a PoC static SQL query builder using generics, but it's pretty opinionated and kinda assumes that the binary is controlling the DB, instead of the DB being independently managed. And since it's static, it would generate every possible reachable permutation at compile time, so yeah :/ EDIT: Now that I think about it, you could have a partially dynamic query that's still statically checked: new WHERE conditions don't change the structure of the query, so as long as they're valid (which can be statically checked) they can be added to the query without changing the type. Same for e.g. ORDER BY or LIMIT. That would make the combinatorial explosion much more manageable, and it would actually represent different code paths (requesting a different set of fields).