Interconvert Database Formats in Rails with YamlDB

March 06, 2009

The database backends for both Zusammen and the Metamolecular Product Blog started out as SQLite databases during development. If you're not familiar with SQLite, it's a fast, single-file, zero-administration database system. And it works very well in some cases, such as when you're developing a Web application.

But deployment is another matter. In Rails, for example, the SQLite database file lives inside your project directory. This means that it takes some finagling to get it to work right with automated deployment systems such as Capistrano. Everything I read on this subject pointed to overcomplexity, so I decided to revert back to MySQL.

Surprisingly, converting from SQLite to MySQL format is not easy, as this question on StackOverflow demonstrates. Fortunately, buried in the discouraging responses to this question was a true gem: YamlDB from Adam at Heroku.

YamlDB dumps any Rails database into an intermediate YAML format, which you can then import back into any other database format. This bypasses the thorny problem of incompatible database dump file formats. Although others had reported some problems, in my case it worked perfectly the first time.