this post was submitted on 29 Mar 2024
14 points (88.9% liked)
SQL
470 readers
1 users here now
Related Fediverse communities:
- #sql on Mastodon
- #postgresql on Mastodon
- c/PostgreSQL on programming.dev
Icon base by Delapouite under CC BY 3.0 with modifications to add a gradient
founded 1 year ago
MODERATORS
you are viewing a single comment's thread
view the rest of the comments
view the rest of the comments
Have you considered raw queries (properly parametrized and escaped and all that)? You can’t beat the speed of getting exactly what you need with no overhead.
What you’re describing is basically a fancy ORM.
My #1 issue with raw sql is its just absolutely a nightmare to maintain.
I simply just can't easily, at a glance, do something as simple as "give me the list if every single chunk of code that touches this column on this table", which is like, 80% of my start points for debugging an error showing up on our backend.
"We sometimes get NULL being set on this column that should no longer be NULL if (other column) is getting set, can you investigate how that us happening?"
If you have an application that uses raw sql, simply just step 1 of "find all backend code that touches that column" is already 100x more effort than it should be, and that's even on a well maintained project.
If the sql is even slightly poorly maintained (and since you are tasking BE (some language other than sql) devs with maintaining SQL, it very often is very poorly maintained, often just shoved as raw magic strings in the middle of their code, so.etimes even generated dynamically.
At which point its just a fucking nightmare to figure out what the fuck is writing to that column.
With an ORM, the issue suddenly becomes as easy as clicking the "find references" button on the field for that column and, boom, all bits of code that touch that field in any way are now listed put for you, ez.
You can still do that.
For example, you'd still write classes for your tables:
and then you'd just do
That let's you write raw sql about as close as it gets, while still having some degree of type-safety. You could drop a query like that into Dapper, and you're pretty close to just using raw sql.
I don't see why I'd do that over
Which will produce pretty much the exact same sql under the hood but be 100x easier to read, maintain, and debug.
Because just Dapper will perform a lot better executing raw sql queries than EF having to go through an entire expression tree builder.
Anyway, I wasn't saying that that example is a better way than doing it with EF, I was just going over your points where you mentioned that with raw SQL it's just all unreferenced magic strings with no references to tables or columns. And that you can't find where anything is used.
So that's just to explain - if you write your sql inside code in the poorest possible way - yea, you're gonna have a poor experience. But if you want to write raw sql instead of using an ORM, it's pretty easy to negate all those downsides about not having references
I'd like to see some benchmarks on truly how much this difference matters when running on the cloud.
I expect latency alone between the App<->Db will dwarf whatever microseconds your raw sql would save that it's hard to distinguish from the chaos of latency variance.