How to delete all data from all tables in Rails?

Question

I can do Post.delete_all to delete all my posts, but what if I want to delete all posts, comments, blogs, etc.?

How do I iterate over all my models and run the delete_all method?

Answer 1

rake db:reset 

It recreates your table from migrations.

As suggested in the comments, a faster way to do it (but you have to add a new rake task) is:

namespace :db do
  desc "Truncate all tables"
  task :truncate => :environment do
    conn = ActiveRecord::Base.connection
    tables = conn.execute("show tables").map { |r| r[0] }
    tables.delete "schema_migrations"
    tables.each { |t| conn.execute("TRUNCATE #{t}") }
  end
end

Response copied from: answer on SO.

Answer 2

You can have finer control with:

rake db:drop:all

And then create the database without running the migrations,

rake db:create:all

Then run all your migrations,

rake db:migrate 

You can also do:

mysqladmin drop databasename

Answer 3

If you're trying to do this from code instead of the command line, say from a Test::Unit::TestCase#teardown method, you could do either

class MyTest < Test::Unit::TestCase

  def teardown
    ActiveRecord::Base.subclasses.each(&:delete_all)
  end

end

or

class MyTest < Test::Unit::TestCase

  def teardown
    Rake::Task['db:reset'].invoke
  end

end

I warn you, though: neither is particularly fast. You're definitely better off with transactional tests if you can.

Answer 4

If you simply want to start fresh with a fresh set of empty tables, you can first ensure you have an up-to-date definition of the schema in db/schema.rb:

rake db:schema:dump

and then:

rake db:schema:load

which has the effect of dropping tables and then re-creating them, without running through your entire battery of migrations.

Answer 5

In Rails 6, you can do rails db:truncate_all to remove all data without dropping any tables.

If you would like to seed your db after that, you could also do rails db:seed:replant to truncate all data and seed database

Answer 6

rails db:purge

has recently been added to ActiveRecord in the master branch of rails 4.2.0.alpha

https://github.com/rails/rails/commit/e2f232aba15937a4b9d14bd91e0392c6d55be58d

Answer 7

A faster way to just delete table rows is to use the TRUNCATE command.

Many of the other answers seem to ignore the difference between deleting rows and dropping a table. Dropping a table destroys the table data and schema; meaning that you need extra steps to recreate the tables. Sean McLeary's answer was the best I saw, so I used it as a starting point. However, I think it is better to take advantage of the TRUNCATE command, because it should be faster and it also resets auto-increment keys. Also, using map instead of each shortens the code a bit.

namespace :db do
  desc "Truncate all tables"
  task :truncate => :environment do
    conn = ActiveRecord::Base.connection
    tables = conn.execute("show tables").map { |r| r[0] }
    tables.delete "schema_migrations"
    tables.each { |t| conn.execute("TRUNCATE #{t}") }
  end
end

Answer 8

Accepted answer with Postgres db:

namespace :db do
  desc "Truncate all tables"
  task :truncate => :environment do
    conn = ActiveRecord::Base.connection
    postgres = "SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname='public'"
    tables = conn.execute(postgres).map { |r| r['tablename'] }
    tables.delete "schema_migrations"
    tables.each { |t| conn.execute("TRUNCATE \"#{t}\"") }
  end
end

If there are foreign_key error like ActiveRecord::StatementInvalid (PG::FeatureNotSupported: ERROR: cannot truncate a table referenced in a foreign key constraint)

Then, CASCADE option would help.

tables.each { |t| conn.execute("TRUNCATE \"#{t}\" CASCADE") }

Answer 9

If you want to delete only the data without touching the tables while using it inside your application or rails console :

Rails.application.eager_load!
ActiveRecord::Base.connection.disable_referential_integrity do
  ApplicationRecord.descendants.each do |model|
    model.delete_all
  end
end

With this code, you don't have to bother with referencing manually your models and/or with foreign key constraints (thanks to disable_referential_integrity).
ApplicationRecord.descendants returns only true application models unlike ActiveRecord::Base.descendants (no more ApplicationRecord, schema_migrations and ar_internal_metadata).

Answer 10

This will work also for Rails 4

(ActiveRecord::Base.connection.tables - ['schema_migrations']).each do |table|
    table.classify.constantize.destroy_all
end

Answer 11

You could list all the models in the seed-file (seeds.rb), and simply run

rake db:seed

The seed file would then look something like this:

Model1.delete_all
Model2.delete_all
Model3.delete_all
Model4.delete_all
Model5.delete_all
Model6.delete_all
Model7.delete_all

...

rake db:reset is too much for your job here. That will completely kill off your database and rebuild it from scratch, running all migrations etc. To run the seed command is faster.

Answer 12

We have been remiss here at Stack Overflow for not mentioning the database_cleaner gem:

Database Cleaner is a set of strategies for cleaning your database in Ruby. The original use case was to ensure a clean state during tests. Each strategy is a small amount of code but is code that is usually needed in any ruby app that is testing with a database.

By 'strategy', Mr. Mabey means: truncation, transaction, and deletion.

ActiveRecord, DataMapper, Sequel, MongoMapper, Mongoid, and CouchPotato are supported.

Here is a quick code snippet from the Database Cleaner README:

require 'database_cleaner'
DatabaseCleaner.strategy = :truncation

# then, whenever you need to clean the DB
DatabaseCleaner.clean

Answer 13

# fast truncation of all tables that need truncations (select is 10x faster then truncate)
# http://grosser.it/2012/07/03/rubyactiverecord-fastest-way-to-truncate-test-database/
def truncate_all_tables
  connection = ActiveRecord::Base.connection
  connection.disable_referential_integrity do
    connection.tables.each do |table_name|
      next if connection.select_value("SELECT count(*) FROM #{table_name}") == 0
      connection.execute("TRUNCATE TABLE #{table_name}")
    end
  end
end

Answer 14

I know this is an old question, but I thought this might be helpful to someone. This is a very fast way of cleaning out all data from a database.

tables = []
ActiveRecord::Base.connection.execute("show tables").each { |r| tables << r[0] }
tables = tables - ["schema_migrations"]
tables.each do |table|
  ActiveRecord::Base.connection.execute("DELETE FROM #{table} WHERE 1 = 1")
end

I use this technique in certain specs in an after(:all) block. This is much faster and more efficient than any of the Rails rake tasks for purging, migrating, reseting the database.

BTW: I'm pretty sure this would likely fail if you were enforcing foreign key constraints on the database side.

Answer 15

My 50 cents, for cleaning db and able to run migrations again (in cases when you can't delete database, eg AWS RDS):

# get connection
conn = ActiveRecord::Base.connection
# find all tables needed to be removed
tables = conn.execute("SELECT * FROM pg_catalog.pg_tables WHERE schemaname='public' AND tablename<>'schema_migrations'").to_a.map { |r| r['tablename'] }
# remove all tables except schema_migrations
tables.each { |t| conn.execute("DROP TABLE #{t}") }
# clean migrations table
conn.execute("TRUNCATE TABLE schema_migrations")

And now you can run rake db:migrate to have your db in a clean state.

Answer 16

Building up on @Vlad Zloteanu's answer, here is a version to remove all tables while keeping the user records and login sessions together with some meta information. Feel free to adjust the list of tables to your requirements.

# lib/tasks/db/truncate.rake

namespace :db do
  desc 'Truncate all tables except users state and meta'
  task truncate: :environment do
    conn = ActiveRecord::Base.connection
    tables = conn.tables - %w[
      sessions
      users
      roles
      users_roles
      schema_migrations
      ar_internal_metadata
    ]
    tables.each { |t| conn.execute("TRUNCATE #{t}") }

    puts "Truncated tables\n================\n#{tables.sort.join("\n")}"
  end
end

Answer 17

If you want to delete all the tables and re-initialize with default values you can use.

rails db:bootstrap