For an ORM supporting data validation, should constraints be enforced in the database as well?

Question

I have always applied constraints at the database level in addition to my (ActiveRecord) models. But I've been wondering if this is really required?

A little background

I recently had to unit test a basic automated timestamp generation method for a model. Normally, the test would create an instance of the model and save it without validation. But there are other required fields that aren't nullable at the in the table definition, meaning I cant save the instance even if I skip the ActiveRecord validation. So I'm thinking if I should remove such constraints from the db itself, and let the ORM handle them?

Possible advantages if I skip constraints in db, imo -

• Can modify a validation rule in the model, without having to migrate the database.
• Can skip validation in testing.

Possible disadvantage?

If its possible that ORM validation fails or is bypassed, howsoever, the database does not check for constraints.

What do you think?

EDIT In this case, I'm using the Yii Framework, which generates the model from the database, hence database rules are generated also (though I could always write them post-generation myself too).

Answer 1

Your guiding principle should be Don’t Repeat Yourself:

In software engineering, Don't Repeat Yourself (DRY) is a principle of software development aimed at reducing repetition of information of all kinds, especially useful in multi-tier architectures. The DRY principle is stated as "Every piece of knowledge must have a single, unambiguous, authoritative representation within a system."

The ORM is essentially an extra layer (or tier if you prefer), sitting comfortably between your application and your data storage(s). Your constraints should be in one place, and one place only, be it the ORM or the data storage, otherwise soon enough you’ll end up maintaining different versions of them. You really don’t want to do that.

However, in practice, most half decent ORMs automatically generate a great deal of your models from your data schema. Although there’s still duplication, the chances for maintenance hell are minimal since the duplicated ORM code is generated following the same pattern each time. It would be ideal to have no duplicate code, but automatically generated constraints is the next best thing.

Also, having your constraints in one place doesn’t necessarily mean you should have all your constraints in the same place. Some, like referential integrity constraints, may be more fitting for the data storage (but may be lost if you move to another data storage), and some, mostly those that are about complex business logic, are more fitting for your ORM. It’d be preferable to have all your apples in the same basket, but…

Failures

You mention the ORM failing. That’s absolutely irrelevant to your question, your application should think of the ORM and the data storage(s) as a single entity. If it fails, it failed, bypassing the ORM to talk to the data storage directly is not a good idea.

Bypassing the ORM for anything else

Also not a good idea. However, it can happen for a variety of reasons:

1. Legacy parts of the application that were build before the ORM was introduced.

   That’s a tough one, and exactly the situation I’m dealing with right now, hence my constant repeat of “maintenance hell”. Either you keep maintaining the non ORM parts, or you rewrite them to use the ORM. The second option might make more sense initially, but it’s a decision that’s solely based on what exactly those parts of your application are doing, and how valuable a complete rewrite would be in the long run.

   Try changing a key in a badly designed 2*10^8 rows MySQL table (without downtime) and you’ll understand where I’m coming from.

2. Non legacy parts of the application that absolutely need to directly talk to the data storage:

   Even trickier. ORMs are fancy tools, and they take care of almost everything, but sometimes they just get in the way or even are absolutely useless. The buzzword (buzzphrase really) is object-relational impedance mismatch, simply put it’s not technically possible for your ORM to do everything your relational database does, and for some of the stuff they do, there’s a significant performance penalty.

Comments

From the point of data integrity, constraints MUST be on the database, and SHOULD be on the application. What if your application is accessed from a web and a desktop applications, or a mobile app, or a webservice? – Luiz Damim

This is where adding an extra layer would be extremely helpful, and if we are talking about a web application I’d go with a REST API. An overly simplistic design for this would be:

The ORM would sit between the API and the data storages, and everything behind the API (including it) would be considered a single entity from the various applications.

Answer 2

This is actually a very difficult question to answer and I have found it to be a very controversial subject.

As Yannis Rizos pointed out in his answer, having the constraint logic in both the database and the ORM layer would seem to violate DRY, which "can lead to maintenance nightmares, poor factoring, and logical contradictions".

However, removing the constraint logic from the database and keeping it only at the ORM layer would not work if you have any of the following conditions:

1. Manual DB updates (they seem to happen at every company)

2. DB updates from another system that cannot always share the ORM constraint logic easily (ex/ a Perl script that performs routine tasks when the ORM layer is implemented in Hibernate and used by a Java application for day to day activity)

This would suggest that you only add the constraint logic to the DB and remove from your ORM layer in order to prevent a DRY violation. However, this can lead to cases where the application code cannot successfully capture the actual issue and relay to the user (though as a developer debugging the issue, you most likely can). This may not be acceptable for some projects.

Your last option is to automate the creation of the constraints in the ORM (and any other system) from the DB constraints (or, really...vice versa). Though you will eventually end up with two or more implementations of the constraints, it will not be a violation of DRY principle as described in "The Pragmatic Programmer" as they recommend the utilization of code generation to avoid DRY violations. Of course, it is not that simple because, for example, every change to a DB constraint may force a re-build and re-deploy of all of your applications which use it (not trivial to automate).

Really, it would have to be evaluated on a case by case basis. I can tell you that, to this point, I have been met with blank stares when I suggest that constraint logic not be repeated.

Answer 3

I would definitely add constraints to the database as my default option. This is because for an enterprise data is king and data quality is paramount. @Yannis Rizos brought the DRY principle to the discussion. Well another principle is Defense in Depth. For Data I would use this principle.

I have worked in real enterprises where the DB has data created 30 years ago. It was and still is accessed by COBOL application and now by a .Net application. In 10 years time it may be a vendor app, who knows. There was a merger and millions of rows of data were converted and migrated from the other company to this database using SQL. No ORM can do this. Bottom line is Data remain, applications change, way that Data is generated change. So why not reduce the chance of data corruption ?

Answer 4

I think you do both to some extent.

• The primary constraints should live in the ORM -- programming languages are lots more flexible, it is easier to test and easier to tweak when requirements change; no need to worry about DDL fixes at least. And you generally avoid hard to test data regression issues.

• Some very hard and fast constraints should also live in the database. I'm not talking about non-nullable names, for instance. I'm talking about things like referential integrity or requiring some absolutely crucial identifiers. Structural requirements so your code does not need to deal with "what if the Order has a non-existent product".

Answer 5

The database is IMO the only place where DRY can be violated, because if something bypasses your ORM and has bad data, that's it. Game Over. Having corrupt data is the killing blow.