Skip to content

PATTERN Cited by 1 source

Sequential numbered migration files

Problem

Schema changes in a versioned-migration paradigm need to be applied in a specific order — column A must exist before the index on column A, table X must be dropped after table Y's FK to X is dropped, a backfill must run after the column it populates is added. The paradigm lacks a declarative dependency graph; the order is implicit in which script was authored first.

A naive implementation could track order via an in-database column (authored-at timestamp) or a checkout-time counter, but either approach requires the tool to read state before ordering — expensive and race-prone. The simpler mechanism is encode the order in the filename itself, so that any agent with a filesystem listing can determine the apply order without opening a database connection.

Shape

Each migration is a file whose filename begins with a monotonically-increasing sortable prefix — either a timestamp (YYYY_MM_DD_HHMMSS_…) or a zero-padded sequence number (001_…, 002_…) — and a descriptive suffix. The filesystem's lexical ordering of filenames gives the apply order for free. The tool's migrate command:

  1. Lists the migration directory.
  2. Sorts filenames lexicographically.
  3. Reads the tracking table to determine which files have been applied.
  4. Applies the sorted set's pending suffix in order, writing a tracking row after each success.

Canonical framing verbatim (Morrison II, 2023-04-05, PlanetScale):

"Those files are usually numbered in the order they need to be applied."

(Source: sources/2026-04-21-planetscale-versioned-schema-migrations)

Worked example — Laravel default scaffold

A fresh Laravel project ships with:

database/migrations/
├── 2014_10_12_000000_create_users_table.php
├── 2014_10_12_100000_create_password_resets_table.php
├── 2019_08_19_000000_create_failed_jobs_table.php
└── 2019_12_14_000001_create_personal_access_tokens_table.php

Filename convention: YYYY_MM_DD_HHMMSS_description.php. Lexical sort = chronological sort = apply order. Each file is immutable once authored — edit-after- apply would break the checksum (for tools that validate) or silently mismatch the applied state (for tools that don't). New schema changes = new files appended, never in-place edits.

On artisan migrate, Laravel's console output enumerates each applied file with its timing:

2014_10_12_000000_create_users_table .................. 45ms DONE
2014_10_12_100000_create_password_resets_table ........ 64ms DONE
2019_08_19_000000_create_failed_jobs_table ............ 38ms DONE
2019_12_14_000001_create_personal_access_tokens_table . 44ms DONE

Post-apply, the tracking table has one row per file, all stamped batch = 1.

Filename convention trade-offs

Three common conventions, each with characteristic failure modes:

  • Timestamp prefix (Laravel, Rails, Django, Alembic with timestamps): YYYYMMDDHHMMSS_description.py. Advantage: two developers branching from the same HEAD won't collide unless they author in the same second. Disadvantage: timestamps from different timezones can interleave surprisingly; clock skew between developer machines can produce out-of-order files.
  • Sequential integer (Flyway's V1__, V2__, sequence-per-branch): V001__create_users.sql. Advantage: trivially human-readable ordering. Disadvantage: merge conflicts are guaranteed when two developers each author V042__… on separate branches. Teams usually resolve this by renaming on merge, which breaks immutability.
  • Hybrid (timestamp + sequence): Laravel's 2019_12_14_000001_… — timestamp to the second plus a 6-digit sequence suffix. Second-level resolution covers most collision cases while the sequence suffix absorbs the edge case.

Composition with Git semantics

The post frames versioned migrations as "very similar to a system you may already be familiar with: git." The filename-based ordering inherits Git's properties:

  • Forward-only immutable history — each new commit appends to the chain.
  • Branching produces divergent chains — two developers branching from the same HEAD each add new files; merging produces a linear chain with both files interleaved by timestamp.
  • Replay from ancestor — git log HEAD~N..HEAD has an analog: the tool replays files newer than the last applied row in the tracking table.

Caveat: unlike Git, the merge semantics are naive concatenation, not three-way merge. If two developers each add a column called nickname to users in files with adjacent timestamps, both migrations apply in sequence and the second one fails on "duplicate column" — the tool doesn't see a conflict, the database does. Canonicalised in the wiki as the "timestamp-collision failure mode" of versioned tooling.

When the naive pattern breaks down

  • Environments rebased onto a different HEAD (dev was on main, rebased onto feature/X, pulled new migrations whose timestamps are older than the ones already applied). Tool may either silently skip the older files (wrong: they never ran here) or re-apply them (wrong: they already ran on a sibling branch).
  • Copy-paste timestamp — two files with byte- identical filename prefix from shell-history paste. Tool applies one, the second fails as duplicate.
  • Filename renamed after apply — tracking table row refers to the old filename, tool can't find it to run a down().

Composition on PlanetScale

When PlanetScale's safe-migrations is on, the sequential-files-in-a-folder mechanism still works fine on a development branch — the versioned tool lists files, queries the dev branch's tracking table, applies the pending suffix. The interaction with the deploy request workflow is that production doesn't see the individual files at all; it sees the cumulative schema diff. The files remain the authoring artefact; the deploy request is the applying artefact.

See patterns/branch-based-schema-change-workflow.

Seen in

Last updated · 550 distilled / 1,221 read