How can I diff 2 SQLite files?

Question

Using SQLite-manager (in its XUL form) on a Mac.

How can I diff a SQLite file from one submitted by someone else on the team, and incorporate his changes?

Thanks.

nesv · Accepted Answer · 2011-01-04T06:14:48.427

31

I believe you could use the following, in combination:

$ diff sqlite-file-1.sql sqlite-file-2.sql > sqlite-patch.diff
$ patch -p0 sqlite-file-1.sql sqlite-patch.diff

I hope that works for you. Otherwise, I highly suggest consulting the man-pages:

$ man diff
$ man patch

EDIT: Alright, here's the whole walk-through.

First, dump the databases:

$ sqlite test1.sql .dump > test1.sql.txt
$ sqlite test2.sql .dump > test2.sql.txt

Next, generate a diff file:

$ diff -u test1.sql.txt test2.sql.txt > patch-0.1.diff

And, finally, to apply the patch:

$ patch -p0 test1.sql.txt patch-0.1.diff

edited Jan 04 '11 at 06:14

answered Jan 02 '11 at 20:34

nesv

786
6
10

Doesn't work, as SQLite is a binary file, the only result you get is "binary files differ" – Traveling Tech Guy Jan 02 '11 at 20:54
2

Won't this break if you modify the schema that already has data in it? For instance if you add a field and want to patch the additional field to an existing database. – Jeremy Edwards Jan 13 '11 at 06:22
4

User may need sqlite3 instead of sqlite. – toliveira Dec 04 '15 at 16:25

Bernardo Ramos · Answer 2 · 2016-06-11T05:14:36.873

16

We can use the sqldiff Utility Program:

https://www.sqlite.org/sqldiff.html

It will compare the source to the destination databases and generate SQL commands to make source equivalent to destination.

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.

~~We have to download the sources and compile it, from the tool folder.~~

edited Jun 11 '16 at 05:14

answered Jun 19 '15 at 23:06

Bernardo Ramos

4,048
30
28

2

No need to compile, it's available as a precompiled binary on the [download page](https://www.sqlite.org/download.html) – user Jan 29 '16 at 11:38
You don't have to mess around with the `tool` folder. Just `./configure && make sqldiff`. – Warren Young Jun 04 '16 at 22:24

score 1 · Answer 3 · answered Jan 02 '11 at 20:39

1

Maybe using this tool: http://download.cnet.com/SQLite-Diff/3000-10254_4-10894771.html ? But you can use the solution provided by @indienick provided you first dump the binary sqlite database with something like: sqlite x.db .dump > output.sql

Hope this helps, Moszi

answered Jan 02 '11 at 20:39

Moszi

3,236
2
22
20

How exactly do I dump my SQLite? I do not have a sqlite command line installed. – Traveling Tech Guy Jan 02 '11 at 21:21
1

If you use a standard Mac box, then you should have sqlite in /usr/bin/sqlite3 ;) – Moszi Jan 02 '11 at 21:27
The last time I checked, you did not need to worry about dumping SQLite files, as SQLite works directly with a .sql file - it's not all hidden away in binary flat-files, like PostgreSQL or MySQL. – nesv Jan 02 '11 at 23:05
Got it, and it works. Now I have to decide who gets the V - @indienick got it 90% right, but you brought it home... :) – Traveling Tech Guy Jan 02 '11 at 23:06
hahh :) - thats all right :) award it to indienick, his answer was faster then mine ;) – Moszi Jan 02 '11 at 23:09

sarh · Answer 4 · 2023-02-07T15:51:29.457

There is a free web tool to compare SQLite databases, both schema and data - https://ksdbmerge.tools/for-sqlite-online Works on wasm-compatible browsers including Safari on Mac.

I am the author of that tool, it is an uno-platform port of my desktop tool for SQLite. Unlike few other online tools I could find - it does not upload your data to the server to generate data diff. However it uploads schema to handle the rest of logic.

As of June 2022 it is diff-only tool, web version does not produce any syncronization scripts.

How can I diff 2 SQLite files?

4 Answers4

Linked