Best way to export a database table to a YAML file?


Best way to export a database table to a YAML file?

I have some data in my development database that I would like to utilize as fixtures in my test environment.  What is the best way in Rails 2.x to export a database table to a YAML fixture?


Solution 1:

There is a rake task for this. You can specify RAILS_ENV if needed; the default is the development environment:

rake db:fixtures:dump
    # Create YAML test fixtures from data in an existing database.

Solution 2:

I have been using YamlDb to save the state of my database.

Install it with the following command:

script/plugin install git:// 

Use the rake task to dump the contents of Rails database to db/data.yml

rake db:data:dump

Use the rake task to load the contents of db/data.yml into the database

rake db:data:load

This is the creators homepage:

Solution 3:

This plugin will add the functionality you want. It was extracted from ActiveRecord so no longer comes by default.

script/plugin install

Then run:

rake db:fixtures:dump MODEL=ModelName

Solution 4:

For Rails 3, if you want to dump yaml from the DB and use it as a fixture, I use this code:

module DbToFixture

  TEMP_FIXTURE_PATH = Rails.root.join("test", "new_fixtures")

  def fixturize(model)
    Dir.mkdir(TEMP_FIXTURE_PATH) unless File.exists?(TEMP_FIXTURE_PATH)
    fname = model.table_name
    file_path = TEMP_FIXTURE_PATH.join(fname), 'w') do |f|
      model.all.each do |m|


I just run it from the console with

require './lib/db_to_fixture'
include DbToFixture
fixturize ModelName

I have not been able to get ar_fixtures to work with Rails 3 (haven’t tried very hard though). Yaml db is great for dumping and saving the db, but its format is not compatible with fixtures.

Solution 5:

Iron Fixture Extractor was built for exactly this purpose. It’s particularly good for situations where you want to use different fixture sets for different testing scenarios (rather than having all fixtures for all tests). It provides functionality for extracting, loading, rebuilding fixtures, truncating tables, or snagging particular hashes from your fixture yaml files.

Solution 6:

rake db:fixtures:dump

has been changed to

rake db:extract_fixtures

Solution 7:

Very simple gem will create yaml fixtures from existing database…

Works with Rails 4.

Solution 8:

Here’s a rake task that will do exactly that (tested in Rails 3.2.8):

namespace :db do
    task :extract_fixtures => :environment do
      sql  = 'SELECT * FROM "%s"'
      skip_tables = ["schema_migrations"]
      if (not ENV['TABLES'])
        tables = ActiveRecord::Base.connection.tables - skip_tables
        tables = ENV['TABLES'].split(/, */)
      if (not ENV['OUTPUT_DIR'])
        output_dir = ENV['OUTPUT_DIR'].sub(/\/$/, '')
      (tables).each do |table_name|
        i = "000""#{output_dir}/#{table_name}.yml", 'w') do |file|
          data = ActiveRecord::Base.connection.select_all(sql % table_name.upcase)
          file.write data.inject({}) { |hash, record|
            hash["#{table_name}_#{i.succ!}"] = record
          puts "wrote #{table_name} to #{output_dir}/"


Note: I had to make a few changes to the blog code to make it more cross-database compatible and work in Rails 3.2

Solution 9:

 > rails c
 irb> puts Modelname.all.to_yaml

then copy & paste it in file and edit it to match what fixtures expect.

It’s manual labor but if you need this just once probably the fastest way.

Solution 10:

For dumping for rspec/cucumber test fixtures in Rails 3 this is the best answer I’ve found:
What is the standard way to dump db to yml fixtures in rails?