Arguments for/against Business Logic in stored procedures [closed]

Question

What are the arguments for and against business logic in stored procedures?

Sightly

This question is flawed since it does not separate write-oriented business logic from read-oriented business logic. The more I read arguments for and against logic in stored procedures, and the more I read about things like CQRS and immutability, the more I see that optimizing writes and reads require different classes of business logic. What are you asking about, both categories of write and read logic or only one? – Honeycomb

Answer 1

Against stored procedures: business logic in programming space

I place a high value on the power of expression, and I don't find the SQL space to be all that expressive. 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.

But it depends. If you have multiple applications interacting with one storage mechanism and you want to make sure it maintains its integrity and workflow, then you should offload all of the logic into the database server. Or, be prepared to manage concurrent development in multiple applications.

Charming

To address your concurrent development statement: rather than storing business logic in stored procedures, why would you not target an application server which in turn implements the business rules? Using this, all applications can get the same functionality with the benefits of de-duplication of work. – Sarene

Unsure what you mean by application server. So in your model no one is talking to the database directly but through some sort of intermediary application server? – Charming

Right. Using WCF as an example, the data access layer would be contained within a WCF service, which would transform and implement any business rules as necessary. In turn, your UI would not access anything directly, but would use service references to the WCF service, which would provide the appropriately transformed result. This also has the benefit of allowing multiple applications to use the services without much work. The service would provide metadata about what it returns, as well as what it expects as arguments. – Sarene

@MarkCanlas you would use a service layer when multiple applications/services want to use storage. Ideally the applications/services shouldn't even be concerned how or where the data is stored. The persistence can be handled by a microservice that is either injected or located via a service registry. Look at CQRS patterns + event sourcing for a start. – Folkrock

Answer 2

I am thoroughly against it. One of the biggest reasons is the first reason earino stated - it lives in one place. You can not integrate it into source control very easily. It is next to impossible to have two devs working on a stored proc at the same time.

My other main complaint is that SQL is just not very good at representing complex logic. You have no concept of scope, code tends to be copy-pasted because there is a less ability to reuse code (as opposed to an OO language).

You have to give developers access to the database to develop there. In many organizations I have worked at the data people are in a different world than the devs, with different permissions, etc. Keeping the devs out of the database in these cases would be harder.

Energy

I completely agree. I cannot believe developers continue to put complex logic in stored procedures. This shows a true lack of leadership. – Culbert

Answer 3

I'm of the school of thought that says that 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 don't care if the logic lives in a stored procedure, in a J2EE middle tier, in a clips expert system, or wherever. No matter where you store our business logic the "law of conservation of misery" is going to guarantee that someone will say it was the wrong idea because component/repository X needs to be swapped out for technology/method Y.

Ladner

Business logic can sit in a layer which handles persistence without being a stored procedure. Central or "one place" isn't really the right world when describing distributed systems that have eventual consistency patterns though. Many microservices will have business logic that relates to its own context. The word "central" or "one place" works well when describing monolithic systems. – Folkrock

@DasithWijes when I answered this question in 2009, we weren't really focused on microservices :) – Ladner

I have seen lots of business logic in stored procedures at various companies and it has NEVER been the best solution. In EVERY case there were no unit tests and debugging was more difficult. It also added sprawl to the business logic, spreading it out among too many code sources. You can use RESTful serves or add projects as externals to other code sources to share business logic. The SP way of doing it has been by far the worst solution I’ve seen. – Culbert

Answer 4

Some thoughts: Please note this is a Java centric response, but its the bulk of my recent (last 10years) experience

(1) Concurrent development by a (a large) team of developers. If you're application is sufficiently complex that each developer can't set up their own private version of the DB (with attended links/ref data/etc...)it is very difficult to have an entire TEAM of developers all working on the same set of PL-SQL (for example) packages at the same time stored in a shared DEVL DB? Then your stuck (my experience) with working in a DB with invalid procedures / mismatch of code to tables as people make changes...

As a Java architect, I think its much easier to have each developer have a private JBoss instance on their desktop and work easily on their own set of functionality, and integrating at their own pace without impacting everyone else ... which brings me to ...

(2) Continuous Integration toolsets While there exist some similar 'concepts' in the DB world, my experience has shown me that the combo of (i'm picking my current best-of-breed favs here):

• mvn - build system
• junit - automated unit testing
• nexus - repo manager (manages artifact's lifecycles version, snapshots and releases)
• hudson - ci build server
• sonar - static analysis tool / code coverage reports / ALOT more

Running a large project using all of the above (free tools) allows a consistent / easy way to deliver XP to the masses and enforce quality controls over a whole IT staff. Oracle / PL-SQL doesn't have the toolsets to match

(3) tools / libraries / etc... Java has access to an amazing set of services that other platforms cannot touch - some free, some not. even basic ones, like log4j (yes they have it for PL/SQL, but pulease...its not nearly the same) allows for things like allowing developers to create flexibly adjustable logging that can be changed on the fly (perfect for dubugging). Automated API documentation (via javadoc). Automated unit test coverage reports. Incredible IDEs (Eclipse) with integrated debuggers / autodeploy to app servers. An API to interface with every type of service under the sun, open source libraries to do ANYTHING, and 100% support by every vendor

(4) reuse of services. what someone commented on is true. If you have heavy duty data driven business rules then you can argue that these should live in the DB layer. Why? to prevent having the middle tier(s) all having to duplicate that logic.

But the same can be said for business rules that are not data driven or sufficiently complex that OO is a more natural choice. If you stick ALL business logic in the DB, then they're available only via the DB.

• What if you want to have validation done in the client or middle app tier and save a round trip to the DB?
• What if you want to cache read only data in the middle tier (for performance) and have business rules execute against the cached data?
• What if you have a middle tier service that doesn't require DB access, or you have a client that can supply their own data?
• What if the data dependent portion of the business rules then needs to access external services? Then you end with with fragmented business logic that looks like this:

i

retCode = validateSomeDate(date);
if (retCode == 1) then
   evaluateIfCustomerGetsEmail(...)//probably more stored proc invocations here...
   sendEmailMsg(....)
else if (retCode == 2) then
   performOtherBizLogicStuf(...) //again, may need data, may not need data
   triggerExternalsystemToDoSomething(...) //may not be accessible via PL/SQL 
fi

I'm sure we've all seen systems written like the one above and had to debug them at 2AM. Its extremely difficult to get a coherent sense of a complex process when the business logic is fragmented between tiers, and in some cases it gets to be impossible to maintain.

Chromate

Answer 5

"You can not integrate it into source control very easily." - if you put the code that creates the stored proc into a script that's version controlled, that objection goes away. If you follow Scott Ambler's agile database ideas, that's exactly what you should be doing.

Not all developers are good data modelers. I can think of horrible schemas created by developers who thought that a dabbling knowledge of SQL made them database experts. I think there's a lot of value to having developers working with DBAs and data modelers.

If only one application uses the database, I'd say that business logic can appear in the middle tier. If many apps share the database, perhaps it's better to put it in the database.

SOA offers a middle way: services own their data. Only the service has access to the data; getting to data means going through the service. In that case, it's possible to put the rules in either place.

Applications come and go, but data remains.

Packer

+1 on source control. I have read a number of comments here about source control. It's versioned just like anything else. – Manxman

Answer 6

One more reason NOT to store business logic in sprocs - limited scaling abilities of the DB. It is very common situation where your database is your bottleneck, that is why it is a good idea to take as much load of the DB as possible.

Wingding

nope. you can use virtualization on a database server and scale it forever – Administrate

@UğurGümüşhan Data virtualization can get very complicated and slow (specially change management) when you can build modern systems based on microservices + CQRS that take advantage of eventually consistent patterns. That's why almost all of today's major distributed systems are being built on these patterns. – Folkrock

Answer 7

Business logic should be encapsulated in one place. We can guarantee that the logic is always run and run consistently. Using classes that all activity involving an entity on the database must run through we can guarantee that all validation is run properly. There is one place for this code and any developer on the project can easily open this class and see the logic (because documentation can and does get out of date, the code is the only reliable form of documentation).

This is difficult to do with stored procedures. You may have more than one sproc dealing with the same table(s). Chaining multiple sprocs together so that the logic resides in only one gets unwieldy. That is strike one. How do you determine "What are all of the business rules surrounding entity X" within the database? Have fun searching thousands of sprocs trying to track that down.

Number two is that you are tying your business logic to your persistence mechanism. You may not store all of your data in the same database, or some may reside in XML etc. This type of inconsistency is difficult on the developer.

Validation is difficult to perform if the logic resides only in the database. Do you really call a sproc to validate every field on your data entry form? Validation rules and business logic are close cousins. This logic should all be performed in the same place!

Tarrant

Answer 8

My few observations:

In favour of stored procedures:

• after some time of project life, in most cases the bottleneck is the database not the web server - and stored procedures are much, much faster

• using sql profiler with ORM generated sql queries is very difficult; this is easy with stored procedures

• you can deploy a fix for stored procedure instantly, without a service window

• for performance, it is easier to optimize a stored procedure than ORM-code

• you may have many applications using the same db / stored procs

• any complex data scenario is a vote for stored procedures

In favour of application/ORM:

• you may use code repository (with stored procs it is still possible but expensive)

• java / c# language is a better tool to express business logic

• java / c# is easier to debug (except for the dynamically-generated ORM sql)

• independence of database engine (however this is not very likely that a project will change database to another one)

• ORM provides data model which is easy to use

In my opinion: for large projects - go for stored procedures, for the others - Application/ORM will work fine.

In the end of a day, the only thing which matters is the database.

Overpowering

The first time I've seen mentioned the granularity of stored procedures - you can deploy a small fix/enhancement immediately without having to compile and deploy something larger that may contain incomplete code. – Dormie

The bigger the application the harder is it to scale with stored procedures. That's why eventually consistent models and event sourcing patterns are useful. – Folkrock

Your SPs, DB schema, and compiled code, and configuration are all ONE APPLICATION. It should all be deployed together. – Culbert

Answer 9

+: SQL server sometimes optimizes the code

+: You are forced to pass parameters, which limits SQL injection issues

-: Your code depends on a single database (some dbs don't even have SP)

-: To change code you need to connect to database

-: Logic is not organized well

Personally I'm against it, but I had to use it once on a really busy website. Using SP in MS SQL brought huge benefits, but once I implemented caching those benefits were not so big anymore.

Carilyn

Answer 10

There is a saying...

When all you have is a hammer, everything looks like a nail.

In my humble opinion there is no one answer that will fit all circumstances. It seems to me that many people just assume that putting Business Logic in the database is always wrong.

A lot of work has been done to make transaction processing, especially bulk operations, very efficient when done on the database side. Also the code management in databases has vastly improved since most of the opinions against databases were formed.

I think it is wrong to consider the database server as just a persistence layer. If your processing activities are most efficient when done on the DB Server then do them there.

If not then do them elsewhere.

It all comes down to what best fits the application you are working on at the moment, the team with whom you are working and the customer who hired you.

That just my 2 cents.

Mannerism

I have only seen a few cases where stored procedures were useful (e.g. aggregations). But I have NEVER seen a single SP that was good for business logic, NEVER! It has been 100% of the time better to put into code or into a RESTful service. – Culbert

Answer 11

You can unit test business logic when its in a business logic layer. If it's completely separate the persistance operations can be mocked so you are testing the BL only. A stored proc is far more difficult to maintain/debug/unit test than e.g. linq and c#.

Compile

Answer 12

DBMS != Application server

• Functional programming (DB Stored procedure) vs OOP. For big programs is OOP just standard.
• IDE - eclipse, intellij, netbeans with all plugins for debugging, testing and analysis works only with real programming languages. Static code tools.
• Version control if you get one for PLSQL & co. is great. If you get "synchronize view" direct from you IDE - you are real the lucky one.
• Scale your system. For DB system is hell. You need expensive hardware for other "nodes". Replication. And probably licence for every node. Don't forget you are still in "functional programming" and effort to understand and maintain such systems is much bigger.
• You are stuck with your DB, try to change or add a new one from another company
• And so on...

Stored procedure for business logic is bad practise today. Use 3-Tier architecture instead.

Punner

Answer 13

There are different kinds of "business logic". Consider partitioning it based on how it's related to other layers or services. Here are some rules of thumb from an MVC perspective:

a) In the database (stored procedure) if it's mostly data-related, and can be done with joins and relatively simple WHERE and SELECT clauses.

b) In the controller if it's mostly routing or dispatching related; that is, larger-scale UI flow control in terms of screen or resource selection.

c) In the model or view-model if it involves complex or intricate computations and/or conditionals.

d) In the view (such as Razor) if it's primarily a display issue, such as "friendly" re-formatting and relatively simple to implement. (If it's complex, consider putting it in a view-model.)

Dysthymia

Answer 14

My rule of thumb (once I recognized what it was by thinking about the question) is that stored procedures should contain code that ensures data integrity within the database, whether the stored procedure prohibits certain data insertion, deletion or modification, or makes other changes necessary for data consistency. Business logic, especially logic that cannot be implemented in a handful of set-based operations, should be implemented elsewhere. Databases are not applications. Databases should be the ultimate authority for relationships and constraints, even if the business rules are also implemented elsewhere for, say, the provision of feedback in user interface code that reduces postbacks from a web server or eliminates otherwise unnecessary hits on a busy server. One can argue whether "data consistency" includes the result of complex processing of complex business rules, but I think it's usually clear once the context is understood. Not all business rules are implemented as data relationships or constraints. Not all operations in a stored procedure are faster than code running in a separate process, even on a process running on a separate machine across a network. I recently did a demonstration showing that many operations in SSIS, for example, (INSERT INTO () SELECT FROM) perform faster in SSIS running across a network on a separate machine than running in a stored procedure (that also inserts the results to a database across the network). This is an almost unbelievable result (where SSIS is faster than raw SQL statements), and demonstrates that the discovery of the best optimization of any performance issues comes from reality (testing) and not from logic based on only a few concepts. (We still have to make decisions on what to test by rules of thumb learned by experience.) (SSIS performed faster by automatically implementing multithreading and pipelines, using BULK INSERT even where it wasn't specified in the raw SQL statement, and sending batches of inserts on one thread while creating additional BULK INSERTs on other threads. In this instance, it performed about twice as fast as raw SQL statements.) When I used to teach programming and SQL Server courses, PowerBuilder users seemed to have the statement "Native drivers provide the fastest data access" burned into their tongue, and while it might be justified through additional (unrecognized by them) explanation, the thinking behind it is misleading.

Outguard

Answer 15

@Nick "I am thoroughly against it. One of the biggest reasons is the first reason earino stated - it lives in one place. You can not integrate it into source control very easily. It is next to impossible to have two devs working on a stored procedure at the same time."

Not that I'm arguing for putting business logic on stored procedures (all the contrary). But these reasons you put forward make no sense. A stored procedure is simply a sql/DDL artifact that can be stored on source control, and which is also a deployment artifact (that is, something handed over to the dba for deployment, much the same way you would hand over your war/ear artifacts to the IT/deployment liasons) One or more developers can work on the same stored procedure off source control just in the same way you'll do with plain old source code - by branching, versioning and merging.

Now, if the only copy of the stored procedure (and the packages that contain them) only exist in the database, then obviously you cannot control that with source control (and all the problems associated to that). However, that's not a problem of stored procedure but a problem of ineptitude in regard of how to use that code. It is as equally a display of ineptitude as having only one copy of your source code living on production.

I've worked in systems with massive amounts of code, both Java and PLSQL/DDLs and they were all versioned on clearcase. They were all treated as source code that would be compiled and deployed with a strict process, with different teams working on them. Never had any problem as what you are describing.

There are context-specific reasons not to put business logic in stored procedures, but these aren't valid ones.

Clinometer

Answer 16

Performance will be massively improved by moving logic into the stored procs, especially if explicit transactions are involved.

In my experience, application developers aren't very good at writing optimised database code, and don't tend to think about concurrency or performance issues.
If business logic is kept in the application layer, you tend to have to shift large amounts of data (often in many round-trips) across the network, duplicate it in memory on the DB server, and at least once in the app server, and do a load of row-by-row processing in the app while you're holding open a transaction. Then the app developers complain that the database is slow and keeps deadlocking.

If you put the logic in the DB wherever it's possible, you tend to just pass a few parameters across the network, transactions aren't held while you wait for network resources, and the whole thing goes like greased lightning. Databases should of course go in source control like any other source code.. there are plenty of tools for that.

Rondure

Answer 17

With all this micro-services and micro business components ideology we are way ahead of questioning the right place to put our business logic.

Even though the ideology is well accepted we still have temptation and some instances where we ended up putting some decision making and business logic in database. It’s worth visiting detailed answer to why it should not be done, but at macro level I would advise thinking about one good reason why it should stay in stored-procedure and not in the application layer.

Having that counter thought process would always direct taking the right decision. Ask these questions before deciding:

1. What if down the line we change our database from SQL to Mongo?
2. What if want to expose API which takes the data (that database is providing) and apply this business logic on top?
3. Unit testing this business logic?
4. SDLC steps involved if we make change in conditions of this business logic?
5. What if we need another user input for the decision making in this business logic?
6. What if it requires high computation power (not data processing) and we want to be run off a separate process (or platform) ?

In all cases it would make natural choice to not put any logic (other than just data retrieval) in business logic.

Codpiece