Rails: Keeping structure.sql stable between developers

Why Rails has multiple schema formats

When you run migrations, Rails will write your current database schema into db/schema.rb. This file allows to reset the database schema without running migrations, by running rails db:schema:load.

The schema.rb DSL can serialize most common schema properties like tables, columns or indexes. It cannot serialize more advanced database features, like views, procedures, triggers or custom ditionaries. In these cases you must switch to a SQL based schema format:

Copy# in application.rb
config.active_record.schema_format = :sql

This now creates a file db/structure.sql instea of db/schema.rb. As structure.sql is created by a database-specific tool like pg_dump, you can now serialize every single schema property, including proprietary features. Unfortunately using structure.sql comes with some disadvantages.

Why structure.sql can churn wildly

A big drawback of db/structure.sql is that different developer PCs or servers may create slightly different SQL output. Since this file is rewritten with every migration run, this can generate a lot of noise and merge conflicts in your repository.

Possible reasons for this churn include:

• Two developers use different versions of the database server (e.g. PostgreSQL 16 vs. 17).
• Two developers use the same version of the database server, but different versions of their CLI tools (e.g. pg_dump 16 vs. 17).
• Two developers use the same versions of everything, but one developer has sourced a dump from a production server, and that production server has a slightly different schema. This can happen due to database server upgrades in the past, or by using advanced DB features while still on schema.rb.

Normalizing structure.sql

It is possible to normalize structure.sql, but it involves pain. Because of this I recommend to stick with db/schema.rb as long as possible.

A simple approach is to run your own code after the db:schema:dump task. For example, this patch will remove a line SET transaction_timeout=0;, which is only understood by PostgreSQL 17:

Copy# in lib/tasks/normalize_structure_sql.rake`
Rake::Task['db:schema:dump'].enhance do
  path = 'db/structure.sql'
  structure = File.read(path)
  structure.sub!('SET transaction_timeout=0;', '')
  File.write(path, structure)
end

Many normalization rules have already been implemented by the activerecord-clean-db-structure Show archive.org snapshot gem. It also works with multiple databases.