Migrating structure.sql in Complex Rails Apps

By USEO Engineering Team December 25, 2025

BLUF (Bottom Line Up Front): The default Rails schema.rb file is database-agnostic and cannot represent advanced PostgreSQL features like Views, Stored Procedures, Triggers, or complex constraints. If your legacy application relies on these features, you must migrate your schema format to structure.sql. This uses pg_dump natively, ensuring your test environments perfectly match production.

Phase 1: The Limitations of schema.rb

Glossary entry: Missing Materialized Views.

When scaling a legacy Rails application, engineers often push complex data integrity logic or heavy reporting down to the database level using PostgreSQL-specific features.

Synthetic Engineering Context: The Test Environment Failure

A developer adds a database trigger to automatically calculate a materialized view. They run rake db:migrate, and it works locally. However, when the CI runs rake db:test:prepare, the test suite fails.

# CI Failure Log
Failure/Error: MaterializedReport.refresh!
ActiveRecord::StatementInvalid:
  PG::UndefinedTable: ERROR:  relation "materialized_reports" does not exist

Because schema.rb is written in pure Ruby, it simply ignores the SQL CREATE MATERIALIZED VIEW or CREATE TRIGGER statements executed in the migration. The test database is built incompletely.

Phase 2: Transitioning to structure.sql

Switching to the SQL format forces Rails to use the database’s native dumping and loading tools, capturing the exact state of the schema.

Execution: Configuration Change

Update your application configuration to change the schema format.

# config/application.rb
module LegacyApp
  class Application < Rails::Application
    # Rails 6.0 and below:
    # config.active_record.schema_format = :sql
    
    # Rails 6.1+:
    config.active_record.schema_format = :sql
  end
end

Execution: Generating the Structure

Run the database tasks to generate the new file and delete the old one.

$ bin/rails db:structure:dump
$ rm db/schema.rb

The resulting db/structure.sql file will contain raw PostgreSQL DDL, perfectly preserving all constraints, triggers, and views.

-- Extract from structure.sql
CREATE TRIGGER update_audit_trail
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_changes();

Phase 3: Next Steps & Risk Mitigation

While structure.sql solves environment parity, it can create noisy Git diffs because pg_dump output can vary slightly between PostgreSQL minor versions on developers’ machines. Ensuring your team uses uniform database versions (e.g., via Docker) is critical.

Need Help Stabilizing Your Legacy App? Pushing logic to the database can dramatically improve performance, but it complicates the Rails monolith architecture. Our team at USEO can help you restructure your data models and leverage advanced Postgres features correctly.