Putting database logic in the application instead of trigger, stored procedures, constraints etc

(Note: This is a Postgres centric post. I have several years experience with MySQL, and a few Postgres and Oracle. I prefer Postgres by a country mile, but that's not the point of this post.)

This is really a question about two schools of thought: should the database be a simply a data store or should it contain application logic? There are cases for both.

IMHO the answer used to be a lot simpler before several DBs (esp Postgres) got some really, really good procedural languages in the DB itself. Before that point, it made sense to kick all the all the logic you could in the application because it had to be rather kludgey to get some things done in basic SQL.

Dave Markle in another answer makes a good point about triggers, they tend to be "magic" to the developer. Changing input on the way in can be terribly confusing. If I say UPDATE foo set X=3; but then I go check foo and x is really 4 because some trigger intercepted it, it can be confusing. Like Dave, I tend to promote triggers for auditing functions and the like. The other issue with triggers is performance, they can just suck the life right out of a good batch operation.

Stored procedures - I hold in much higher regard. The developer is explicitly invoking them and they're named, so he should not be seeing them as a black box. A good stored procedure can greatly increase the speed by reducing the number of rows that need to be "sent over the wire". For a DB such as Postgres with a strong set of PL langauges, there's really nothing that can't be in an SP (that doesnt mean it SHOULD be done, but can be). Take a look at SimplyCity for an example of how stored procedures can be your friend. Additionally, you could share application logic classes between your application code and PL/python, PL/Perl, or PL/Php, PL/Ruby, or PL/Java. I believe Oracle can do something similar with Java - it's just not something the company I currently do Oracle work for is working with.

If you plan on staying database agnostic, you're going to be sacrificing a lot of features, a lot of speed, and a lot of your time. ORMs can make this easier, but ultimately there is fundamental difference in most databases engines that just can't be fully abstracted away.

Overall, you need to test, test, test (with real data) and make the decision that's best for your app. It's often a balancing act between performance, future maintenance, cost, and the resources you have to work with. Often times (not every time), moving the logic out of the database and into the application greatly reduces the performance of the application.

Even if you don't decide to put application logic in the DB - take the time to really learn about your DB of choice. It will make you a far better application developer in the long run.

I will give an answer probably the opposite of what everyone else will say.

Database logic belongs as close to the data as you can possibly implement it, and that means in the database. Doing anything else will, at best, require you to repeat yourself in different applications and have a big update headache. At worst (and this is pretty likely) you'll have inconsistent database logic implemented in different applications.

You need to separate out what's "database logic" and what's "application logic". My normal example is defining what your organization means by "FullName" for a customer. Imagine that you write code in your application that combines the first and last names into a full name field. Later, you add a middle initial (or decide to include an already existing middle initial column) as part of the full name.

If your full name logic is implemented in a view or stored procedure, you need only make a single change to enforce the new definition across all applications (including third-party applications for which you don't have source code).

The problem is somewhat eased in situations (like Ruby) where all data access is done through a shared ORM layer where you can put logic that will be used by all applications written in the same language. In my situation, however, I rarely work in situations in which only a single language or product will be used for programming against the corporate database.

If you're already using Ruby on Rails then you've committed to using opinionated software. Rightly or wrongly, Rails' opinion on this matter is that the logic goes within the Rails application. It even takes it as far as enforcing referential integrity itself through ActiveRecord and its associations, although you can of course enforce this in the database if you wish by adding constraints to your tables. A lot of Rails developers don't bother, either through ignorance or choice.

• The productivity gains Rails provides tend to diminish when you start to stray from its opinions and conventions
• If you're writing the sort of application that manipulates large quantities of data and would benefit from taking advantage of specific RDBMS features, then this is a bad fit for Rails anyway because it's optimized for creating new CRUD web applications