Rails 3 + rspec + postgres: How to run test suite against a database with foreign key constraints?

Question

I have a Rails 3 application which use PostgreSQL 9.1 as a database. We use RSpec and FactoryGirl for testing.

The data model has lots of models in 1-to-many or many-to-many relationships, and those constraints are encoded in the Rails models using has_many, belongs_to, has_many :through, et cetera. We have code which looks something like:

class User < ActiveRecord::Base
   attr_accessible :name
   has_many :phones
end

class Phone < ActiveRecord::Base
   attr_accessible :number, user_id
   belongs_to :user
end

The schema in PostgreSQL looks some thing like this

CREATE TABLE users (id integer, name VARCHAR(20));
CREATE TABLE phones (id integer, number VARCHAR(20), user_id integer);

However, I prefer to encode the data constraints in the database using foreign key constraints instead of only in the model. To do this, I created a Rails migration and added the foreign key constraints doing something like:

sql = "ALTER TABLE phones ADD CONSTRAINT phones_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT;"
ActiveRecord::Base.connection.execute(sql)

When I added foreign key constraints, and started the application in development mode, the database enforced the constraints, and I wasn't able to delete a user if it had any dependent phones. And this held true whether I was in the "psql" PostgreSQL console or the IRB Rails console.

However, when I tried writing a RSpec test to see if the foreign key was enforcing delete restrictions, the test failed and I was able to delete a user with dependent phones. Apparently, "rake db:test:prepare" does not prepare databases with foreign keys.

Is there a way I can run my RSpec test suite against a database which enforces foreign key constraints?

Answer 1

In Rails 4, it's easy:

In config/application.rb, set

module YourApp
  class Application < Rails::Application
    config.active_record.schema_format = :sql
  end
end

rake RAILS_ENV=test db:migrate will then dump your schema in SQL to db/structure.sql, and rake RAILS_ENV=test test:load will load it. Those tasks use pg_dump for Postgres, so they'll preserve every aspect of your schema.

Rails 3.2's test-database-related Rake tasks didn't respect config.active_record.schema_format, so I didn't even bother setting it. Instead, I overrode the tasks to use db:structure:load rather than db:schema:load. In lib/tasks/db.rb:

namespace :db do
  namespace :migrate do
    # We override the original of this task for consistency. Like the original, it doesn't seed.
    Rake::Task['db:migrate:reset'].clear
    task reset: [ 'db:drop', 'db:create', 'db:structure:load', 'db:migrate' ]
  end

  # This overrides the original, which does db:schema:load. The original doesn't migrate; this version does,
  # since, unlike schema.rb, *_structure.sql does not necessarily include all migrations.
  Rake::Task['db:setup'].clear
  task setup: [ 'test:ensure_environment_is_test', 'db:create', 'db:structure:load', 'db:migrate', 'db:seed' ]

  Rake::Task['db:reset'].clear
  task reset: [ 'test:ensure_environment_is_test', 'db:drop', 'db:setup' ]

  namespace :test do
    desc "rspec tasks depend on this task, so we override it to set up the database in the way that we want."
    Rake::Task['db:test:prepare'].clear
    task prepare: [ 'db:reset' ]
  end

end

namespace :test do
  task :ensure_environment_is_test do
    raise "Don't know how to db:setup RAILS_ENV=#{Rails.env}" unless Rails.env.test?
  end
end

Run rake db:structure:dump manually to create db/structure.sql, and do it again occasionally to roll up your migrations. You could modify the above to dump every time you migrate, like Rails 4's tasks do, but you might or might not find that inconsequential changes in the dump make that annoying.

Even more hacking was necessary in earlier versions of Rails; hope we don't have to go there.

Regardless of Rails version, it's worth reading the activerecord gem's lib/active_record/railties/database.rb.