Setting up a test database on a ruby on rails continuous integration server using SQL instead of schema.rb

by on June 18, 2009 · 4 comments

For developing our Ruby on Rails based web site, we usually take regular SQL dumps from our production servers (of course, anonymizing sensitive customer data along the way). Always having a fresh dump allows us to be on the safe side when writing database migrations. Having an up to date development database enables us to run our test suite as well; it’s just a matter of rake db:test:prepare to get our test database up-to-date. So far, so good.

Due to our use of some MySQL specific stuff (namely full-text-indices), which are not supported by the database agnostic schema.rb, we’re using config.active_record.schema_format = :sql. This makes rake db:test:prepare use db:test:clone_structure instead of db:test:load. Unfortunately, db:test:clone_structure always calls db:structure:dump, which tries to dump the development database structure to an SQL file. This is ok on a developer's box but we ran into trouble on our continuous integration server.

We don't want to setup a development environment on our CI server (which requires loading an anonymized production dump - quite a beast). It's enough for us to have a test environment there, using a checked out development_structure.sql (created on a development box and checked into version control) to create the test database. Unfortunately, the rake tasks provided by rails do not cover this scenario.

So, we simply came up with the following addition to rails' set of rake tasks:

namespace :db do
  namespace :test do
    desc "Recreate the test database from an existing db/development_structure.sql dump file"
    task :load_structure => [ "db:test:purge" ] do
      ActiveRecord::Base.connection.execute('SET foreign_key_checks = 0')
      IO.readlines("#{RAILS_ROOT}/db/#{RAILS_ENV}_structure.sql").join.split("\n\n").each do |table|

rake db:test:load_structure reads the checked out development_structure.sql and applies it to the test database (which it recreates first by defining db:test:purge as a pre-requisite). That's all we need to have a fully up-to-date test database on our CI server! No need to worry about creating a full development environment there just to be able to run migrations and dump the updated schema prior to setting up the test database.

Did you enjoy this article? Get new articles for free by email:


  1. Christian Lescuyer says

    Thanks for your article! I’m currently testing devver ( and was stumped by the database schema.db/development_structure.sql.

    I assume the .split(“nn”) is intended to split the SQL file at blank lines between table declarations. It should be two “backslash n” (“\n\n” if it displays correctly in the comment).


  2. says

    This looks great, though I may be making a dumb mistake. I’m running this command, to ensure the db is created before we run tests:

    RAILS_ENV=test rake db:test:load_structure test:units –trace

    load_structure works great, but then when test:units runs, it calls db:test:clone_structure which dies with mysql errors, because the tables already exist. Am I doing something wrong? Output looks like this:

    ** Invoke db:test:load_structure (first_time)
    ** Invoke db:test:purge (first_time)
    ** Invoke environment (first_time)
    ** Execute environment
    ** Execute db:test:purge
    ** Execute db:test:load_structure
    ** Invoke test:units (first_time)
    ** Invoke db:test:prepare (first_time)
    ** Invoke db:abort_if_pending_migrations (first_time)
    ** Invoke environment
    ** Execute db:abort_if_pending_migrations
    ** Execute db:test:prepare
    ** Invoke db:test:clone_structure (first_time)
    ** Invoke db:structure:dump (first_time)
    ** Invoke environment
    ** Execute db:structure:dump
    ** Invoke db:test:purge
    ** Execute db:test:clone_structure
    rake aborted!
    Mysql::Error: Table ‘adsense_stats’ already exists: CREATE TABLE `adsense_stats` (

  3. says

    We do not use rake test:units but run our tests directly from the command line (spec ./spec in our case).

    You could try to re-define the test:units task, removing the db:test:prepare dependency (just an idea, not tested):

    namespace :test do => :environment) do |t|
        t.libs << "test"
        t.pattern = 'test/unit/**/*_test.rb'
        t.verbose = true

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>