No, I’m not going to say that using MySQL with Ruby on Rails is a bad thing. It works fine. But I do think that using the mysql command line tool to modify your Rails databases is a no-no. The reason is simple, Rails does a lot of work to abstract database details away, so that programmers don’t have to know anything about them. We should respect and embrace that abstraction, not step around it.

So if you’re not supposed to use mysql from the command line, how do you make changes to your models from outside of your application? The answer is script/console. Using console is even better than using mysql, because it a) uses Ruby, a language we already know and love, and b) exercises the same bug-free, well tested code that your Rails application does. Of special use here are ActiveRecord’s validations, associations, and before_* and after_* callbacks.

As a brief example, let’s say you’re writing the ubiquitous blog. You need to delete a user on the blog, but you haven’t implemeted the web framework yet. You open up your trusty mysql command line client and do something like this:

mysql> DELETE FROM users WHERE username = 'foobar';
Query OK, 1 row affected (0.08 sec)


And you think to yourself, “Job well done” as you wipe your hands and pat yourself on the back. But many of you are probably asking, “What happened to all of foobar’s posts?” They’re still there. I guess you have to delete them too:

mysql> DELETE FROM posts WHERE user_id = 666;
Query OK, 1 row affected (0.11 sec)


It’s a good thing you happened to remember foobar’s user_id huh? So that should do it… right? But what about foobar’s user image? Now you have to go into the filesystem and delete his user image.

$ rm RAILS_ROOT/public/images/users/foobar.jpg


Whew. There, that’s all of it. Finally. Or is it? What if your colleague added the concept of “friendships” between users, and now you have a bunch of orphaned table rows? If this doesn’t seem error prone (and tedious!) already, then you’re a lot braver than I am. Especially since if you hadn’t tried so hard to work around Rails’s database abstraction, all you would have had to do is:

>> User.find_by_username("foobar").destroy


That’s it. One simple line. Your well-designed models will take care of the rest. In case you’re new to Rails, and wondering just what this magical User model might look like in Rails, here it is:

class User < ActiveRecord::Base
  after_destroy :delete_user_image
  has_many :posts, :dependent => :destroy

  private

  def delete_user_image
    FileUtils.rm(RAILS_ROOT + "/public/images/users/#{username}.jpg")
  end
end

Yes, if you’re a true wiz, you could write all of that into your database, but you’re trying to write a Ruby on Rails application, remember? Besides, what happens when your CTO decides that your application will now use DatabaseX, since it’s the hottest thing since the sun? If that frightening occurance isn’t enough, consider that DHH, the creator of Rails, frowns on such database trickery.

I guess the real point of what I’m trying to say is, Rails uses the database so you don’t have to. Try to pretend there is no database, and use script/console instead. You’ll save yourself a lot of grief in the end.