On the last day of RailsConf 2006, I had the opportunity to speak on Rails migrations. The talk appeared to be well received and was well attended. My guess is around 100 people showed up, which is not bad for a Sunday morning session. The questions were good and several people came up to speak with me afterwards.
The main point of the talk was to show how well migrations dovetail with the near-constant flow of changes we experience during continuous development. The talk was kind of a mix of introductory material on migrations with more advanced topics such as transaction issues and monkey patching Rails for foreign key support. I also put together a handy collection of tips for more effective migration development and a corresponding list of gotchas as well. I built the slides so they would be a good reference after the conference. I hope that you find something useful in there. Enjoy.
Agile Databases with Migrations - RailsConf [pdf]
(7000+ downloads as of 2/1/07)
For those of you keeping score at home, I’m happy to announce that I will be giving a talk at RailsConf on Rails Migrations.
If you have specific topics or questions you’d like to have addressed besides the ones mentioned in the talk proposal, please leave a comment. Thanks!
Looking forward to meeting you in Chicago!
The default behavior in a Rails UI is to rely on numeric identifiers such that URLs (/myobject/kick/1) reference the database ID directly. The ID for myobject is 1.
This means that if you ever want to delete a record in your db, any URLs which reference that ID will get the Oops page. Now, granted if you are referencing a now deleted object somewhere, you’re going to have to deal with it somehow. Perhaps having the database IDs in the URL is a little close to the metal for some. Others like the simplicity and have no problem with it.
This practice, combined with some database defaults used in Rails, can lead to an issue however. MySQL migrations in Rails default to the InnoDB table type. This is probably all to the good, what with the support for transactions and all.
There is something that you may want to know about auto_increment id fields in InnoDB, however. On MySQL server startup, the InnoDB auto_increment value is grabbed from the database as the max value of the id column + 1. This value is stored in memory only and not persisted.
Taking a quick example…
If you have 10 objects in your database (ids 1-10) and you decide to delete them, your next ID will be 11. That is, unless, you restart your database server. An uncommon event for sure, but an eventual one indeed. When you restart the database, your auto_increment id is 1.
So, the /myobject/kick/1 now is not the same /myobject/kick/1 that was referenced before you decided to use those transactionally-safe InnoDB tables and restart your database.
About now, you might be wondering how to solve this and I think you have a couple of different options, if you really want to continue using auto_increment ids in this way.
You could take a look at Rick Olson’s acts_as_paranoid plugin and never actually delete your records. The plugin uses a deleted_at column to denote when something was “deleted” and it monkey patches Rails queries to ignore the records with a non-null deleted_at.
Otherwise, you’ll have to keep track of the real next auto_increment id value and manually set the value on the table.
ALTER TABLE myobjects AUTO_INCREMENT=11;
A more robust option would be to take ID generation out of the database altogether. This also has the benefit of deploying on a different database without worrying about the nuances of each database (or table type’s) behavior with respect to IDs.
Reference: How AUTO_INCREMENT Columns Work in InnoDB
