Timeline for Pros and Cons of holding all the business logic in stored procedures in web application
Current License: CC BY-SA 3.0
22 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jun 22, 2022 at 10:28 | comment | added | Steven de Salas | Surprised you didn't mention anything about unit testing. | |
| Oct 9, 2021 at 21:56 | comment | added | Eric Mutta | Number 5 (the difficulty of switching databases when all your logic is in the database's stored procedure language) is currently hurting us where I work (and by hurt I mean we can't stop paying thousands of dollars a month to switch away from the commercial RDBMS we use). Much of the pros/cons can be debated to death, but there's nothing funny or subjective about bleeding to death financially due to vendor lock-in caused by having all/most of your logic in stored procedures! | |
| Oct 21, 2020 at 20:37 | comment | added | Jacob Stamm | @FrankZhu Parameterization has been supported for ages in every ORM and data access framework worth its salt, which renders injection attacks moot. In fact, there are even some edge cases when it's easier to parameterize queries generated in code rather than in stored procedures, such as constructing a dynamic SQL query with a variable number of parameters. | |
| Nov 23, 2019 at 16:34 | comment | added | FrankZhu | one more pros: Stored procedures can be used to protect against injection attacks. Stored procedure parameters will be treated as data even if an attacker inserts SQL commands. | |
| May 31, 2018 at 2:20 | comment | added | Eric J. | I once evaluated an eCommerce platform at the beginning of the .NET era. It was the hot item, and my company (largest retailer in Europe) was considering both using the platform and making a heavy investment in the company. All of the business logic was in the database. That database was Sybase. Why lock your business future into a specific data storage technology? | |
| Aug 8, 2017 at 17:50 | comment | added | user441521 | @Paul-SebastianManole Some of that issue is that you can do logic just fine in a proc. You can even do the logic inside a single sql statement if you wanted to. Having those options makes people want to use them. | |
| Aug 8, 2017 at 17:47 | comment | added | user441521 | @GrahamS. OMG! Get out as fast as you can! | |
| Sep 9, 2016 at 8:52 | comment | added | Paul-Sebastian Manole | I think this age-old debate still goes on because different people have different opinions about what business logic is. My opinion is that what most of us will criticise is places where the database has been made to do things it isn't designed to do and places where operations on data sets were better off being done on the server because of better optimisations and honestly, that's what a database should do. Sure, nowadays you can serve a REST API from a database like PostgreSQL but that as well should be limited to set operations and data queries. | |
| Dec 14, 2015 at 21:39 | comment | added | Graham S. | Currently working in a place where we do all our "business logic" in stored procedures. And by business logic, I mean HTML, CSS, all JS scripts, and Oracle code. Literally, all of our HTML pages are generated from the database. Our boss says this is the "best" way to do it and then wonders why it takes two weeks to get a basic CRUD app done. I would understand the SPs to do some heavy-lifting when we need to consistent querying, but he is vehemently against the use of MVC or an ORM (because he doesn't understand it). This is why it helps to be well-rounded in DBA and coding. God help us here | |
| Sep 24, 2015 at 18:27 | comment | added | nanonerd | #6 is not correct. Source control for procs is easy with Redgate SVN. But it's not a free product. However, it works very well. | |
| Oct 2, 2014 at 5:02 | comment | added | Earlz | @Nick.McDermaid well of course. Like I said, it depends on what your doing. For a simple CRUD app like a blog site or something, I can't think of any reason you'd want stored procedures... My main argument is to rarely use stored procedures when a standard single-trip query would work just as well. My mindset is to only use stored procedures when it is such a rich query that it makes the application code more readable and understandable when it's put into a stored procedure, or when it's doing something data heavy, like generating reports and such. | |
| Sep 22, 2014 at 23:29 | comment | added | Nick.Mc | Most of this has already been addressed by the very first comment, and argued all over the internet, but specifically the two points about load on the database may not be valid (depending on what the app does). A database is good for matching, aggregating etc. If you're sending large recordsets to a client for processing then your database is doing a lot of I/O work that it shouldn't be doing and things are more efficient if done in the database. If the app does a lot of text manipulation, XML stuff etc. it's probably better out of the database. | |
| Sep 22, 2014 at 14:35 | comment | added | Earlz | @Nick.McDermaid which ones? Yes, I'm a coder, not a DBA. So, I'm going to be a bit skewed, but I think all my points are valid. | |
| Sep 20, 2014 at 0:50 | comment | added | Nick.Mc | Wow that's a very coder-centric response. Most points are invalid or equally applicable to a web layer approach. | |
| Nov 19, 2013 at 16:11 | comment | added | sam yi | Database is no longer the bottleneck in most cases... databases can be highly scale-able. EF, hibernate etc are ideal for smaller shops where the data needs are small and dba resources are scarce. | |
| Nov 19, 2013 at 16:09 | comment | added | sam yi | Keeping in the database is ideal for most applications but it all boils down to a single point... "1. A DBA is required... " I've been consulting and re-architecting applications for several years now and this is the main deciding factor. If this resource is not readily available, stay away from the database. Also keep in mind databases handle set operations much better than the application. Application code tends to be very iterative and can be very slow. I've once tuned an app code that ran for 7 hours... once converted to a store proc... 2 minutes. | |
| Jul 28, 2012 at 18:50 | comment | added | Jim G. | -1: One place to contain all of the business logic - It's unlikely that you'll be able to accomplish this with modern-day applications. | |
| Jul 28, 2012 at 18:16 | comment | added | Darknight | -1 disagree with "Now, practically, only a fool would have all business logic in the database" I'm sorry this is just plain bias. "Stored Proc vs App Code" is an aged old debate, your answer is CLEARLY biased. | |
| Jul 28, 2012 at 16:32 | comment | added | Aadaam | What about version handling? In my experience, it's hard with plain SQL, you need a kind of frontend which acts like (or exports to) a VCS | |
| Jul 28, 2012 at 16:19 | comment | added | gbjbaanb | mostly right, but 3) a SP often reduces load as it turns a handful of crap client queries into a single optimised query. 6) total nonsense. 7) happens with normal source code. 8) you'd better ask a DBA if his tools are not up to the task - Toad for example was very good. Still, if you are going to have a DB-based data tier, then hire dedicated SQL experts to create it, just like you'd hire dedicated javascript devs for a web GUI. You'd still only write data-related code in SQL though, it doesn't replace the entire stack. | |
| Jul 28, 2012 at 14:30 | vote | accept | droidsites | ||
| Jul 28, 2012 at 8:23 | history | answered | Earlz | CC BY-SA 3.0 |