Accidental Technologist

Musings about Entrepreneurship, Technology and Software Development

  • Home
  • About
  • Still River Software
  • Privacy Policy

Powered by Genesis

You are here: Home / Ruby / Change Databases in Rails with YamlDb

Change Databases in Rails with YamlDb

March 10, 2009 by Rob Bazinet

Tweet

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.

 

Technorati Tags: Ruby on Rails,SQLite3,MySQL,YamlDb

Share this:

  • LinkedIn
  • Twitter
  • Facebook
  • Email
  • More
  • Pinterest
  • Tumblr
  • Pocket
  • Reddit

Related

Filed Under: Ruby

Care about your privacy? I do and use Fathom Analytics on this site.

Fathom Analytics

Comments

  1. Kevin Tyll says

    March 11, 2009 at 9:17 am

    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.

  2. Kevin Tyll says

    March 11, 2009 at 9:17 am

    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.

  3. Rob Bazinet says

    March 11, 2009 at 10:47 am

    @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.

  4. Rob Bazinet says

    March 11, 2009 at 10:47 am

    @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.

  5. Marc Jeanson says

    March 13, 2009 at 3:44 pm

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

  6. Marc Jeanson says

    March 13, 2009 at 3:44 pm

    Great post!

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

    thanks again

  7. Rob Bazinet says

    March 13, 2009 at 4:04 pm

    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.

  8. Rob Bazinet says

    March 13, 2009 at 4:04 pm

    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.

  9. Mark says

    May 12, 2009 at 3:37 am

    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!

  10. Mark says

    May 12, 2009 at 3:37 am

    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!

  11. Rob Bazinet says

    May 13, 2009 at 9:28 am

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

  12. Rob Bazinet says

    May 13, 2009 at 9:28 am

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

  13. Mark says

    May 14, 2009 at 9:05 pm

    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!

  14. Mark says

    May 14, 2009 at 9:05 pm

    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!

  15. Rob Bazinet says

    May 14, 2009 at 11:23 pm

    @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.

  16. Rob Bazinet says

    May 14, 2009 at 11:23 pm

    @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.

  17. taylor says

    August 11, 2009 at 8:57 pm

    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!

  18. taylor says

    August 11, 2009 at 8:57 pm

    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!

  19. ????? says

    August 15, 2009 at 11:26 pm

    Thanks for the article. Exactly what I needed

  20. ????? says

    August 15, 2009 at 11:26 pm

    Thanks for the article. Exactly what I needed

Recent Posts

  • How to Fix Rails Flash Rendering When Using Hotwire
  • Hotwire Fix for CORS Error when using Omniauth
  • Fix Installation of Ruby using rbenv on macOS Big Sur
  • RailsConf 2021 and the Future of Conferences
  • Fixing Out of Diskspace Errors on Amazon EC2

Categories

Services I Love

HatchBox - Easy Rails Deploys Fathom Analytics
Follow @rbazinet

Rob Bazinet
@rbazinet

  • Exactly this…. https://t.co/yWj7fZ01HR
    about 17 hours ago
  • https://t.co/EmagdpLoNv "Introducing GitHub Copilot X · GitHub"
    about 1 day ago
  • RIP Gordon Moore: https://t.co/c5J9LaHrj8
    about 1 day ago
  • Our daughter works as a teaching assistant at a local K-8 public school. It’s become apparent that students today r… https://t.co/p2t912GVyc
    about 2 days ago
  • Working to wrap up my current consulting gig by mid-April. I will be looking for the next thing soon. If anyone has… https://t.co/sg3cSV9yqM
    about 3 days ago
  • RSS - Posts
  • RSS - Comments
Find me on Mastodon