Rawdogging SQL when you're not a seasoned DB administrator basically makes an arcane art look occult.
Most people reach out towards an ORM or query building engine and otherwise don't really go far beyond the basic CRUD, joins, and some simple aggregations with groups. Since they try to be DB agnostic you'll rarely get an adaptor over CTEs or window functions or partitioning.
An LLM is great at exposing what a database is capable of doing with SQL and might even manage to navigate the most poorly designed of schemas. And it might even manage to design one to an acceptable standard if it has enough domain knowledge in its context.
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.
It’s not that hard to pass values as query parameters of a manually written query. With inferior databases that don’t support array parameters it’s a bit more work to construct the correct number of $ parameters in the query, but still not that hard
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.
> Rawdogging SQL when you're not a seasoned DB administrator basically makes an arcane art look occult.
Isn't that true of most languages? SQL has pretty simple syntax; I think the only reason it's sometimes seen as arcane is that fewer and fewer people bother to learn it.
dba here and I really don't get why SQL is so feared... I get that it requires very different way to think about data but it is quite simple in terms of you tell it what to do, and if it does it badly you probably told it wrong so just try something different...
1. SQL isn't composable (you can't assign fragments to variables except for CTEs) so you can't easily test out subparts and build them up incrementally without just copy/pasting stuff around.
2. Joins are an unnatural way to dereference pointers.
3. SQL is more than SELECT. Once you get into updates you encounter lots of scary edge cases and traps. How many engineers really understand isolation levels? Why doesn't skipping the column list in an INSERT substitute nulls for the nullable columns that aren't provided? What changes can you make to a schema that are 'safe' for your environment (won't take table locks)? What locks are being taken by the RDBMS behind your back - sometimes it matters!
4. Site outages caused by optimizer plan shifts are scary because people don't feel in control.
Good databases have features to ameliorate these issues, but most people's experience is of databases that are merely OK and not good.
very interesting thanks for the insight, I feel like there are broadly two camps of developers, one like yourself probably wants to know every last detail, and the other that are somewhat prefer to stay naive and taking the "declarative" part very broadly...
there's a bit of cyclic relationship at play here, the data changes the query plan, and the query plan affects the performance, while the performance is being optimised by the engine. if the architecture is bad then sooner or later the engine runs out of tricks and performance suffers but then nobody never is quite sure whether the current architecture's good enough.
I totally get the whole nosql that was the rage for a while... quick to prototype but I do believe once it's somewhat settled, moving the consolidated parts back to SQL is much easier to manage and optimise
GraphQL joins are entirely up to the underlying resolvers, it has no syntax for expressing different kinds of joins or filter conditions on values from multiple joined relations
They aren't the same. Views are persistent objects, not like local variables that exist only for the scope of an operation. CTEs are the closest equivalent but you can't factor out predicates that way.
Why does it matter? It’s like a named operation, and if you want local scope you can create CTEs, which is like an anonymous local operation. So you have both options.
> How many engineers really understand isolation levels?
I feel like there’s no excuse for this one. You need to know how your data store will interact with your query and others.
The problem, I think, is what the tail end of that is, and is what you hinted at when discussing locks: RDBMS interaction. I have come around on this recently (quite recently - after reading and re-reading this article, and the comments), so forgive me if any past comments in my history indicate otherwise.
It is unreasonable to expect a developer to administer an RDBMS. If you're a small startup, you kind of have to out of necessity; maybe if you're lucky, you hire a dev who's also done infra work, and if the stars align, they've specifically administered an RDBMS at scale. But what counts as administration? Let's look at adding a secondary index, possibly the most common DDL.
AFAIK, no ORMs / frameworks (I am assuming here that most devs are using some kind of abstraction for RDBMS access) default to "safe" builds - no `CONCURRENTLY` for Postgres, and no reducing `lock_wait_timeout` to something sane for MySQL (I've no idea about MSSQL nor Oracle, though I also assume that if you're running one of those, you probably have a DB team). So already, there is an implicit assumption that they've read the pertinent manual section[s] for their RDBMS, which seems unlikely. Even if they did, there's a chance they would also need to have read and understood the paragraphs on handling invalid index builds (Postgres), or the impact that foreign key constraints can have on metadata locks (MySQL).
Let's say the line gets drawn at "devs should be able to understand that they [probably] need secondary indices," with implementing those being entirely on another team or service. OK - how much do they need to understand? I think it's reasonable to expect a developer to understand B+trees; after all, they're just a data structure. Should they need to be able to internalize that such that they can understand why doing a range scan on a column in the middle of a multi-column index removes everything to the right of it from B+tree filtering? Probably, but now we're significantly deeper into specifics. Should they know that there are different kinds of indices, like GIN? Maybe. What about different operator classes (Postgres) for them? Maybe, maybe not. What about knowing about its `fastupdate` option, and the related `gin_pending_list_limit` configuration item? I'd love to say no, those are squarely in the world of ops, but then why should they be allowed to create the index at all if it's going to increase someone else's operational burden?
For all these reasons, I don't think it's prudent to have dev teams managing their own DBs. But then, you get into the fight that most places seem to be in, where the devs want to do something to the DB that the ops team knows will be a headache later, they push back, product gets mad that they aren't shipping, ops capitulates, and then the headache predictably becomes real months down the road. Rinse and repeat.
I have no clue how to fix this while maintaining the modern trend of velocity dominating everything else.
Some of it can be fixed by automation, but yes it's a problem. The NoSQL trend was partly I think a reaction against the complex feature sets and quirks of SQL databases. But then those features existed for good reasons, and just saying "we do less" isn't really a simplification, it's just ... doing less. MongoDB also distinguishes between concurrent and non-concurrent index builds, requiring you to pick up front. And it's not fully concurrent anyway.
> Rawdogging SQL when you're not a seasoned DB administrator basically makes an arcane art look occult.
This is kind of a hot take. Most devs I know know PostGreSQL well. They know how to write complex queries with CTAS, joins, etc, know how to create indexes, views, and add user defined functions.
Most people reach out towards an ORM or query building engine and otherwise don't really go far beyond the basic CRUD, joins, and some simple aggregations with groups. Since they try to be DB agnostic you'll rarely get an adaptor over CTEs or window functions or partitioning.
An LLM is great at exposing what a database is capable of doing with SQL and might even manage to navigate the most poorly designed of schemas. And it might even manage to design one to an acceptable standard if it has enough domain knowledge in its context.