Comment by vjvjvjvjghv
6 hours ago
The problem in my view is that there aren't good tools to debug advanced SQL stuff within the context of the whole system which is usually written in a higher level language. I just spent a few weeks modifying some code where the original dev put a lot of logic into stored procedures. That's in principle fine but it's really hard to figure the actual business logic when it's spread out over C# and then also SQL. It doesn't help that the SQL code looks like FORTRAN code from 1985.
Personally I think we need ORMs that allow expressing advanced SQL stuff with other high level languages. Or even better: The ORM detects where advanced SQL makes sense and uses it.
If I had to pick, I'd try to make the ORM redundant by making 'lower level' SQL easier to deploy rather than depending on sending strings of SQL queries and mutations over the wire.
I haven't worked in a single setup where raw SQL has been encouraged, because it always requires DB migrations and not all of them are safe. Nobody dares touch the DB server's resources by setting up stored procedures, materialised views, etc. etc. and instead people are blowing money on Redis instances and caching and shit.
I don't have an answer to this but I've hit a lot of issues in my career where I think, "this could have been solved months ago by pivoting a couple of tables or creating a new function." You have been able to 'script' the DB for decades but you lose a lot of what you gain from the traditional SDLC at the app layer.
Dapper in .net is fantastic to deal with raw sql, to the point I think I’m delusional because it’s so damn simple to send outrageous queries to the database and have those multiple mixed results turned into objects very simply.
I’ve never had an issue of raw SQL requiring migrations? Unless you’re talking of changing database engine? In which case I think it’s a bit of folly to imagine changing the database engine will not mean changes to your stack higher up the chain.