* You can't test/debug BL in DB (or it's very hard)
* You can't do continuous integration
* The code a mess and hard to maintain/make sense of
If your answer is "majority in app":
* Do you have an API on top of DB that enforces the rules?
* Is the security/data validation also done in the API/app layer?
As a broad generalization, if you are latency bound (milliseconds or less) then everything is done in code and the DB is never in the main loop.
At the other end, if you are throughput bound - think end of day/month activities - then stored procedures all the way.
For everything in between, prefer to have the logic in code due to the maintenance benefits that come with it.
> Do you have an API on top of DB that enforces the rules?
In general, a database is owned by the app it is associated with and the app mediates all access to that data. The usual enterprise integration patterns (esb, messaging, web services, file dumps) are used to share data with other systems.
> Is the security/data validation also done in the API/app layer?
Largely yes. There's some level of security (like app access control and encryption at rest) that's handled in the DB tier but authorization is largely an app level concern.
Similarly, some constraints are imposed in the DB schema but those are quite basic. Most validation is done in the app tier.
You test/debug in a separate, isolated full-stack environment that has a copy of the data.
> * You can't do continuous integration
Correct.
> * The code a mess and hard to maintain/make sense of
This is conjecture, and it's entirely dependent on the quality of the code.
I am particularly interested in this part since it seems to be a common theme/argument against BL in DB
You get one level of namespacing, and function names have to be less than 32 characters, so abbreviated names are common. This can be unwieldy, but alleviated by good comments and docs.
You want to code each function to do one thing: have a well-defined input, and a well-defined output. Living inside the database gives us transaction support for free -- atomic operations that either succeed or rollback. The application running inside the DB defines the external interface with its functions (the same way a web service or a library would), and keeps the raw tables as an implementation detail.
If we moved more functions out of the app into a higher layer (ie. Java/Python/etc), we'd actually have more functions overall, as you'd either have to have a bridge method that calls the equivalent function inside the DB, or rely on an ORM and invent some instrumentation to deal with concurrency requirements [1]
At the end of the day, a modern RDBMS allows you to implement applications that present a well-defined interface to the outside world, with the internal implementation remaining a private 'black box'.
[1] https://docs.jboss.org/hibernate/orm/3.3/reference/en-US/htm...
Most of the time, the code I work with operates on a handful of records per transaction; bulk operations require entirely different tradeoffs and I don't have much to offer here.
All of the problems I've seen stem from the understandable urge to treat the database as a giant global variable. Oracle provides a myriad of conveniences to facilitate this from within pl/sql so developers do exactly that:
- In the middle of some logic and realised you needed another piece of data to work with? Just splat another inline SELECT in the code and move on.
- Want to update a table but you can't find an existing SP which writes what you need? Just splat an inline UPDATE in the code and move on.
Over time, these SQL statements littered throughout the stored procs become a complete mess. Another issue is that client code is often given unfettered access to the base tables. If those tables don't force data integrity either by constraints or triggers, then god help you if you want to update any column anywhere in your database: you simply cannot know which clients may break.
Oracle pl/sql offers both FUNCTIONs and PROCEDURES and these form the core of a number of idioms I've come to recommend:
- if your pl/sql performs any side effects, it's a PROCEDURE. End of.
- FUNCTIONs never contain side effects (or use OUT parameters).
- PROCEDURE status is "toxic": if a FUNCTION calls a PROCEDURE, it must also become a PROCEDURE.
- client code never accesses base tables. Wherever possible, try to provide access through a package API: instead of SELECTs, call functions which return REF CURSORs, instead of UPDATEs and INSERTS, call PROCEDUREs. These mechanisms are all supported by both jdbc and .Net though you'll have to do a bit more work.
The key thing is to put your BL in functions, not procedures. They're much easier to test, especially if your variables can be passed in as parameters rather than being lifted from tables via the seductive inline SELECT option.
PROCEDURES are always tricky to test. Try to keep them as simple as possible and try to minimise conditional logic leaking into them.
Data integrity is difficult to get right. Oracle provides table constraints and triggers but beware of putting BL into either of them (the dividing line is often blurry and the risk is that your future applications may be overly constrained whilst your existing ones may come to rely on leaky assumptions if you relax the integrity guarantees). I've not seen constraints or triggers used to great effect anywhere, to be honest. Most shops avoid them, it seems.
The easiest way to stay on top of data integrity is to force all updates through higher level SPs. Don't let clients touch base tables and, instead, force them to call facade packages. Write one facade package per client - Oracle will mark the package as invalid if you modify anything in the execution path of said package and this provides a valuable tool for impact analysis.
CI is nearly impossible in a live database unless you're running a version of Oracle with Edition Based Redefinition. The only alternative I can think of is pulling your logic out into middleware libraries.
Testing is a trade-off. You can test the end points of stored procedures that the app has access to for example. Although where I worked admittedly there was no automated testing.
You can do CI if you have a way to deploy changes to scripts automatically.
The code doesn't need to be a mess but admittedly PLSQL doesn't have the same nice abstraction patterns as OO so it does get a bit W.E.T. sometimes.
Although every project is different, as a general rule I prefer to use stored procedures to make changes to the data. The SPs are written to ensure data integrity. Of course, there are some very basic business rules evident at this level but the primary concern is the correctness of data as updated/inserted. I would characterise these as the "data integrity rules".
Each SP encompasses a document, a test suite and the implementation. It goes through the DEV-TEST-UAT-PROD QA - promotion stepwise deployment. I see that as a reasonable substitute for continuous integration.
The OO layer implements business rules. (The SPs are transactional/procedural). I consider the BRs as a lower level business logic. Then the application provides the high level business logic, e.g. validation, workflow, etc.
In reference to your question, I generally end up with three layers, DB, BR (API), BL (app). Technically the DB also has an API, but that is only visible to the BR layer.
One of my wise old developers frowned and when I asked why he told me there were literally hundreds of stored procedures in this database and no documentation.
It was at this time he told me to when possible keep business logic in code so it can be managed by a version control, and easily replicated for CI.
He advised the exception to this rule was when the stored procedure gave a large performance boost.
DB handles data integrity. App/API handles data validation.
A pattern that applies to most industries is Service-oriented Architecture (SOA) -- Where each layer (presentation, logic, data) is independent of each other.
This allow for fantastic scalability and granular debugging.
First, weirdly enough I prefer to call the Business logic layer just the Logic layer. This is just personal, for all intensive purposes its the Business logic.
Goes without saying this example is basic and open for improvements. Let's suppose an application exists where a user can donate $10 a month and the app would randomly distribute it to charities of the users choice.
-- Presentation layer, usually a public facing web application or mobile app will handle user interaction with said app. Here the pretty UI will make sure the user provides proper card information, charities etc.
-- Logic layer, will be some API that will process the users information and distribute the money programmatically.
-- Data layer, is some kind of relational database where data will can be persist if necessary. e.g User credentials.
Standard security protocols such as form validation and sanitization should always be performed both at Presentation level and at Logic level. For compliance, Things like encryption at data rest and while its in transit are important. Method of communication between each layer would be critical as well. There is a whole LOT more things to consider so when thinking security. These are just an example.
In fact, it's pretty hard to do CI without tools like this, even with zero stored procs.