Are there tools for Rails to validate referential integrity of the database?

Question

Applications have bugs or get bugs when updated, some hidden that they get detected months or years later, producing orphaned records, keys pointing nowhere etc. even with proper test suites.

Allthough Rails doesn't enforce referential integrity on the database level - and for some good reasons discussed elsewhere it will stay like that - it would still be nice to have tools that can check if the database is in a consistent state.

Since the models describe what 'should be', wouldn't it be possible that an offline tool validates the integrity of all the data. It could be run regularly, before backing up data or just for the sake of the developers good sleep.

Is there anything like this?

Answer 1

How about using the DBMS to enforce RI? You don't need Rails to do that for you.

Answer 2

I don't know of such a tool. At least you are aware of the dangers of referential integrity hazards. So why make yourself suffer? Just use foreign key references in the first place, as dportas suggested.

To use it in a migration, add something like this:

execute('ALTER TABLE users ADD FOREIGN KEY (language_id) REFERENCES languages(id)')

to make the language_id column of users reference a valid row in the languages table.

Depending on your DBMS this will even automatically create an index for you. There are also plugins for rails (check out pg_on_rails) which define easy to use alias functions for those tasks.

Checking the integrity only on the backup file is pointless, as the error has already occured then, and your data may be messed up already. (I've been there) On the other hand, when using foreign key contraints as stated above, every operation which will mess up the integrity will fail.

Think of it as going to the dentist when you feel pain (=having surgery) vs. brushing your teeth ONCE with a magical tooth paste that guarantees that your teeth will be fine for the rest of your life.

Another thing to consider: An error in your application will be much more easier to locate, because and exception will be raised at the code which tries to insert the corrupt data.

So please, use foreign key contraints. You can easily add those statements to your existing database.