Dumping and restoring data with the YamlDB gem in Rails
What is YamlDB (yaml_db)?
YamlDb is a Rails plugin and a gem for dumping and restoring data in YAML format. It complements the database-independent schema format found in db/schema.rb. The data is saved into db/data.yml.
Some Real-world usages
- Backup data in YAML format (database-independent)
- Restore YAML data to virtually any database
- Switching from say SQLite to mySQL, or other databases
- Replicate production database data to development database with ease
You can find out how to do all of this in the following blogs:
http://www.railslodge.com/plugins/830-yaml-db
http://accidentaltechnologist.com/ruby/change-databases-in-rails-with-yamldb/
http://blog.heroku.com/archives/2007/11/23/yamldb_for_databaseindependent_data_dumps/
This plugin/gem is really good, the only thing I think it's lacking of is a command-line utility. Why? Because some times you only need to switch the database engine or replicate data one time, why would we have to install a plugin to a specific Rails application if we can have a command-line that works for all the Rails application we work in?
One of the main issues is that yaml_db works with rake tasks so although it's a gem you have to install the rake tasks in your Rails application manually.
That was the main reason I forked the yaml_db project and added it a command-line utility, the only think you'd need to do is to install my fork of the yaml_db gem, not the official one.
For instructions to install my fork click here.
Once installed just issue:
$ yaml_db -h
to see the help.
Usage:
yaml_db task environment
- task must be an existing rake task in the yaml_db plugin.
Dumping and restoring data with the command-line utility
yaml_db won't work if you're outside a Rails app folder, so...
$ cd ~/projects/my_dummy_rails_application
Replicate production database data to the development one
$ yaml_db data:dump production $ yaml_db data:load development
Dumping production schema to db/schema.rb and data to db/data.yml
$ yaml_db dump production
Loading db/schema.rb and db/data.yml to development database
$ yaml_db load #environment defaults to development
Adding a :noselect option to ActiveRecord (Rails 2.3.x)
Let's say you have an users table with the following columns
firstname, lastname, email, crypted_password and salt
so you get all the records with:
User.find(:all) User.all #it calls find internallywell, you should know that ActiveRecord creates one instance of the User model for every record it founds and that every column of such records is stored in a instance variable of the User model instance.
now, let's say your table stores 2,000 users, ActiveRecord would create 2,000 instances of the User model and let's say your table have 15 columns, so each of this 2,000 instances of the User model will have 15 instances variables and at least 30 dynamic methods (setters and getters), so, it'd be a good idea if we can reduce, at least, the quantity of instance variables and setters and getters, don't you think?
well, for me, the first approach would be to check if I need all the columns, once I realize that I am not, I'd change the call, let's say I don't need the salt column:
User.all(:select => "firstname, lastname, email, crypted_password")let's benchmark! I'll use a table with 130 users.the results are:Only :all (3528.6ms):all with :select (3439.1ms)3439.1ms is less than 3528.6ms!! yes, milliseconds, but what about a table with thousands and thousands of users?ok, now let's say you have a table with 20 or 30 columns, can you imagine?User.all(:select => "col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_n")all of this just because you don't want one or two columns???well, I made a patch to add a :noselect option:User.all(:noselect => "salt")and it'll result in the same SQL query thanUser.find(:all, :select => "firstname, lastname, email, crypted_password")which means:SELECT firstname, lastname, email, crypted_password FROM usersAnd here is the link to the patch:
https://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/4012-patch-add-noselect-option-to-activerecordfind