Saturday, May 26

I gave up on referential integrity

I did the unthinkable last week. I gave up on referential integrity with my latest project. It's simply too painful to support in Ruby on Rails.

When I started the project, the first plug-in I installed was Simon Harris's Foreign Key Migrations. This plug-in rocks!

So things were cooking along and I brought on another developer. One night I got home and went through my pre-coding ritual: Get the latest code from Subversion, migrate the database, rake the tests to make sure I'm starting with a solid foundation.

Two of the tests failed.

I was livid.

How dare my colleague commit broken code to the trunk!

We started a back and forth and we eventually discovered that our databases didn't match. He was missing foreign keys that I had, and vice versa. Not good.

I shot Simon and e-mail informing him of the assumed bug and asked if he'd seen it before and had any suggestions. It was news to him. He was incredibly receptive and helpful. He asked for as much information as I could give and I sent him my entire migration set along with all the plug-ins we were using.

Simon discovered that the Engines plug-in was breaking his foreign key plug-in.

As an aside, I had worked with engines in the early days and the experience was so traumatic I swore off them for life. But my colleague had a plug-in that required them and we needed it so I caved... and shot myself in the other foot.

I thought I had fixed the problem by hacking it so that the foreign key plug-in loaded before the engines plug-in. After a fresh migration all the tests passed. But it was temporary. A few dev cycles later we found another database inconsistency.

So in the wee hours of the morning at the second or third day of Railsconf I sat outside the ballroom and wrote unit tests with fixtures for every single foreign key in the database. That's when things really started going down the tubes.

The testing framework in Rails isn't clever enough to load the fixtures in the correct order to avoid violating foreign key constraints. Ditto for the unloading between each test. It was carnage.

So I'd had it. I was fed up. I made possibly the hardest and most painful architectural decision of my software development career... I gave up on referential integrity. I removed the foreign key plug-in from trunk and committed a migration to remove all the legacy constraints. Then, I wept.

One of my biggest pet peeves is fighting the tools. Yeah it's fun to hack and extend the tools with plug-ins and monkey patches but at the end of the day if the tools are working against you instead of with you, something has to give, and in this case I think the pros of productivity, support, and community with Rails outweighed the cons of having my cake and eating it to, so I gave up. I hope I don't live to regret it.


fxn said...

Did you find out why are the plugin and engines incompatible?

Dave said...

We have a test helper method called "all_fixtures" that loads the fixtures in the correct order. We then use this at the top of each test, rather than loading individual fixtures manually.

Rails doesn't make it easy, but I still feel it is worthwhile to use FK constraints. Every now and then we find a bug when the database complains...

Jason said...

Sounds like the safer decision would be to give up on Rails. Databases last longer than applications, and databases without RI is like a library of books all thrown in a pile when you go to build the next app that uses them.

Mike Pence said...

Yeah, ActiveRecord is only loosely associated with the relational model. You have to think of the database as an object persistence layer that serves the needs of the objects and not as something that is compatible with 3NF.

There are other Ruby-based database layers, including Og and RBatis, that may be more appropriate to your needs. Or, like me, you can learn to accept, with great reluctance, that Rails is not about following strong relational design, it is about using a database as a quick and easy object persistence mechanism.

Mike Pence
Boca Raton, FL

Ceesaxp said...

MySQLisms in ActiveRecord, that's what it is. Has it been built on Oracle or PostgreSQL first, it'd sure be a lot more FK-friendly...

siener said...

My screwdriver is broken, so I have no choice but to assemble this car without using any screws. I hope this hammer and nails will do the job.

Per Olesen said...

Nice tip about the foreign key migrations, didn't now about that. I simply do this in my migrations:

execute "alter table xxx add constraint fk_xxx_yyy foreign key (yyy_id) references yyy(id)"

Which has always worked for me.

With regard to loading fixtures in correct order, I've made a blog post about that here: Rails Fixture Tips.
It solves both the issue with loading and unloading order.

Ryan Platte said...

Fixtures are broken -- they introduce all kinds of needless assumptions about the environment, as you've seen, and they make tests run much slower. I feel they should be made way less prominent in the default Rails experience.

Alex Le said...

Check out ActiveFixture plugin. Fixtures are now loaded in correct order so you will be able to dump data through fixtures

hjkl said...
This comment has been removed by a blog administrator.