Rake db:test:prepare task deleting data in development database

Question

Using a simple Rails sqlite3 configuration example in my config/database.yml for a Rails 3.2.6 app, I used to reset my development database, re-seed it, and prepare my test database simply by performing:

$ rake db:reset
$ rake db:test:prepare 

After looking at this blog entry about testing a Rails application with Travis CI on different database engines, I thought I'd give it a try, so I installed mysql and postgresql using Homebrew (I'm on OSX Snow Leopard), set them up as per the brew info instructions. I installed the relevant gems, and configured the database and Travis files as follows:

Gemfile

# ...
group :development, :test do
  # ...
  gem 'sqlite3', '1.3.6'
end

group :test do
  # ...
  # Test mysql on Travis CI
  gem 'mysql2', '0.3.11'
end

group :test, :production do
  # ...
  # Test postgres on Travis CI and deploy on Heroku
  gem 'pg', '0.13.2'
end

config/database.yml

sqlite: &sqlite
  adapter: sqlite3
  database: db/<%= Rails.env %>.sqlite3

mysql: &mysql
  adapter: mysql2
  username: root
  password:
  database: my_app_<%= Rails.env %>

postgresql: &postgresql
  adapter: postgresql
  username: postgres
  password:
  database: my_app_<%= Rails.env %>
  min_messages: ERROR

defaults: &defaults
  pool: 5
  timeout: 5000
  host: localhost
  <<: *<%= ENV['DB'] || "sqlite" %>

development:
  <<: *defaults

test: &test
  <<: *defaults

production:
  <<: *defaults

cucumber:
  <<: *test

.travis.yml

language: ruby
rvm:
  - 1.9.2
  - 1.9.3
env:
  - DB=sqlite
  - DB=mysql
  - DB=postgresql
script:
  - RAILS_ENV=test bundle exec rake --trace db:migrate
  - bundle exec rake db:test:prepare
  - bundle exec rspec spec/
before_script:
  - mysql -e 'create database my_app_test'
  - psql -c 'create database my_app_test' -U postgres
bundler_args: --binstubs=./bundler_stubs

Now, though, when I run rake db:reset, I get a Couldn't drop db/development.sqlite3 error message before the development database is successfully created. So, it seems that there are now multiple calls being made to drop the same database(?). The traced output looks like:

$ rake db:reset --trace
** Invoke db:reset (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:reset
** Invoke db:drop (first_time)
** Invoke db:load_config (first_time)
** Invoke rails_env (first_time)
** Execute rails_env
** Execute db:load_config
** Execute db:drop
Couldn't drop db/development.sqlite3 : #<Errno::ENOENT: No such file or directory - my_app/db/development.sqlite3>
** Invoke db:setup (first_time)
** Invoke db:schema:load_if_ruby (first_time)
** Invoke db:create (first_time)
** Invoke db:load_config 
** Execute db:create
db/development.sqlite3 already exists
# ...

This is odd, but at least the development database gets created and seeded. The real issue comes when I run rake db:test:prepare: although there are no error messages, as well as the test database not being created, the data in the development database gets blown away (schema is still in tact, though). I tried directly specifying the Rails environment for the command and got:

$ rake db:test:prepare RAILS_ENV=test
You have 7 pending migrations:
20120503193649 CreateUsers
# ...
Run `rake db:migrate` to update your database then try again.

After running rake db:migrate RAILS_ENV=test, I could run my rspec tests again. So, my rake commands to get the same results have now changed to:

$ rake db:reset # (with an error)
$ rake db:migrate RAILS_ENV=test

If I change my config/database.yml file back to a simple sqlite3 only configuration, db:reset and db:test:prepare work as I expect.

So, does this mean that my mysql and/or postgres settings are causing rake tasks to repeat and/or they're messing with the Rails environment settings? Where should I be looking to confirm if my environment is really set up to work properly with these 3 database engines?

Edit

Looking at the release notes for Rails 3.2.8.rc2, I found a change to ActiveRecord potentially related to this question:

• Do not set RAILS_ENV to development when using db:test:prepare and related rake tasks. This was causing the truncation of the development database data when using RSpec. In RC2 was fixed again when using config.active_record.schema_format = :sql

config/application.rb has the following explanation:

# Use SQL instead of Active Record's schema dumper when creating the database.
# This is necessary if your schema can't be completely dumped by the schema dumper,
# like if you have constraints or database-specific column types
# config.active_record.schema_format = :sql

My schema doesn't have constraints or database-specific column types, so I didn't uncomment this line, however, given the content of the release note, I wagered that RAILS_ENV defaulting to development could be responsible for the deleted data in the development environment. So, I tried out a few things and got expected results by doing what I did before (after upgrading Rails to 3.2.8.rc2):

$ rake db:reset # (with an error)
$ rake db:test:prepare RAILS_ENV=test # (with no "pending migrations" issue)

This is a bit better, but still seems wrong to me since there is still an error with rake db:reset, and it doesn't make sense to me to have to set RAILS_ENV=test when running a rake command specifically tailored for the test database.

Update

It would seem that upgrading to Rails 3.2.9 solves this issue due to the following fix:

• Fix bug where rake db:test:prepare tries to load the structure.sql into development database. Fixes #8032.

Grace Liu + Rafael Mendonça França

I can now again reset my development database, re-seed it, and prepare my test database simply by performing:

$ rake db:reset
$ rake db:test:prepare 

Solution

Your development database is being purged because ActiveRecord::Base.configurations has the test database set to "development.sqlite3". When the rake task is run, the yaml configuration is eval'ed into the ActiveRecord::Base.configurations hash and at that time Rails.env is set to development.

If RAILS_ENV=development, the database value for test will be set to

database: db/development.sqlite3

or for a different adapter:

database: my_app_development

You can reproduce this with a simple sqlite only configuration buy changing the test block inside database.yml to the following:

test:
  adapter: sqlite3
  database: db/<%= Rails.env %>.sqlite3
  pool: 5
  timeout: 5000

If you inspect the full ActiveRecord::Base.configurations hash you'll see that test is set to use the development db if no RAILS_ENV is specified. And if you were to specify 'production' or 'staging' it would be set to that. From the console:

# rails c
> ActiveRecord::Base.configurations['test']['database']
  => "db/development.sqlite3" 

compared with:

# RAILS_ENV=test rails c
> ActiveRecord::Base.configurations['test']['database']
  => "db/test.sqlite3"

Update

The issue you are seeing with db:reset is also because your yaml file is interpreted once and then the config is set.

db:reset will invoke db:drop and db:setup for the given environment. However, if the environment is development, it also does those tasks for the test environment. So it succeeds in dropping for the development environment and then when it executes for test, the database key of the configuration is identical to the development section, hence it can't drop something that no longer exists. Here is what the ActiveRecord::Base.configurations hash looks like when Rails.env == 'development'

"development" => {
    "adapter" => "sqlite3",
    "database" => "db/development.sqlite3", 
    "pool" => 5, 
    "timeout" => 5000
}, 
"test" => {
    "adapter" => "sqlite3", 
    "database" => "db/development.sqlite3",
    "pool" =>5, 
    "timeout"=>5000
}, 
"production" => {
    "adapter" => "sqlite3", 
    "database" => "db/development.sqlite3",
    "pool"=>5, 
    "timeout"=>5000
}

And once it's in that hash, it doesn't go back and re-read the database.yml file. That hash is what is generated given this database.yml

development:
  adapter: sqlite3
  database: db/<%= Rails.env %>.sqlite3
  pool: 5
  timeout: 5000

test:
  adapter: sqlite3
  database: db/<%= Rails.env %>.sqlite3
  pool: 5
  timeout: 5000

production:
  adapter: sqlite3
  database: db/<%= Rails.env %>.sqlite3
  pool: 5
  timeout: 5000