I’ve had some notes laying around after I needed to drop some database tables a while back. Now that I have the need  to drop a few tables again in an upcoming sprint, I thought I’d get my thoughts down in a quick post and share.

A Few Things to Keep In Mind…

The normal course of development and system refactoring occasionally renders database tables obsolete. I don’t bother dropping tables until after a successful production deployment has rendered the target tables obsolete. For example, if Sprint N introduces code deprecating a table, I’ll wait for Sprint N+1 to introduce the ActiveRecord::Migration to drop it. There’s no harm in letting the obsolete table sit around for another sprint; and since you’re dropping it in the very next sprint, it should still be fresh enough in your memory to keep from forgetting about it. Waiting an extra sprint is just an overly cautious way of handling this type of thing and leaving the table gives me some peace of mind should the improbable event arise that I need to reintroduce the code again that uses the table.

 

When dropping a table, ALWAYS make sure it exists before doing so. This ensures no deployment errors are introduced if the table has already been dropped for some reason.

 

Expanding on my prior points… How would a table already be “dropped for some reason”? Let’s assume that a deployment failed for some reason after the deployment migrations have already executed. The deployment will rollback all migration scripts ensuring the database is in a pre-deployment state so the deployment can be attempted again after the error(s) have been fixed. Running the migration script again will attempt to drop the deprecated tables again, but only if they exist (and they shouldn’t)… It’s a good thing we’re checking for their existence first!

ActiveRecord::IrreversibleMigration

The ActiveRecord documentation states, “Migrations [that are irreversible] should raise an ActiveRecord::IrreversibleMigration exception in their down method.” Dropping tables would suggest this type of change, right? …especially since you wouldn’t want to recreate the table with all of its existing data (possibly moving it over from a backup table) just to drop it again. I highly discourage using an ActiveRecord::IrreversibleMigration exception for this purpose unless you’re comfortable killing your database rollbacks during a failed migration on a production deployment. That’s an unnecessary headache when you’ve already got problems with a deployment failure.

ActiveRecord::Migration Safe DROP Example

class SafeTableDropExample < ActiveRecord::Migration

  def up
    drop_table 'obsolete_table' if ActiveRecord::Base.connection.table_exists? 'obsolete_table'

    if ActiveRecord::Base.connection.table_exists? :another_obsolete_table
      drop_table :another_obsolete_table
    end
  end

  def down
    # We're not going to reverse this migration even if the current deployment fails...
    # Since a previous sprint deployment has made these tables obsolete, we're completely sure they're no longer used.
    # There's no need to recreate them in the event of a rollback just so they can be DROPped again when the current
    # deployment succeeds.
  end

end

Resources