Testing migrations from sandboxed Rails console

I usually download the staging or production database in order to test the migration locally. This enables getting migration errors before the deployment. However, I regularly screw up the database with some silly mistake in migration, which requires recreating the database.

The mistake doesn't even have to be a schema change, it can be a simple data change that is wrong. In order to make it right, I have to recreate the database and try again, which takes time.

There's a trick to avoid that.

First run Rails console in a sandbox mode: rails c --sandbox. This will rollback any changes you make, including mistakes, once you exit the console. Next, require the migration:

> require "./db/data/20200213144332_enable_invoice_editing"

Now run the migration:

> EnableInvoiceEditing.new.up

And that's it! You can now check if your migration is valid. Once you exit the console, the state of the database will rollback to the state before running the migration.

Never use this in production

Do not use console in sandbox mode in production, since this locks the updated database rows with the ROW EXCLUSIVE lock. This will cause any subsequent updates to rows updated from console to hang until you exit console.

Thanks to /u/tomthecool for pointing this out.