Everything old is new again I suppose. Back in the late 90s when this was the popular approach, the downside was you were effectively limited performance-wise to vertically scaling your database as opposed to being able to horizontally scale your application layer.
Also…with the Hickey quote….it could be argued that you’re keeping things simpler by making the primary function of the database to store data…that placing one’s business logic / transforms within the database is increasing the complexity.
Anyways, like anything else, there’s no one clear answer. It’s always good to revisit assumptions, best practices, etc. as times change to see if there’s anything that is ripe for change / can be done better.
Exactly. It is (typically) much easier to scale your application layer than your DB layer. By putting all of this logic in your DB server, you’re causing yourself extra woe when it comes time to replicate.
Also, the examples here are relatively simple, but once you start trying to do more complex queries purely through stored procedures, you’re again just eating up memory in your precious DB layer. So, lets say you start to split things up into smaller function which are called in sequence from your….application layer. And it all quickly breaks down from there.
There is a reason this is something we used to do.
I’m not against stored procedures, but I don’t find the overall argument convincing. I think triggers and table constraints are very useful, but API functions in the database are probably going too far. As others have stated, the application layer is easier to scale and it can make some things harder to extend. Although the post states “I’m not trying to convince everyone to do things this way”, the language and tone comes across as such.
That said, I’m glad to see more people challenging the assumption that a database should be nothing more than a data store. In my work, I’ve written a fair number of stored procedures due to the use cases presented. I weighed the options and it came out that the complication of doing something in the database was worth it. It was a good optimization. It prevented excessive data transfer to the app, which was slower – too slow, in fact. (And remember, not all of us are building the same thing when there is a database in the mix.)
I’ve always resented the idea that you should never use stored procedures becuase they sucked back in the early days. As a professional, I can’t, in good conscience, reject an option simply due to dogma. I agree that moving logic into the database is something that you should do with caution and has its downsides, but sometimes it may be a good option.
I don’t agree with most of the points. Even if you don’t put logic in the database, you are not necessarily treating it as “dumb storage” - you are preferrably treating it as an indexed, remote data source, which is already quite a lot! Treating it as dumb storage would mean raw K/V access with everything else being done outside.
Writing your logic in the app doesn’t necessarily mean strong coupling to the database - it ties it to the presence of a data store. You can replace that source by whatever you’d like.
Logic in the database is also not simple and often constrained by how the DB works (I assume the author meant “SQL Database (Application) Server” like PG, Oracle and to some extend MySQL).
Any kind of rewrite introduces some kind of friction, I find that argument rather weak.
If you put the business logic in the scripts, you can’t easily change the language, if you put it in the database, you can’t easily change the database. There’s a drawback to both approach. And I would argue that changing the database or language is something that should rarely happens, and when it does, wherever the business logic is, there will be lots of refactoring to do.
In my opinion, it’s better to put all the business logic in the code base, because then there’s only one language and one place where all the code is. If you put it half in the db and half in the code base (because not everything can be expressed in triggers/db functions), it becomes a mess that is hard to maintain. Versioning and unit testing is also simpler when all the code is outside the db.
I came here to say that, versioning is “free” for the source code, I know you can export a database to a text file to do a backup, but it is generally treated as a black box where as your source code is in plain text files and already versioned and accessible to everyone.
Everything old is new again I suppose. Back in the late 90s when this was the popular approach, the downside was you were effectively limited performance-wise to vertically scaling your database as opposed to being able to horizontally scale your application layer.
Also…with the Hickey quote….it could be argued that you’re keeping things simpler by making the primary function of the database to store data…that placing one’s business logic / transforms within the database is increasing the complexity.
Anyways, like anything else, there’s no one clear answer. It’s always good to revisit assumptions, best practices, etc. as times change to see if there’s anything that is ripe for change / can be done better.
Exactly. It is (typically) much easier to scale your application layer than your DB layer. By putting all of this logic in your DB server, you’re causing yourself extra woe when it comes time to replicate.
Also, the examples here are relatively simple, but once you start trying to do more complex queries purely through stored procedures, you’re again just eating up memory in your precious DB layer. So, lets say you start to split things up into smaller function which are called in sequence from your….application layer. And it all quickly breaks down from there.
There is a reason this is something we used to do.
Operationally it is unclear if this simpler. Changing the code and deploying and knowing what is in your DB can be difficult.
You can do automatic versioning of your functions, using checksums like Redis does.
I’m not against stored procedures, but I don’t find the overall argument convincing. I think triggers and table constraints are very useful, but API functions in the database are probably going too far. As others have stated, the application layer is easier to scale and it can make some things harder to extend. Although the post states “I’m not trying to convince everyone to do things this way”, the language and tone comes across as such.
That said, I’m glad to see more people challenging the assumption that a database should be nothing more than a data store. In my work, I’ve written a fair number of stored procedures due to the use cases presented. I weighed the options and it came out that the complication of doing something in the database was worth it. It was a good optimization. It prevented excessive data transfer to the app, which was slower – too slow, in fact. (And remember, not all of us are building the same thing when there is a database in the mix.)
I’ve always resented the idea that you should never use stored procedures becuase they sucked back in the early days. As a professional, I can’t, in good conscience, reject an option simply due to dogma. I agree that moving logic into the database is something that you should do with caution and has its downsides, but sometimes it may be a good option.
Previous articles/threads on similar topics: It’s Time To Get Over That Stored Procedure Aversion You Have and Actually Using the Database. It’s worth noting that the database mentioned is always Postgres, which I guess it’s not by accident, and it is indeed related to its expressiveness (rich native data types) and extensibility.
Yeah. Well, MySQL explicitly doesn’t place a priority on this sort of capability. Oracle certainly does, but essentially nobody can afford Oracle.
Also, Oracle is horrible.
I’d like to decide that for myself someday, but it doesn’t look likely I’ll ever be able to justify the expense. :)
No BOOLEAN type? That says it all to me.
[Comment removed by author]
I don’t agree with most of the points. Even if you don’t put logic in the database, you are not necessarily treating it as “dumb storage” - you are preferrably treating it as an indexed, remote data source, which is already quite a lot! Treating it as dumb storage would mean raw K/V access with everything else being done outside.
Writing your logic in the app doesn’t necessarily mean strong coupling to the database - it ties it to the presence of a data store. You can replace that source by whatever you’d like.
Logic in the database is also not simple and often constrained by how the DB works (I assume the author meant “SQL Database (Application) Server” like PG, Oracle and to some extend MySQL).
Any kind of rewrite introduces some kind of friction, I find that argument rather weak.
If you put the business logic in the scripts, you can’t easily change the language, if you put it in the database, you can’t easily change the database. There’s a drawback to both approach. And I would argue that changing the database or language is something that should rarely happens, and when it does, wherever the business logic is, there will be lots of refactoring to do.
In my opinion, it’s better to put all the business logic in the code base, because then there’s only one language and one place where all the code is. If you put it half in the db and half in the code base (because not everything can be expressed in triggers/db functions), it becomes a mess that is hard to maintain. Versioning and unit testing is also simpler when all the code is outside the db.
We threw Postgrest on top of a pre-existing DB at our recent hack week and it just worked. Really quite nice brainless interface.
Interesting to see that project.
[Comment removed by author]
I came here to say that, versioning is “free” for the source code, I know you can export a database to a text file to do a backup, but it is generally treated as a black box where as your source code is in plain text files and already versioned and accessible to everyone.