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.

Comparing the answers here and on SO, the gap is striking. The developers protest at the delays entailed from centralizing processes in the database, but to the DBAs that's a good thing. Forcing people to put more time and effort into requesting a new view or sproc reduces the number of contact points with the data, making it easier to maintain consistency and reducing the number of points of optimization. — Jon of All Trades, Apr 10 '12 at 21:35
It seems to me that the answers here assume a certain way of using the database (multiple applications, allowing some users direct database access, etc) I think that's the main reason for the difference. — JMD Coalesce, Oct 12 '17 at 09:57

score 65 · Answer 1 · answered Apr 29 '11 at 17:26

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

@JMDCoalesce: You still need business logic and are liable to have multiple client apps.So what is your point? — gbn, Oct 12 '17 at 10:00

score 43 · Answer 2 · answered Apr 30 '11 at 01:43

43

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?

answered Apr 30 '11 at 01:43

Mike Sherrill 'Cat Recall'

10,766
1
36
43

This only applies if multiple applications use one database – JMD Coalesce Oct 12 '17 at 09:52
1

@JMDCoalesce which is common in many environments. Main app, Excel reporting, server side reporting, bulk extracts: it soon adds up. Almost any banking application has a myriad of client apps, – gbn Oct 12 '17 at 09:58
Sure but not all applications are for banking. – JMD Coalesce Oct 12 '17 at 16:56

score 31 · Answer 3 · answered May 02 '11 at 02:50

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.

score 24 · Answer 4 · answered Apr 29 '11 at 17:25

24

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

answered Apr 29 '11 at 17:25

Jack Douglas

39,869
15
101
176

1

I think that whoever sponsors the system owns the data - they've paid for it. Also I solve lots of concurrency problems before they hit the database - in many cases it is much easier. – A-K Jan 22 '13 at 01:26
6

you are using 'own' in a different sense than I am: my point is that if you 'solve' concurrency problems before they hit the database you have to also ensure that yours are the only applications hitting the database or they have to be solved all over again at that level. I agree with the top voted answer: "Your database code will [likely] outlive your application client technology." – Jack Douglas Jan 22 '13 at 07:38

score 19 · Answer 5 · edited Mar 07 '14 at 13:57

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…

…

score 13 · Answer 6 · answered Mar 07 '14 at 14:32

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.

score 12 · Answer 7 · answered Apr 29 '11 at 23:13

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)

score 7 · Answer 8 · answered May 04 '11 at 21:16

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.

score 4 · Answer 9 · answered Oct 17 '12 at 12:21

4

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.

answered Oct 17 '12 at 12:21

Ruud van de Beeten

588
6
17

1

The same logic in two layers? – András Váczi Oct 17 '12 at 12:45
If you want to create a new customer and you have to store his/her name and a customer number (which always contains 4 numbers) I'd like the application to check if the customer number is valid, before sending a SQL statement to my database (knowing the statement will not pass my businness logic in the database). – Ruud van de Beeten Oct 17 '12 at 13:04
Could you then amend your answer with a few hints on where/how to divide logic into app and DB parts? – András Váczi Oct 17 '12 at 13:09
2

All businness logic should be implemented in the database (so don't divide 'logic'). Everything you can easly check in the applications (E.g. with regular expressions in Javascript) is less work for the database (in case of invalid input). – Ruud van de Beeten Oct 17 '12 at 13:15
2

+1 this is what I do–I just call it "putting the business login in the database and putting convenience checks in the app" – Jack Douglas Oct 17 '12 at 13:44
1

You need to have a systematic approach to make this work. Core integrity logic which makes the data always match expectations needs to be done in the database first. Maintaining good communication back to the app from the database of the exceptional conditions and the client being able to communicate them adequately to the user comes next. Then anticipating those before making a trip to the database will be the most duplicative part and necessarily will have to be kept in sync - if you can minimize the need for keeping these in sync you will be best off. – Cade Roux Oct 17 '12 at 14:06

score 3 · Answer 10 · answered Jun 23 '14 at 20:21

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.

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

10 Answers10

Linked

Related