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

Question

I'm using Rails and it does not provide support for database specific actions like triggers, stored procedures and various constraints (not all of them).

I wonder if I should put the database logic in the application itself instead.

Because then you can make more complex logic than the database provides and it is also database independent (I can move from mysql to postgresql and vice versa), and it won't be if you are putting these things in the database.

Is this the right way to go?

Thanks

Answer 1

(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.

Answer 2

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.