Small. Fast. Reliable.
Choose any three.

About Documentation Download License Support Purchase |

## The sqldiff.exe Utility Program

The `sqldiff.exe` is a command-line utility program that displays the differences between SQLite databases. Example usage:

>     sqldiff [options] database1.sqlite database2.sqlite

The usual output is SQL statements that will transform database1.sqlite (the "source" database) into database2.sqlite (the "destination" database). This behavior can be altered using command-line switches:

--changeset FILE

  Do not write changes to standard output. Instead, write a (binary) changeset file into FILE. The changeset can be interpreted using the sessions extension to SQLite.

--lib LIBRARY
-L LIBRARY

  Load the shared library or DLL file LIBRARY into SQLite prior to computing the differences. This can be used to add application-defined collating sequences that are required by the schema.

--primarykey

  Use the schema-defined PRIMARY KEY instead of the rowid to pair rows in the source and destination database. (See additional explanation below.)

--schema

  Show only differences in the schema not the table content

--summary

  Show how many rows have changed on each table, but do not show the actual chagnes

--table TABLE

  Show only the differences in content for TABLE, not for the entire database

--transaction

  Wrap SQL output in a single large transaction

### How It Works

The sqldiff.exe utility works by finding rows in the source and destination that are logical "pairs". The default behavior is to treat two rows as pairs if they are in tables with the same name and they have the same rowid, or in the case of a WITHOUT ROWID table if they have the same PRIMARY KEY. Any differences in the content of paired rows are output as UPDATEs. Rows in the source database that could not be paired are output as DELETEs. Rows in the destination database that could not be paired are output as INSERTs.

The --primarykey flag changes the pairing algorithm slightly so that the schema-declared PRIMARY KEY is always used for pairing, even on tables that have a rowid. This is often a better choice for finding differences, however it can lead to missed differences in the case of rows that have one or more PRIMARY KEY columns set to NULL.

### Limitations

1. The sqldiff.exe utility is unable to compute differences for rowid tables for which the rowid is inaccessible. An example of a table with an inaccessible rowid is:

   >     CREATE TABLE inaccessible_rowid(
   >        "rowid" TEXT,
   >        "oid" TEXT,
   >        "_rowid_" TEXT
   >     );

2. The sqldiff.exe utility does not (currently) display differences in TRIGGERs, VIEWs, or virtual tables.