JMM’s

SQLite notes

Command Line

Use the command sqlite3 somedb.sqlite to open “somedb.sqlite”. sqlite3 is provided by the “sqlite-interactive” nixpkg. The plain “sqlite” nixpkg doesn’t provide readline support, as far as I can tell.

Dot commands

Special SQLite commands are listed in SQLite’s documentation. You can also type .help to get a list.

.tables
Get a list of tables.

Emacs

In recent versions of Emacs (compiled with SQLite), you can use sqlite-mode-open-file to view a SQLite file.

sqlite-mode-open-file displaying a SQLite database for Firefox (places.sqlite)

sql-mode

In sql-mode, use M-x sql-sqlite to connect to a SQLite database.

Programs that use SQLite

(At least programs or applications I use.)

Nix

Nix derivations are stored in a SQLite database located at /nix/var/nix/db/db.sqlite. It’s got three tables: DerivationOutputs, Refs, and ValidPaths. I haven’t looked at this further.

Anki

See also my Anki note page.

When exporting decks, the .apkg files seem to be a ZIP archive that include a SQLite database (either named collection.anki2 or collection.anki21) that have the fields: cards, col, graves, notes, and revlog.