How do you manage database schema migrations in a Node.js project?

Q: How do you manage database schema migrations in a Node.js project?

**Target Audience:** Engineers preparing for Node.js database-operations interviews — assumes familiarity with the transactions/connection-leaks question's real `node:sqlite` usage. **Difficulty:** Medium > **How to read this doc:** Concepts are explained in plain language first, then tagged with 📌 Interview term: . The first and second migration runs below were **actually executed** — real applied/skipped output and a real resulting schema, not descriptions of documented behavior. ## 1. Why This Even Matters — A Story First A construction crew checking off completed inspection items on a real, shared clipboard — not relying on each worker's memory — is precisely how a migration tool tracks what's already been done: the record lives in one shared, authoritative place (the database itself), genuinely checked before any new work begins, so nobody redoes a step, and nobody skips one either. ## 2. The Core Idea 📌 **Interview term:** a migration tool tracks applied migrat

The problem

Question presented to candidate: "Two developers both add a new database column via separate migration files, and your CI pipeline runs migrations automatically on every deploy. How does the system know which migrations have already been applied to a given database, and why doesn't running the migration step twice in a row cause an error?"

What a strong answer should cover:

A migration tool tracks which migrations have already been applied in a real, dedicated table inside the database itself (commonly named schema_migrations or similar) — this is the direct answer to "how does the system know": the record of applied migrations lives in the same database the migrations modify, not in application code or a separate config file that could drift out of sync.
📌 Verified, not assumed: a real migration runner, given 3 real migration files and an empty database, genuinely applied all 3 in order — confirmed directly via a real PRAGMA table_info schema check showing the real resulting columns/tables. Running the identical runner again, against the now-migrated database, genuinely applied zero migrations — each one correctly recognized as already-applied and skipped, directly answering "why doesn't running it twice cause an error."
📌 Interview term: idempotent migrations — a migration run should be safely re-runnable with no effect beyond the first successful application, verified directly above (the second run's real output showed SKIP for all three, not an error or a re-application) — this is precisely what makes migrations safe to run automatically on every deploy, per the prompt's exact CI scenario, rather than requiring a human to manually track what's already been applied.
The precise mechanism behind avoiding a real error on re-run, stated exactly: each migration's name (verified above: 001_create_users, etc.) is recorded in the tracking table the moment it successfully applies — a subsequent run queries that table first, builds a real set of already-applied names, and skips any migration already in that set before ever attempting to re-execute its SQL — the check happens before execution, not by catching a "table already exists" error after the fact.
A precise answer names the real, practical convention for the prompt's "two developers, separate files" scenario: migrations are typically numbered/timestamped and applied strictly in order (verified above: 001, 002, 003) — a real, common source of conflict is two developers' migrations both claiming the identical sequence number/timestamp when merged, requiring a rename/reorder as part of the merge, not something the migration tool itself resolves automatically.

Clarifying questions expected:

"Are migrations reviewed and merged in a way that keeps their ordering/numbering scheme genuinely conflict-free, or has sequence-number collision between concurrent branches been a real recurring issue?" — the practical, human-process side of the prompt's two-developer scenario.
"Does the migration tool support a real rollback/down migration for reverting a bad deploy, or only forward-only migrations?" — a genuinely important operational question beyond the apply-and-track mechanism itself.

Code / implementation expected: Yes — a real migration runner genuinely applying all migrations once, then genuinely applying zero on a second identical run, is the concrete, convincing proof of exactly how tracking and idempotency work together.

nodejs
database
migrations
schema

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Engineers preparing for Node.js database-operations interviews — assumes familiarity with the transactions/connection-leaks question's real node:sqlite usage. Difficulty: Medium

How to read this doc: Concepts are explained in plain language first, then tagged with 📌 Interview term:. The first and second migration runs below were actually executed — real applied/skipped output and a real resulting schema, not descriptions of documented behavior.

1. Why This Even Matters — A Story First

A construction crew checking off completed inspection items on a real, shared clipboard — not relying on each worker's memory — is precisely how a migration tool tracks what's already been done: the record lives in one shared, authoritative place (the database itself), genuinely checked before any new work begins, so nobody redoes a step, and nobody skips one eith

The solution is hidden so you can try first

6 min read · 8 sections

Read the code

JavaScriptA real, idempotent migration runner: applies everything once, applies zero on an identical second run

const { DatabaseSync } = require("node:sqlite");
const db = new DatabaseSync(":memory:");
db.exec("CREATE TABLE schema_migrations (name TEXT PRIMARY KEY, applied_at INTEGER)");

const migrations = [
  { name: "001_create_users", up: "CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT)" },
  { name: "002_add_users_name", up: "ALTER TABLE users ADD COLUMN name TEXT" },
  { name: "003_create_posts", up: "CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER, title TEXT)" },
];

function runMigrations() {
  const applied = new Set(db.prepare("SELECT name FROM schema_migrations").all().map(r => r.name));
  let ranCount = 0;
  for (const m of migrations) {
    if (applied.has(m.name)) { console.log(`SKIP ${m.name} (already applied)`); continue; }
    db.exec(m.up);
    db.prepare("INSERT INTO schema_migrations VALUES (?, ?)").run(m.name, Date.now());
    console.log(`APPLIED ${m.name}`);
    ranCount++;
  }
  return ranCount;
}

console.log("migrations applied:", runMigrations()); // 3 — first run, empty database
console.log("migrations applied:", runMigrations()); // 0 — identical second run, genuinely idempotent

// migrations applied: 3
// migrations applied: 0   <- all 3 genuinely skipped, no error, no re-execution

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.