Compare SQLite Databases Safely with sqldiff

Applying an untested sqldiff patch to a live database can quietly rewrite rows you meant to keep. This guide uses sqldiff to compare two SQLite database files, isolate schema or table changes, review the generated SQL, and verify a copy of the source before anything touches the real one. Allow about 15 minutes for a small database, plus time to inspect the output.

Before you start

You need two readable SQLite database files and the sqldiff executable. The command takes the source database first and the destination database second:

sqldiff [options] SOURCE.sqlite DESTINATION.sqlite

Check the executable before preparing a comparison:

command -v sqldiff
sqldiff --help

The installed Ubuntu package metadata on this host reports sqlite3 version 3.45.1-1ubuntu2.8, while the local sqldiff(1) page is dated 2018-05-10. The executable is not present on this host, so the examples below follow that manpage and the current SQLite project documentation. Check the help output from your own executable if its options differ.

Checkpoint: Do not continue until command -v sqldiff prints a path and the two input files exist.

1. Make disposable copies

sqldiff reads both inputs, but the SQL it emits can contain DELETE, UPDATE, INSERT, schema changes, or index changes. Never test that SQL against the only copy of a working database.

mkdir -p /tmp/sqldiff-work
cp -- SOURCE.sqlite /tmp/sqldiff-work/source.sqlite
cp -- DESTINATION.sqlite /tmp/sqldiff-work/destination.sqlite
sqlite3 /tmp/sqldiff-work/source.sqlite 'PRAGMA integrity_check;'
sqlite3 /tmp/sqldiff-work/destination.sqlite 'PRAGMA integrity_check;'

Each integrity check should print ok. If either check fails, stop and repair or restore that database before comparing it. The copies under /tmp are disposable; remove them when the review is complete with rm -- /tmp/sqldiff-work/source.sqlite /tmp/sqldiff-work/destination.sqlite.

2. Generate a reviewable SQL diff

Run the basic comparison and save standard output to a file. The first database is the source that the SQL is intended to transform; the second is the destination whose state it describes.

sqldiff /tmp/sqldiff-work/source.sqlite /tmp/sqldiff-work/destination.sqlite > /tmp/sqldiff-work/change.sql
sed -n '1,160p' /tmp/sqldiff-work/change.sql

An empty file means no differences were emitted. Otherwise, expect SQL statements such as INSERT, UPDATE, DELETE, or schema statements. Treat this as a proposed change set, not as proof that it is a suitable migration.

The default pairing rule matches rows in same-named tables by rowid. For a WITHOUT ROWID table it uses the primary key. If a row's logical identity is its declared primary key even though the table has a rowid, ask sqldiff to pair rows that way:

sqldiff --primarykey /tmp/sqldiff-work/source.sqlite /tmp/sqldiff-work/destination.sqlite > /tmp/sqldiff-work/change-by-key.sql

--primarykey can be a better match for application data, but the SQLite documentation warns that rows with one or more NULL primary-key columns can produce missed differences. Inspect the table definitions before selecting it.

3. Narrow the question before applying anything

Use a schema-only comparison when you are checking table, column, or index definitions rather than row content:

sqldiff --schema /tmp/sqldiff-work/source.sqlite /tmp/sqldiff-work/destination.sqlite

Use a table filter when one table is the subject of the investigation:

sqldiff --table orders /tmp/sqldiff-work/source.sqlite /tmp/sqldiff-work/destination.sqlite

For a quick inventory, --summary reports how many rows changed per table without printing the row-level SQL:

sqldiff --summary /tmp/sqldiff-work/source.sqlite /tmp/sqldiff-work/destination.sqlite

These filters are useful checkpoints. If a supposedly unchanged table appears in the summary, stop and investigate its row identity, triggers, generated data, or schema before applying a broad diff.

4. Make the output easier to apply

For a text SQL diff that you have reviewed, --transaction wraps the output in one large transaction:

sqldiff --transaction --primarykey \
  /tmp/sqldiff-work/source.sqlite \
  /tmp/sqldiff-work/destination.sqlite \
  > /tmp/sqldiff-work/change.sql
sed -n '1,200p' /tmp/sqldiff-work/change.sql

A transaction gives the application a single commit boundary when the SQL runs successfully. It does not make an unsuitable schema change safe, and it does not replace a backup. Read the file, search for destructive statements, and check that the source and destination really are the intended pair:

grep -nE '(^|[[:space:]])(DROP|DELETE|UPDATE|ALTER TABLE)' /tmp/sqldiff-work/change.sql

Warning: Do not run an unreviewed diff against production or a live service. Applying a file changes state and may acquire database locks. Ordinary comparison and review do not need elevated privileges. Use sudo only if filesystem permissions genuinely require it, and prefer making a readable working copy first.

5. Apply to a copy and verify

Copy the source again, apply the reviewed SQL with the SQLite shell, then compare the result with the destination:

cp -- /tmp/sqldiff-work/source.sqlite /tmp/sqldiff-work/applied.sqlite
sqlite3 /tmp/sqldiff-work/applied.sqlite < /tmp/sqldiff-work/change.sql
sqlite3 /tmp/sqldiff-work/applied.sqlite 'PRAGMA integrity_check;'
sqldiff --summary /tmp/sqldiff-work/applied.sqlite /tmp/sqldiff-work/destination.sqlite

The integrity check should print ok. The final summary should report no remaining row differences. For a stricter check, generate a normal diff and confirm it is empty:

sqldiff /tmp/sqldiff-work/applied.sqlite /tmp/sqldiff-work/destination.sqlite > /tmp/sqldiff-work/remaining.sql
test ! -s /tmp/sqldiff-work/remaining.sql && echo 'databases match'

If the command leaves SQL behind, do not keep retrying against a production file. Save the remaining diff, compare the schemas, and check whether rowids, NULL primary-key values, virtual tables, triggers, or views explain the result.

6. Know the limits before you trust the diff

sqldiff does not currently report differences in triggers or views. It also cannot compare a rowid table whose rowid is hidden by columns named rowid, oid, and _rowid_. The default comparison ignores virtual-table differences, but it can see the real shadow tables used internally by some virtual tables. Applying such output to a database that is not an exact match can corrupt virtual-table content.

For FTS3, FTS5, and rtree virtual tables, --vtab tells sqldiff to ignore their shadow tables and include virtual-table differences directly:

sqldiff --vtab --table documents \
  /tmp/sqldiff-work/source.sqlite \
  /tmp/sqldiff-work/destination.sqlite

Use --changset FILE only when you specifically need a binary changeset for SQLite's sessions extension. The local manpage uses that spelling, while current upstream documentation spells the option --changeset FILE. Confirm the spelling with sqldiff --help on the installed build before scripting it. A changeset is not ordinary SQL and should not be redirected into a text file for manual editing.

Done means