Comparing two databases

SQLite Expert can compare two databases and show you exactly how they differ — invaluable when you need to see what changed between a development database and a production one, or between two versions of the same design. Structure and data are compared in the same window, and the window will write the SQL that puts the two in step.

To compare two databases:

1. Open both databases.
2. Choose Tools → Compare... from the main menu.
3. Pick the two databases in Left and Right, and press Compare.

The objects that differ are listed on the left; the one selected is shown side by side on the right, with the differences marked.

Along the top are the two databases, what to compare of them, and the commands. Compare data decides whether the rows are read as well as the schema, and Integrity check how much of one runs before a migration script is written — both are described below, under Compare data and Integrity check.

Everything the script does is written to make the database on the right match the one on the left, so put the database you want to keep on the left.

Compare data

Comparing the rows means reading every row of every table on both sides, which is the expensive half of the job. Leave Compare data unticked and only the schema is compared — and only the schema is scripted.

What differs

The Differences tab lists the objects that differ: tables, views, virtual tables and triggers, each with what kind of difference it has — structure, data, both, or present on one side only. Choose Show all objects to see the ones that match as well.

Beside the list is the object the list is standing on, in the comparison's own words — how many differences it has, how the rows count up on each side, and any note the comparison left.

A virtual table's rows are compared and migrated when its module keeps them in the table itself and accepts new ones — the full-text search modules fts3, fts4 and fts5, and rtree. Anything else is left alone and says so in its note: some virtual tables are a window onto another table, whose rows are already accounted for there, and some cannot be written to at all. The shadow tables a module keeps beside its virtual table are never written directly; filling the virtual table fills them.

• Structure shows the two CREATE statements side by side, with the changed words marked. Where the two are written differently but mean the same thing, the line above the panes says so.
• Data lists the rows of the selected table that differ, and shows the selected row's columns on each side.

The toolbar over the two panes decides what counts as a difference: whether case matters, what to do about whitespace, whether to mark whole lines or the words or characters within them, and whether to fold long lines into the pane rather than scroll sideways. These settings are remembered between sessions.

Nothing is written to either database by comparing. It is safe to run against live databases.

Looking at one value

Under the list of changed rows, Column picks which of the row's disagreeing columns the two panes show. Beside it are the ways it can be shown:

• Text — the value as text. The everyday choice.
• Hex — a classic dump: the offset, sixteen bytes in hex, then the printable ones. Available for any value.
• Image — the value drawn as a picture, scaled to the pane. Offered when the value is a BLOB that begins the way a PNG, JPEG, GIF, BMP or TIFF does.

The way you choose stays chosen as you move from row to row, so long as the next value can be shown that way.

Tip

Reach for Hex when the two panes look identical and the row is still listed as changed. That is what a trailing space, a tab that used to be spaces, CR LF against LF, a byte-order mark, a non-breaking space or an accent written as its own character rather than composed looks like in the text view: nothing at all. In the dump they are plain, and the changed bytes are marked like any other difference.

A value is read up to a megabyte for the dump and up to sixteen megabytes to be drawn. Where the dump holds only the front of a value, its first line says how much of how much it is showing, so a comparison that stops early never reads as agreement.

Comparing one object

Choosing an object in either Left or Right compares that object alone, which is quicker than comparing the whole database and is often all you want. The migration script then covers that object alone as well.

Writing the report

Report... writes what the comparison found to a file — something to keep, to attach to a ticket, or to compare against the same report taken next week. It is enabled once a comparison has run.

The format comes from the name you give the file:

Name it	And you get
.html	a page to read in a browser or print
.md	Markdown, to paste into a ticket or a pull request
.json	the same report as data, for another program to read

The JSON is the report; the other two are rendered from it, so a person and a program reading the same comparison are never told two different things.

Every report holds both databases with their file names and the time it was written, one entry per object with what differs about it, and every structure difference in full. Where the rows were compared it also holds them, table by table: which rows are on one side only, which changed, and — for a changed row — the columns that disagree with both values.

The rows are compared table by table as the report is written, so a report over a large pair of databases takes a while. While it runs, Report... becomes Cancel and the line under the boxes says which table it is on. Cancelling writes nothing.

Note

A report never truncates in silence.

• Run with Compare data unticked, it says the rows were not compared, rather than leaving the section out and reading as "the rows agree".
• A value longer than 1000 characters is cut, and the cut says how long the value really was.
• Where more rows differ than are listed, it says so. The counts beside them are exact either way — they are counted, not counted off the list.
• A BLOB is described by its size rather than quoted: read as text it would stop at its first zero byte, which is not what it holds.

A row that exists on only one side is written out in full, column by column, with the side that has not got it left empty — the whole row is the difference, so the whole row is what the report shows. Its Key column still holds just the key, which is what you would search the report for.

Rows are matched on the primary key

A table with a primary key is compared row by row on that key, so SQLite Expert can tell a changed row from a removed one and report which columns disagree.

A table without a primary key has no identity to match rows on, so it is compared as a set: rows present on one side and not the other. Such a table never reports "changed" rows — a changed row simply looks like one row removed and another added.

Note

The rowid is deliberately not used as an identity. Two databases that were populated separately will have given the same logical row different rowids, so matching on it would report almost every row as changed.

While something is running

Compare, Generate and Execute all run in the background. The window stays usable while they do: the bottom of it shows what is happening, how far it has got, and a Stop button. Everything that would start a second one is greyed until the first finishes, and the window will not close until then — Stop is the way out.

Stop is always safe. A comparison or a generated script is simply abandoned. A migration is undone: it runs inside a savepoint, so stopping it rolls back everything it had applied and the log says so. What it is not is instant on a statement that is already running — a single long one is interrupted where the engine next looks, which is usually within a moment.

Some of this work has no measure to report. A single integrity check reads every page of a database and SQLite says nothing about how far it has got — so for those the bar moves rather than fills, and the line beside it says how much there is to read and how long it has been reading, which are the two things that can honestly be said.

Integrity check

Before Generate writes anything it checks the integrity of what it is about to rewrite, because rebuilding a table in a database that is already damaged is how a recoverable problem becomes an unrecoverable one. It is the same check as Database ▸ Integrity Check, and a database that fails it is repaired the same way. On a large database that check is the whole cost of generating a script — reading two 680 MB files end to end takes minutes, against seconds for the migration itself — so Integrity check beside Compare data decides how much of it to do.

• Tables being changed is the default. Only the tables the script will write to are checked, with their indexes. Nothing else is going to be rebuilt, so nothing else is at risk from the rebuild. Several tables are checked one at a time and the bar counts them.
• The whole database is the thorough one, for a database you have reason to doubt. It reads every page of both sides.
• Do not check skips it. Quick, and yours to judge.

The choice is remembered.

The migration script

The Migration Script tab writes the SQL that would make the database on the right match the one on the left.

• Generate writes the script into the pane. Read it — and edit it, if you want to: what Execute applies is what the pane holds, statement count and all. An edited script is no longer what the comparison wrote, so the question Execute asks says so, and the log records it before the first statement. Generate again to go back to the script as it came.

- Execute applies it to the database on the right, inside a savepoint and with foreign keys off for the duration, so it either all lands or none of it does. It asks first, and the log underneath says what happened, statement by statement. A statement longer than 500 characters is shortened there, with its full length in brackets after it -- a row carrying a blob runs to hundreds of kilobytes written out as hex, and the pane above holds all of it anyway. If one fails, the log names it, repeats it at the end, and says that nothing was applied. The last line of a run that worked is how long it took.

A bar at the bottom of the window follows a run, beside a line saying what
it is doing. It measures statements, not work, so it can move unevenly —
the statements that build tables and indexes cost more each than the ones
that fill them.

**Execute** greys out once the script in the pane has been applied —
running the same script a second time would not do nothing, it would fail,
on a `DROP` of a table already gone or an `INSERT` of a row already there —
and again whenever a fresh **Compare** replaces the result the script was
written for. The script stays in the pane either way. **Generate** again,
or change the script yourself, and **Execute** comes back. A run that
failed leaves it enabled, since nothing was applied.

The structure comes first and the rows after it, because a column the script is about to add cannot be filled before it exists — and a table only the left has is created before it is filled. The row statements are ordered deletes, then updates, then inserts, so a key freed by a delete can be reused by an insert in the same run. Values are written by SQLite itself, so blobs, NULLs and embedded quotes all come out correctly.

Its first line names the database it was written for. The script can be saved or sent on, and a SQL tab runs against whichever database is selected there — not necessarily the one the script is meant for.

For a table with no primary key, rows that exist only on the right cannot be deleted — there is no way to write a WHERE clause that identifies one of them — so the script says so in a comment instead of guessing.