53

In some organization I worked for web applications are developed basing all the business logic in Database stored procedures. For example, use html for view and servlet as controllers to divert the client request to appropriate Database stored procedures.

What are the advantages and disadvantages of this kind of designs? In my opinion if the business logic highly depends on the database than better follow this kind of design!!!!!

superM
  • 7,363
  • 4
  • 29
  • 38
droidsites
  • 1,027
  • 2
  • 11
  • 10
  • 1
    While there is noting wrong with your question, the term "Business Logic" has different meanings. For example, 1-M mandatory relationship is a "business logic" that must be checked on the server, whereas missing key value is also a business logic that should be checked on the GUI (at least). – NoChance Jul 28 '12 at 16:50

6 Answers6

71

In theory, the pros and cons are as so:

Pros:

  1. One place to contain all of the business logic
  2. Possibly faster applications because multiple SQL queries and such can be performed in one "round trip" to the database
  3. Trivial to make use of the stored procedures from multiple applications

Cons:

  1. A DBA will be required for performance tuning
  2. All developers will have to be very well versed in your particular SQL dialect(T-SQL, Pl/SQL, etc)
  3. SQL code isn't as expressive and thus harder to write when covering higher level concepts that aren't really related to data
  4. A lot more unnecessary load on the database

Now, practically, only a fool would have all business logic in the database.

  1. Very few developers will be able to create a consistent stored procedure interface that works easily across applications. Usually this is because certain assumptions are made of that calling application
  2. Same goes for documenting all of those stored procedures
  3. Database servers are generally bottlenecked enough as it is. Putting the unnecessary load on them just further narrows this bottleneck. Intricate load balancing and beefy hardware will be required for anything with a decent amount of traffic
  4. SQL is just barely a programming language. I once had the pleasure of maintaining a scripting engine written as a T-SQL stored procedure. It was slow, nearly impossible to understand, and took days to implement what would have been a trivial extension in most languages
  5. What happens when you have a client that needs their database to run a different SQL server? You'll basically have to start from scratch -- You're very tied to your database. Same goes for when Microsoft decides to deprecate a few functions you use a couple hundred times across your stored procedures
  6. Source control is extremely difficult to do properly with stored procedures, more so when you have a lot of them
  7. Databases are hard to keep in sync. What about when you have a conflict of some sort between 2 developers that are working in the database at the same time? They'll be overwriting each others code not really aware of it, depending on your "development database" setup
  8. The tools are definitely less easy to work with, no matter which database engine you use.

So, to objectively answer the question. In most cases, stored procedures are only needed in some cases. For instance, if you have a report to generate where you need to do a lot of conditional processing across a couple of big tables, you wouldn't want your application to be making a couple hundred SQL queries to the database. You'd want to make a stored procedure so you didn't have the network lag overhead. And, you'll usually only want to make stored procedures otherwise for when clients want to run a custom-ish query across your database. Stored procedures and views can make this easily possible without your clients being a database whiz.

Earlz
  • 22,658
  • 7
  • 46
  • 60
  • 13
    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. – gbjbaanb Jul 28 '12 at 16:19
  • 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 – Aadaam Jul 28 '12 at 16:32
  • 13
    -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. – Darknight Jul 28 '12 at 18:16
  • 5
    -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. – Jim G. Jul 28 '12 at 18:50
  • 1
    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. – sam yi Nov 19 '13 at 16:09
  • 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. – sam yi Nov 19 '13 at 16:11
  • Wow that's a very coder-centric response. Most points are invalid or equally applicable to a web layer approach. – Nick.Mc Sep 20 '14 at 00:50
  • 2
    @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. – Earlz Sep 22 '14 at 14:35
  • 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. – Nick.Mc Sep 22 '14 at 23:29
  • @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. – Earlz Oct 02 '14 at 05:02
  • #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. – nanonerd Sep 24 '15 at 18:27
  • 1
    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 – Graham S. Dec 14 '15 at 21:39
  • 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. – Paul-Sebastian Manole Sep 09 '16 at 08:52
  • @GrahamS. OMG! Get out as fast as you can! – user441521 Aug 08 '17 at 17:47
  • @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. – user441521 Aug 08 '17 at 17:50
  • 1
    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? – Eric J. May 31 '18 at 02:20
  • 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. – FrankZhu Nov 23 '19 at 16:34
  • @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. – Jacob Stamm Oct 21 '20 at 20:37
  • 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! – Eric Mutta Oct 09 '21 at 21:56
  • Surprised you didn't mention anything about unit testing. – Steven de Salas Jun 22 '22 at 10:28
21

Cons:

  • The database is the least scalable part of your architecture.
  • Especially in an age of web services and REST-ful 3rd party web applications, you won't be able to contain all of your business logic in stored procs.
  • SQL implementations vary, some can be tricky, and none read as fluidly as a first-class application-layer programming language.
  • Remember that code is read much more often than it is written.
Jim G.
  • 8,006
  • 3
  • 35
  • 66
  • 1
    +1 for scalability. While most DBs are incredibly fast, and so handle a large load, they are also incredibly expensive to scale (SQLServer, Oracle, and DB2 anyway). – rally25rs Jul 28 '12 at 22:53
  • 5
    +1 for Remember that code is read much more often than it is written – Ajay Bhasy Feb 22 '18 at 06:16
10

Pros of holding all the business logic on stored procedures in web application for:

  • One place is easier to maintain than multiple
  • Databases are fast and optimized and can be scaled well.
  • The same procedures can be called from mutiple, different frameworks and languages.
  • The logic is decoupled from the implementation in particular applications.
  • Rework may be reduced for changing applications when the database stays the same.

Cons of holding all the business logic on stored procedures in web application: against:

  • Good SQL knowledge can be hard to find in many locations.
  • Good SQL coders can be expensive.
  • All application developers will need to be able to either change it or request a quick change.
  • Some data that is less well suitable to a SQL database, e.g. image data or document data may not be available in the database and it may be hard to integrate it with the stored procedures..
Michael Durrant
  • 13,101
  • 5
  • 34
  • 60
  • 4
    *"All application developers will need to be able to either change it or request a quick change."* This assumes a dba. I think a decent developer should know SQL, for me if you are a programmer I expect you to know sql + at least 5 other paradigms. Why set the bar low? Other than that +1 – Darknight Jul 28 '12 at 18:18
  • 3
    So basically the only significant con is the availability of qualified SQL programmers? – Robert Harvey Jun 02 '15 at 21:13
  • "One place is easier to maintain than multiple" I guess I'd say this is what a web service is for. With micro-services it makes them little contained business logic machines and decouples the caller from the database which would have been handy for a number of apps I've worked on. – user441521 Aug 08 '17 at 17:51
7

There are 2 big cons that I usually encounter:

  1. Inability to unit test your stored procedures. Sure there are some unit test frameworks out there, but you usually run into issues of running more than 1 set of tests at once (concurrency and transaction troubles).
  2. Most companies will want to put a lot of login in to stored procs, but then hire 5x more application programmers (be it C#, Ruby, Java, whatever) than database programmers or DBAs, and for some reason expect that all their app coders should fully understand and be able to write performant, scalable database procedures (in PL/SQL, T-SQL, or whatever). Management usually doesn't realize that doing that is a lot like hiring a C# expert to write you some Python code, and wondering why they aren't writing amazing applications.
rally25rs
  • 549
  • 3
  • 7
  • 5
    I thought SPs were easy to unit test - they're already discrete units, you just need to work out what data to pre-populate the tables with to make them work. – gbjbaanb Jul 28 '12 at 16:21
  • 1
    Stored procedures are pretty easy to unit-test, and every dev should have their own database, so there won't be any "concurrency and transaction troubles" – kevin cline Jul 28 '12 at 17:16
  • Well, in my situation dev's can't have their own DB, both in Oracle and DB2, because the "free" ones don't support all the features of the "real" ones. – rally25rs Jul 28 '12 at 22:48
  • A agree this preference is generally tied to db resource scarcity. But it's also due to the "top" developers thinking they are dbas. Most good programmers are a confident bunch and they sell themselves as sql experts. They do not realize their shortcomings until they hire a competent dba... but most shops never get there. – sam yi Nov 19 '13 at 16:16
  • Why cant one unit test stored procs ?? I user TSQLT to unit test my TSQL – Pushpendra May 26 '18 at 08:56
6

It depends

It all depends on your team's experience and your project's requirements. Bring in a DBA to your team and decide what should you do to meet the requirements.However, with multitier application design it might not be the good decision to encapsulate business logic in stored procedure.

It will NOT matter

As long as business logic:

  • lives in one place
  • where it is properly documented
  • proper access is provided through services that can be loosely coupled
  • through a published abstracted interface

I think that business logic in programming space makes more sense when Power of Expression is important in your team/project.

I belief that SQL space is not as expressive, but it can do the job. Use the best tools you have on hand for the most appropriate tasks. Fiddling with logic and higher order concepts is best done at the highest level. Consequently, storage and mass data manipulation is best done at the server level, probably in stored procedures.

Yusubov
  • 21,328
  • 6
  • 45
  • 71
  • 3
    +1, for the first bit, -1 for multiple applications advice. There is no need to put everything in the database when multiple apps are involved. Just make all applications go through the same business layer. Three tier development wasn't invented just for the fun of it. – Marjan Venema Jul 28 '12 at 06:33
5

In the modern age this isn't really a common approach anymore. There was a time that putting business logic in stored procedures was a common thing to do early in web programming because the alternatives where few and often difficult to use. Today I would recommend against it.

I would have to say in 2012 there is nothing about business logic in stored procedures that could be justified as somehow a choice having beat out other approaches after having weighed the pro's and cons's. Any PRO points would be academic at best.

CON's It's not linq code. The future of TSQL is compiled linq, mine as well start to learn it.

Compiled code is more stable and easier to work with. If I'm debugging something there is so much information available if I used C# or Java.

Stored procedures are likely to end up with too many responsibilities, it's a best practice to try to keep your responsibilities to 1.

Lack of separation of responsibilities will lead to an application that just gets harder and harder to work on and less and less stable as it's core classes just get bigger and bigger.

If you like Microsoft Asp.Net MVC combined with Entity Framework Code First will allow you to rapidly build any application and you won't need any stored procedures.

Honorable Chow
  • 85
  • 1
  • 3
    -1 this is complete non-sense. LINQ eventually translates to SQL. You end up with the N+1 problem. Yes LINQ maybe ok for simple tasks. But once you have some complex problem, you need to take of the training wheels and do some real SQL. – Darknight Jul 28 '12 at 18:25
  • 1
    LINQ is in the process of being deprecated. SQL is not. – Jay Jun 12 '13 at 16:04
  • 2
    @Jay Where in the world did you hear that in 2013? It's 2016 and it's stronger than ever. – user441521 Aug 26 '16 at 20:05
  • 1
    The world is larger than what you see – Jay Aug 27 '16 at 12:05
  • 1
    @Jay - I think you're confused. LINQ to SQL is no longer recommended, but not yet deprecated. LINQ itself is strong. MS's recommended approach to data access is now Entity Framework, and that is self evidently under development, and works well with LINQ. – Rob G Oct 26 '16 at 13:11
  • Not deprecated yet. The writing is on the wall though – Jay Oct 27 '16 at 19:01
  • I think he's talking about excess business logic in stored procedures. I have never seen a scenario where thousands and thousands of lines of stores procedures is maintainable or optimal. They can almost always be reduced to some core SQL functionality like aggregations, data validation, security, etc., then the rest is done in the .NET layer. – Jeremy Ray Brown Oct 02 '18 at 02:03