Tools for dumping/loading a SQLite database to diffable directory structure

pip install sqlite-diffable

Given a SQLite database called fixtures.db containing a table facetable, the following will dump out that table to the out/ directory:

sqlite-diffable dump fixtures.db out/ facetable

To dump out every table in that database, use --all:

sqlite-diffable dump fixtures.db out/ --all

The repository at simonw/simonwillisonblog-backup contains a backup of the database on my blog, https://simonwillison.net/ - created using this tool.

Each table is represented as two files. The first, table_name.metadata.json, contains metadata describing the structure of the table. For a table called redirects_redirect that file might look like this:

{
    "name": "redirects_redirect",
    "columns": [
        "id",
        "domain",
        "path",
        "target",
        "created"
    ],
    "schema": "CREATE TABLE [redirects_redirect] (\n   [id] INTEGER PRIMARY KEY,\n   [domain] TEXT,\n   [path] TEXT,\n   [target] TEXT,\n   [created] TEXT\n)"
}

It is an object with three keys: name is the name of the table, columns is an array of column strings and schema is the SQL schema text used for tha table.

The second file, table_name.ndjson, contains [newline-delimeted JSON] for every row in the table. Each row is represented as a JSON array with items corresponding to each of the columns defined in the metadata.

That file for the redirects_redirect.ndjson table might look like this:

[1, "feeds.simonwillison.net", "swn-everything", "https://simonwillison.net/atom/everything/", "2017-10-01T21:11:36.440537+00:00"]
[2, "feeds.simonwillison.net", "swn-entries", "https://simonwillison.net/atom/entries/", "2017-10-01T21:12:32.478849+00:00"]
[3, "feeds.simonwillison.net", "swn-links", "https://simonwillison.net/atom/links/", "2017-10-01T21:12:54.820729+00:00"]