when to add validations to database

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

Adrian Caceres <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).

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.

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.

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

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

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

Hassan Schroeder <hassan.schroeder@...> writes:

> 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

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

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.

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

Milan Dobrota

rubylove.info

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.