Anything using complex non-basic SQL (heck even json_agg or computed results) tends to devolve into raw SQL and lose ORM and end up fighting ORM a bit due to things like shared transaction/connection pool.
Too tired to think deeply, but yes, I think ORMs often have hidden costs while still being net positive.
SQL and relational DBs are so powerful and flexible that an ORM’s approach will usually foreclose or complicate something. I think ORMs make the DB basics easier and faster for devs, but… 1/4
ORMs help less a) the more the complexity or constraints of the work increase and b) the more the ORM insists on One Right Way to approach relational DBs. Dev time is probably required to overcome the ORM in such cases. 2/4
On balance and in general, I think their benefits usually outweigh their negatives, at least with recent vintage ORMs. But DB work of any substance can get tricky, and ORMs can’t fix that. 3/4
I guess I’m feeling this topic because yesterday my ORM forced me to use SQL instead of its nifty ORM-ness. Not sure if it was the problem or if the problem was me, but I couldn’t make it reference a CTE in place of an ORM entity. (Which is stupid easy in SQL)
To prevent SQLIs, it's sufficient to use PreparedStatents.
Hidden costs for ORMs may occur if ORMs get updates containing CBCs (Code Breaking Changes).
However, ORMs make developer's life a bit easier overall. As such, it outweighs potential hidden costs, IMHO.
I would add that developers still make mistakes with prepared statements (especially when the SQL string is built via complex conditions/concatenation/etc, or junior developers make edits), so you must also validate the SQL strings are developer defined: https://eiv.dev
Comments
SQL and relational DBs are so powerful and flexible that an ORM’s approach will usually foreclose or complicate something. I think ORMs make the DB basics easier and faster for devs, but… 1/4
*sigh* ORMs
4/4
There are actually cases where a raw SQL query can be more secure than an ORM, depending on the ORM's syntax: https://www.nodejs-security.com/blog/raw-sql-queries-better-for-security-than-orms
ORMs often have things that are not a fit to their proposed abstractions, and have to provide an escape hatch.
I think it's often a net positive, nonetheless.
I wrote something in relation to security about raw SQL vs ORMs: https://www.nodejs-security.com/blog/raw-sql-queries-better-for-security-than-orms
Hidden costs for ORMs may occur if ORMs get updates containing CBCs (Code Breaking Changes).
However, ORMs make developer's life a bit easier overall. As such, it outweighs potential hidden costs, IMHO.
https://eiv.dev
https://github.com/craigfrancis/php-is-literal-rfc/tree/main/examples
If memory serves, you’re using Go atm; so this is how I would start, with a package that’s for the database abstraction (maybe taking an SQL string, or individual parameters for an ORM):
https://github.com/craigfrancis/php-is-literal-rfc/blob/main/others/go/index.go
https://jdriven.com/blog/2017/10/sql-injection-prepared-statement-not-enough
https://github.com/craigfrancis/php-is-literal-rfc/blob/9e9223a4b98987166938f53b4c4bd37ed7cd50e9/examples/sql-basic.php#L55