Speed up test db transactions with optimized-ish SQL queries!

fast_spec_helpers.rb

def clean!(*classes)
  execute!(classes.map { |klass| "DELETE FROM #{klass.table_name}" }.join('; '))
end

def create!(klass, keys, values)
  query = make_mass_query(klass, keys, values)
  execute!(query).map { |row| klass.new(row) }
end

private

def execute!(query)
  ActiveRecord::Base.connection.execute(query)
end

def make_mass_query(klass, keys, values)
  "WITH rows AS (INSERT into #{klass.table_name} (#{keys.join(', ')})
  VALUES #{values.map { |*vals| "(#{escape_values(vals)})"}.join(', ')} RETURNING *) SELECT * FROM rows"
end

def escape_values(vals)
  vals.map do |val|
    if val.is_a?(Array)
      escape_values(val)
    elsif val.is_a?(Fixnum)
      val
    else
      "'#{val}'"
    end
  end.join(', ')
end

I added a few helper methods to the spec_helper file to replace the standard rails creation convention of Author.create with something a little faster. When creating dozens of records, the biggest slow down in our test speed is the constant querying of the db before and after each test. We can avoid these slow downs by being explicit in our db calls and by grouping like calls together into single sql statements.

clean! Author, Paper
#runs -> 'DELETE FROM Author; DELETE FROM Paper;'
#=> nil

clean! takes an arbitrary number of database table backed classes and deletes all records from them. Call this method at the start of your test for all tables you will be interacting with to ensure a clean test run.

create! Author, %w(name id), %w(john sally).map.with_index(1)
#runs -> "WITH rows AS (INSERT into authors (name, id) VALUES ('john', 1), ('sally', 2), ('billy', 3) RETURNING *) SELECT * FROM rows"
#=> [#<Author id: 1, name: "john">,
     #<Author id: 2, name: "sally">]

create! takes an active record class, an array of attributes/column names, and an array of arrays full of values that map to those columns. Using this method will create a single sql to create all the background data you may need for a test setup. This method will also return the results back to you as an array of active record objects. Comes in handy when you need to use those objects that you just created.

A friend and former colleague of mine has a gem, activerecord-import, that might help, especially for larger sets of data.