Better Habits with Structure.sql

At Beam, our Apps are built with Ruby on Rails and we utilize a structure.sql file. For context, a structure.sql is supposed to describe the correct database schema. It is crucial in keeping our Apps working properly. It also lets us do cool things like change our database and keep track of those changes over time by running migrations. It’s an important and heavily used tool, but it can be tricky to work with.

Have you ever committed unintentional changes to your structure file several times? Only to have to revert the whole commit and pick and choose your intentional changes and recommit. Well you aren’t alone. Developers of all experience levels run into issues with structure files.

Here are some common issues, their causes, and the best ways to avoid them in the future!

Why is my structure file wrong?

There are probably multiple ways your structure file could end up astray. However, there are 2 common ones we often see at Beam.

The first common issue with our structure file is a merge conflict. (which is common, the structure file changes frequently). Here is how it can happen:

1. Manually handle the merge, rather than rolling back
2. Rebase
3. Migrate again

Then rails will handle the structure file changes and it can be different from the base branch.

The second issue is caused when:

1. A migration is done on a feature branch
2. Switch back to master or another branch without rolling back

Then the structure file will again be different.

Both of these scenarios are good practices when it comes to writing and reviewing code with git. It just so happens to lead to a borked file! Luckily there are habits that we practice at beam to avoid getting the file into a broken state and causing us a headache.

Good habits to avoid issues with the structure file

One habit you can use to avoid issues with merge conflicts. After resolving merge conflicts and before doing a rebase, run rake db:migrate. This will run migrations that haven’t run yet and therefore will fix your structure file.

One trick that can be time consuming is to drop all of your databases, recreate them, and do a restore from backup. This will align your databases with the structure file.

A cool trick to do practically the same thing but faster is to switch to a different database.

1. Switch to a nonexistent branch
2. Checkout the base branch you want to create your feature off of
3. Run db:structure:load
4. Switch back to your branch
5. Then migrate against that switched database

Overall, it is very common to run into these issues but it’s also very easy to start developing good habits to avoid running into them again — you just have to know what to do! I hope these tips can help you now and in the future!

Photo by Jordan Wozniak on Unsplash