The ORM discussion reminds me of something Rich Hickey said in his talk, “Simple Made Easy”. He distinguished between “simple” (when a system is inherently low in complexity) and “easy” (when a system is made more complex so it is theoretically easier to use).
ORM’s are easy, but not simple. If your system uses a relational database but not an ORM, you have to understand your particular database and also SQL to understand your data layer. If you add an ORM, you aren’t actually saved from having to understand those things, you just also have to understand your ORM on top of all that. There is some positive tradeoff you get in return, since you don’t necessarily have to grapple with the added complexity all of the time.
The main saving grace of ORM (or at least query builders) seems to be that the alternatives aren’t well-supported in tooling, so the “raw SQL” alternatives often end up implemented as string concatenation hell, which is admittedly terrible. Installing parameterized SQL on the DB itself in stored procedures works great, but you have to go out of your way to do it; it never really comes across as a plug-and-play option even though in principle it easily could be.
ORM libraries may not be simple, but what matters is the code that uses them.
My experience was that the main code was often easier and simpler. Easier because small tasks are made easy. Simpler because it makes the code more consistent: for instance, once you know the ORM, you don't have to deal with hundreds of specific cases that insert or update various records.
Promoting stored procedures as an alternative to the complexity of an ORM seems strange to me. They still have to be carefully written like any raw SQL. There is no canonical way to keep them in sync with the code. They are vendor-specific. As far as I know, you need to learn specific tools to debug them or to analyze their performance.
> Simpler because it makes the code more consistent: for instance, once you know the ORM, you don't have to deal with hundreds of specific cases that insert or update various records.
That's not what 'simpler' means in this context. Simpler means conceptually independent things aren't coupled together. For example, ORMs couple together object-oriented code and relational objects. That's the essential complexity that they introduce.
> There is no canonical way to keep them in sync with the code.
There's still the old-fashioned way of checking them into the repo as part of migrations.
> They are vendor-specific.
Most people will almost certainly stay with a single vendor anyway. If you never take advantage of your platform for fear that you'll migrate away from it, you're throwing away a lot of potential benefits for some perceived future risk.
> As far as I know, you need to learn specific tools to debug them or to analyze their performance.
No more than you would with an ORM–in fact, with an ORM you'd need to learn two different stacks to tune performance or troubleshoot.
> Simpler means conceptually independent things aren't coupled together. For example, ORMs couple together object-oriented code and relational objects. That's the essential complexity that they introduce.
I don't actually agree that this is where the complexity comes from. The complexity comes from what makes ORM so appealing in the first place. Your ORM can dynamically generate an infinite variety of queries based on how you use it, which makes it a big piece of machinery with many moving parts, the behavior of which you will have to understand and manage even if you didn't build it yourself. This machinery may break, it may behave in undesired or unpredicted ways, it may consume compute resources inefficiently, and if it does any of those things, you're still on the hook to take care of it because it affects the behavior of your product.
Ultimately, you will always have to map some behavior in your service code to an intended SQL query and then map the result set back to in-memory data. Doing so in a consistent way can potentially reduce complexity, though in a lot of cases, you can probably get away with treating a SQL result set as a list of associative arrays, or even a list of objects as long as you tell it what class to deserialize into (which is what many ORMs do anyway).
> There's still the old-fashioned way of checking them into the repo as part of migrations.
Yes, even many ORM-based services have DB migrations in the service repo already.[1]
You could also version-control the stored procedures, database migrations, etc. as its own software artifact. You can think of a relational database as a service (microservice?) that speaks SQL instead of HTTP or GraphQL or GRPC. It's listening to a port somewhere on your network, it consumes computational resources, it will be deployed independently of your service in such a way that you have to worry about backwards compatibility, it can become unavailable to your service, and so forth. And like most services, you may prefer to have a defined, optimized, versioned interface to your DB instead of just trusting your consuming services to execute arbitrary (SQL) code. This is not necessarily the right or wrong solution for you, but it's reasonable enough.
[1] One possible niche might be an ORM that builds all of your DB manipulation commands into parameterized SQL at compile time and installs those SQL statements as stored procedures via the migration mechanism. Then you really can write raw SQL when you need to, by hardcoding your stored procedure instead of compiling it. This would work best on a DB that you could migrate whenever you felt like it, but I've heard Postgres is one of those.
> You can think of a relational database as a service (microservice?) that speaks SQL
Exactly. As a colleague of mine once said: stored procedures are the original microservices. A bit tongue-in-cheek, but the point is you can treat stored procs as these lightweight services. You don't even have to write the SQL, just call the procs with the right arguments. That's one scenario, anyway.
ORM’s are easy, but not simple. If your system uses a relational database but not an ORM, you have to understand your particular database and also SQL to understand your data layer. If you add an ORM, you aren’t actually saved from having to understand those things, you just also have to understand your ORM on top of all that. There is some positive tradeoff you get in return, since you don’t necessarily have to grapple with the added complexity all of the time.
The main saving grace of ORM (or at least query builders) seems to be that the alternatives aren’t well-supported in tooling, so the “raw SQL” alternatives often end up implemented as string concatenation hell, which is admittedly terrible. Installing parameterized SQL on the DB itself in stored procedures works great, but you have to go out of your way to do it; it never really comes across as a plug-and-play option even though in principle it easily could be.