Keeping your schema.rb file clean and consistent between environments

by Santiago Herrera

What issues does an inconsistent schema.rb file cause?

Having an inconsistent schema.rb file that doesn’t really reflect the structure in production, and changes every time people run rake db: migrate in main is not just annoying, it also creates an array of problems:

• Developers have no reliable way to know what the database structure really is without having access to the production database, something most developers at mid and large sized companies don’t have.
• Tests usually run on their own database that is built from schema.rb. This means that the unit tests wouldn’t be using real assumptions. Inconsistent database schemas across environments. Everyone has their own version, different from everyone else.
• Every time you run rake db:migrate you get the schema.rb file changed. You then end up having to disregard these changes via some flavor of git checkout ..
• Since rake db:migrate changes people’s schema.rb every time by default, whenever you want to add a new migration you have to pick one of two options:
  • 1. Blindly commit all the changes in schema.rb. This pollutes your pull request with a bunch of changes that you did not really make.
  • 2. Manually manipulate schema.rb so that it only contains the changes that you introduced via migrations. This makes database changes tedious. Also, schema.rb is not meant to be manipulated manually, so this option aggravates the problem.
• An inconsistent schema.rb doesn’t only cause practical problems. It also impacts the overall quality standards of your engineering team. The broken window theory says that disorder creates an environment that encourages further disorder. This is also true in development. If the codebase is messy and disorganized, contributors lose incentives to keep their code clean and consistent.

How are these inconsistencies introduced?

Most of these inconsistencies are introduced by running migrations in a different order on different databases. This happens more often than people realize. Here is an example of how this happens. Let’s say we have 3 developers using git for their project: Ifraah, Santiago, and Lionel. They have origin pointing to, say Github, and they have a production server running their application. The project has only two tables: schema_mi grations and students. The former should sound familiar as it is used by ActiveRecord to keep track of whether migrations have been executed. The latter has 2 columns: first_name and last_name.

1. Lionel creates a migration with timestamp 0107 (db/migrate/0107_add_nickname_to_students.rb) locally that adds the column nick name to the students table. He does this in his local environment only.

2. Some time later, Santiago creates a migration with timestamp 0108 that adds the column nationality to the same students table. Also in his local environment.

At this point, this is what Lionel has in his local database:

TABLE: schema_migrations
+-------------------+
| version           |
+-------------------+| ...               |
+------- ... -------+
| 0107              |
+-------------------+

TABLE: students
+------------+-----------+------------+
| first_name | last_name | nickname   |
+------------+-----------+------------+
| ...        | ...       | ...        |

And this is what Santiago has:

TABLE: schema_migrations
+-------------------+
| version           |
+-------------------+
| ...               |
+------- ... -------+
| 0108              |
+-------------------+

TABLE: students
+------------+-----------+-------------+
| first_name | last_name | nationality |
+------------+-----------+-------------+
| ...        | ...       | ...         |

3. Santiago merges his code to main, pushes to origin, and deploys the code on the production servers.

At this point the structure of the database in production and the content in the schema_migrations table is the same as Santiago’s:

TABLE: schema_migrations
+-------------------+
| version           |
+-------------------+
| ...               |
+------- ... -------+
| 0108              |
+-------------------+

TABLE: students
+------------+-----------+-------------+
| first_name | last_name | nationality |
+------------+-----------+-------------+
| ...        | ...       | ...         |

4. Origin’s main is ahead of Lionel’s private branch, so he decides to pull the latest main and merge it onto his private branch.

This step usually uncovers a conflict in the schema.rb file. Lionel decides to fix the conflict manually, and once it is resolved he runs rake db: migrate on his now up-to-date private branch. Lionel’s database looks like this after this step:

TABLE: schema_migrations
+-------------------+
| version           |
+-------------------+
| ...               |
+------- ... -------+
| 0107              |
+-------------------+
| 0108              |
+-------------------+

TABLE: students
+------------+-----------+------------+-------------+
| first_name | last_name | nickname   | nationality |
+------------+-----------+------------+-------------+
| ...        | ...       | ...        | ...         |

5. Lionel merges his code to main, pushes to origin, and deploys the code on the production servers.

This is how the production database would look like after this step:

TABLE: schema_migrations
+-------------------+
| version           |
+-------------------+
| ...               |
+------- ... -------+
| 0108              |
+-------------------+
| 0107              |
+-------------------+

TABLE: students
+------------+-----------+-------------+-------------+
| first_name | last_name | nationality | nickname    |
+------------+-----------+-------------+-------------+
| ...        | ...       | ...         | ...         |

Notice that the order of the last two columns of the students table is different between production and Lionel’s databases.

6. Santiago pulls the latest code from main and runs the migrations locally. Since his local database already has migration 0108 in his schema_m igrations file, rake db:migrate for Santiago will only run migration 0107. Ifraah, who hasn’t run any migrations yet, pulls the latest code and runs both migrations.

This is what Santiago will see in his database:

TABLE: schema_migrations
+-------------------+
| version           |
+-------------------+
| ...               |
+------- ... -------+
| 0108              |
+-------------------+
| 0107              |
+-------------------+

TABLE: students
+------------+-----------+-------------+-------------+
| first_name | last_name | nationality | nickname    |
+------------+-----------+-------------+-------------+
| ...        | ...       | ...         | ...         |

And this is what Ifraah will see:

TABLE: schema_migrations
+-------------------+
| version           |
+-------------------+
| ...               |
+------- ... -------+
| 0107              |
+-------------------+
| 0108              |
+-------------------+

TABLE: students
+------------+-----------+------------+-------------+
| first_name | last_name | nickname   | nationality |
+------------+-----------+------------+-------------+
| ...        | ...       | ...        | ...         |

Production’s database has the column nationality first and nickname second. However, anyone running the migrations locally will have these 2 columns switched compared to Production.

How to manually fix an inconsistent schema.rb that diverted from the current production database schema.
To fix schema.rb such that it actually reflects the state of production, we need to run rake db:migrate on a database that has the desired

schema. Here is the step by step.
1. Create a new, temporary, local database.

 CREATE DATABASE schema_fix_development;

2. Get “create table” statements from production.

 mysqldump -h <prod-host> -u <prod-username> --password=****************
--no-data --compact <database-name> > ~/tmp/db/prod-create-tables.sql

3. Run “create table” commands on the database you just created in step 1.

   mysql -u root schema_fix_development < ~/tmp/db/prod-create-tables.sql

4. Get all migration versions from the main branch (make sure main doesn’t have a migration that hasn’t been run in prod yet)

for migration_file in `ls ./db/migrate | sort`; do echo $migration_file
| grep '^[0-9]\+' -o; done > ~/tmp/migration_versions.txt
for migration_version in `cat ~/tmp/migration_versions.txt`; do echo
"INSERT INTO schema_migrations VALUES ('$migration_version');"; done > ~
/tmp/migration_versions.sql

5. Populate the schema migrations table with all the migrations

mysql -u root schema_fix_development < ~/tmp/migration_versions.sql

6. Point local server to this new schema_fix_development database. This is done by changing the database.yml file or the DATABASE_URL envar.

7. Run rake db:migrate

8. The previous step is going to make changes to your schema.rb. Those changes reflect your deviation from production! Commit and merge those changes.

9. Once the changes are committed, fix your local database:

9.1. Make sure you roll back any migrations that you might have in your private branches, and go back to main

9.2. Run rake db:migrate. If it doesn’t change schema.rb congratulations, you’re done and your local database reflects what’s in production.

9.3. If changes are made to your schema.rb, take a specific change and revert it manually in your own database

9.4. Go back to step 9.2

How do we prevent it from happening moving forward?

The solution is to ensure that migrations run in the same order everywhere. The problem in the example above was during step 4. Instead of blindly updating his local branch, Lio should have take these steps:

git checkout private-branch && rake db:migrate:
down VERSION=0107 && git checkout . && git clean -
fd

git checkout main && rake db:migrate

git checkout private-branch && mv db/migrate
/0107_add_nickname_to_students.rb db/migrate
/0109_add_nickname_to_students.rb && git add . &&
git commit -m 'Update migration version'

git checkout private-branch && git merge main --no-
edit

git checkout private-branch && rake db:migrate &&
git add . && git commit -m 'Update schema.rb'

Before merging anything that introduces a migration to main, make sure that:

• No migrations in the head branch should have the same or earlier version from the one specified in schema.rb in the base branch.
• The schema version in schema.rb must always go up. It should never be either left untouched or going back in time.

Taking these steps will ensure that your schema.rb file never gets out of sync with your production database structure.

If you had issues with this before and came up with a different solution, please leave a comment below. We’re interested to know what other people do about this.