DEV Community

Kaziu
Kaziu

Posted on

What Is Rails' structure.sql? What's It For?

When you work on a Rails project, you'll come across a huge file called db/structure.sql.
"What is this? It changes on its own, so am I allowed to touch it?" It's an easy file to be confused by, so here's a beginner-friendly rundown.


Bottom line: the DB's "finished blueprint"

In a nutshell, structure.sql is

a "finished blueprint (snapshot)" that bundles the SQL statements needed to recreate the current database structure entirely from scratch

What's more, it's a file that Rails generates and updates automatically; it's not something people write by hand.

Here's roughly what's inside:

CREATE TABLE `deals` (              -- Statement that creates the "deals" table
  `id` bigint unsigned NOT NULL,
  `name` varchar(255),
  ...
);

CREATE TABLE `contacts` ( ... );    -- Statement that creates the "contacts" table
...(continues for every table)...

-- At the end, a ledger of "which schema changes have been applied so far"
INSERT INTO `schema_migrations` VALUES
('20260806000000'),
('20260805000000'),
...
Enter fullscreen mode Exit fullscreen mode

The first part has CREATE TABLE for every table, and the end has a ledger (schema_migrations) of "how far the changes have been applied."


Background: how it relates to migrations

The shortcut to understanding structure.sql is to think about it together with migrations.

  • Migrations = the change history of the DB structure (= a stack of instructions for schema changes)
    • e.g. a file like "add a status column to the deals table," one file per change
    • File names are date + number, like 20260803000000
  • structure.sql = a snapshot of the final result after applying that entire history

As a diagram:

① A developer writes a migration file (instructions for the change)
         e.g. "add a status column to deals"

② Run db:migrate → the DB actually changes

③ ★At this point, Rails automatically updates structure.sql to "what the DB looks like now"★
Enter fullscreen mode Exit fullscreen mode

In other words, structure.sql is automatically updated as a by-product of db:migrate. As a rule, you don't edit it by hand.


What's it for?

The biggest reason it exists is that it's fast when you build a new DB from scratch.

When a new team member sets up their environment, or when you rebuild the test DB every time, you need to take the DB from "blank → latest shape." There are two ways to do that.

Method What it does Speed
Run all the migrations Runs hundreds of instruction files one by one, in order Slow
Load structure.sql Recreates the finished shape in one shot Fast

Why is running all the migrations slow?

Reason ①: It retraces history, so there's a lot of waste

Migrations are a change history, so running all of them recreates every past detour too.

① Create the deals table
② Add a status column to deals
③ Remove the status column from ②   ← changed our minds
④ Add a state column to deals
Enter fullscreen mode Exit fullscreen mode

All we actually want in the end is "no status, but a state column." But running everything faithfully recreates even the pointless round trip of creating it in ② and deleting it in ③. structure.sql only has the final result, so there are no detours.

Reason ②: They can only run one at a time, in sequence (overhead)

Migrations depend on order, so they can't be parallelized, and each one involves setup and teardown: "boot Rails → load code → convert to SQL → execute → record in the ledger." That piles up hundreds of times.
structure.sql just pours in one giant SQL file, so this repeated cost is nearly zero.

Reason ③: Rebuilding intermediate states adds up

Even for the same "add a state column," migrations rebuild intermediate states over and over: create table → add column → remove column → add a different column… It gets even slower when things like rebuilding indexes are involved.

An analogy

  • Running all migrations = watching a cooking show from episode 1, and recreating even the failed dishes along the way
  • structure.sql = looking at a photo and recipe of the finished dish and making just that

All you want is the finished dish, so the latter is way faster.


So you use them for different things

Neither structure.sql nor migrations is "better"; you use each one depending on the situation.

Situation What to use Why
Setting up a new environment, creating the test DB structure.sql (finished shape) No need to retrace history; you just want the latest state fast
Changing the live production DB Migrations (only the diff) You want to safely apply just "this change" while keeping existing data

A new member's environment setup is just "go from blank → latest shape," so structure.sql is the perfect fit.


Extra: schema.rb vs. structure.sql

Rails blueprints come in two formats, and each project picks one.

File Contents Characteristics
db/schema.rb A blueprint written in Ruby Simple. The Rails default
db/structure.sql A blueprint written in raw SQL Can faithfully express even triggers and DB-specific features

Projects that use features the Ruby schema.rb can't fully express, like MySQL triggers or detailed constraints, choose structure.sql.


Summary

Question Answer
What is structure.sql? A "finished blueprint" that bundles the SQL to recreate the whole current DB structure
Does Rails create it? Yes. It's automatically generated/updated when you run db:migrate
Do you write it by hand? As a rule, no. It's updated automatically
What's it good for? Instantly getting a new environment or test DB to the latest structure

Top comments (0)