Truncate table(s) with rails console
Ruby on-RailsRuby on-Rails-3TruncateRails ConsoleRuby on-Rails Problem Overview
I have this testingdatabase which, by now, is stuffed with junk. Now I've done a few Table.destroy_all commands in the rails console which deletes all records and dependencies which is awesome. However; I'd like to truncate everything so the ID's etc. start at 1 again. Is there any way in Rails 3?
Ruby on-Rails Solutions
Solution 1 - Ruby on-Rails
The accepted answer only works if you need to recreate the whole database.
To drop a single table (with the callbacks) and to get the IDs to start from 1:
Model.destroy_all # Only necessary if you want to trigger callbacks.
ActiveRecord::Base.connection.execute("TRUNCATE #{table_name} RESTART IDENTITY")
If you are using Sqlite, it does not support truncate so do the following:
Model.destroy_all # Only necessary if you want to trigger callbacks.
ActiveRecord::Base.connection.execute("Delete from #{table_name}")
ActiveRecord::Base.connection.execute("DELETE FROM SQLITE_SEQUENCE WHERE name='#{table_name}'")
Solution 2 - Ruby on-Rails
Model.connection.truncate(Model.table_name)
Solution 3 - Ruby on-Rails
This worked for me -
ActiveRecord::Base.connection.execute("TRUNCATE table_name")
Solution 4 - Ruby on-Rails
Simply rebuild the database on the next test run (this will happen automatically after dropping it).
rake db:drop RAILS_ENV=test
Solution 5 - Ruby on-Rails
You could also do rake db:rollback STEP=3 RAILS_ENV=test
where 3 represents the number of migrations that you have in db/migrate. In example: If I have in
db/migrate
20140121065542_create_users.rb
20140121065710_create_profiles.rb
20140121065757_create_articles.rb
20140121065900_create_comments.rb
20140121065929_create_categories.rb
So I have 5 migrations in total to remove. If I do rake db:rollback STEP=5 RAILS_ENV=test
all the tables will be drop from my TEST database and if I remove RAILS_ENV=test than all the ENVIRONNMENT (production, test, development) tables will be delete and it cleans also db/shema.rb file from it's migration datas.
Solution 6 - Ruby on-Rails
rake db:reset
will perform rake db:drop db:setup
. In other words, drop the database and setup the database again.
Solution 7 - Ruby on-Rails
Assuming you're using MySQL or Postgre and not SQlite3 (which doesn't support TRUNCATE
), you could do the following:
MyModel.connection_pool.with_connection { |c| c.truncate(MyModel.table_name) }
Note that this would not invoke ActiveRecord callbacks.
Solution 8 - Ruby on-Rails
Rails 6.0+ add new method: truncate_tables
ActiveRecord::Base.connection.truncate_tables([:table1_name, :table2_name])
Solution 9 - Ruby on-Rails
I used below code to truncate all tables
ActiveRecord::Base.establish_connection
ActiveRecord::Base.connection.tables.each do |table|
next if table == 'schema_migrations'
case ActiveRecord::Base.connection.adapter_name.downcase.to_sym
when :mysql2 , :postgresql
ActiveRecord::Base.connection.execute("TRUNCATE #{table}")
ActiveRecord::Base.connection.execute("TRUNCATE #{table} RESTART IDENTITY")
when :sqlite
ActiveRecord::Base.connection.execute("DELETE FROM #{table}")
end
end
Solution 10 - Ruby on-Rails
You can use ModelName.delete_all
in rails console.
Solution 11 - Ruby on-Rails
(A bit late to the party, I know)
Asking to do this in the console:
2.1.2 :001 > Post.all.each do |post|
2.1.2 :002 > post.destroy!
2.1.2 :003 > end
Works as well...
This essentially loops through all of the posts and destroys them. It does not change your auto increment value though...
Same logic should work for Rails 3 as well (though I am using Rails 4)