I have a few small Rails web sites I run and use SQLite3 for my database. SQLite3 is the default Rails database provider and works great, but there are times when you outgrow or anticipate a need to change databases. Perhaps a change to MySQL or PostgreSQL is in order. This could be a tricky task, especially in a production environment with existing data. I had this scenario where I had an existing SQLite3 database and wanted to move it over to MySQL but I had a fairly large amount of data I needed to move as well.
Thanks to Twitter and @hassan on a great Rails plug-in recommendation from Adam Wiggins called YamlDb, which is hosted on GitHub. This handy little plug-in helps when you want to move from one database platform to another, say SQLite3 to MySQL.
Installation is just a simple little plug-in install, so on Rails 2.1
script/plugin install git://github.com/adamwiggins/yaml_db.git
The plug-in simply uses the database.yml file from the Rails project to determine which database to connect and dumps data to a file, db/data.yml.
Since I really needed to do this in a live environment I needed to see figure how to minimize downtime and maximize my success, which with YamlDb was pretty trivial. A few steps did the trick, based on the the instructions for the project on GitHub.
- Backed up my production.sqlite3 file to production.sqlite3.bak.
- Ran the command
rake db:dump(dumped all data and schema out to db/data.yml).
- Verified db/data.yml contained data instead of some 0K file.
- I had already had MySQL setup on another node on my Linode account with a new, empty database created. If this didn?t exist it would have to be created.
- Changed my config/database.yml file to point to the new database on MySQL.
- Tested application and all was working.
- Removed SQLite3 database from db directory in Rails application.
- Tested application again to make sure still works.
This handy little plug-in saved me a lot of time and effort and worked flawlessly.