Keeping your schema.rb file clean and consistent between environments

What issues does an inconsistent schema.rb file cause?

Having an inconsistent schema.rb file that doesn’t really reflect the structure in production can create an array of problems:

• Developers have no reliable way to know what the database structure really is without having access to the production database, which most developers at mid and large-sized companies don’t have.
• Tests usually run on their own database that is built from schema.rb, meaning that the unit tests wouldn’t use real assumptions.
• Every time you run rake db:migrate, you get the schema.rb file changed, leading to needing to disregard these changes.
• An inconsistent schema.rb doesn’t only cause practical problems but also impacts the overall quality standards of your engineering team. The broken window theory indicates that disorder creates an environment that encourages further disorder.

How are these inconsistencies introduced?

Most inconsistencies are introduced by running migrations in a different order on different databases. Here’s an example:

1. Developer Lionel creates a migration that adds the column nickname to the students table locally.

2. Later, Santiago creates a migration that adds the column nationality to the same students table in his local environment.

3. Santiago merges his code to main, which updates production with his migrations.

4. Developer Lionel pulls the latest main and merges it onto his branch, encountering conflicts in schema.rb.

5. After resolving the conflict and running the migrations, Lionel has a different versioning in schema.rb compared to production.

6. Future runs of migrations lead to inconsistencies in table column ordering.

How to manually fix an inconsistent schema.rb that has deviated from the production database schema

To fix schema.rb to reflect the state of production, follow these steps:

1. Create a new, temporary 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 new database:

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

4. Get all migration versions from the main branch:

for migration_file in `ls ./db/migrate | sort`; do echo $migration_file | grep '^[0-9]\+' -o; done > ~/tmp/migration_versions.txt

5. Populate the schema migrations table with all migrations:

for migration_version in `cat ~/tmp/migration_versions.txt`; do echo "INSERT INTO schema_migrations VALUES ('$migration_version');"; done > ~/tmp/migration_versions.sql

6. Point your local server to this new schema_fix_development database by updating your database.yml file or the DATABASE_URL environment variable. 7. Run rake db:migrate to update schema.rb. 8. Commit and merge changes to schema.rb after verifying the production schema. 9. Rollback migrations on your local database if needed and repeat steps until no differences exist.

How do we prevent these issues moving forward?

Ensure that migrations run in the same order on all databases. Follow these guidelines:

• Roll back your migration(s) locally before updating your branch.
• Ensure that rake db:migrate on main doesn’t alter schema.rb.
• Introduce new migrations with an updated version that exceeds the latest version in schema.rb.
• Use git operations to catch up with the main branch carefully.

Before merging any migration to the main branch, confirm:

• No migrations in your current branch should have the same or older version compared to schema.rb in the base branch.
• The schema version in schema.rb should always increase.