How to Reset a Single Table from Your Rails Database (without touching the others)

How to Reset a Single Table from Your Rails Database (without touching the others)

I’m in the process of coding out a demo app for browsing curated human genome data. As such, I’ve decided to establish a canonical backbone for each chromosome and standardize not only the genes, but their structures, mutations, primers, SNPs, etc., and all data I can get my hands on to this one backbone. As such, I’ve set upon loading all 3 billion+ bases of the human genome into my rails db.

After loading the first 4 chromosomes, chromosome 5 encountered an error (mind you, the first chromosme is the largest with just under 250 million bases, and the 21st chromosome is the smallest with just under 50 million bases). By this time, I’m already pushing a billion nitrogenous base entries in my database. Due to constraints of most relational databases I researched (1.7 billion primary keys per table), I made the decision to split this data among different tables (one per chromosome).

After the error loading chromosome 5, it became apparent that the indexing of the bases according to position on the chromosome was effed up. I wanted to drop and restart. This operation is not readily apparent with Rails, and the top StackOverflow answer after googling “how to reset a table in rails” doesn’t really have any instructions. I had only previously used the rake db:reset in order to clear the whole database, but I didn’t want to lose all the other bases I had loaded.

A Simple Way to Reset One Table from Your Rails Database

What I decided to do was to generate a migration that would drop the table of interest uponĀ rake db:reset, and would reinstate this table upon rake db:rollback. Once this was complete, I could delete the associated migration, and my table would be back to point 0.

Step 1: Generate Table Drop Migration

To generate the migration:
rails generate migration DropChromosome5
…or whatever…

In the migration, you will have to specify the drop_table instructions as the ‘up instructions’ as well as the same instructions for when you initially created the table as the ‘down instructions.’ Mine looked something like this:

/db/migrate/drop_chromosome_5.rb

class DropChromosome5 < ActiveRecord::Migration
    def up
        drop_table :chr5_nucleotides
    end

    def down
        create_table :chr5_nucleotides do |t|
            t.string :nitro_base
            t.integer :chr_position
            t.integer :chromosome_id
            t.timestamps
        end
    end
end

Step 2: Run Your New Migration

exec rake db:migrate

Step 3: Rollback Your New Migration

exec rake db:rollback

Step 4: Delete Your Migration File

Once this step is completed, you are all done!

Although this is conceptually a simple problem, it wasn’t immediate obvious for me how to do this, and I hope that other newbie rails developers might find this useful. Thanks for reading, and let me know if there is an even simpler way of reseting a single table.

6 Comments

  1. Alex,

    Good post. I was about to write a migration until I discovered the delete_all and destroy methods.

    You can invoke the console with ‘rails console’
    Then type: Chr5Nucleotide.delete_all or Chr5Nucleotide.destroy

    The destroy method is transactional, so it makes sure it locks the row for the deletion. delete_all is a straight SQL delete command.

    Hope this helps,

    Flaviu

  2. Thanks Flaviu,

    Sounds like a much simpler method! Do you happen to know if it resets the primary key or not?

    -Alex

  3. Just encountered this problem. Doesn’t appear that Flaviu’s solution resets the primary key. Thanks for the post Alex.

  4. Glad it helped!

  5. Thanks this really helped

  6. I read a lot of interesting posts here. Probably you spend
    a lot of time writing, i know how to save
    you a lot of work, there is an online tool that creates readable,
    google friendly posts in seconds, just type in google –
    laranitas free content source

Leave a Reply

Spam Protection by WP-SpamFree