Reverting structure.sql to schema.rb
First, I know what you’re thinking. Why would you ever have to revert a
structure.sql file to a
schema.rb file in a Rails app? What an incredibly bizarre problem to have! Well, there are some advantages to
structure.sql, for example, being able to use
sql features that can’t be put in a Ruby file. I suppose at some point, long before I came on the scene, we needed these extra features. But then we didn’t need them anymore and it was my job to change back to
schema.rb. Here’s how I did it:
Since this is such a rare problem, there aren’t many resources on this particular topic. I thought my findings might be useful in the unlikely event of someone having to deal with this same problem.
The thing is, you can’t just run a command to convert
schema.rb. I mean, maybe you could if you were really super lucky and nothing broke, but that wasn’t the case for me. The most important thing in my case was cleaning up the database, unifying the data structure inside the
structure.sql, and getting the data in a Rails-friendly format before making “the big change”.
Lots of empty strings
There were lots of empty strings inside the database. This is not a good scene. First task: make empty strings null. I wrote a rake task to get the job done.
Tip: Before you run this rake task, decide if you want to run it on all models and columns. You can skip a model/column using something like this
next if model.name == "MyModel"
Also, don’t forget to run this on production once deployed.
Remove custom column triggers
created_at and the default for these columns should be
nil. In this case, there was another application that was using the database and inserting new records. That application was using triggers in the database to set the
updated_at. But because we were no longer using this second application, and the Rails app was going to manage the DB again, I had to set the column defaults back to
nil, letting Rails handle these columns.
Change all character sets to be the same
There was a mix of different collations in the DB and this can cause problems. The database had
latin1_swedish_ci. I wanted them to all be the same, so I chose
I based this migration off of this. In my case, I had three types of
column.sql_type, so I had to run cases for
ENUM. I know this is a big messy thing with lots of repeated code, but it was a one-time migration just to get the job done and I didn’t want to waste time refactoring. In retrospect, I should have done this after the next step (you’ll see why if you make it that far), which would have prevented the need to run this on
Change CHARS and ENUMS to VARCHARS
There were two column types that don’t play nice in Rails:
ENUM. In order to revert to
schema.rb, we needed Rails-friendly column types. Therefore, I decided to change these all to
Here are the migrations to do this:
Can we revert the damn thing already?
Once everything was in good shape, I got to run that one little line that finally reverts the
schema.rb. Are you ready for it?
That magic line! It generates a new schema based on what’s actually in the database. But, as you can see, your database needs to be in good shape before running this task.
Thanks for reading and may you be blessed with a healthy database and beautiful schema.rb so that you may never suffer the hardships described above.