How to check database schema version

Read both records the database keeps and compare them with the files on disk: sqlite3 app.db "SELECT version, name, applied_at FROM schema_migrations ORDER BY version" and PRAGMA user_version. A row states that a runner executed a file. It does not state that the objects that file creates are present.

Why check this

Run this at the start of any test pass against a shared environment, and again when a bug reproduces on staging and not on your machine. The version the database reports decides whether you are testing the build you think you are testing.

The failure it prevents is a two-day hunt. A tester files a defect against a column that the release notes describe, the developer cannot reproduce it, and the environment is three migrations behind. The application does not announce that. The migrations table does, in one query.

All output below came from SQLite 3.50.6 and Node 22.23.2 on one Windows machine. The records have different names on other engines and other tools, the reading of them does not.

Prerequisites

• SQLite 3.50.6 on the command line, and Node 22.23.2 for the runner. node:sqlite is experimental in Node 22 and prints a warning on stderr, which --no-warnings removes. See PRAGMA user_version.
• The runner. It records a version, a name, a timestamp and a checksum of the file it ran, and it sets user_version after every step.

// migrate.mjs: a migration runner small enough to read. node:sqlite, Node 22.
import { DatabaseSync } from 'node:sqlite';
import { readdirSync, readFileSync } from 'node:fs';
import { createHash } from 'node:crypto';

const [, , cmd = 'status', arg] = process.argv;
const db = new DatabaseSync(process.env.DB ?? 'app.db');
db.exec(`CREATE TABLE IF NOT EXISTS schema_migrations (version INTEGER PRIMARY KEY,
  name TEXT NOT NULL, applied_at TEXT NOT NULL, checksum TEXT NOT NULL)`);

const sum = (p) => createHash('sha256').update(readFileSync(p)).digest('hex').slice(0, 12);
const all = readdirSync('migrations').filter((f) => f.endsWith('.up.sql')).sort()
  .map((f) => ({ version: Number(f.slice(0, 3)), name: f.replace('.up.sql', ''),
    up: `migrations/${f}`, down: `migrations/${f.replace('.up.sql', '.down.sql')}` }));
const applied = () => db.prepare('SELECT * FROM schema_migrations ORDER BY version').all();

function run(m, dir) {
  db.exec('BEGIN');
  try {
    db.exec(readFileSync(dir === 'up' ? m.up : m.down, 'utf8'));
    if (dir === 'up') db.prepare('INSERT INTO schema_migrations VALUES (?,?,?,?)')
      .run(m.version, m.name, new Date().toISOString().slice(0, 19) + 'Z', sum(m.up));
    else db.prepare('DELETE FROM schema_migrations WHERE version = ?').run(m.version);
    db.exec('COMMIT');
  } catch (e) {
    db.exec('ROLLBACK');
    console.error(`FAIL ${dir} ${m.name}: ${e.message}`);
    process.exit(1);
  }
  const top = applied().at(-1)?.version ?? 0;
  db.exec(`PRAGMA user_version = ${top}`);
  console.log(`${dir}   ${m.name}`);
}

if (cmd === 'up') {
  const done = new Set(applied().map((r) => r.version));
  const target = arg ? Number(arg) : Infinity;
  for (const m of all) if (!done.has(m.version) && m.version <= target) run(m, 'up');
} else if (cmd === 'down') {
  const target = Number(arg ?? 0);
  for (const r of applied().reverse()) {
    if (r.version > target) run(all.find((m) => m.version === r.version), 'down');
  }
} else {
  const done = new Map(applied().map((r) => [r.version, r]));
  for (const m of all) {
    const r = done.get(m.version);
    const state = !r ? 'pending' : r.checksum === sum(m.up) ? 'applied' : 'CHANGED';
    console.log(`${String(m.version).padStart(3, '0')}  ${state.padEnd(8)} ${m.name}`);
  }
  console.log(`user_version = ${db.prepare('PRAGMA user_version').get().user_version}`);
}

• Four migrations in migrations/, numbered so their order is the sort order of the file names.

-- migrations/001_users.up.sql
CREATE TABLE users (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT '1970-01-01'
);
CREATE INDEX idx_users_name ON users(name);

-- migrations/001_users.down.sql
DROP INDEX idx_users_name;
DROP TABLE users;

-- migrations/002_email.up.sql
ALTER TABLE users ADD COLUMN email TEXT;
CREATE INDEX idx_users_email ON users(email);

-- migrations/002_email.down.sql
DROP INDEX idx_users_email;
ALTER TABLE users DROP COLUMN email;

-- migrations/003_email_required.up.sql
CREATE TABLE users_new (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT '1970-01-01',
  email      TEXT NOT NULL DEFAULT ''
);
INSERT INTO users_new (id, name, created_at, email)
  SELECT id, name, created_at, COALESCE(email, '') FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
CREATE INDEX idx_users_name ON users(name);
CREATE INDEX idx_users_email ON users(email);

-- migrations/003_email_required.down.sql
CREATE TABLE users_old (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT '1970-01-01',
  email      TEXT
);
INSERT INTO users_old (id, name, created_at, email)
  SELECT id, name, created_at, email FROM users;
DROP TABLE users;
ALTER TABLE users_old RENAME TO users;
CREATE INDEX idx_users_name ON users(name);

-- migrations/004_orders.up.sql
CREATE TABLE orders (
  id      INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  total   NUMERIC NOT NULL DEFAULT 0
);
CREATE INDEX idx_orders_user ON orders(user_id);

-- migrations/004_orders.down.sql
DROP TABLE orders;

• Three databases standing in for three environments. stage.db gets the last migration copied into its record without being run, which is what a mark-as-applied or baseline command does.

DB=dev.db node --no-warnings migrate.mjs up
DB=ci.db node --no-warnings migrate.mjs up 3
DB=stage.db node --no-warnings migrate.mjs up 3
sqlite3 stage.db "ATTACH 'dev.db' AS dev; INSERT INTO schema_migrations SELECT * FROM dev.schema_migrations WHERE version = 4; DETACH dev; PRAGMA user_version = 4;"

Steps

1. 1

   Step 1.

   Read the record the runner keeps, without the runner.

   sqlite3 -header -column dev.db "SELECT version, name, applied_at, checksum FROM schema_migrations ORDER BY version;"
   

   version  name                applied_at            checksum
   -------  ------------------  --------------------  ------------
   1        001_users           2026-09-12T07:44:52Z  985a02525e64
   2        002_email           2026-09-12T07:44:52Z  cf922bee46d9
   3        003_email_required  2026-09-12T07:44:52Z  5926bc9cce4c
   4        004_orders          2026-09-12T07:44:52Z  04df71fe677a

2. 2

   Step 2.

   Read the other version number SQLite keeps, on all three databases.

   for d in dev.db ci.db stage.db; do printf "%-9s " "$d"; sqlite3 "$d" "PRAGMA user_version;"; done
   

   dev.db    4
   ci.db     3
   stage.db  4

3. 3

   Step 3.

   Compare the record with the files on disk, on the environment that is behind.

   DB=ci.db node --no-warnings migrate.mjs status
   

   001  applied  001_users
   002  applied  002_email
   003  applied  003_email_required
   004  pending  004_orders
   user_version = 3

   pending on the last line is the answer to the question that started the pass: the build under test has one migration that the code expects and the database has not run.

4. 4

   Step 4.

   Run the same report on the environment that claims to be current.

   DB=stage.db node --no-warnings migrate.mjs status
   

   001  applied  001_users
   002  applied  002_email
   003  applied  003_email_required
   004  applied  004_orders
   user_version = 4

5. 5

   Step 5.

   Ask whether the objects that migration creates are there.

   sqlite3 -header -column stage.db "SELECT (SELECT count(*) FROM schema_migrations WHERE version = 4) AS recorded, (SELECT count(*) FROM sqlite_master WHERE type='table' AND name='orders') AS present;"
   

   recorded  present
   --------  -------
   1         0

   The record says the migration ran. The schema says its table does not exist. Step 4 reported this database as fully migrated, because the report reads the record and never looks at the schema.

6. 6

   Step 6.

   Add a line to a migration file that already ran, then read the report again.

   printf -- '-- hotfix 2026-09-12: index added later\n' >> migrations/002_email.up.sql && DB=dev.db node --no-warnings migrate.mjs status
   

   001  applied  001_users
   002  CHANGED  002_email
   003  applied  003_email_required
   004  applied  004_orders
   user_version = 4

7. 7

   Step 7.

   Dump the database, restore it into a new file, and read both version records there.

   sqlite3 dev.db .dump > dev.dump.sql && sqlite3 restored.db < dev.dump.sql && sqlite3 -header -column restored.db "SELECT (SELECT max(version) FROM schema_migrations) AS table_says, (SELECT * FROM pragma_user_version) AS pragma_says;"
   

   table_says  pragma_says
   ----------  -----------
   4           0

   .dump writes no PRAGMA user_version statement, so the restored copy reports version 0 while its own migrations table says 4. Any check that trusts the pragma alone calls a restored database unmigrated.

8. 8

   Step 8.

   Compare two environments line by line.

   sqlite3 dev.db "SELECT version || ' ' || name FROM schema_migrations ORDER BY version;" > dev.list; sqlite3 ci.db "SELECT version || ' ' || name FROM schema_migrations ORDER BY version;" > ci.list; diff dev.list ci.list
   

   4d3
   < 4 004_orders

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | Every file applied, and the counts agree | The record matches the files | Confirm one object per recent migration exists, as in step 5. | | pending on the last rows | The environment is behind the build | Deploy the migrations before filing anything against that environment. | | recorded 1, present 0 | The row was written without the SQL running | Ask who baselined it. Re-run that migration on a copy first. | | CHANGED | A file changed after it was applied | The database and the repository disagree about what version 2 means. Add a new migration instead. | | pragma_says 0 with rows in the table | The database came from a dump | Trust the table. Reset user_version after a restore if anything reads it. | | A version in the table with no file | Someone deployed a migration that is not in this branch | Find the branch. The database is ahead of the code. |

Common mistakes

What to check next

• How to test database migration: what to do before the migration this page counts reaches an environment.
• How to compare database schemas: when the records match and the two databases still differ.
• How to verify a database backup: step 7 is one of the things a restore quietly changes.
• Database migration checklist: the rest of the pass around a release that carries DDL.

FAQ

How to check schema version without the migration tool?

Query the table the tool writes to, as step 1 does. The tool is convenient and it is also the thing under suspicion when a report and the schema disagree. A plain SQL client reads the same rows and adds no interpretation.

Which record is authoritative, the table or the pragma?

The table. It holds one row per migration, with a name and a time, and it survives a dump and restore. PRAGMA user_version is one integer in the SQLite file header, set by whatever last remembered to set it, and step 7 shows it reaching 0 on its own.

What does CHANGED mean when the migration already ran?

The file on disk no longer hashes to the checksum stored when it was applied. The database holds the old version of that migration and the repository holds a new one. Nothing will reconcile them, so the fix is a new migration, not an edit.

Does a restored backup keep its schema version?

The migrations table does, because its rows are inside the dump. The header value does not, as step 7 shows. After a restore, read the table and set the pragma again if any code reads it.

How do I check this on another engine?

The same two questions: what does the record say, and does the schema agree. The table name is set by your migration tool and its documentation names it. None of the commands here were run against PostgreSQL or MySQL, so nothing on this page states how those engines behave.

Verified

Every command ran against SQLite 3.50.6 on Windows 11, with the runner and the four migrations printed above. The machine has no PostgreSQL and no MySQL, so no claim here has been tested on either.