What are the arguments against or for putting application logic in the database layer?

Question

NOTE The audience of programmers.se and dba.se is different, and will have different viewpoints, so in this instance I think it's valid to duplicate What are the arguments against or for putting application logic in the database layer? on programmers.se.

I couldn't find discussion on dba on this already, and the original post says it all, so:

Most software developers want to keep application logic in the application layer, and it probably feels natural for us to keep it here. Database developers seem to want to put application logic in the database layer, as triggers and stored procedures.

Personally I would prefer to keep as much as possible in the application layer to make it easier to debug and keep the responsibilities of the layers separate.

What are your thoughts on this, and what should or should not be ok to implement in the database layer?

N.B. I'm not the OP for that question, but left the original wording intact.

Answer 1

Assorted thoughts...

Your database code will outlive your application client technology. Think of ADO.NET -> Linq -> EF as well as assorted ORMs. Whereas you can still run SQL Server 2000 code from last millenium against all of the above client technologies.

You also have the multiple client issue: I have .net, java and Excel. That's 3 sets of application logic.

"Business logic" shouldn't be confused with "data integrity logic". If you do have clients starting transactions and doing assorted checks, that's a lot of db calls and a long transaction.

Application logic doesn't scale for high data volumes. We have 50k rows per second using stored procs. A sister team using Hibernate can't get one per second

Answer 2

I want all the logic that has to apply to all users and all applications in the database. That's the only sane place to put it.

The last Fortune 500 I worked at had applications written in at least 25 languages hitting their OLTP database. Some of those programs moved to production in the 1970s.

The alternative to implementing this kind of requirement in the database is to let every application programmer reimplement all or part of it 100% correctly, every time they fire up their editor, from the day they first walk through the door until the company goes out of business.

What are the odds?

Isn't this the single biggest "don't repeat yourself" on the planet?

Answer 3

I'm moving my old answer across unedited from programmers.se, as answers seem pretty polarised between sites.

I know I'm in for a world of hurt here, but put business logic in the database because:

• You can allow business power users direct access to the database and not worry about them screwing it up (or worry less than you would with app-based logic)
• A power user can create new reports without waiting for a new software release.
• You can test SP / TRIGGER code in an a copy of the database, just like you do testing app-based logic
• You can keep the SQL to create sp's and triggers in text files (you should be doing this anyway for table/view code)
• You can mix and match languages without porting business logic
• You can make changes to business logic without upgrading every bit of software
• You audit structure changes the same way you audit database activity - via logging
• Vastly improved security and fine-grain access control (most app-based logic implementations use their own security model, so the data is far easier to compromise. Reversible password encryption is not uncommon)
• Database-side user security greatly reduces the damage/theft rogue SQL can do

The cons are: - Developers threatened when users become less reliant on developers for custom reports - Developers need to learn another programming language

Neither of those should matter to a skilled developer.

Interesting to note, most answers talk in terms of 'application logic', not 'business logic', as if the software isn't there to provide a business function.

Answer 4

The most important issue is whether any 'layer' above the database thinks that it owns the data. Concurrency and data integrity are problems for which the solution is an RDBMS - some applications are developed as if the database is just their personal bit bucket and of course they end up trying to re-invent the wheel in all sorts of ways, as well as being irreparably broken as soon as some other application accesses the same database

Answer 5

I wrote up my answer to this in my blog. My conclusion is, doing it in the application just doesn't scale once you consider the entire application lifecycle.

…
3. Add integrity/check constraints to the underlying database, with more complex code implemented in the database’s stored procedure language. From this we get one central location to maintain and we get absolute enforcement of the rules even for applications that we don’t know about! We get one language to express the business rules in, throughout the entire application portfolio and lifecycle, since the language du jour changes far more often than the database. And it runs on a system that is already as mission-critical as the most important applications. Errors are handled by the existing code that handles database errors in those applications. There is still the risk that an application might break of course, but of the three scenarios, this is the least, and only the broken application needs any modification, not all of them (and most SP/database mechanisms will allow an exception to be made for one application if that is really, really necessary). Think this doesn’t matter in your greenfield site or small company? Well if your business succeeds, in 30 years time you will wish you had paid heed to my wisdom!

…Some [objections] I often hear:

• It’s difficult to version control SP code deployed in the DB. I don’t think that’s any truer than saying it’s difficult to version control Java code deployed in an app server, which is to say, it isn’t difficult at all, it’s commonplace. And over in Ruby-land, entire books are written just about how to get your code from a development environment into production, something that no other language community seems to struggle with. Yet version controlling a stored procedure is apparently too difficult.
• Stored procedures are hard to test. This is a strange one. For a start, SPs are strongly typed; the compiler will tell you if there’s a code path in or out that doesn’t make sense, and in Oracle at least, will calculate all the dependencies for you. So that’s one set of common unit tests you might need in Ruby eliminated off the bat. To test OO code requires mocking to coerce the object into the internal state required to represent the test scenario – how is setting up test data any different? There is a TAP producer for PL/SQL and other tools besides. There are debuggers and profilers too.
• A stored procedure language is not a fully-featured language. Well, we aren’t trying to write an entire application just in stored procedures! Most dedicated SP languages have all the modern constructs that you would expect, and in Oracle at least, you can use Java Stored Procedures with all the language features OO developers are familiar with, or external procedures in any language. What matters is where the logic is implemented – in one place, close to the data – the actual language is just a detail. PL/SQL compiles to native code and runs in-process with the database; there is no higher-performance architecture than that.
• I don’t want to have to learn another language. Overlooking for a second this is a huge red flag in any developer (especially one that proposes modifying production apps which might be in other languages anyway!) there is a lot to learn to work in any modern environment: a typical Java shop might have Eclipse, WebLogic, Maven, Hudson, Anthill, Subversion, and a whole plethora of others, that you need to learn before writing a single line of application code. A working knowledge of a very high level SP language is straightforward in comparison, and there will more than likely be a specialist or a DBA around to help you too. Not to mention that developer favourite Hibernate comes with its own query language…

…

Answer 6

One con that people aren't necessarily discussing - the pros have been exhausted here - is cost.

The CPUs on the database server are frequently the most expensive CPUs in any organization when baking the cost of software licensure. So moving business logic to the data tier is something that should be done judiciously, not necessarily uniformly.

Answer 7

Does the SQL do things like set logic and application oriented results filtering? SQL is a wonderful set manipulation language.

Additionally, as GBN pointed about above, the SQL code is going to almost universally outlive the application code.

While it's true that EF or NHibernate or LinqToSql or whatever will allow you to generate code faster, every programmer worth their performance knows that only optimizing the SQL will optimize the data retrieval. The RDBMS only understands SQL, so you have to make everything into SQL before it's all said and done. (assuming that we can agree that TSQL and PLSQL are still SQL)

Answer 8

Here is where the meeting of the minds, that is to say, the minds of Developers (DVs) and DBAs, must inevitably happen. Working with Business Logic (BL) and storing such in a database can have an impact that can either glorify or horrify its implementation.

For some RDBMS products, there exists superior libraries/tools/APIs for Business Logic and Object Infrastructures one could quickly learn and employ in their applications. For other RDBMS, no libraries/tools/APIs exist.

In the past, client server apps made the bridge into BL via Stored Procedures (SP). For products such as Oracle and SQL Server, this was done early. As open source databases such as PostgreSQL and MySQL came into being, those using them were at risk of breaking new ground with stored procedures in BL. PostgreSQL matured very quickly in this, since not only stored procedures were implemented but also the ability to craft customer languages also came along. MySQL basically stopped evolving in the world of stored procedures and came in a stripped-down form of a language with many restrictions. Thus, when it comes to BL, you are completely at the mercy of MySQL and its Stored Procedure language.

There only really remains one question: Regardless of the RDBMS, should BL resides in whole or in part in the database ?

Think of the Developer. When things go awry in an application, the debug process will have the Developer hop in and out of a database to follow data chanages that may or may not be correct intermittently. It is like coding a C++ application and calling Assembler code in the middle. You have to switch from source code, classes and structs to interrupts, registers and offsets AND BACK !!! This taking debugging to that same level.

Developers may be able to craft a high speed method of executing BL in conjunction with language configurations (compiler flags for C++, different settings for PHP/Python, etc) via business objects sitting in memory rather than in a database. Some have tried to bridge this ideology for faster runnng code into the database by writing libraries where debugging Stored Procedures and Triggers is well integrated in the Database and seemlessly useable.

Thus, the Developer is challenged to develop, debug, and maintain source code and BL in two langauges.

Now think of the DBA. The DBA wants to keep the Database lean and mean as much as possible in the realm of stored procedures. The DBA may see BL as something external to the Database. Yet, when SQL calls for the data needed for BL, the SQL needs be lean and mean.

Now, for the meeting of the minds !!!

Developer codes SP and uses iteractive methods. DBA looks at the SP. DBA determines that a single SQL statement can replace iteractive methods written by the Developer. Developer sees that the SQL statement suggested by the DBA requires calling other BL-related code or SQL that does not follow normal execution plans of the SQL statement.

In light of this, the configuration, performance tuning, and SP coding becomes a function of the depth and data-intensiveness of BL for data retrieval. The more depth and data-intensiveness the BL, the more Developers and DBA must be on the same page for the amount of data and processing power given to the Database.

CONCLUSION

The manner of data retrieval should be always involving both Developer and DBA camps. Concessions must always be made as to what coding methods and data retrieval paradigms can work together, for both speed and efficiency. If the preparation of data for source code to handle is done only one time before the code gets the data, the DBA should dictate the use of the lean and mean SQL. If the BL is something the DBA is not in tune with, the reins are then in the hands of the Developer. This is why the DBA should see himself/herself and part of the project team and not an island unto himself/herself, while the Developer must let DBA do the fine tuning of the SQL if it does warrant it.

Answer 9

It's a nice question to ask at a website full of DBA's. Hopefully most of the answers will be "pro" towards keeping the database in an ACID state, and thus keeping business logic in the database. :-)

As for my opinion I think you should implement the business logic in both your application(s) and database(s). This approach will cost more time and money but I think it will have a qualitative better business solution as a result.

Answer 10

As Adam Musch said above, there is more to consider here for performance. CPU usage. Memory usage.

Block obviously wrong things from getting to the database.

• Weed out email addresses that don't conform in some basic way.
• Check lengths

When you get deeper that's when decisions need to be made. The DB server is a very expensive place to do things that the client could do easily. example: data formatting, format dates, assemble strings, etc client side.

Do you do the math/processing at client or on the DB server? For me that depends on complexity and number of records involved. Business logic should really be done in the DB itself so that everything is treated the same way.
You really should create an API of views to read and stored procs to write the data to the DB to save yourself headaches in future.

Use the strengths of each end to your benefit.