When to add validations to database

Rails
1

Normally all my validation is done in my models.

Michael Hart in his rails tutorial explains why the uniqueness
validation should also be done at the database layer (
http://ruby.railstutorial.org/chapters/modeling-and-viewing-users-one#sec:the_caveat).

I am curious if people have experienced other validations that should
also be done at the database layer?

thanks

2

Adrian C. <caceresad@…> writes:

Normally all my validation is done in my models.

Michael Hart in his rails tutorial explains why the uniqueness
validation should also be done at the database layer (
http://ruby.railstutorial.org/chapters/modeling-and-viewing-users-
one#sec:the_caveat).

I am curious if people have experienced other validations that should
also be done at the database layer?

thanks

Models are the gatekeepers to your database. So long as you are in an
environment where you fully control access to the underlying database,
there
should be no need to add any database validations (although I know a few
DB
administrators who choke on such ideas).

3

On Jul 4, 2011, at 5:24 PM, Andrew S. wrote:

So long as you are in an
environment where you fully control access to the underlying database…

And where does one find such an environment in the real world???

–
Scott R.
[email protected]
http://www.elevated-dev.com/
(303) 722-0567 voice

4

On Mon, Jul 4, 2011 at 5:04 PM, Scott R. [email protected]
wrote:

So long as you are in an
environment where you fully control access to the underlying database…

And where does one find such an environment in the real world???

I was going to say “most”, but actually – I don’t currently maintain
any applications where that isn’t the case. Obviously YMMV.

–
Hassan S. ------------------------ [email protected]

twitter: @hassan

5

On Jul 4, 2011, at 6:47 PM, Hassan S. wrote:

I was going to say “most”, but actually – I don’t currently maintain
any applications where that isn’t the case. Obviously YMMV.

Really? ALL access through RoR and the models? NEVER any
direct access for maintenance? Not ever???

–
Scott R.
[email protected]
http://www.elevated-dev.com/
(303) 722-0567 voice

6

Models are the gatekeepers to your database. So long as you are in an
environment where you fully control access to the underlying database, there
should be no need to add any database validations (although I know a few DB
administrators who choke on such ideas).

I don’t think this is true even if you had full control over the
database. in practice, there are some migrations that can’t be written
without hitting the database directly, thus bypassing some models.

John F.
Principal Consultant, BitsBuilder
LI: http://www.linkedin.com/in/johnxf
SO: User John Feminella - Stack Overflow

7

On Mon, Jul 4, 2011 at 7:17 PM, Scott R. [email protected]
wrote:

Really? ALL access through RoR and the models? NEVER any direct
access for maintenance? Not ever???

That wasn’t the stated premise – do I “fully control access to the
underlying database”? Yes. I am the only one accessing the DB w/
write privileges, so I’m fully empowered to shoot myself in the foot
if I choose :slight_smile:

And yes, sometimes in dev mode I’ll dump the DB, test drive an idea
in raw SQL, restore, rewrite that idea as a migration or rake task and
test, restore, etc.

But that’s not the same as having a non-Rails app without validation
constraints accessing the same DB (which I believe was Andrew’s
point - apologies if I’m misinterpreting).

–
Hassan S. ------------------------ [email protected]

twitter: @hassan

8

In a large scale environment, there may be times when someone accesses
the
database directly, bypassing Rails.

This may include the DBAs, for instance, or even the maintainers of the
system.

This may happen in trying to resolve a production problem in as short a
time
as possible, and it seems like a good idea at the time to access the
database directly.

Fred

9

Hassan S. <hassan.schroeder@…> writes:

On Mon, Jul 4, 2011 at 7:17 PM, Scott R. <scott_ribe@…> wrote:

Really? ALL access through RoR and the models? NEVER any
direct access for maintenance? Not ever???
But that’s not the same as having a non-Rails app without validation
constraints accessing the same DB (which I believe was Andrew’s
point - apologies if I’m misinterpreting).

I seem to have started a religious war. Of course there are many
environments
in which is may be necessary (even desirable) for direct access to the
database to be granted to clean data, perform transformations, or fix
issues.
Indeed, there are also many cases where Rails is access an existing
system, or
utilising a database common to many applications.

If you are in on eof these environments, then DB level validations are
probably a good idea since Rails expects the database to be structured
in a
certain fashion. Other applicaitons may mess this up and cause your
Rails app
to exhibit weird behaviour, or stop working altogether. Hell, even
within
Rails you can deliberately bypass model validations.

At the end of the day, be mindful of how your app will behave if you
suddenly
can’t save/update a record because the database is enforcing validations
without Rails knowing about it. I have been through this pain and now
encourage other’s scripts to access services using REST - at least then
I can
perform Ruby magic on the way in or out of the DB.

10

Good point.

I was only talking about ad hoc access not regular access from outside
of
Rails. That doesn’t seem possible – or at least shouldn’t be.

Fred

11

Andrew S. wrote in post #1009048:

Models are the gatekeepers to your database. So long as you are in an
environment where you fully control access to the underlying database,
there
should be no need to add any database validations (although I know a few
DB
administrators who choke on such ideas).

This is an oversimplification of a larger problem. As the OP mentioned
the uniqueness validation cannot possibly be guaranteed at the model
level. A race condition would cause the model level uniqueness
validation to fail. The constraint can only be guaranteed by the
underlying database.

I also like to use other database level constraints such as nullability
and uniqueness constrains for join table foreign keys.

Bottom line is that the benefits of having them outweigh the “object
purity” of model layer only validation.

12

Pretty much any custom validation querying the database is a suspect.

Milan D.
rubylove.info

13

Actually, we really do some validation in database layer. Our RoR
software manage huge tables, and add some stuffs like unique indexes
and foreign keys really helps to improve performance.