30th March 2023, 1 min read

SQLite3 sqldiff command

Task at hand: You want to compare two SQLite3 databases. What data has changed? What has changed in the schema?

SQLite3 provides a simple, but neat tool called sqldiff.

$ sqldiff --help
Usage: sqldiff [options] DB1 DB2
Output SQL text that would transform DB1 into DB2.
Options:
  --changeset FILE      Write a CHANGESET into FILE
  -L|--lib LIBRARY      Load an SQLite extension library
  --primarykey          Use schema-defined PRIMARY KEYs
  --rbu                 Output SQL to create/populate RBU table(s)
  --schema              Show only differences in the schema
  --summary             Show only a summary of the differences
  --table TAB           Show only differences in table TAB
  --transaction         Show SQL output inside a transaction
  --vtab                Handle fts3, fts4, fts5 and rtree tables
See https://sqlite.org/sqldiff.html for detailed explanation.

See sqldiff.exe: Database Difference Utility.

For example, to compare two J-Pilot SQLite3 databases goes like this:

$ sqldiff jptables.db.2 jptables.db
INSERT INTO Addr(rowid,Id,Category,Private,showPhone,Lastname,Firstname,Title,Company,PhoneLabel1,PhoneLabel2,PhoneLabel3,PhoneLabel4,PhoneLabel5,Phone1,Phone2,Phone3,Phone4,Phone5,Address,City,State,Zip,Country,Custom1,Custom2,Custom3,Custom4,Note,InsertDate,UpdateDate) VALUES(11167,15601,8,0,0,'Zal...','Ga...','Atlassian support','C...',0,1,2,3,4,'+420 ...',NULL,NULL,NULL,'ga...','R120, T02'||X'0a'
||'Prague Branch',NULL,NULL,NULL,NULL,'ga...',NULL,NULL,NULL,NULL,'2023-03-30T14:37:21','2023-03-30T14:37:30');

UPDATE Alarms SET UpToDate='2023-03-30T18:15' WHERE rowid=1;

INSERT INTO Datebook(rowid,Id,Private,Timeless,"Begin","End",Alarm,Advance,AdvanceUnit,RepeatType,RepeatForever,RepeatEnd,RepeatFreq,RepeatDay,RepeatDaySu,RepeatDayMo,RepeatDayTu,RepeatDayWe,RepeatDayTh,RepeatDayFr,RepeatDaySa,Exceptions,Exception,Description,Note,InsertDate,UpdateDate) VALUES(60469,230423,0,0,'2023-03-29T09:00','2023-03-29T12:00',0,0,0,0,1,NULL,0,0,0,0,0,0,0,0,0,0,NULL,'CZU10: COOBJKID muß Null sein bei "Merge Party"',NULL,'2023-03-29T13:09:18',NULL);