Bootstrapping your Database with Ordered Fixtures

Ruby on Rails provides a convenient way to ensure consistent database state for all unit tests and this is accomplished through Fixtures.

In the Rails Weenie forums technoweenie presented a technique that allows us to ‘bootstrap’ our development or production database using the same underlying mechanisms.

The way it works is first we place YAML files similar to the ones we use for testing under db/bootstrap (instead of test/fixtures). Then it’s a simple matter of executing:

rake db:bootstrap:load

Fixtures and Referential Integrity

The only problem with the above technique is when your tables have foreign key constraints (as they easily could if you use ActiveRecord Migrations with my own Migrations Constraints Plug-in).

When using fixtures for unit tests, we can explicitly specify the order of the fixtures in code. For example:

fixtures :foo, :bar_references_foo

The original technique by technoweenie allows us to specify a fixture order by an environment variable:

FIXTURES="foo bar_references_foo" rake db:bootstrap:load

While it works, it requires us to specify our fixture names and their order every time – not very DRY if you ask me.

Ordered Fixtures

Sandro Paganotti on RailsOnWave.com also realized this and presented his own version of rake db:bootstrap:load that attempts to address this.

The idea is to let us number our fixtures, similar to how we number our migrations, so that they can be loaded/unloaded in proper order according to our foreign key constraints.

Except, when I looked more closely at his code, I had my reservations.

First, and most glaring, is that he sorts the fixtures array, even if it’s passed in through the FIXTURES environment variable. I think this totally defeats the purpose of explicitly specifying the fixtures that way.

Second, and relatively minor, is that he hard-codes the format of the fixture file names so they have to be 2 digit numbers followed by an underscore, then the table name.

My rake db:bootstrap:load

So I set about to write my own, slightly more evolved version of Sandro Paganotti’s rake task.

namespace :db do
  desc "Loads a schema.rb file into the database and then loads the initial database fixtures."
  task :bootstrap => ['db:migrate', 'db:bootstrap:load']

  namespace :bootstrap do
    desc "Load initial database fixtures (in db/bootstrap/*.yml) into the current environment's database.  Load specific fixtures using FIXTURES=x,y"
    task :load => :environment do
      require 'active_record/fixtures'
      if ENV['FIXTURES']
        fixture_files = ENV['FIXTURES'].split(/\s*,\s*/).map {|file| [File.join(RAILS_ROOT, 'db', 'bootstrap', file), file]}
      else
        found_files = Dir.glob(File.join(RAILS_ROOT, 'db', 'bootstrap', '*.{yml,csv}'))
        numbered_files = found_files.select {|path| path =~ /\d+_[^.]+\.yml\Z/i }
        fixture_files = numbered_files.sort {|x,y| x[/(\d+)_.*\Z/,1].to_i <=> y[/(\d+)_.*\Z/,1].to_i }.
          map {|fixture_path| [fixture_path[/(.*)\.(yml|csv)\Z/i,1], fixture_path[/\d+_([^.]+)\.(yml|csv)\Z/i, 1]] }
        fixture_files += (found_files - numbered_files).map {|file| path = file[0, file.rindex('.')]; [path, File.basename(path)]}
        raise "No fixtures found matching \"db/bootstrap/*.{yml,csv}\"! Specify fixtures using \"FIXTURES=\"" if fixture_files.empty?
      end

      ActiveRecord::Base.establish_connection(RAILS_ENV.to_sym)
      connection = ActiveRecord::Base.connection
      fixtures = fixture_files.map do |fixture_path, table_name|
        Fixtures.new(connection, table_name, nil, fixture_path)
      end
      connection.transaction(Thread.current['open_transactions'] == 0) do
        fixtures.reverse.each { |fixture| fixture.delete_existing_fixtures }
        fixtures.each { |fixture| fixture.insert_fixtures }

        # Cap primary key sequences to max(pk).
        if connection.respond_to?(:reset_pk_sequence!)
          fixture_files.each do |fixture_path, table_name|
            connection.reset_pk_sequence!(table_name)
          end
        end
      end
    end
  end
end

While the code looks substantially changed, as far as behavior it really isn’t very different.

The first major change is that fixtures can now be numbered using any number of digits, followed by an underscore, followed by the table name and the file extension (.yml or .csv). Fixtures will be ordered properly, numerically, even if we various combinations of 1, 2 or 3-digit numerical prefixes (though, for consistency’s sake you’ll probably want to stick to 3 digits like migrations).

Note that any other *.yml or *.csv files found under db/bootstrap are loaded after the numbered fixtures – so this essentially remains backward compatible with the first two versions discussed earlier.

Finally, we can still specify an explicit set of fixtures to load via FIXTURES, except this time the code uses an array (instead of a Hash) and processes the fixtures according to the order given. This lets us bootstrap our database with selected tables only, while still letting us work around our foreign key constraints.

Of course, none of this will let you properly bootstrap your database if your tables have any circular foreign key constraints. That’s an entirely different problem (and database schema design ’smell’) that deserves its own discussion.

Try it out

To use the above code, simply copy it and save it as a .rake file under lib/tasks, say, lib/tasks/boostrap.rake. That’s all there is to it. Then simply create you fixtures under db/bootstrap as you would your unit test fixtures, and number them accordingly when needed!