I've worked in some projects where most of the business logic was implemented on the database (mostly through stored procedures). On the other side, I've heard from some fellow programmers that this is a bad practice ("Databases are there to store data. Applications are there to do the rest").

Which of these approaches is the generally better?

The pros of implementing business logic in the DB I can think of are:

  • Centralization of business logic;
  • Independency of application type, programming language, OS, etc;
  • Databases are less prone to technology migration or big refactorings (AFAIK);
  • No rework on application technology migration (e.g.: .NET to Java, Perl to Python, etc).

The cons:

  • SQL is less productive and more complex for business logic programming, due to the lack of libraries and language constructs the most application-oriented languages offer;
  • More difficult (if possible at all) code reuse through libraries;
  • Less productive IDEs.

Note: The databases I'm talking about are relational, popular databases like SQL Server, Oracle, MySql etc.

Thanks!

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

  • 3
    You might find the answer to this question useful. – Blrfl Apr 9 '13 at 15:14
  • 7
    This argument has already been debated exhaustively. What more could we meaningfully add to the conversation here? – Robert Harvey Apr 9 '13 at 15:18
  • 2
    @gnat: Not even close. – Robert Harvey Apr 9 '13 at 23:57
  • 1
  • 7
    Consider that the database is going to far (far) outlive your application. The database might even outlive the language you write your application in. The data itself typically is the business, and the database should be able to protect the integrity of the data it contains. In that vein, every foreign key constraint is, frankly, the implementation of a business rule. Unless you get rid of all the relational constraints in your relational database, you really can't get business logic completely out of the database. – Craig Jun 2 '15 at 22:50
up vote 66 down vote accepted

Business logic doesn't go into the database

If we're talking about multi-tier applications, it seems pretty clear that business logic, the kind of intelligence that runs a particular enterprise, belongs in the Business Logic Layer, not in the Data Access Layer.

Databases do a few things really well:

  1. They store and retrieve data
  2. They establish and enforce relationships between different data entities
  3. They provide the means to query the data for answers
  4. They provide performance optimizations.
  5. They provide access control

Now, of course, you can codify all sorts of things in a database that pertain to your business concerns, things like tax rates, discounts, operation codes, categories and so forth. But the business action that is taken on that data is not generally coded into the database, for all sorts of reasons already mentioned by others, although an action can be chosen in the database and executed elsewhere.

And of course, there may be things that are performed in a database for performance and other reasons:

  1. Closing out an accounting period
  2. Number crunching
  3. Nightly batch processes
  4. Fail-over

Naturally, nothing is engraved in stone. Stored Procedures are suitable for a wide array of tasks simply because they live on the database server and have certain strengths and advantages.

Stored Procedures Everywhere

There's a certain allure to coding all of your data storage, management and retrieval tasks in stored procedures, and simply consuming the resulting data services. You certainly would benefit from the maximum possible performance and security optimizations that the database server could provide, and that's no small thing.

But what do you risk?

  1. Vendor lock-in
  2. The need for developers with special skill sets
  3. Spartan programming tools, overall
  4. Extremely tight software coupling
  5. No separation of concerns

And of course, if you need a web service (which is probably where this is all heading, anyway), you're still going to have to build that.

So what is typical practice?

I would say that a typical, modern approach is to use an Object-Relational Mapper (such as Entity Framework) to create classes that model your tables. You can then speak to your database through a repository that returns collections of objects, a situation that is very familiar to any competent software developer. The ORM dynamically generates SQL corresponding to your data model and the information requested, which the database server then processes to return query results.

How well does this work? Very well, and much more rapidly than writing stored procedures and views. This generally covers about 80% of your data access requirements, mostly CRUD. What covers the other 20%? You guessed it: stored procedures, which all of the major ORMs support directly.

Can you write a code generator that does the same thing as an ORM, but with stored procedures? Sure you can. But ORMs are generally vendor-independent, well-understood by everyone, and better supported.

  • 2
    Thank you for your great answer, @Robert Harvey. But I was thinking about the "vendor lock-in" argument: insn't using a particular technology (say, the .NET or Java stack) to build an application also a vendor lock-in? Or are there advantages of an app-oriented stack vendor lock-in versus a DB one? – Raphael Apr 10 '13 at 12:58
  • 3
    @RobertHarvey, But the part of the application logic that is in .NET is still locked in to .NET. Same goes for PHP and Java. – Pacerier Dec 7 '14 at 16:54
  • 2
    @Pacerier: By vendor-lockin, I'm referring to the database vendor. In actual practice, the database (and the programming stack) are rarely replaced. – Robert Harvey Dec 7 '14 at 16:55
  • 3
    Maybe late but I am of the opinion that stored procedures implementing business logic belong to the business logic layer, not the data layer. They are kind of separate lang with no need for ORM. – Paralife May 4 '16 at 7:56
  • 2
    Business logic belongs into one single place in an application. If you can put all of the business logic into the database (most probably, you cannot), then fine, go for it. But fiddling together parts that are handled in the db layer, other parts in the application code (and maybe even some more somewhere else) is simply a hassle in maintenance. – tofro Oct 2 '16 at 9:43

I am a strong believer in keeping business logic out of the database as much as possible. However, as my company's performance developer, I appreciate that sometimes it's necessary to achieve good performance. But I think it is necessary far less often than people claim.

I dispute your pros and cons.

You claim that it centralizes your business logic. On the contrary, I think it decentralizes it. In a product that I currently work on, we use stored procedure for a lot of our business logic. Many of our performance issues come from calling functions repeatedly. For instance

select <whatever>
from group g
where fn_invoker_has_access_to_group(g.group_id)

Problem with this approach is that it generally (there may be cases where this is false) forces the database to run your function N times, once per row. Sometimes that function is expensive. Some databases support function indexes. But you can't index every possible function against every possible input. Or can you?

A common solution to the above problem is to extract the logic from the function and merge it into the query. Now you have broken encapsulation and duplicated logic.

Another issue I see is calling stored procedures in a loop because there is no way to join or intersect stored proc result sets.

declare some_cursor
while some_cursor has rows
    exec some_other_proc
end

If you pull the code from the nested proc out, then you again decentralize. Therefore, you are forced to choose between encapsulation and performance.

In general, I find that databases are bad at:

  1. Computation
  2. Iteration (they are optimized for set operations)
  3. Load balancing
  4. Parsing

Databases are good at:

  1. Locking and unlocking
  2. Maintaining data and their relationships
  3. Ensuring integrity

By taking expensive operations like loops and string parsing and keeping them in your app tier, you can horizontally scale your application to get better performance. Adding multiple app servers behind a load balancer is usually far cheaper than setting up database replication.

You are correct, however, that it decouples your business logic from your application's programming language, but I don't see why that is an advantage. If you have a Java app, then you have a Java app. Converting a bunch of Java code into stored procedures doesn't change the fact that you have a Java app.

My preference is to keep database code focused on persistence. How do you create a new widget? You must insert into 3 tables and they must be in a transaction. That belongs in a stored procedure.

Defining what can be done to a widget and the business rules for finding widgets belongs in your application.

  • 8
    In SQL server only poorly written sps need to be called in a loop, you can send it sets of data in a parameter and do a set-based process. – HLGEM Apr 9 '13 at 21:27
  • 2
    SQL Server will generate a sub-optimal query plan whenever there's a UDF in a WHERE clause. – Jim G. Apr 9 '13 at 23:28
  • 7
    Looks like your performance problem is not the fault of logic in database vs app.. it's just poorly written and architected. That problem will follow you in the ORM world just the same. ORMs can be a real headache outside of CRUD operations. If your system is data heavy, reporting type of system, please use caution. – sam yi Jul 15 '14 at 18:06
  • That is true. Most of our performance issues are simply due to poorly written code and over-complex architecture. But I still believe that we put the wrong type of work into our databases. Coding as much as possible into the database has caused us to do things a database is not good at. – Brandon Jul 15 '14 at 19:24
  • 1
    This example is even an argument to place core parts of biz logic in the DB: to avoid iterative approach (code or cursor loops instead of set-based expressions) like the plague. Programmers tend to treat sets of objects in an iterative manner (loop, traverse), likely leading to unnecessary loads or the SELECT N+1 problem of many single query roundtrips. By using SQL or language based expressions (e.g. LINQ), they will be forced to use a set based approach instead, whenever possible. – Erik Hart Oct 1 '16 at 15:09

I have worked in 2 different companies that had different vision on the subject.

My personal suggestion would be to use Stored Procedures when execution time is important (performance). Since Stored Procedure are compiled, if you have a complex logic to query the data, it's better to keep that on the database itself. Also, it will only send the final data to your program at the end.

Otherwise, I think the logic of a program should always be in the software itself. Why? Because a program needs to be testable and I don't think there is an easy way to unit test stored procedure. Don't forget, a program that is not tested is a bad program.

So use Stored Procedure with caution, when it's needed.

  • 3
    Stored procedures are unit testable. See here for some techniques. – Robert Harvey Apr 9 '13 at 15:23
  • 4
    afaik, an unit test never use database or file. So technically, "unit testing" a stored procedure is not unit testing and it will be slow as hell. A unit test suite should be run in seconds (or maybe minutes with very big application) at any time during development. – Jean-François Côté Apr 9 '13 at 15:28
  • 1
    The OP was talking about "business logic" and business logic should be unit tested. By putting it in a stored procedure, you mix it with database query which slow the whole process. Like I said, you can use Stored Procedure (it's not a crime) but it will blur the line between the business logic and database layer which is bad. Use it with care :) – Jean-François Côté Apr 9 '13 at 15:34
  • 1
    If you create the db and necessary objects, the sp, test, and then tear it down afterwards, it's a unit test. It tests a unit of work. – Tony Hopkinson Apr 9 '13 at 20:39
  • 2
    Hasn't the performance gains with stored procedures myth been debunked? – JeffO Apr 9 '13 at 20:50

There's a middle ground that you need to find. I've seen scary projects where the programmers use the database as nothing more than an overpriced key/value store. I've seen others where the programmers fail to use foreign keys & indices. On the other end of the spectrum, I've seen projects where most if not all of the business logic is implemented in database code.

As you've noted, T-SQL (or its equivalent in other popular RDBMSs) is not exactly the best place to be coding complex business logic.

I try to build a reasonably decent data model, use features of the database to protect my assumptions about that model (i.e., FKs and constraints), and use database code sparingly. The database code is useful when you need something (i.e., a sum) that the database is very good at doing and can spare you from moving a zillion records over the wire when you don't need them.

  • 2
    Using the database as an "overpriced" key/value store is a perfectly valid technique, as the legions of NoSQL practitioners will attest. – Robert Harvey Apr 9 '13 at 15:28
  • 1
    @RobertHarvey You're obviously correct, but somehow my gut continues to insist that there must be a simpler/cheaper/faster solution than a database if all you need is a key/value store. I need to learn more about NoSQL. – Dan Pichelman Apr 9 '13 at 15:47
  • 2
    I don't see using stored procedures as a cure for a poorly designed database. – JeffO Apr 9 '13 at 20:39
  • 2
    @RobertHarvey, I readed "overpriced key/value store" literally. Paing an Oracle or SQL Server license for something like that, when there are options like MongoDB available for free, seems like wasting money. – Raphael Apr 10 '13 at 13:34
  • @Raphael Or you could use PostgreSQL 😉 – Demi Mar 3 at 14:19

If your business logic involves set operations, most likely a good place for it is in the database because database systems are really good at performing set operations.

http://en.wikipedia.org/wiki/Set_operations_(SQL)

If the business logic involves some sort of calculation it probably belongs outside of the database/store procedure since databases are not really designed for looping and calculating.

Although these are not hard and fast rules, its a good starting point.

There is no one right answer to this. It depends on what you use the database for. In an ENterprise application you ned thelogin the database through foreignkeys and constraints and triggers, etc because it is the only place where all possible applications share code. Further, putting the reuired logic in code generally mean s the database is inconsistent and the data is of poor quality. That may seem trivial to an application devloper who is only concerend with how the GUI works, but I assure you that the people trying to use the data in compliance reports find it very annoying and costly when they get billion dollar fines for having data that didn't follow the rules correctly.

In a non-regulartory environment when you don;t care as much about the whole set of records and only one or two applciations hit the database, maybe you can get away with keeping it all in the application.

After a few years, the question is still important...

Simple rule-of-thumb for me: if it's a logical constraint or an ubiquitous expression (single statement), place it in the database (yes, foreign keys and check constraints are business logic, too!). If it's procedural, by containing loops and conditional branches (and really can't be changed into an expression), put it in code.

Avoid trash dump DBs

Attempts to place really all business logic in application code will likely degenerate the (relational) database into a trash dump, where relational design is mostly to completely omitted, where data can have any inconsistent state, and normalization is missing (often mainly XML, JSON, CSV etc. trashbin columns).

This kind of application-only logic is probably one of the main reasons for the rise of NoSQL - of course with the downside that the application has to take care of all the logic itself, what has been built into relational DB for decades. However, NoSQL databases are more suitable for this kind of data handling, for example, data documents maintain an implicit "relational integrity" within themselves. For relational DBs, it's simply abuse, causing ever more trouble.

Expressions (set-based) instead of procedural code

In best case, every data query or operation should be coded as an expression, rather than procedural code. A great support for this is when programming languages support expressions, such as LINQ in the .NET world (unfortunately, only queries currently, no manipulation). On the relational DB side, it has been taught for a long time, to prefer SQL statement expressions over procedural cursor loops. So the DB can optimize, do the operation parallel, or whatever may be useful.

Utilize DB data integrity mechanisms

When it comes to RDBMS with Foreign Key and Check constraints, calculated columns, possibly triggers and views, this is the place to store the basic business logic in the database. Proper normalization helps to maintain data integrity, to ensure a unique and distinct instance of the data. Even if you have to duplicate it in code and DB, these basic mechanisms of data integrity should not be omitted!

Stored Procedures?

Stored Procedures are rarely necessary nowadays, since databases keep compiled execution plans for SQL and reuse them when the same query comes again, only with different paramters. So the precompile argument for SPs is no longer valid. One can store or auto-generate SQL queries in the application or ORM, which will find precompiled query plans most of the time. SQL is an expression language, as long as you don't explicitly use procedural elements. So, in best case, you use code expressions which can be translated into SQL.

While application side, including ORM generated, SQL, is no longer inside the database, unlike Stored Procedures, I still count it as database code. Because it still requires SQL and database knowledge (except the most simple CRUD), and, if applied properly, works greatly different than the procedural code usually created with programming languages like C# or Java.

It realy depends on the business, its culture and legacy. Technical considerations aside (these have been covered from both sides), the answers given tells you that it comes down to where people are coming from. In some organizations, data is king and the DBA is a powerful figure. This is your typical centralized environment, a data center with a bunch of terminals attached to it. The preferrence in this type of environment is clear. The desktop may change radically many times before anything changes in the data center and there will be little in between.

The other end of the spectrum is the pure 3-tier architecture. Or maybe multi-tier in a web oriented business. Your will likely hear a different story here. The DBA, if there is any, will be just a sidekick that performs some administrative tasks.

A modern times application developer will have more affinity with the second model. If you grew up with a large client-server system you would likely be in the other camp.

There are often so many non-technical environment related factors involved here, there is no general answer to this question.

The term business logic is open to interpretation. When building systems, we want to ensure the integrity of the database and its contents. As a first step there should be different user access grants in place. As a very simple example, let us consider an ATM application.

To get the account balance, doing a select on an appropriate view should fine. But to transfer funds, you would want the transaction to be encapsulated by a stored procedure. The business logic should not be permitted to directly update the tables for the credit and debit amounts.

In this example, the business logic could check the balance before requesting the transfer or simply invoke the stored proc for the transfer and report the failure. IMHO, the business logic, in this example, should pre-emptively check that sufficient funds are available and that the target account exists and only then invoke the transfer funds. If another debit happens between the initial steps and the stored proc invocation, only then would an error be returned.

  • Nice example and explanation. – user251748 Oct 6 '17 at 14:45

protected by gnat Oct 1 '16 at 17:23

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.