Yesterday I read this article, from Derek Sivers’ blog. I encourage you to read it as well, but here’s the summary:
- We should move logic more into the database, because “Classes, models, and methods (OOP) are an unnecessary complication.”
- Data structure doesn’t change often, but code (logic) does. So let’s move the logic closer to the data structure
- “PL/pgSQL is not the most beautiful language, but the simplicity of having everything in the database is worth it.”
- We can do that by using triggers, stored procedures and the likes. We then call them directly from the application layer (there follows an example of a REST api in sinatra).
First I thought it was a long winded joke, but the article was over before the expected “haha jk”. I actually had to scroll down quite a bit to find the first dubious commenter.
Now, let’s clarify one thing: his approach makes sense – under very specific conditions. Those conditions loosely being that the application is mostly data centric (data in, data out, CRUD and a little validation), and most of the work is related to how data is presented.
Fact is, we have already the perfect solution for this scenario, and we have had it for years: it’s called Microsoft Access.
So, why aren’t all applications out there written in MS Access, or one of its clones?
Enter: design space
So, what is the problem with having all the logic in the database?
Anyone who’s actually worked on a big application, with hundreds of triggers and stored procedures, knows perfectly what the problem is. It might be slightly more difficult to articulate it in clear words – I myself could not place a finger on the definition for a long time.
When you decide to store all logic in the database, you have made the decision to fight in a very tight space. Everytime something unexpected pops up, you have to work it out inside the very cramped code room you’ve created for yourself.
The advantage is that everything is in reach – add, delete, change a trigger here and a stored procedure there, you can alter a great deal of things with very little code.
The disadvantage is that you’re effectively coding yourself into a corner. Moving is impossible except by making very complicated contorsions. Everytime you do, you’re at risk of breaking something you didn’t want to touch at all. Side effects galore.
And, the more you do this, the more space you’re taking away from yourself – the room becomes more and more cramped with each trigger and each stored procedure you add. It turns effectively into the same OOP-bound complexity Sivers mentions – without the room needed to work the complexity out.
The reason why OOP (or really, anything that tries to handle this kind of complexity in a different way) exists is to give us programmers space. Yes, having to make data flow through different layers makes more work. Yes, you have to change lots of things every time something deep in the database changes.
It’s a tradeoff – what you get back from it is all the joints, the clean points of connection, the interfaces you can program against. If you do things right, you can make yourself all the room you need to solve a specific problem. Yes, maintaining those joints is work. It doesn’t mean that it’s automatically better to get rid of them.
So, if you know for a fact you do not need the design space, you can save yourself some time and some effort by storing your logic in the database or, even better, using some kind of MS Access clone – since there is not much logic for you to write anyway, it’s better to have something pre-made do the heavy lifting.
But at the same time, if you don’t need the design space, it is probably the case that your project does not need ad-hoc software and you are much better off buying something existing.
Which strategy to choose should be a conscious decision, to be taken for each organization and for each project inside of it. If you’re a senior programmer, that is the kind of decision you should be able to take. The fact that there seems to be absolutely no clue inside the community about seemingly simple topics like this one is, honestly, a bit scary.