Deleting all records in a database table

Question

How do I delete all records in one of my database tables in a Ruby on Rails app?

score 295 · Accepted Answer · answered Mar 16 '11 at 07:47

295

If you are looking for a way to it without SQL you should be able to use delete_all.

Post.delete_all

or with a criteria

Post.delete_all "person_id = 5 AND (category = 'Something' OR category = 'Else')"

See here for more information.

The records are deleted without loading them first which makes it very fast but will break functionality like counter cache that depends on rails code to be executed upon deletion.

answered Mar 16 '11 at 07:47

HakonB

6,977
1
26
27

13

It's worth noting that if you've got associations with :dependent => :destroy, or anything that needs to be cleaned up upon deletion, you'll probably want Post.destroy_all - though it is much slower. See http://apidock.com/rails/ActiveRecord/Base/destroy_all/class – Michael Hellein Oct 17 '11 at 14:44
This answer assumes that the table has a model associated with it. The OP didn't specify this - what if the table is a join table? – Toby 1 Kenobi Aug 25 '15 at 18:48
1

@BradWerth whether or not it is considered by some to be good or bad style, I'm just saying it is feasible that a Rails db has tables that aren't `ActiveRecord` models. The question asks about deleting record from a 'table' and I'm just pointing or the assumption held in the answer. – Toby 1 Kenobi Dec 26 '15 at 22:55
I have same query as @Toby1Kenobi 's. – NBSamar Jul 26 '19 at 05:18

score 34 · Answer 2 · answered Mar 16 '11 at 08:11

34

To delete via SQL

Item.delete_all # accepts optional conditions

To delete by calling each model's destroy method (expensive but ensures callbacks are called)

Item.destroy_all # accepts optional conditions

All here

answered Mar 16 '11 at 08:11

lebreeze

5,094
26
34

score 26 · Answer 3 · answered Mar 16 '11 at 08:09

26

if you want to completely empty the database and not just delete a model or models attached to it you can do:

rake db:purge

you can also do it on the test database

rake db:test:purge

answered Mar 16 '11 at 08:09

KensoDev

3,285
21
38

score 6 · Answer 4 · answered Oct 29 '14 at 22:01

6

If you mean delete every instance of all models, I would use

ActiveRecord::Base.connection.tables.map(&:classify)
  .map{|name| name.constantize if Object.const_defined?(name)}
  .compact.each(&:delete_all)

answered Oct 29 '14 at 22:01

dfaulken

476
8
18

1

Prefer `select` whenever you need to use an if expression inside a block, this way you avoid having to chain the compact method to remove nil elements. – Sebastián Palma Jan 18 '18 at 17:18

score 4 · Answer 5 · answered May 24 '13 at 14:57

4

BlogPost.find_each(&:destroy)

answered May 24 '13 at 14:57

Philip

6,827
13
75
104

1

This is great for low memory circumstances. – Epigene Feb 10 '15 at 12:13
This is the only answer that takes memory consumption into account. – John Jan 25 '17 at 14:54
2

Omg, why to make a loop for this... no sense. just delete all records DELETE FROM table, Model.delete_all – Imnl Jun 16 '17 at 11:03
@John why one single query consumes more memory than a loop of queries? – Imnl Jun 21 '17 at 14:40
@Imnl Each iteration instantiates a new instance of the model in question so that it can process the callbacks for the delete method. – John Jun 22 '17 at 15:00
@John I will execute the equivalent callbacks only, still doesnt have sense for me to perform all those queries to a db separately, it will take a lot more time. – Imnl Jun 22 '17 at 16:34
@lmnl You realize this batches them in groups of 1000 by default? That was the point of my original comment about memory. See http://api.rubyonrails.org/classes/ActiveRecord/Batches.html#method-i-find_each. – John Jun 23 '17 at 18:01
@John i usually use find_each, but not for destroying groups of elements, that was de point of the question. find_each is a good tool but its not good for this case. – Imnl Jun 26 '17 at 07:42
@imnl if you have a low memory server with 2 billion records that you want to delete... – Philip Jul 11 '17 at 09:30

score 2 · Answer 6 · edited May 23 '17 at 12:34

More recent answer in the case you want to delete every entries in every tables:

def reset
    Rails.application.eager_load!
    ActiveRecord::Base.descendants.each { |c| c.delete_all unless c == ActiveRecord::SchemaMigration  }
end

More information about the eager_load here.

After calling it, we can access to all of the descendants of ActiveRecord::Base and we can apply a delete_all on all the models.

Note that we make sure not to clear the SchemaMigration table.

score 2 · Answer 7 · answered Mar 16 '11 at 07:45

2

If your model is called BlogPost, it would be:

BlogPost.all.map(&:destroy)

answered Mar 16 '11 at 07:45

stef

14,172
2
48
70

2

this will fetch every single BlogPost and load it into a Ruby array before destroying them. – hdgarrood Jan 18 '13 at 17:10
Depends on the ORM. Datamapper wouldn't do that because you're not requesting anything about each model. And here's a Mongoid stacktrace that shows it doesn't select any fields before destroying each entry: `MOPED: 127.0.0.1:27017 QUERY database=a_database collection=nothings selector={} flags=[:slave_ok] limit=0 skip=0 batch_size=nil fields=nil (0.3378ms)` – stef Jan 18 '13 at 17:43
5

since it's a rails question, and the asker hasn't said which ORM he's using, we should assume ActiveRecord – hdgarrood Jan 18 '13 at 19:37

score 0 · Answer 8 · answered Jun 13 '22 at 17:02

0

If you have model with relations, you need to destroy models that are related as well.

answered Jun 13 '22 at 17:02

Antonija Šimić

162
2
8

score 0 · Answer 9 · answered Jul 28 '22 at 21:26

The fastest way to achieve this:-

Want to delete all data from the table

Post.delete_all

Want to delete specific data from the table, then the right way to do it is

Post.where(YOUR CONDITIONS).delete_all
# this above solution is working in Rails 5.2.1, delete_all don't expect any parameter
# you can let me know if this works in different versions.

# In the older version, you might need to do something like this:-
Post.delete_all "Your Conditions"

score 0 · Answer 10 · answered Feb 06 '23 at 15:25

This way worked for me, added this route below in routes.rb

get 'del_all', to: 'items#del_all' # del_all is my custom action and items is it's controller

def del_all #action in ItemsController
 if Item.any? 
  Item.destroy_all
  redirect_to items_url, notice: "Items were destroyed." 
 else  
  redirect_to items_url, notice: "No item found here." 
 end     
end

According to documentation:

2.5 Singular Resources - Sometimes, you have a resource that clients always look up without referencing an ID. For example, you would like /profile to always show the >profile of the currently logged in user. In this case, you can use a singular >resource to map /profile (rather than /profile/:id) to the show action: get 'profile', to: 'users#show'

Deleting all records in a database table

10 Answers10