Change Databases in Rails with YamlDb

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.

  1. Backed up my production.sqlite3 file to production.sqlite3.bak.
  2. Ran the command rake db:dump (dumped all data and schema out to db/data.yml).
  3. Verified db/data.yml contained data instead of some 0K file.
  4. 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.
  5. Changed my config/database.yml file to point to the new database on MySQL.
  6. Ran rake db:load.
  7. Tested application and all was working.
  8. Removed SQLite3 database from db directory in Rails application.
  9. Tested application again to make sure still works. 

This handy little plug-in saved me a lot of time and effort and worked flawlessly.

 

  • http://www.icanhazbinky.com/ Kevin Tyll

    Thanks for highlighting this. I can see how this would also be very beneficial to move data around, say from production to a dev or staging environment in order to run scenarios or performance testing with real data. Very nice.

  • http://www.icanhazbinky.com Kevin Tyll

    Thanks for highlighting this. I can see how this would also be very beneficial to move data around, say from production to a dev or staging environment in order to run scenarios or performance testing with real data. Very nice.

  • http://www.accidentaltechnologist.com/ Rob Bazinet

    @Kevin – yes, those scenarios exactly or as I pointed out between DB platforms. It is a pretty simple plugin, could be enhanced to do so much more as well.

  • http://www.accidentaltechnologist.com Rob Bazinet

    @Kevin – yes, those scenarios exactly or as I pointed out between DB platforms. It is a pretty simple plugin, could be enhanced to do so much more as well.

  • http://redlinesoftware.com/ Marc Jeanson

    Great post!
    I followed your playbook here and everything worked out great. I went from sqlite3 to postgresql without issue.
    thanks again

  • http://redlinesoftware.com Marc Jeanson

    Great post!

    I followed your playbook here and everything worked out great. I went from sqlite3 to postgresql without issue.

    thanks again

  • http://www.accidentaltechnologist.com/ Rob Bazinet

    Hey @Marc, glad it worked out for you. I am happy to add this tool to my collection of utilities, it was very handy for me too.
    I guess it would work pretty well as a nice lightweight backup solution, kicked off as a cron job.

  • http://www.accidentaltechnologist.com Rob Bazinet

    Hey @Marc, glad it worked out for you. I am happy to add this tool to my collection of utilities, it was very handy for me too.

    I guess it would work pretty well as a nice lightweight backup solution, kicked off as a cron job.

  • Mark

    I am working with heroku.com to test my code and cannot seem to get the yaml_db process to work. I install the plugin as described above, but everytime I run rake db:data:dump, I get a "Don’t know how to build task ‘db:data:dump’ message. Any help would be GREATLY appreciated!

  • Mark

    I am working with heroku.com to test my code and cannot seem to get the yaml_db process to work. I install the plugin as described above, but everytime I run rake db:data:dump, I get a "Don’t know how to build task ‘db:data:dump’ message. Any help would be GREATLY appreciated!

  • http://www.accidentaltechnologist.com/ Rob Bazinet

    @Mark Did you try to run just rake db:dump instead of db:data:dump? This may help.

  • http://www.accidentaltechnologist.com Rob Bazinet

    @Mark Did you try to run just rake db:dump instead of db:data:dump? This may help.

  • Mark

    Yes I did Rob.
    It’s weird, I run the "script/plugin install git://github.com/adamwiggins/yaml_db.git" command, and as expected, after the first time it gives me a
    "already installed: yaml_db (git://github.com/adamwiggins/yaml_db.git). pass –force to reinstall" message.
    What’s weird is that "vendorpluginsyaml_db" directory has nothing in it, and all versions of the rake db:dump commands are unknown to rake. It seems like this yaml_db works so well for everyone else… what gives?? =)
    Thanks for your comment Rob!

  • Mark

    Yes I did Rob.

    It’s weird, I run the "script/plugin install git://github.com/adamwiggins/yaml_db.git" command, and as expected, after the first time it gives me a

    "already installed: yaml_db (git://github.com/adamwiggins/yaml_db.git). pass –force to reinstall" message.

    What’s weird is that "\vendor\plugins\yaml_db" directory has nothing in it, and all versions of the rake db:dump commands are unknown to rake. It seems like this yaml_db works so well for everyone else… what gives?? =)

    Thanks for your comment Rob!

  • http://www.accidentaltechnologist.com/ Rob Bazinet

    @Mark – I would just go to the vendor/plugins directory and do a rm -rf yaml_db directory and then run your script/plugin install …. again and see if the yaml_db directory is now populated.
    Give it a try…or go to GitHub and download the tarball and install it manually to the vendor/plugins folder, just use the yaml_db directory name, which you will have to rename because once the tarball is extracted the resulting directory name will be long.
    Hope this helps.

  • http://www.accidentaltechnologist.com Rob Bazinet

    @Mark – I would just go to the vendor/plugins directory and do a rm -rf yaml_db directory and then run your script/plugin install …. again and see if the yaml_db directory is now populated.

    Give it a try…or go to GitHub and download the tarball and install it manually to the vendor/plugins folder, just use the yaml_db directory name, which you will have to rename because once the tarball is extracted the resulting directory name will be long.

    Hope this helps.

  • http://manythingsblue.com/ taylor

    Thanks for the article. Exactly what I needed.
    I did have the same "vendorpluginsyaml_db" directory has nothing in it" problem that Mark had — I don’t have git installed, so I imagine that the install git:// link just doesn’t work in that case (though it doesn’t give an error, so I may be wrong).
    Regardless, downloading the tarball from GitHub and installing manually worked great.
    Thanks again!

  • http://manythingsblue.com taylor

    Thanks for the article. Exactly what I needed.

    I did have the same "\vendor\plugins\yaml_db" directory has nothing in it" problem that Mark had — I don’t have git installed, so I imagine that the install git:// link just doesn’t work in that case (though it doesn’t give an error, so I may be wrong).

    Regardless, downloading the tarball from GitHub and installing manually worked great.

    Thanks again!

  • http://www.2sw2r.com/vb/f62/ افلام

    Thanks for the article. Exactly what I needed

  • http://www.2sw2r.com/vb/f62/ افلام

    Thanks for the article. Exactly what I needed