Rails Migrations: schema.rb vs structure.sql and When Each Wins

Rails keeps two things in db/: the migrations, which are the history of changes, and a schema file, which is the current result of that history. The schema file comes in two formats. schema.rb is a Ruby DSL, portable and readable but limited to what Active Record can express. structure.sql is a full SQL dump produced by your database's own tools, complete but engine-specific. Stay on schema.rb until you use a database feature it cannot describe; the moment you do, switch to structure.sql and do not look back.

How Active Record migrations work#

A migration is a Ruby class in db/migrate, prefixed with a UTC timestamp and inheriting from a versioned base class:

class CreateOwnersAndPets < ActiveRecord::Migration[8.1]
  def change
    create_table :owners do |t|
      t.string :first_name, limit: 50, null: false
      t.string :last_name,  limit: 50, null: false
      t.string :address,    limit: 255
      t.string :city,       limit: 80
      t.string :telephone,  limit: 20
    end

    create_table :pets do |t|
      t.string     :name, limit: 50, null: false
      t.date       :birth_date
      t.references :owner, null: false, foreign_key: true
      t.timestamps
    end
  end
end

The version in Migration[8.1] pins the behaviour of the DSL to the Rails version the migration was written for, so an old migration keeps producing the same schema after an upgrade. Each applied timestamp is recorded in the schema_migrations table; ar_internal_metadata stores the environment the database was created for, which is what stops you from accidentally running db:drop against production data with a development configuration.

The everyday commands:

bin/rails generate migration AddTelephoneToOwners telephone:string
bin/rails db:migrate
bin/rails db:rollback STEP=1
bin/rails db:migrate:status

After a successful db:migrate in development, Rails dumps the schema file automatically, which is why the schema file shows up in the same commit as the migration.

change, up and down#

Most migrations only need change. Active Record knows how to reverse create_table, add_column, add_index, add_reference, add_foreign_key, rename_column and the other common operations, so db:rollback works without extra code. Two operations are not reversible from change: remove_column without a type, because Rails cannot recreate a column it knows nothing about, and execute with raw SQL.

For those, either give Rails the missing information (remove_column :pets, :nickname, :string) or write explicit directions:

class AddVisitsCountToOwners < ActiveRecord::Migration[8.1]
  def up
    add_column :owners, :visits_count, :integer, default: 0, null: false
    execute <<~SQL
      UPDATE owners
      SET visits_count = (SELECT COUNT(*) FROM visits
                          JOIN pets ON pets.id = visits.pet_id
                          WHERE pets.owner_id = owners.id)
    SQL
  end

  def down
    remove_column :owners, :visits_count
  end
end

reversible do |dir| ... end lets you mix both styles in one change, and revert can undo an earlier migration class wholesale. Whichever form you use, run db:rollback once before opening the pull request; a migration whose down has never executed is a migration whose down does not work.

schema.rb: portable, readable, lossy#

db/schema.rb is what Rails writes by default. It is the same DSL as the migrations, collapsed into one file:

ActiveRecord::Schema[8.1].define(version: 2024_01_03_000003) do
  create_table "owners", force: :cascade do |t|
    t.string "first_name", limit: 50, null: false
    t.string "last_name", limit: 50, null: false
    t.string "address", limit: 255
    t.string "city", limit: 80
    t.string "telephone", limit: 20
  end

  create_table "pets", force: :cascade do |t|
    t.string "name", limit: 50, null: false
    t.date "birth_date"
    t.bigint "owner_id", null: false
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["owner_id"], name: "index_pets_on_owner_id"
  end

  add_foreign_key "pets", "owners"
end

Its strengths are real. Anyone on the team can read it in a diff. It loads on any adapter, which lets you develop on one engine and test on another. It is what db:schema:load uses to build a database without replaying every migration, and what db:test:prepare uses for the test database.

Its weakness is that it can only express what the DSL knows. Over the years the DSL has learned a lot: foreign keys, check constraints, partial and expression indexes, PostgreSQL enums and extensions are all dumped now. What still falls through, depending on the adapter and Rails version, includes database functions and triggers, views, materialized views, custom types beyond enums, row-level security policies, sequences you configured by hand, and vendor-specific storage options. Those objects exist in your database and are missing from the file Rails uses to recreate it. The test database, built from schema.rb, therefore differs from production, and the first sign is a test that passes and a deploy that fails.

structure.sql: complete and engine-specific#

Switching is one line of configuration:

# config/application.rb
config.active_record.schema_format = :sql

From then on Rails shells out to pg_dump, mysqldump or the SQLite equivalent and writes db/structure.sql, followed by INSERT statements into schema_migrations for every applied version. Everything the database knows is in the file: functions, triggers, views, comments, extension versions, exact index definitions. db:schema:load now pipes that file back through the database's own client.

The price is that the file is tied to one engine and, to a degree, one version of the dump tool. A developer with a newer pg_dump than CI will produce a different header and different formatting, which shows up as noise in every diff. Two mitigations help: pin the client tools in a container or via your package manager, and set ActiveRecord::Tasks::DatabaseTasks.structure_dump_flags to drop comments and ownership statements. The file will still be longer and harder to read than schema.rb, but it is correct.

Choosing between them#

The decision rule that holds up: use schema.rb for as long as it reproduces your production database exactly, and switch the day it does not. Do not switch pre-emptively; do not stay out of habit after you have added your first trigger. Check with a fresh database: run db:schema:load, then compare the result against a database built by running all migrations. If they differ, schema.rb is already lying to you.

Pipelines: db:prepare, CI and strong_migrations#

bin/rails db:prepare is the command to put in setup scripts and pipelines. If the database does not exist it creates it, loads the schema file and runs seeds; if it exists it runs pending migrations. That gives new developers and fresh CI containers a database in seconds instead of replaying years of migrations, and existing databases the incremental path.

Because schema loading skips the migrations, the migrations themselves should still be exercised somewhere. A CI job that runs db:create db:migrate from scratch, then db:rollback STEP=3 and db:migrate again, catches a broken down and a migration that depended on a model method that no longer exists. Old migrations that reference application models are a well-known trap: define a minimal model class inside the migration instead of using the real one.

Safer migrations with strong_migrations#

The strong_migrations gem intercepts dangerous operations before they run: adding a column with a default on an old PostgreSQL, adding an index without algorithm: :concurrently, adding a NOT NULL constraint that validates the whole table under a lock, renaming a column that running code still reads. It fails the migration with an explanation and the safe alternative, which is usually a two-step version of the same change. On a table with real traffic, that is the difference between a routine deploy and an outage; the general pattern is described in Zero-Downtime Database Migrations.

Team friction on the schema file#

The schema file is the most conflicted file in most Rails repositories, because every migration touches it and the version number at the top changes with each one. Practices that reduce the pain:

• Regenerate rather than hand-merge. After resolving a conflict, run db:migrate (or db:schema:dump) and commit what Rails writes, so the file always reflects a real database.
• Keep the dump deterministic: pin dump tools for structure.sql, and avoid running db:migrate against a database with local experiments in it.
• Add a CI check that the committed schema file matches a freshly dumped one. A stale schema file is the Rails form of schema drift.

Where SchemaDrawer fits#

Neither schema file is easy to picture once the application has fifty tables. SchemaDrawer takes the files in db/migrate, runs them with Active Record's migrator in Docker against a throw-away PostgreSQL, MySQL or SQLite of the version you choose, reads the resulting schema back and returns an SVG diagram of tables, columns, primary keys and foreign keys. Because it draws what the migrations actually produced, a missing foreign_key: true shows up as a reference without an arrow. The Active Record page lists the accepted layout, and the CI guide shows how to keep the diagram in the repository next to the schema file.

Key takeaways#

• Migrations are the history; the schema file is the current state that db:schema:load, db:prepare and the test database rely on.
• schema.rb is portable and readable but omits anything the DSL cannot express; structure.sql is complete but engine-specific.
• Switch to structure.sql the day production contains an object that schema.rb drops, and pin your dump tools.
• Use change where Rails can reverse it, explicit up and down otherwise, and roll back once before merging.
• Run migrations from scratch in CI, protect busy tables with strong_migrations, and regenerate the schema file instead of hand-merging it.