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.
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.
To generate the migration:
rails generate migration DropChromosome5
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:
class DropChromosome5 < ActiveRecord::Migration
create_table :chr5_nucleotides do |t|
exec rake db:migrate
exec rake db:rollback
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.