{"repo":"simonw/sqlite-diffable","free":true,"listed":false,"github":"https://github.com/simonw/sqlite-diffable","clone":"git clone https://github.com/simonw/sqlite-diffable.git","description":"Tools for dumping/loading a SQLite database to diffable directory structure","language":"Python","stars":140,"topics":["sqlite","datasette-tool","datasette-io"],"license":"Apache-2.0","category":"databases-storage","readme_excerpt":"sqlite-diffable Tools for dumping/loading a SQLite database to diffable directory structure Installation pip install sqlite-diffable Demo The repository at simonw/simonwillisonblog-backup contains a backup of the database on my blog, https://simonwillison.net/ - created using this tool. Dumping a database Given a SQLite database called fixtures.db containing a table facetable , the following will dump out that table to the dump/ directory: To dump out every table in that database, use --all : To dump all table except some specific ones, use --exclude one or more times: Loading a database To load a previously dumped database, run the following: This will show an error if any of the tables that are being restored already exist in the database file. You can replace those tables (dropping them before restoring them) using the --replace option: Converting to JSON objects Table rows are stored in the .ndjson files as newline-delimited JSON arrays, like this: Sometimes it can be more convenient to work with a list of JSON objects. The sqlite-diffable objects command can read a .ndjson file and its accompanying .metadata.json file and output JSON objects to standard output: The output of that command looks something like this: Add -o to write that output to a file: Add --array to output a JSON array of objects, as opposed to a newline-delimited file: Output: Storage format Each table is represented as two files. The first, table name.metadata.json , contains metadata describing the s","default_branch":null,"files":null,"tree":[],"storefront":"/r/simonw","claimed":false,"request_supported":{"post":"https://gitbuyer.com/r/simonw/sqlite-diffable/request-supported","requests":0},"note":"indexed from public GitHub; nothing is for sale on this page. Clone it from GitHub. Paid listings live at /search."}