Admitting that I’m not fully up-to-speed on the current sqlite track, while thinking about the SELECT concept exercise I ran across sqldiff
An example:
$ brew install sqldiff
$ sqlite3 expected.db
SQLite version 3.51.0 2025-06-12 13:14:41
Enter ".help" for usage hints.
sqlite> create table results (id integer primary key autoincrement, value int not null);
sqlite> insert into results (value) values (10),(20),(30);
sqlite> select * from results;
1|10
2|20
3|30
sqlite> .q
$ cp expected.db actual.db
$ sqldiff expected.db actual.db
[no output]
$ sqlite3 actual.db
SQLite version 3.51.0 2025-06-12 13:14:41
Enter ".help" for usage hints.
sqlite> insert into results (value) values (42);
sqlite> update results set value = 11 where id = 1;
sqlite> .q
$ sqldiff expected.db actual.db
UPDATE results SET value=11 WHERE id=1;
INSERT INTO results(id,value) VALUES(4,42);
UPDATE sqlite_sequence SET seq=4 WHERE rowid=1;
$ sqldiff --summary expected.db actual.db
results: 1 changes, 1 inserts, 0 deletes, 2 unchanged
sqlite_sequence: 1 changes, 0 inserts, 0 deletes, 0 unchanged
I’m wondering if this could be applied to testing the sqlite track.
Obvious cons: another tool to download (although it is included in the precompiled “sqlite-tools-*” downloads; dependency on an external tool to run sqldiff and examine the output (bash/powershell, or other)
Possible pros: the expected database can be stored in git as a test fixture; perhaps a simpler testing mechanism (?)
Question to maintainers: would this simplify things at all?