How to compare database schemas

Compare the objects rather than the text of the schema. Read every column and index out of both databases with PRAGMA table_info and PRAGMA index_list, then diff the two listings. The table names matched in the run below while orders.total held two different types, and that difference changed the result of a query.

Why check this

Run this before a regression pass on a shared environment, and after any release where someone had to fix a table by hand. It answers one question: does the database the tests run against have the same shape as the database the code was written for.

The failure it prevents is a test suite that passes on one environment and fails on another for no visible reason. A column that is NUMERIC in production and TEXT in the test database sorts differently and aggregates differently. Every query still runs, no error appears, and the numbers are wrong.

Both sides here are SQLite 3.50.6 files. The idea carries to any engine, the pragmas do not.

Prerequisites

SQLite 3.50.6 on the command line and GNU diff 3.10. See the PRAGMA documentation for the table-valued form used below.
Two databases to compare. These stand in for production and a test environment that was built by hand from an old copy. They carry the same two tables and disagree about a type, a default and an index.

sqlite3 prod.db <<'SQL'
CREATE TABLE users (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT '1970-01-01',
  email      TEXT NOT NULL DEFAULT ''
);
CREATE INDEX idx_users_name ON users(name);
CREATE INDEX idx_users_email ON users(email);
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);
SQL

sqlite3 test.db <<'SQL'
CREATE TABLE users (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  email      TEXT NOT NULL DEFAULT ''
);
CREATE INDEX idx_users_name ON users(name);
CREATE INDEX idx_users_email ON users(email);
CREATE TABLE orders (
  id      INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  total   TEXT NOT NULL DEFAULT 0
);
SQL

for d in prod test; do sqlite3 $d.db "INSERT INTO users (id,name) VALUES (7,'kent'); INSERT INTO orders (id,user_id,total) VALUES (1,7,9),(2,7,100);"; done
sqlite3 test.db "ANALYZE;"

Two query files, one for columns and one for indexes. notnull is a keyword, so the column has to be quoted or the query fails to parse.

-- cols.sql
SELECT m.name || '.' || p.name || '  ' || p.type
       || '  notnull=' || p."notnull"
       || '  default=' || ifnull(p.dflt_value, '(none)')
  FROM sqlite_master m JOIN pragma_table_info(m.name) p
 WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite_%'
 ORDER BY m.name, p.cid;

-- idx.sql
SELECT m.name || ' -> ' || il.name || ' (' || group_concat(ii.name) || ')'
  FROM sqlite_master m JOIN pragma_index_list(m.name) il
       JOIN pragma_index_info(il.name) ii
 WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite_%'
 GROUP BY m.name, il.name ORDER BY 1;

A rebuild script for step 6, which produces a table identical to the one it replaces.

-- rebuild.sql
BEGIN;
CREATE TABLE orders_new (
  id      INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  total   NUMERIC NOT NULL DEFAULT 0
);
INSERT INTO orders_new SELECT id, user_id, total FROM orders;
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;
CREATE INDEX idx_orders_user ON orders(user_id);
COMMIT;

Steps

1
Step 1.
Compare the table names, the way most drift checks start.
```
diff <(sqlite3 prod.db "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;") <(sqlite3 test.db "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;")
```
```
1a2
> sqlite_stat1
```
The one difference reported is an internal table that ANALYZE created to hold query planner statistics. It is not drift, and it is the only thing this comparison found.

Exclude the internal tables and compare the names again.

diff <(sqlite3 prod.db "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name;") <(sqlite3 test.db "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name;") && echo "table names identical"

table names identical

Compare every column of every table.

diff -u --label prod.db --label test.db <(sqlite3 prod.db < cols.sql) <(sqlite3 test.db < cols.sql)

--- prod.db
+++ test.db
@@ -1,7 +1,7 @@
orders.id  INTEGER  notnull=0  default=(none)
orders.user_id  INTEGER  notnull=1  default=(none)
-orders.total  NUMERIC  notnull=1  default=0
+orders.total  TEXT  notnull=1  default=0
users.id  INTEGER  notnull=0  default=(none)
users.name  TEXT  notnull=1  default=(none)
-users.created_at  TEXT  notnull=1  default='1970-01-01'
+users.created_at  TEXT  notnull=1  default=CURRENT_TIMESTAMP
users.email  TEXT  notnull=1  default=''

Two differences that the name comparison could not see: a column type and a default. The default matters to any test that inserts a row without a timestamp and then asserts on it.

Compare the indexes.

diff -u --label prod.db --label test.db <(sqlite3 prod.db < idx.sql) <(sqlite3 test.db < idx.sql)

--- prod.db
+++ test.db
@@ -1,3 +1,2 @@
-orders -> idx_orders_user (user_id)
users -> idx_users_email (email)
users -> idx_users_name (name)

5
Step 5.
Run the same query on both sides to see what the type difference does.
```
for d in prod test; do printf "%-8s " "$d.db"; sqlite3 $d.db "SELECT max(total), typeof(max(total)), (SELECT group_concat(total) FROM (SELECT total FROM orders ORDER BY total)) FROM orders;"; done
```
```
prod.db  100|integer|9,100
test.db  9|text|100,9
```
Both tables hold 9 and 100. On the side where total is declared TEXT, the values are stored as text, so the largest is 9 and the sort order is 100 before 9. No error, no warning, a different number.

Rebuild one table into an identical definition, then compare the schema text.

cp prod.db prod2.db && sqlite3 prod2.db < rebuild.sql && diff -u --label prod.db --label prod2.db <(sqlite3 prod.db .schema) <(sqlite3 prod2.db .schema)

--- prod.db
+++ prod2.db
@@ -6,7 +6,7 @@
);
CREATE INDEX idx_users_name ON users(name);
CREATE INDEX idx_users_email ON users(email);
-CREATE TABLE orders (
+CREATE TABLE IF NOT EXISTS "orders" (
 id      INTEGER PRIMARY KEY,
 user_id INTEGER NOT NULL REFERENCES users(id),
 total   NUMERIC NOT NULL DEFAULT 0

7
Step 7.
Compare the same pair by column instead.
```
diff -u --label prod.db --label prod2.db <(sqlite3 prod.db < cols.sql) <(sqlite3 prod2.db < cols.sql) && echo "columns identical"
```
```
columns identical
```
The two databases are the same shape. SQLite stores the CREATE TABLE text as it was written, and a table that has been renamed comes back quoted, so a text comparison reports a change that does not exist. That is a false positive a column comparison does not produce.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | sqlite_stat1 on one side only | ANALYZE ran there | Exclude sqlite_% names. It is statistics, not schema. | | A type difference on one column | The two databases store that column differently | Fix the environment that was built by hand, then re-run step 5 on the queries that touch it. | | A default difference | Inserts that omit the column produce different rows | Decide which default the code expects. A test asserting on a timestamp fails on one side. | | An index on one side only | One environment is missing an index | Check whether a migration created it, or whether someone dropped it during an incident. | | CREATE TABLE "x" against CREATE TABLE x | The table was rebuilt or renamed on that side | Ignore it and compare columns. The shape is unchanged. | | Identical columns and different query results | The difference is in the data, not the schema | Stop here and compare rows instead. |

Common mistakes

Sign: The drift check compares table names and reports two environments as equal.Cause: Names are the part that rarely drifts. Types, defaults, nullability and indexes drift because they are what a hand fix changes. Step 3 found a type and a default that step 2 declared identical.

Sign: A byte comparison of .schema reports a difference nobody can explain.Cause: SQLite keeps the original text of every CREATE statement, so how a table was built shows up in the comparison. A rebuilt or renamed table comes back with a quoted name, and a column added by ALTER TABLE is appended to the end of the stored text.

Sign: The comparison passes on an empty test database and the suite still fails.Cause: A schema comparison says nothing about sequences, trigger bodies on other engines, or the rows. Run it as the first of three checks, not as the whole answer, and compare row counts separately.

What to check next

How to check database schema version: drift usually starts as one environment being a migration behind.
How to test database migration: a rollback that forgets an index is one way the two sides drift apart.
How to check table schema: the single-table version of step 3, for when you know where to look.
Database migration checklist: the wider pass before a release that changes the schema.

FAQ

How to compare two databases that have the same tables?

Compare their columns and indexes, as in steps 3 and 4. Table names are the least likely part to drift. The comparison that finds real defects reads every column type, nullability and default out of both sides and diffs the two lists.

Why does a schema text comparison report changes that are not there?

SQLite stores the CREATE TABLE statement as written. A rebuild, a rename or an ALTER TABLE ADD COLUMN changes that text without changing the shape of the table. Steps 6 and 7 show the same pair of databases reported as different and then as identical.

Does this compare the data too?

No. It reads schema objects only. Step 5 runs one query on both sides, which is a way to confirm what a difference costs, not a data comparison. Row counts and checksums are a separate check.

How do I compare schemas on PostgreSQL or MySQL?

The sequence holds: list the objects from the catalogue on both sides, sort them, diff the listings. The catalogues differ, and no command on this page was run against either engine, so treat the SQLite syntax here as an example rather than a recipe.

Should this run in continuous integration?

Yes, if a real environment is involved. Store the listing from step 3 as a file in the repository, regenerate it against the environment, and fail the job on a difference. That turns drift into a failing build instead of a strange test result.

Verified

Verified 2026-09-12by Maks Vernysqlite3 3.50.6diff 3.10

Each output block is what the command above it printed on that date, on the host named in the step. Figures read from a live site move between runs. Compare the shape of the answer rather than the digits, and see the methodology for how a page is re-verified.

Both databases were SQLite 3.50.6 files on one Windows 11 machine, created by the commands in Prerequisites. The machine has no PostgreSQL and no MySQL, so nothing here has been checked on another engine.

Database migration checklist15 checks
All databases and migrations checkscluster

intermediate10 minpublished 2026-09-12updated 2026-09-12Maks Verny