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.


One response to “Deleting all records in a Rails project”

  1. Bragaadeesh Avatar

    Nice post! Thanks..

Leave a Reply

You may also like:

Hi, I'm Pablo, this is my web site. You can follow me or connect with me:

Or get new content delivered directly to your inbox.

Join 4,048 other subscribers

I'm writing a book

Stack of copies of How to Hire and Manage Remote Teams

How to Hire and Manage Remote Teams, where I distill all the techniques I've been using to build and manage distributed teams for the past 10 years.

I write about:

announcement blogging book book review book reviews books building Sano Business C# Clojure ClojureScript Common Lisp database Debian Esperanto Git ham radio history idea Java Keep on Posting Kubuntu Lisp Non-Fiction OpenID programming Python Radio Society of Great Britain Rails rant re-frame release Ruby Ruby on Rails Sano science science fiction security self-help Star Trek startups technology Ubuntu web WordPress

I've been writing for a while:

%d bloggers like this: