# 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](https://medium.com/@matryer/broken-windows-theory-why-code-quality-and-simplistic-design-are-non-negotiable-e37f8ce23dab) 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:
```sql
CREATE DATABASE schema_fix_development;
```
**2.** Get “create table” statements from production:
```bash
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:
```bash
mysql -u root schema_fix_development < ~/tmp/db/prod-create-tables.sql
```
**4.** Get all migration versions from the main branch:
```bash
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:
```bash
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.