Note that this code only works with PostgreSQL, but it wouldn’t be too hard to adapt our approach for other relational databases.

database_state_loader.rb

# Add to `spec/support/database_state_loader.rb`

class DatabaseStateLoader
  class EnvironmentError < RuntimeError; end

  def self.load(path)
    new(path).load
  end

  def initialize(path)
    @path = path
  end

  def load
    # Just double check we're in the right environment
    raise EnvironmentError.new("This can only be run in development") if not Rails.env.development?

    puts "Loading #{@path}"
    data = JSON.parse(File.read(@path))

    created_database_config = create_and_switch_to_temporary_database
    insert_data(data)

    username = created_database_config['username']
    password = created_database_config['password']
    host = created_database_config['host'] || "127.0.0.1"
    port = created_database_config['port'] || "5432"
    name = created_database_config['database']
    database_url = "postgres://#{username}:#{password}@#{host}:#{port}/#{name}"

    puts ""
    puts "State data was succesfully inserted into database: #{name} 👍"
    puts ""
    puts "You can startup a console to access this database by running:"
    puts ""
    puts "    DATABASE_URL=#{database_url} DISABLE_SPRING=1 rails console"
    puts ""
    puts "When you're done, you can remove the database by running:"
    puts ""
    puts "    dropdb #{name}"
    puts ""
  end

  private

  def create_and_switch_to_temporary_database
    # Parse and load database.yml
    database_yml_path = Rails.root.join("config", "database.yml")
    parsed_database_yml = ERB.new(database_yml_path.read).result
    database_config = YAML.load(parsed_database_yml)
    test_database_config = database_config['test']

    # Create a new database for this state
    puts "Creating state database..."
    state_database_name = "#{test_database_config['database']}_state_#{Time.now.to_i}"
    ActiveRecord::Base.connection.create_database(state_database_name)

    # Connect to the jdatabase and recreate structure
    puts "Connecting `#{state_database_name}`"
    state_database_config = test_database_config.merge("database" => state_database_name, "pool" => 30)
    ActiveRecord::Base.establish_connection state_database_config
    ActiveRecord::Base.connection.execute(Rails.root.join("db/structure.sql").read)

    state_database_config
  end

  def insert_data(data)
    puts "Inserting data into database..."

    ActiveRecord::Base.transaction do
      data.each do |(table, rows)|
        rows.each do |row|
        columns = []
        values = []

        row.each do |(key, value)|
          columns << key
          values << begin
                      case value
                      when nil
                        "null"
                      when Hash
                        case connection.columns(table).find { |column| column.name == key }.sql_type
                        when "hstore"
                          connection.quote connection.lookup_cast_type("hstore").serialize(value)
                        when "json"
                          connection.quote connection.lookup_cast_type("json").serialize(value)
                        else
                          raise "Not sure how to insert: (#{key}: #{value.inspect})"
                        end
                      when Array
                        if value.empty?
                          "null"
                        else
                          "(#{value.map { |v| connection.quote(v) }.join(", ")})"
                        end
                      else
                        connection.quote value
                      end
          end
        end

        connection.execute(<<~SQL)
          INSERT INTO #{quote_table_name(table)} (#{columns.map { |column| quote_column_name(column) }.join(", ")})
          VALUES (#{values.join(", ")})
        SQL
      end
      end
    end
  end

  delegate :connection, to: "ActiveRecord::Base"
  delegate :quote_table_name, :quote_column_name, to: :connection
end

database_state_saver.rb

# Add to `spec/support/database_state_saver.rb`

class DatabaseStateSaver
  def initialize(path)
    @path = path
  end

  def save(example)
    path = path_to_state_file(example)

    FileUtils.mkdir_p(File.dirname(path))
    File.write(path, Yajl::Encoder.encode(generate_database_state, pretty: true) + "\n")

    puts %{\033[0;33mDatabase state saved to: #{path}\033[0m}
    puts %{\033[0;33mTo load the state locally: ./script/load_test_database_state "#{path}"\033[0m}
  end

  private

  def path_to_state_file(example)
    path = File.expand_path(example.file_path, Rails.root.to_s)
    path = path.sub(%r{\A#{Regexp.escape(Rails.root.to_s)}/*}, "")
    path = path.sub(%r{\.rb\Z}, "")
    path << "_line_#{example.metadata[:line_number]}.json"

    File.join(@path, path)
  end

  def generate_database_state
    Rails.application.eager_load!

    {}.tap do |dump|
      ActiveRecord::Base.descendants.each do |klass|
        table_name = klass.table_name
        next if table_name == ActiveRecord::Migrator.schema_migrations_table_name

        dump[table_name] = klass.all.map { |record| record.attributes }
      end
    end
  end
end

RSpec.configure do |config|
  state_saver = DatabaseStateSaver.new(Rails.root.join("tmp", "state", "database"))

  config.around do |example|
    example.call
    state_saver.save(example) if example.exception.present? && (ENV['CI'] || ENV['DATABASE_STATE_SAVER'])
  end
end

load_test_database_state

#!/usr/bin/env ruby

# Add to `script/load_test_database_state`

require './config/environment'
require Rails.root.join("spec", "support", "database_state_loader")

file = ARGV[0]
if file.blank?
  puts "Missing file to load. Specify the file like this:"
  puts ""
  puts "./script/load_test_database_state [path-to-file-here]"
  exit 1
end

DatabaseStateLoader.load(file)