But now you're filling your application with arcane and inscrutable logic, with an extra layer of abstraction via the ORM to make it even less scrutable.
I think one should view a SQL DB like a microservice. Instead of REST endpoints (or gRPC or whatever), create stored procedures. These define a strong contract with your DB, the capabilities that it provides to your app(s). Now you know what the query and insert patterns are, and can tweak the table layout under the covers without screwing up your application code.
Of course you can abuse this into a spaghetti monolith, just like you can evolve a microservice into a spaghetti monolith, but you shouldn't. There's no technology that will prevent you from making poor architectural decisions, you just have to not go down those dark paths.
> Instead of REST endpoints (or gRPC or whatever), create
> stored procedures. These define a strong contract with your DB
Exactly. It took me years to grasp this, but when I did my code became much simpler.
A REST API (that returns JSON) has to contort a tabular data structure into a loosely-typed hierarchical data structure (JSON) which has to be read back, reconstructed and in many instances type-checked (e.g. DATETIMEs are not native to JSON, nuanced datatypes like DECIMAL(18,3) are lost).
Whereas a SQL interface returns data in its native tabular format with all the correct types.
I remember people I used to work with arguing against stored procedures for two main reasons.
1) Version Control - I guess a lot of the stored procedures were being put straight into the DB without recording a history of the changes. These days you could easily do this using DB migrations I guess.
2) Testing - is unit testing a thing for Stored Procedures? I guess again, you might be able to do this from code as well programatically adding a stored procedure, running a bunch of tests and removing it again.
I do wonder - what do people generally do in practice for overcoming these objections?
Does anyone have any other objections around using stored procedures?
1) I find this kinda funny. Why are you worried about this in SQL but not for other code? It's not like it's hard to chuck Python, JS, Ruby, ASP, etc code straight into prod, you just don't do that because it's stupid. Don't do it for SQL either. If you really want to, build out user permissions that only allow your CICD system to change them.
2) TBH we never built anything complex enough to need this, and I would tend to think that if you do need this you're probably overcomplicating your DB. But you could probably do something that creates a temp DB, populates example data, and then runs tests.
I think one should view a SQL DB like a microservice. Instead of REST endpoints (or gRPC or whatever), create stored procedures. These define a strong contract with your DB, the capabilities that it provides to your app(s). Now you know what the query and insert patterns are, and can tweak the table layout under the covers without screwing up your application code.
Of course you can abuse this into a spaghetti monolith, just like you can evolve a microservice into a spaghetti monolith, but you shouldn't. There's no technology that will prevent you from making poor architectural decisions, you just have to not go down those dark paths.