In my experience, logic in stored procedures is disastrous in the long term. Stored procedure programming is more limited and very side-effectful. It naturally tends to become a spaghetti mess as time goes on. Using logic in SPs also split-brains the logic, because there are some things which are really difficult/awkward for procedures to do but are easy in code. So you put that part in code, and have hidden complexity and dependency between the steps in code and those in the SP. SPs are generally a pain to keep in version control. You also don't have good organizational tools at your disposal for SPs. Common practice is segmented naming like "customer_create", "customer_update". Whereas in code you have more organizational tools like folders, namespaces, and classes.
Things I might use SPs for: required combinations of updates, complex queries which need temp tables, etc.
After writing this, I realized you didn't specify which database you were using. Some of them have access to full programming languages for stored procedures. However, I'd still recommend against this practice for most cases.
Side note, controllers are not the best place for logic either. :) Controllers basically organize side effects. Logic is best in its own module or library and kept as free as possible from side effects. After all, the business logic is the most important thing to the business, so it merits its own independent representation, not mixed in with all the incidental integrations required to accomplish the use case. It's a little extra work up front to separate it out, but its extra work I always end up doing anyway.
I agree with you, specially on the source control part. Issue here is that some companies already implement this kind of design and it's very hard change.
Then if using a relational database as storage, might be just a good to use a very fast nosql database, even if you lose the relational part?
About your side-note, yes, I know that. But you know what I meant :)
Yes, it is really common to see multiple legacy apps share the same database and use the shared data for different purposes. (This is called an Integration Database.) It creates a really unfortunate situation where it becomes increasingly difficult to make changes for fear of breaking one of the apps. In a system like this, new functionality usually means new tables because the risk of changing existing data is too great.
My solution for that is typically to insert an API (could be a service or a library) in between the database and the apps, and gradually migrate the apps to use the API. At least in the API you can have a better overview for how all the data is modified. Once apps are no longer accessing the database directly, you can make structural changes to the database with a decreased risk of breakage. As long as the API contract with the app remains the same.
Stored procedure isn't a purely relational database feature. So skipping them doesn't imply you should use NoSQL. Relational still does set-based constraints (unique constraints, set membership) really well whereas NoSQL databases are generally not good at those. The place where NoSQL really shines from a coding perspective is when you have a hierarchical object. Splitting it into a lot of child relational tables and reconstructing that data back to objects is very painful and is the main reason ORMs exist. But it's super easy in NoSQL because you just load/store the whole thing as one object. Many relational DBs can be used as key-value storage too nowadays (e.g. Postgres has json/jsonb types, as well as hstore). The other main reason to use NoSQL is for scaling out. Most relational databases will only scale up. There are also certain types of NoSQL database better for certain jobs, like graph databases.
I wasn't trying to be rude with my side note. I literally wrote business logic in controllers and code-behind pages for many years. Over time it became really hard to untangle the business code from all the integration code. I still face this problem with some legacy apps that haven't justified a rewrite yet.
Yes, we are exactly planing to do that next year. It'd require lots of work, mind you it's about 300 tables and 500 Stored procedures.
My plan is similar than yours, which makes me relax a bit thinking that it's the way to go :)
I never found your side note rude in any way, but the other way around.
You know, maybe there is someone who is doing that might read it and think about it. :) Side notes with good knowledge are a must in this kind of conversations.
agree 100% - was going to write something, but you've already put it perfectly :)
Those separations of concerns are critical, otherwise you're well on your way to a monolith
Thanks! Separation of concerns is one of the largest factors in maintainability IMO. So much so that nowadays I will accept some level of duplication (mainly of data and data structures) across different subsystems in order to remove dependencies between them. It is quite freeing to be able to change a data structure and not worry about breaking somebody else.
Monoliths are not so bad. They are one way to package a system. Whether to choose monolith vs microservices vs something-in-between mainly depends on your organizational structure and problem domain.
agree on the monolith - knew it would come back to bite me :) Horses for courses definitely, but i've found monoliths always outgrow their original purpose, and end up adopting different business needs in new and interesting ways, when separating them out would have made things easier in the long run.
Yeah, I think you are spot on there. Many successful apps started monolithic and as the business became successful and grew, that model no longer suited their needs. Here's a good talk in that vein.
In my experience, logic in stored procedures is disastrous in the long term. Stored procedure programming is more limited and very side-effectful. It naturally tends to become a spaghetti mess as time goes on. Using logic in SPs also split-brains the logic, because there are some things which are really difficult/awkward for procedures to do but are easy in code. So you put that part in code, and have hidden complexity and dependency between the steps in code and those in the SP. SPs are generally a pain to keep in version control. You also don't have good organizational tools at your disposal for SPs. Common practice is segmented naming like "customer_create", "customer_update". Whereas in code you have more organizational tools like folders, namespaces, and classes.
Things I might use SPs for: required combinations of updates, complex queries which need temp tables, etc.
After writing this, I realized you didn't specify which database you were using. Some of them have access to full programming languages for stored procedures. However, I'd still recommend against this practice for most cases.
Side note, controllers are not the best place for logic either. :) Controllers basically organize side effects. Logic is best in its own module or library and kept as free as possible from side effects. After all, the business logic is the most important thing to the business, so it merits its own independent representation, not mixed in with all the incidental integrations required to accomplish the use case. It's a little extra work up front to separate it out, but its extra work I always end up doing anyway.
I agree with you, specially on the source control part. Issue here is that some companies already implement this kind of design and it's very hard change.
Then if using a relational database as storage, might be just a good to use a very fast nosql database, even if you lose the relational part?
About your side-note, yes, I know that. But you know what I meant :)
Yes, it is really common to see multiple legacy apps share the same database and use the shared data for different purposes. (This is called an Integration Database.) It creates a really unfortunate situation where it becomes increasingly difficult to make changes for fear of breaking one of the apps. In a system like this, new functionality usually means new tables because the risk of changing existing data is too great.
My solution for that is typically to insert an API (could be a service or a library) in between the database and the apps, and gradually migrate the apps to use the API. At least in the API you can have a better overview for how all the data is modified. Once apps are no longer accessing the database directly, you can make structural changes to the database with a decreased risk of breakage. As long as the API contract with the app remains the same.
Stored procedure isn't a purely relational database feature. So skipping them doesn't imply you should use NoSQL. Relational still does set-based constraints (unique constraints, set membership) really well whereas NoSQL databases are generally not good at those. The place where NoSQL really shines from a coding perspective is when you have a hierarchical object. Splitting it into a lot of child relational tables and reconstructing that data back to objects is very painful and is the main reason ORMs exist. But it's super easy in NoSQL because you just load/store the whole thing as one object. Many relational DBs can be used as key-value storage too nowadays (e.g. Postgres has json/jsonb types, as well as hstore). The other main reason to use NoSQL is for scaling out. Most relational databases will only scale up. There are also certain types of NoSQL database better for certain jobs, like graph databases.
I wasn't trying to be rude with my side note. I literally wrote business logic in controllers and code-behind pages for many years. Over time it became really hard to untangle the business code from all the integration code. I still face this problem with some legacy apps that haven't justified a rewrite yet.
Yes, we are exactly planing to do that next year. It'd require lots of work, mind you it's about 300 tables and 500 Stored procedures.
My plan is similar than yours, which makes me relax a bit thinking that it's the way to go :)
I never found your side note rude in any way, but the other way around.
You know, maybe there is someone who is doing that might read it and think about it. :) Side notes with good knowledge are a must in this kind of conversations.
agree 100% - was going to write something, but you've already put it perfectly :)
Those separations of concerns are critical, otherwise you're well on your way to a monolith
Thanks! Separation of concerns is one of the largest factors in maintainability IMO. So much so that nowadays I will accept some level of duplication (mainly of data and data structures) across different subsystems in order to remove dependencies between them. It is quite freeing to be able to change a data structure and not worry about breaking somebody else.
Monoliths are not so bad. They are one way to package a system. Whether to choose monolith vs microservices vs something-in-between mainly depends on your organizational structure and problem domain.
agree on the monolith - knew it would come back to bite me :) Horses for courses definitely, but i've found monoliths always outgrow their original purpose, and end up adopting different business needs in new and interesting ways, when separating them out would have made things easier in the long run.
Yeah, I think you are spot on there. Many successful apps started monolithic and as the business became successful and grew, that model no longer suited their needs. Here's a good talk in that vein.
youtube.com/watch?v=9vS7TbgirgY