Rake task to truncate all tables in Rails 3

Question

I would like to have a rake task for truncating all the tables. I have found one on the internet, but it is supposed only for Rails 2 and does not work for Rails 3 (problem is in getting a database connection).

rake db:reset is not an option, because I am using PostgreSQL and it also drops the user. Therefore migration fails. I only want to clear the data.

Do you guys have somehting for me?

Answer 1

I've found this via google, and then I got a much simpler solution than the one approved, so here it is: Use the database_cleaner gem. Here're the steps.

In your Gemfile (execute bundle after modifying):

gem 'database_cleaner' # you might want to limit this to the dev and staging group

With that gem in place, the statement DatabaseCleaner.clean_with :truncation will truncate the database. Adding it to a rake task is trivial:

# tasks/db/clean.rake

namespace :db do

  desc "Truncate all existing data"
  task :truncate => "db:load_config" do
    DatabaseCleaner.clean_with :truncation
  end

end

That's it. You can also use the DatabaseCleaner.clean_with :truncation line inside your db/seeds.rb file directly so that you don't forget to truncate the database before seeding.

Answer 2

So I edited the linked example into this:

namespace :db do
  desc "Truncate all existing data"
  task :truncate => "db:load_config" do
   begin
    config = ActiveRecord::Base.configurations[::Rails.env]
    ActiveRecord::Base.establish_connection
    case config["adapter"]
      when "mysql", "postgresql"
        ActiveRecord::Base.connection.tables.each do |table|
          ActiveRecord::Base.connection.execute("TRUNCATE #{table}")
        end
      when "sqlite", "sqlite3"
        ActiveRecord::Base.connection.tables.each do |table|
          ActiveRecord::Base.connection.execute("DELETE FROM #{table}")
          ActiveRecord::Base.connection.execute("DELETE FROM sqlite_sequence where name='#{table}'")
        end                                                                                                                               
       ActiveRecord::Base.connection.execute("VACUUM")
     end
    end
  end
end

This example is based on Chris Ledet's code bellow (thanks) and works with Rails 3.X.

Thanks for all hints.

Answer 3

According to Chris Ledet answer, this becomes much simpler:

ActiveRecord::Base.connection.tables.each do |table|
    ActiveRecord::Base.connection.execute("TRUNCATE TABLE #{table};")
end

Answer 4

To truncate db in Rails 6

rails db:truncate_all  

Answer 5

You could always migrate to version 0, like so:

rake db:migrate VERSION=0

That way you don't even have to truncate your tables, and you can then migrate again. The only catch is that you need your down migrations to work properly.

This solution does work in rails 3, despite the fact that the versions are timestamp-based.

This solution is as seen here: https://stackoverflow.com/a/1196822/241367

Additionally, you could always run the following, assuming your schema.rb is up to date:

rake db:schema:load

And as @kikito suggests, you can run database_cleaner (it's what cucumber and rspec like to use between tests) like so:

DatabaseCleaner.clean_with :truncation

Answer 6

This will get all of the tables in your database, find a model associated with that table and call #destroy_all.

tables = ActiveRecord::Base.connection.tables
tables.each do |tbl|
 # "users" => User
 tbl.classify.constantize.destroy_all
end

Answer 7

The answer given by lzap has one specific problem. Rails wants to run all the migrations again. The following code is as suggested by Anthony Alberto and it works. This addition checks with the schema_migrations table

namespace :db do
  desc "Truncate all existing data"
  task :truncate => "db:load_config" do
   begin
    config = ActiveRecord::Base.configurations[::Rails.env]
    ActiveRecord::Base.establish_connection
    case config["adapter"]
      when "mysql", "postgresql"
        ActiveRecord::Base.connection.tables.each do |table|
          ActiveRecord::Base.connection.execute("TRUNCATE #{table}") if table != "schema_migrations"
        end
      when "sqlite", "sqlite3"
        ActiveRecord::Base.connection.tables.each do |table|
          ActiveRecord::Base.connection.execute("DELETE FROM #{table}") if table != "schema_migrations"
          ActiveRecord::Base.connection.execute("DELETE FROM sqlite_sequence where name='#{table}'") if table != "schema_migrations"
        end                                                                                                                               
       ActiveRecord::Base.connection.execute("VACUUM")
     end
    end
  end
end