How to quickly wipe and recreate a db table that doesn’t have a migration

Sometimes a table gets messed up and the best thing to do is to wipe it and recreate it from scratch.  Also sometimes, we don’t have a rake migration for it, or it was built up through a series of messy migrations that add bits, remove them, add other stuff, etc.

If we delete all the rows, we DON’T always get the same as an empty table – if, like in most rails tables, we have a primary index (usually id) which is set to autoincrement, if we delete all the records and then add one, the first row won’t be numbered 1, it will be numbered after the last row to be created.  So, we’re not really starting from scratch.

A nice quick way to just recreate a blank version of the table from scratch is as follows.

1) Make an sql dump of the entire database:  at the command line, type

rake db:structure:dump

2) This will create an sql script file called development_structure.sql in the db folder, which contains instructions for adding all of the tables to the db.  Open it in a text viewer and look for the section concerning the table you want.  Copy it out.

3) Lose the existing table – at the sql command line type “drop table your_table_name”

4) In a sql browser, or at the command line, paste in the section of sql script from the dump and run it.

Voila – nice empty table, with indexes all starting from scratch.

5) If you have any migrations to populate the table with data, you can copy the code out of the ‘up’ method, run a rails console, paste in the code and run it.


Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s