In a Rails online-auction app, a trigger on bids created with execute is missing from db/schema.rb; why, and how does schema_format :sql fix it?
answer
- the dumper speaks only the Ruby DSL
- triggers, functions and views are skipped
- test and new databases load the file
- config.active_record.schema_format = :sql
- pg_dump writes, psql loads
basics
~20 sdb/schema.rb holds only what Active Record's Ruby DSL can express, so a trigger made with execute is silently left out and every database built from the file lacks it. With schema_format :sql, Rails writes db/structure.sql with the database's own dump tool, trigger included.
solid answer
~40 sThe Ruby schema dumper writes tables, columns, indexes, check constraints and foreign keys (plus PostgreSQL extensions and enums). Triggers, functions, views and other objects with no DSL equivalent are skipped without a warning. The migration that ran `execute "CREATE TRIGGER ..."` still created it in each migrated database, but every database **built from the file** lacks it: the test database, a new developer's `db:prepare`, `db:setup`, and since 8.0 `db:migrate` on an empty database. Setting `config.active_record.schema_format = :sql` (or `schema_format: sql` per database in `database.yml`) makes `db:schema:dump` write `db/structure.sql` with the adapter's tool, `pg_dump` on PostgreSQL, followed by `INSERT INTO schema_migrations` for the applied versions; `db:schema:load` then runs it through `psql`. The costs: the dump is adapter-specific, needs those client tools wherever it is dumped or loaded, and diffs are noisier.
code
ruby · 7 lines# config/application.rb
module AuctionHouse
class Application < Rails::Application
config.load_defaults 8.1
config.active_record.schema_format = :sql
end
endgo deeper
Know that schema.rb cannot hold everything the database has and that structure.sql is the alternative format.
Explain exactly what the Ruby dumper captures, which tasks build databases from the file, and how to switch the format.
Diagnose bugs that appear only in tests or fresh environments as schema-dump gaps, and plan the switch including client tools in CI and containers.
Decide how much behaviour lives in the database at all, given that every trigger or view commits the team to the SQL dump and its tooling.
## The symptom An online-auction app keeps `auctions.current_price_cents` in step with the highest bid using a PostgreSQL trigger. A migration created it with raw SQL: ```ruby class AddBidPriceTrigger < ActiveRecord::Migration[8.1] def up execute <<~SQL CREATE FUNCTION bump_auction_price() RETURNS trigger AS $$ BEGIN UPDATE auctions SET current_price_cents = GREATEST(current_price_cents, NEW.amount_cents) WHERE id = NEW.auction_id; RETURN NEW; END $$ LANGUAGE plpgsql; CREATE TRIGGER bids_bump_price AFTER INSERT ON bids FOR EACH ROW EXECUTE FUNCTION bump_auction_price(); SQL end def down execute "DROP TRIGGER bids_bump_price ON bids; DROP FUNCTION bump_auction_price();" end end ``` Development and production behave. Then the test suite starts failing on price assertions, and a new teammate's local database shows prices stuck at zero. Searching `db/schema.rb` for `bids_bump_price` finds nothing. ## Why the Ruby dump drops it `db/schema.rb` is written by Active Record's schema dumper. It asks the database for its structure and writes each part it **knows how to express in the migration DSL**: - tables and columns (it iterates tables only, so views are not dumped); - indexes, check constraints and foreign keys; - on PostgreSQL, extensions, enum types and schemas; - on SQLite, virtual tables. Everything else (triggers, functions and stored procedures, views, custom sequences) has no DSL form, and the dumper skips it **silently**. The `execute` call in the migration ran its SQL against the database; nothing records that SQL anywhere except the migration file, and the dump never reads migration files. ## Who loses the trigger Any database created from the schema file instead of by running that migration: 1. the **test database**, which Rails rebuilds from the schema file when it is out of date; 2. a new machine running `bin/setup`, which calls `db:prepare` and loads the schema; 3. `db:setup` and `db:reset`; 4. since Rails 8.0, plain `db:migrate` against an empty database, which loads the schema first and marks the trigger's migration as already applied. Databases that actually ran `AddBidPriceTrigger` keep the trigger, which is why the bug hides in development and production. ## Switching to structure.sql 1. Set the format in `config/application.rb`: `config.active_record.schema_format = :sql`. (A single database can use `schema_format: sql` in its `database.yml` entry instead.) 2. Run `bin/rails db:schema:dump`, or any migration task. Rails writes `db/structure.sql` using the adapter's own tool: `pg_dump` for PostgreSQL, `mysqldump` for MySQL, the `sqlite3` shell for SQLite. 3. Rails appends `INSERT INTO "schema_migrations" (version) VALUES ...` for every applied version, so a loaded database knows what has run. 4. Delete `db/schema.rb` and commit `db/structure.sql`. 5. From now on `db:schema:load` executes the SQL file (through `psql` on PostgreSQL), recreating the trigger and function exactly. ## What :sql costs | Concern | `:ruby` (`schema.rb`) | `:sql` (`structure.sql`) | |---|---|---| | Captures triggers, functions, views | no | yes | | Portable across adapters | yes | no: written in the database's dialect | | Needs database client tools | no | yes, wherever it is dumped or loaded (CI, containers, laptops) | | Diff and merge noise | low | higher: tool settings and dump-tool versions show up | | Readability as a model summary | good | harder | The client tool is the most common surprise: a CI image or a slim container without `pg_dump` and `psql` can migrate fine but fails at the dump or load step. ## Living with structure.sql - **Regenerate, do not hand-merge.** The end of the file is one `INSERT INTO "schema_migrations"` statement listing every applied version, newest first, so two branches that each add a migration conflict there. As with `schema.rb`, run `db:migrate` on the merged code and commit the regenerated file. - **Pin the client tools.** Developers dumping with different `pg_dump` versions produce churn in unrelated lines; matching the client version to the server keeps diffs to real changes. - **Pass extra dump flags centrally.** `ActiveRecord::Tasks::DatabaseTasks.structure_dump_flags` adds options to the dump command, for example to leave out tablespaces, so every machine writes the same file. - **Make the migration reversible anyway.** The SQL dump captures the trigger, but rolling back the migration still needs the `down` (or a `reversible` block) that drops it. ## Choosing Use `:sql` when the database holds behaviour the DSL cannot express and you rely on it in tests and new environments. Stay on `:ruby` when the schema is plain tables, indexes and constraints. Then nothing is lost, and the file stays portable and easy to review.
- Why did the trigger survive in production but not in the test database?Production ran the migration, so the `execute` created the trigger there. The test database is rebuilt from the schema file, which never contained it. Anything built by loading the file, rather than by running each migration, gets only what the Ruby dumper wrote.
- What breaks when CI switches to structure.sql without changing its image?Dumping calls the adapter's command-line tool (`pg_dump` for PostgreSQL) and loading pipes the file through `psql`. If the CI image lacks those clients, or has much older ones than the server, the dump or load step fails even though migrations themselves ran.
- schema.rb drops the trigger only because the migration lacked a down method
- With schema_format :sql, Rails translates triggers into Ruby DSL calls
- db/structure.sql can be loaded into any database adapter, just like schema.rb
- schema.rb also leaves out foreign keys and check constraints
- The test database has the trigger because tests replay every migration