Rails has a great set of tools for managing database changes. These are called Active Record migrations and they were revolutionary when first implemented in the mid 2000s. I remember a friend saying that Rails had solved the “how do I keep my datastore and application in sync” problem that plagues n-tier web applications. And he was right. The idea has spread to a lot of other web frameworks, which is great. If you are using a relational database system with Rails, it’s worth spending some time getting to know migrations.
Rails migrations aren’t magic, they are just the combination of versionable text files, a DSL that abstracts database differences and a database table. Migrations can do anything that DDL and SQL commands can do, and you have the ability to work within the DSL or drop down and just execute SQL.
Often times you’ll make changes to a database that are not destructive (adding a column, for example). Other times you may make destructive changes (removing a column, or changing a column’s data type). Migrations are reversable, either by using the change method, where Active Record manages the reversal, or writing a manual reversal. This is by far the best way to roll back and forward with migrations.
However, there are some times when you need to get under the hood and reverse a migration manually, or back something out. This tends to happen to me on development when I am making some changes (often running multiple statements within a single migration and having one fail). However, I’ve also had to do this on staging and production.
Let’s assume we added a column (column A) that we now want to remove because it collides with an existing column name. We can’t roll back the migration because we added another column (column B) that we want to keep, but the migration never succeeds because of the name collision.
The first thing to do is to identify the DDL or SQL statement(s) you want to make. Test this as best you can in a non production environment. In this case it’d be a drop column statement for column A. Run this statement in the command line client. You also may be able to do this in the rails console if you aren’t comfortable with the command line client.
Then, you want to make sure you remove the migration line that caused the issue. Commit that.
Then, we either need add or remove the migration identifier (a datetime stamp) from the schema_migrations table. We’d add it in this case since column B, which we want, was already added (so that we don’t try to run the migration again). If, on the other hand, a migration succeeded but it was idempotent (updating all rows to some sane default value, for example) then we might want to remove the identifier and just run the migration again after the changes we’ve made.
This is definitely not something to do lightly, as you are mucking with the innards of your Rails application. However, in order to make sure that migrations are consistent, sometimes you have to drop down beneath the pleasant abstraction they provide.