Example of Where Rails Data Integrity Philosophy Fails

May 5, 2009     

Thanks to Obie I came across a great post about the Rails Devs for Data Integrity plugin. Chalk me up as another supporter of database constraints. I love Ruby as much as the next guy, but I’m not going to stick my head in the sand and write broken software just to satisfy some syntactical fetish. A lot of times I will just do the expeditious thing and follow the Rails way of doing things and stick data constraints in the app; it does the trick enough of the time (note to future self: don’t hold yourself accountable for having said this).

However I just came across an extremely simple case where it blows up spectacularly. If you have an AJAX action that can be rapidly fired, possibly to different app instances, your Ruby code will simply not be up to the task of maintaining data integrity. Consider the following action:

@rating = @current_user.ratings.find_or_create_by_film_id(@film.id)

Over the course of generating 200,000 ratings, my app ended up with 250 dups due to multiple ratings in rapid succession. Sure hacking some kind of locking mechanism onto the front-end would probably catch the vast majority of these cases, but a unique key in the database guarantees a solution to the problem.

Note how this problem has nothing to do with multiple apps talking to the database, future devs performing legacy maintainenance, tyrannical DBAs, or any of the other strawman reasons why we need DB constraints. In this case I added a unique key because it just works better than the alternatives.

Jerry says…
May 6, 2009 at 11:42PM

It’s great that Rails provides integrity checks within the app code, but this is no excuse for not using referential integrity constraints provided by the DB.

The Rails constraints are nice for explicitly checking business logic constraints (e.g validates_uniqueness_of :email) in the app code where it is visible to all. However, at the end of the day the job of insuring referential integrity clearly belongs to the DB and your DBA.