Changing DB engine – Rails way

Ruby on Rails is quite DB independent. At least as long You don’t use specific SQL dialect. So changing DB engine shouldn’t be a problem, right? Yes, as long we don’t talk about data itself. I have changed engine in one application – from MySQL to sqlite.

Why such change? Application will be distributed and run locally, so sqlite is much less troublesome to install and maintain. And advanced MySQL features was not used, not counting some simple functions, which can be replaced with ruby code and new fields in model (like MySQLs month() which does not have counterpart in much simpler sqlite dialect). But prototype application is used by me and have some data (it is some kind of financial manager – codename YAFM : Yet Another Financial Manager), and I didn’t want to loose those data. So I needed some clever way of migrate. Simple Google query doesn’t reveal solution (migrations in other direction – no problem), so I wanted to tinker with output from mysqldump. But then I found other solution.

First, in Rails Recipes (Pragmatic Programmers), great book with Rails tips&tricks I found way for dumping data from live DB to fixtures! You don’t have buy this book (but I highly recommend this book, it is real time saver) since You can download this as rake task from Pragmatic Programmer website. Put it in lib/tasks/ directory and leave name (or at least extension) unchanged. Now You have in this application new rake task called extract_fixtures. I have run it for production data (rake extract_fixtures RAILS_ENV="production"), changed database.yml for development environment (adapter: sqlite3 database: db/data.db), created DB structure (rake db:migrate) and loaded all data with rake db:fixtures:load. Now I can start with work to replace month() with Rails code and new fields in DB. Piece of cake.