I'm going to go the other way on the SQL advice, even though the author has clarified below that he was talking about prepared statements.
It doesn't cost me much, if any, developer time to work with stored procedures. I realize most full stack devs don't have as much in-depth experience with SQL. That was literally all I did for the first several years of my career. Before it ever occurred to me to learn other programming languages and. It was pure SQL all day every day.
If you have the resources to implement even half of this checklist, you probably have the resources to go whole hog on stored procs.
I sometimes do this, and sometimes don't. Partly, it's because I need to stay current with ORMs for the sake of potential job opportunities. And partially because it can be more convenient to keep my head in one language for fleshing out an idea. But for serious projects, I'll go the stored proc route.
In that kind of scenario, I think of the database as a type of microservice that's completely separate from the application layer. You write in the most performant language for that service, even if it's different for the main language the app is written in. And you reap the benefits of the database optimizing the procs as well as the database handling transactions instead of trying to manage that in your application.
How much of a burden this is boils down to whether you personally have the chops (or someone on the team does), and if you get your shit together when it comes to deployment. Stored Proc code should be saved in text files under version control, and your deploy process should make updates to the procs automatically.
If you do things this way, it's not any more painful than versioning your database with migrations, which I do on every project, no matter how small. Using stored procs doesn't have to mean you're working with a mess of unmanageable database code. You can use SQL Alchemy to create models and relationships, Alembic (just speaking to the Python world here), and use a thin wrapper around executing the stored proc, and it's really not that much different from writing prepared statements in Alchemy.
In principle, it's really not different from writing a web app that provides an API that your front end hits (a Python app that resents REST for Angular to call or something else equally common). In this case, I'm just pushing the API down one layer in the stack.
People can argue that the logic that applies to the data should be kept with the logic that defines the data--i.e., that if your model defines the shape of the date, your code that defines its behavior should reside there as well: in the model definition. So the ORM is the correct place for that. Methods on the Class object. I'm sympathetic to that argument, but I also disagree with it, sort of.
I think the rules that govern the behavior of data should exist where the data exists rather than any abstraction layer. Otherwise you have to rewrite them for every new thing that wants to access the data.
And every useful application that people use involving data is going to serve that data to more than the first application you designed to work with it. Keeping these rules in sync across even a very minimal web app that consists only of the app itself, an API, and an analytics platform is already some overhead.
And, every data-driven application already has two means of interface regardless of whether you intend that or not: direct database manipulation. That is going to happen sometimes, whether you want it or not or how bad a practice it is. If there are rules about how things should behave (there are always rules about how things should behave) you have to enforce them at the database level.
If you're doing that correctly already, there's really not that much overhead in using stored procedures. Going that route means that every connection to a database for a certain dataset means that there is a single source of truth for that dataset. Execute procedure x to get data y. That's all any developer on any service needs to know.
It also makes the idea of minimum privilege easier to manage. You don't have to grant select on a table for the API user, for example. You can grant execute to the set of procs that user needs, and only those procs are accessible. Combine that with some well-thought views, and you can greatly limit your attack surface. If someone breaches part of your system and manages to obtain so source code, they don't get any real information about the structure of your database. They only get conn.execute('check_valid_user') or whatever.
The obvious problem here is versioning APIs. Which you must always do. So you have to version your stored procs when there are breaking changes. But the overhead in adding a _v1 or _v1.1 to the end of an .sql file for a proc really isn't that big of a deal compared to all the other stuff you have to do when maintaining an API.
Again, I'm sympathetic to objections to this model. It is not perfect, and I've never once tried to inflict it on coworkers who want to do things differently. I'll do this in my own projects, and when I've gone into a company that's already working with that mindset. I don't hold up the train based on philosophical differences.
There's a lot of criticism I agree with that SOA is not a good place to start. It carries a lot of overhead, and you shouldn't go there until you need to. But to me, the reality is that any web app that comprises a data store, a controller layer, and a front end is already by definition SOA--not the monolith that you want to think of it as. And therefore you should treat it as such and give the various components the attention they deserve.
If you're working on a project you have reasonable expectation will be used enough to require some scaling, this is a totally acceptable way to do things. Let the database do what it's best at, let the controller layer do what it's best at, and then use the front end that's best for your use case.
Because this response isn't long enough already, I'll just add this. I don't think the MVC model is really appropriate for web apps. Even the modified MVVC model is still weird to me. It seems like a model we shoehorned onto what a web app really is. To me, the web app model is data, logic, presentation. The data layer comprises both the data and the rules about the data. The logic layer dictates what happens when an event is triggered by the presentation layer. And it should be relatively thin, particularly when working with one of the slower languages like Python or Ruby. The presentation layer simply defines the user interface and sends messages to the logic layer.
These things really are decoupled by nature in ways they aren't necessarily in native or desktop apps or mobile apps. I think that web developers need to understand that this model really doesn't fit very well. And behave accordingly.
There are obviously exceptions to everything I'm saying here. But the exceptions come at very large scales. Most apps that start out as MVC web apps on whatever stack are not going to become Facebook or Reddit and are not going to have the specific problems that come with that level of scale.
Even so, I would encourage people to rethink what web apps are, and what the MVC/MVVC model really means. I really don't think it's the right model, and the decisions we make about architecture are generally not good ones.
Finally, since this is so long now, I want to point out that I didn't generate these ideas all on my own. I've been heavily influenced by Lex de Haan and Toon Kopplaars in their book, Applied Mathematics for Database Professionals.
Data, logic and presentation sounds a lot like model controller and view. I feel like you suggested most logic should be in how days is handled? But let's be serious here, there's no right way to make a web app. There are certainly wrong ways and inefficient ways. If we focus all our efforts on doing things perfectly well then we would miss the point. Essentially, you're just theory crafting how people should code while ignoring the practical issues of people and coding.
Yes, okay. Every part of my real life experience developing web apps at the full stack is just theorycrafting.
I've clearly never done this in the real world, and I am simply thinking about my idea of a way to create a web app. And I've never worked with, thought about, or managed a team.
Do I actually need a sarcasm tag for this? I think I do, sadly. /s
Agree 100%. I've written longer responses like this on various web forums and email lists, but these days don't have the energy to keep hammering on this nail. Thanks for taking the time.
It doesn't cost me much, if any, developer time to work with stored procedures. I realize most full stack devs don't have as much in-depth experience with SQL. That was literally all I did for the first several years of my career. Before it ever occurred to me to learn other programming languages and. It was pure SQL all day every day.
If you have the resources to implement even half of this checklist, you probably have the resources to go whole hog on stored procs.
I sometimes do this, and sometimes don't. Partly, it's because I need to stay current with ORMs for the sake of potential job opportunities. And partially because it can be more convenient to keep my head in one language for fleshing out an idea. But for serious projects, I'll go the stored proc route.
In that kind of scenario, I think of the database as a type of microservice that's completely separate from the application layer. You write in the most performant language for that service, even if it's different for the main language the app is written in. And you reap the benefits of the database optimizing the procs as well as the database handling transactions instead of trying to manage that in your application.
How much of a burden this is boils down to whether you personally have the chops (or someone on the team does), and if you get your shit together when it comes to deployment. Stored Proc code should be saved in text files under version control, and your deploy process should make updates to the procs automatically.
If you do things this way, it's not any more painful than versioning your database with migrations, which I do on every project, no matter how small. Using stored procs doesn't have to mean you're working with a mess of unmanageable database code. You can use SQL Alchemy to create models and relationships, Alembic (just speaking to the Python world here), and use a thin wrapper around executing the stored proc, and it's really not that much different from writing prepared statements in Alchemy.
In principle, it's really not different from writing a web app that provides an API that your front end hits (a Python app that resents REST for Angular to call or something else equally common). In this case, I'm just pushing the API down one layer in the stack.
People can argue that the logic that applies to the data should be kept with the logic that defines the data--i.e., that if your model defines the shape of the date, your code that defines its behavior should reside there as well: in the model definition. So the ORM is the correct place for that. Methods on the Class object. I'm sympathetic to that argument, but I also disagree with it, sort of.
I think the rules that govern the behavior of data should exist where the data exists rather than any abstraction layer. Otherwise you have to rewrite them for every new thing that wants to access the data.
And every useful application that people use involving data is going to serve that data to more than the first application you designed to work with it. Keeping these rules in sync across even a very minimal web app that consists only of the app itself, an API, and an analytics platform is already some overhead.
And, every data-driven application already has two means of interface regardless of whether you intend that or not: direct database manipulation. That is going to happen sometimes, whether you want it or not or how bad a practice it is. If there are rules about how things should behave (there are always rules about how things should behave) you have to enforce them at the database level.
If you're doing that correctly already, there's really not that much overhead in using stored procedures. Going that route means that every connection to a database for a certain dataset means that there is a single source of truth for that dataset. Execute procedure x to get data y. That's all any developer on any service needs to know.
It also makes the idea of minimum privilege easier to manage. You don't have to grant select on a table for the API user, for example. You can grant execute to the set of procs that user needs, and only those procs are accessible. Combine that with some well-thought views, and you can greatly limit your attack surface. If someone breaches part of your system and manages to obtain so source code, they don't get any real information about the structure of your database. They only get conn.execute('check_valid_user') or whatever.
The obvious problem here is versioning APIs. Which you must always do. So you have to version your stored procs when there are breaking changes. But the overhead in adding a _v1 or _v1.1 to the end of an .sql file for a proc really isn't that big of a deal compared to all the other stuff you have to do when maintaining an API.
Again, I'm sympathetic to objections to this model. It is not perfect, and I've never once tried to inflict it on coworkers who want to do things differently. I'll do this in my own projects, and when I've gone into a company that's already working with that mindset. I don't hold up the train based on philosophical differences.
There's a lot of criticism I agree with that SOA is not a good place to start. It carries a lot of overhead, and you shouldn't go there until you need to. But to me, the reality is that any web app that comprises a data store, a controller layer, and a front end is already by definition SOA--not the monolith that you want to think of it as. And therefore you should treat it as such and give the various components the attention they deserve.
If you're working on a project you have reasonable expectation will be used enough to require some scaling, this is a totally acceptable way to do things. Let the database do what it's best at, let the controller layer do what it's best at, and then use the front end that's best for your use case.
Because this response isn't long enough already, I'll just add this. I don't think the MVC model is really appropriate for web apps. Even the modified MVVC model is still weird to me. It seems like a model we shoehorned onto what a web app really is. To me, the web app model is data, logic, presentation. The data layer comprises both the data and the rules about the data. The logic layer dictates what happens when an event is triggered by the presentation layer. And it should be relatively thin, particularly when working with one of the slower languages like Python or Ruby. The presentation layer simply defines the user interface and sends messages to the logic layer.
These things really are decoupled by nature in ways they aren't necessarily in native or desktop apps or mobile apps. I think that web developers need to understand that this model really doesn't fit very well. And behave accordingly.
There are obviously exceptions to everything I'm saying here. But the exceptions come at very large scales. Most apps that start out as MVC web apps on whatever stack are not going to become Facebook or Reddit and are not going to have the specific problems that come with that level of scale.
Even so, I would encourage people to rethink what web apps are, and what the MVC/MVVC model really means. I really don't think it's the right model, and the decisions we make about architecture are generally not good ones.
Finally, since this is so long now, I want to point out that I didn't generate these ideas all on my own. I've been heavily influenced by Lex de Haan and Toon Kopplaars in their book, Applied Mathematics for Database Professionals.