The maze book for programmers!

Algorithms, circle mazes, hex grids, masking, weaving, braiding, 3D and 4D grids, spheres, and more!

DRM-Free Ebook

The Buckblog

assorted ramblings by Jamis Buck

Two tips for working with databases in Rails

13 December 2005 — 2-minute read

First tip: I’ve found recently that if I have a boolean field in the database that is being used to mark whether some event occurred (referrals.pending, or feeds.subscribed) it is often more effective to make the field a datetime and record the moment that the event occurred. Then, a NULL can be used to indicate that the event has not yet occurred. Thus, you have referrals.applied_at with a method on Referral like this:

def pending?

This gives you the capability down the road to not only report whether the event occurred, but how frequently over various periods of time.

The second tip is handy when you’re working on a migration. I find that the process (for me) works like this:

  • Create the migration and run it.
  • Discover I forgot something.
  • Migrate down to the previous schema version.
  • Change the migration and run it again.

(Repeat as necessary.) However, being the imperfect programmer that I am, I find that I often implement the #down method incorrectly, forgetting to drop a table or remove a column. Thus, when I try to run the migration again, it fails saying that the table/column already exists.

Using script/console and ActiveRecord::Schema, it becomes a cinch to clean up the artifacts:

ActiveRecord::Schema.define do
  drop_table :foos
  remove_column :bars, :blitz
  remove_column :bars, :things

And if you have the verbose_migrations plugin installed, you can also get some very useful output describing what was done and how long it all took.

Reader Comments

Happy 1.0, Jamis. Please consider naming your first tip as a pattern so the rest of us can easily communicate about it.
How about, "Datetime Booleans"? (Any other suggestions?)
Big suggestion: use dbi for data munging, not ActiveRecord. Using AR binds you to *current* state of AR, so major changes wreck havoc on migrations.
Brian, I'm not sure I follow what you mean about "current state of AR". I use AR for data munging all the time, and have never had any problems with it. I find it much more convenient than (for instance) dbi.
Wow, I thought that migration thing only ever happened to me. :) Thanks for the great tip!
I generally just do a mysqldump pre migration, that way I can just reload it if something borks. Both dumping and reloading are one line shell commands.