4 ms·

Those tend to be great until two words come into play: Dynamic SQL.

wswope 3y ago
What’s your issue with dynamic SQL in this case? I’ve written aiosql functions that use dynamic SQL (via plpgsql) just fine.
dkersten 3y ago
I’ve been using aiosql and Hugsql for years and have never had any problems. In fact, Hugsql has a “snippets” feature that allows you to compose bits of SQL dynamically. https://www.hugsql.org/using-hugsql/composability/snippets https://www.hugsql.org/using-hugsql/composability/snippets But the vast majority of SQL I’ve worked with simply doesn’t need it. But even when you do, the situation has never been any worse than it is with ORM’s for me.
- mixedCase 3y ago
  I guess I failed to set the context correctly given that you presented solutions for Clojure and Python, where it isn't as much of a problem since from the start the language fails to provide compiler guarantees you usually come to expect out of a SQL driver wrapper in typed languages (even though Clojure macros are probably powerful enough to allow this). As a comparison, DX-wise this is no safer and is indeed very similar to the usual idiom in Go for example, where you just concatenate (pre-interpolated) SQL strings. But when you actually want the compiler to prove the correctness of your queries even in a rudimentary way, these .sql file solutions usually (if not, everytime) fail to provide the necessary external checker that processes templates and uses an accurate model of your database and SQL to verify that all used combinations make sense. The closest thing to a proper take on this I've seen is https://github.com/andywer/squid https://github.com/andywer/squid with https://github.com/andywer/postguard https://github.com/andywer/postguard which, although the SQL is inlined in the code, it uses the right approach for verifying correctness as far as I could tell in the little time I experimented with it.

What’s your issue with dynamic SQL in this case? I’ve written aiosql functions that use dynamic SQL (via plpgsql) just fine.

dkersten 3y ago

I’ve been using aiosql and Hugsql for years and have never had any problems. In fact, Hugsql has a “snippets” feature that allows you to compose bits of SQL dynamically. https://www.hugsql.org/using-hugsql/composability/snippets https://www.hugsql.org/using-hugsql/composability/snippets But the vast majority of SQL I’ve worked with simply doesn’t need it. But even when you do, the situation has never been any worse than it is with ORM’s for me.

mixedCase 3y ago
I guess I failed to set the context correctly given that you presented solutions for Clojure and Python, where it isn't as much of a problem since from the start the language fails to provide compiler guarantees you usually come to expect out of a SQL driver wrapper in typed languages (even though Clojure macros are probably powerful enough to allow this). As a comparison, DX-wise this is no safer and is indeed very similar to the usual idiom in Go for example, where you just concatenate (pre-interpolated) SQL strings. But when you actually want the compiler to prove the correctness of your queries even in a rudimentary way, these .sql file solutions usually (if not, everytime) fail to provide the necessary external checker that processes templates and uses an accurate model of your database and SQL to verify that all used combinations make sense. The closest thing to a proper take on this I've seen is https://github.com/andywer/squid https://github.com/andywer/squid with https://github.com/andywer/postguard https://github.com/andywer/postguard which, although the SQL is inlined in the code, it uses the right approach for verifying correctness as far as I could tell in the little time I experimented with it.