Pablo's blog

A bit of this, a bit of that and a lot about computers

Deleting all records in a Rails project

During the initial phase of development of a Rails application I don’t use migrations as migrations but as table definitions. Until I deploy I feel free to modify the migration files as much as I want and I have one per table.

The downside of that is that the only way to apply the changes is to destroy all the tables and re-build them. I’ve explained how to do that in my post really resetting the database. The nice side effect of doing this is that you end up with a task that sets sample data to work with.

Being able to quickly set up sample data or download production data is very important. It helps new developers getting started with the project but it also allows you to play much more freely with the project, do destructive actions and then in a quick command have system reset to a known state. Once you have sample data you’ll probably become as addictive as I am to reseting.

But the truth is that 90% of the time you reset your data, you don’t need to nuke the database and re-create all records, you just need to delete all records and this is the code I use to do that:

def destroy_data
  puts "==  Data: Destroying all data ".ljust(79, "=")
  sql = ActiveRecord::Base.connection()

  sql.execute "SET autocommit=0"
  sql.begin_db_transaction

  if sql.adapter_name == "MySQL"
    sql.execute("/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */")
    sql.execute("/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */")
  end

  tables = sql.tables - ["schema_migrations"]

  tables.each do |table|
    puts "-- Deleting all for #{table}."
    # So far, disabling and enabling keys was not needed.
    #sql.execute("/*!40000 ALTER TABLE `#{table}` DISABLE KEYS */") if sql.adapter_name == "MySQL"
    record_count = sql.delete("DELETE FROM `#{table}`")
    #sql.execute("/*!40000 ALTER TABLE `#{table}` ENABLE KEYS */") if sql.adapter_name == "MySQL"
    puts "   -> done: #{record_count} reconds"
  end

  if sql.adapter_name == "MySQL"
    sql.execute("/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */")
    sql.execute("/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */")
  end

  sql.commit_db_transaction

  puts "==  Data: Destroying all data (done) ".ljust(79, "=") + "\n\n"
end

Note that I’m not deleting anything in the table schema_migration. The output is more or less like this:

==  Data: Destroying all data =================================================
-- Deleting all for blogs.
   -> done: 4 reconds
-- Deleting all for posts.
   -> done: 10 reconds
-- Deleting all for users.
   -> done: 11 reconds
==  Data: Destroying all data (done) ==========================================

I also have some nice code to generate sample data, but that is for another post.

About these ads

Single Post Navigation

One thought on “Deleting all records in a Rails project

  1. Nice post! Thanks..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 375 other followers

%d bloggers like this: