repos: 195145678
This data as json
| id | node_id | name | full_name | private | owner | html_url | description | fork | created_at | updated_at | pushed_at | homepage | size | stargazers_count | watchers_count | language | has_issues | has_projects | has_downloads | has_wiki | has_pages | forks_count | archived | disabled | open_issues_count | license | topics | forks | open_issues | watchers | default_branch | permissions | temp_clone_token | organization | network_count | subscribers_count | readme | readme_html | allow_forking | visibility | is_template | template_repository | web_commit_signoff_required | has_discussions |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 195145678 | MDEwOlJlcG9zaXRvcnkxOTUxNDU2Nzg= | sqlite-diffable | simonw/sqlite-diffable | 0 | 9599 | https://github.com/simonw/sqlite-diffable | Tools for dumping/loading a SQLite database to diffable directory structure | 0 | 2019-07-04T00:58:46Z | 2022-07-12T17:00:19Z | 2022-08-18T22:49:29Z | 30 | 42 | 42 | Python | 1 | 1 | 1 | 1 | 0 | 3 | 0 | 0 | 3 | apache-2.0 | ["datasette-io", "datasette-tool", "sqlite"] | 3 | 3 | 42 | main | {"admin": false, "maintain": false, "push": false, "triage": false, "pull": false} | 3 | 1 | # sqlite-diffable [](https://pypi.org/project/sqlite-diffable/) [](https://github.com/simonw/sqlite-diffable/releases) [](https://github.com/simonw/sqlite-diffable/blob/main/LICENSE) Tools for dumping/loading a SQLite database to diffable directory structure ## Installation pip install sqlite-diffable ## Demo The repository at [simonw/simonwillisonblog-backup](https://github.com/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: sqlite-diffable dump fixtures.db dump/ facetable To dump out every table in that database, use `--all`: sqlite-diffable dump fixtures.db dump/ --all ## Loading a database To load a previously dumped database, run the following: sqlite-diffable load restored.db dump/ 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: sqlite-diffable load restored.db dump/ --replace ## Converting to JSON objects Table rows are stored in the `.ndjson` files as newline-delimited JSON arrays, like this: ``` ["a", "a", "a-a", 63, null, 0.7364712141640124, "$null"] ["a", "b", "a-b", 51, null, 0.6020187290499803, "$null"] ``` 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: sqlite-diffable objects fixtures.db dump/sortable.ndjson The output of that command looks something like this: ``` {"pk1": "a", "pk2": "a", "content": "a-a", "sortable": 63, "sortable_with_nulls": null, "sortable_with_nulls_2": 0.7364712141640124, "text": "$null"} {"pk1": "a", "pk2": "b", "content": "a-b", "sortable": 51, "sortable_with_nulls": null, "sortable_with_nulls_2": 0.6020187290499803, "text": "$null"} ``` Add `-o` to write that output to a file: sqlite-diffable objects fixtures.db dump/sortable.ndjson -o output.txt Add `--array` to output a JSON array of objects, as opposed to a newline-delimited file: sqlite-diffable objects fixtures.db dump/sortable.ndjson --array Output: ``` [ {"pk1": "a", "pk2": "a", "content": "a-a", "sortable": 63, "sortable_with_nulls": null, "sortable_with_nulls_2": 0.7364712141640124, "text": "$null"}, {"pk1": "a", "pk2": "b", "content": "a-b", "sortable": 51, "sortable_with_nulls": null, "sortable_with_nulls_2": 0.6020187290499803, "text": "$null"} ] ``` ## Storage format 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: ```json { "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-delimited JSON](http://ndjson.org/) 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"] ``` | <div id="readme" class="md" data-path="README.md"><article class="markdown-body entry-content container-lg" itemprop="text"><h1 dir="auto"><a id="user-content-sqlite-diffable" class="anchor" aria-hidden="true" href="#user-content-sqlite-diffable"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>sqlite-diffable</h1> <p dir="auto"><a href="https://pypi.org/project/sqlite-diffable/" rel="nofollow"><img src="https://camo.githubusercontent.com/f397aed437dd402178061cc28cd230ddaa228d7a19d4e6e33dd7d9d6b9aa7016/68747470733a2f2f696d672e736869656c64732e696f2f707970692f762f73716c6974652d6469666661626c652e737667" alt="PyPI" data-canonical-src="https://img.shields.io/pypi/v/sqlite-diffable.svg" style="max-width: 100%;"></a> <a href="https://github.com/simonw/sqlite-diffable/releases"><img src="https://camo.githubusercontent.com/ad89df075a7b5a43a53000aaf22188af53405e2b5177102cb1b550954996dac4/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f762f72656c656173652f73696d6f6e772f73716c6974652d6469666661626c653f696e636c7564655f70726572656c6561736573266c6162656c3d6368616e67656c6f67" alt="Changelog" data-canonical-src="https://img.shields.io/github/v/release/simonw/sqlite-diffable?include_prereleases&label=changelog" style="max-width: 100%;"></a> <a href="https://github.com/simonw/sqlite-diffable/blob/main/LICENSE"><img src="https://camo.githubusercontent.com/1698104e976c681143eb0841f9675c6f802bb7aa832afc0c7a4e719b1f3cf955/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f6c6963656e73652d417061636865253230322e302d626c75652e737667" alt="License" data-canonical-src="https://img.shields.io/badge/license-Apache%202.0-blue.svg" style="max-width: 100%;"></a></p> <p dir="auto">Tools for dumping/loading a SQLite database to diffable directory structure</p> <h2 dir="auto"><a id="user-content-installation" class="anchor" aria-hidden="true" href="#user-content-installation"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Installation</h2> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="pip install sqlite-diffable"><pre class="notranslate"><code>pip install sqlite-diffable </code></pre></div> <h2 dir="auto"><a id="user-content-demo" class="anchor" aria-hidden="true" href="#user-content-demo"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Demo</h2> <p dir="auto">The repository at <a href="https://github.com/simonw/simonwillisonblog-backup">simonw/simonwillisonblog-backup</a> contains a backup of the database on my blog, <a href="https://simonwillison.net/" rel="nofollow">https://simonwillison.net/</a> - created using this tool.</p> <h2 dir="auto"><a id="user-content-dumping-a-database" class="anchor" aria-hidden="true" href="#user-content-dumping-a-database"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Dumping a database</h2> <p dir="auto">Given a SQLite database called <code>fixtures.db</code> containing a table <code>facetable</code>, the following will dump out that table to the <code>dump/</code> directory:</p> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="sqlite-diffable dump fixtures.db dump/ facetable"><pre class="notranslate"><code>sqlite-diffable dump fixtures.db dump/ facetable </code></pre></div> <p dir="auto">To dump out every table in that database, use <code>--all</code>:</p> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="sqlite-diffable dump fixtures.db dump/ --all"><pre class="notranslate"><code>sqlite-diffable dump fixtures.db dump/ --all </code></pre></div> <h2 dir="auto"><a id="user-content-loading-a-database" class="anchor" aria-hidden="true" href="#user-content-loading-a-database"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Loading a database</h2> <p dir="auto">To load a previously dumped database, run the following:</p> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="sqlite-diffable load restored.db dump/"><pre class="notranslate"><code>sqlite-diffable load restored.db dump/ </code></pre></div> <p dir="auto">This will show an error if any of the tables that are being restored already exist in the database file.</p> <p dir="auto">You can replace those tables (dropping them before restoring them) using the <code>--replace</code> option:</p> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="sqlite-diffable load restored.db dump/ --replace"><pre class="notranslate"><code>sqlite-diffable load restored.db dump/ --replace </code></pre></div> <h2 dir="auto"><a id="user-content-converting-to-json-objects" class="anchor" aria-hidden="true" href="#user-content-converting-to-json-objects"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Converting to JSON objects</h2> <p dir="auto">Table rows are stored in the <code>.ndjson</code> files as newline-delimited JSON arrays, like this:</p> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="["a", "a", "a-a", 63, null, 0.7364712141640124, "$null"] ["a", "b", "a-b", 51, null, 0.6020187290499803, "$null"]"><pre class="notranslate"><code>["a", "a", "a-a", 63, null, 0.7364712141640124, "$null"] ["a", "b", "a-b", 51, null, 0.6020187290499803, "$null"] </code></pre></div> <p dir="auto">Sometimes it can be more convenient to work with a list of JSON objects.</p> <p dir="auto">The <code>sqlite-diffable objects</code> command can read a <code>.ndjson</code> file and its accompanying <code>.metadata.json</code> file and output JSON objects to standard output:</p> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="sqlite-diffable objects fixtures.db dump/sortable.ndjson"><pre class="notranslate"><code>sqlite-diffable objects fixtures.db dump/sortable.ndjson </code></pre></div> <p dir="auto">The output of that command looks something like this:</p> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="{"pk1": "a", "pk2": "a", "content": "a-a", "sortable": 63, "sortable_with_nulls": null, "sortable_with_nulls_2": 0.7364712141640124, "text": "$null"} {"pk1": "a", "pk2": "b", "content": "a-b", "sortable": 51, "sortable_with_nulls": null, "sortable_with_nulls_2": 0.6020187290499803, "text": "$null"}"><pre class="notranslate"><code>{"pk1": "a", "pk2": "a", "content": "a-a", "sortable": 63, "sortable_with_nulls": null, "sortable_with_nulls_2": 0.7364712141640124, "text": "$null"} {"pk1": "a", "pk2": "b", "content": "a-b", "sortable": 51, "sortable_with_nulls": null, "sortable_with_nulls_2": 0.6020187290499803, "text": "$null"} </code></pre></div> <p dir="auto">Add <code>-o</code> to write that output to a file:</p> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="sqlite-diffable objects fixtures.db dump/sortable.ndjson -o output.txt"><pre class="notranslate"><code>sqlite-diffable objects fixtures.db dump/sortable.ndjson -o output.txt </code></pre></div> <p dir="auto">Add <code>--array</code> to output a JSON array of objects, as opposed to a newline-delimited file:</p> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="sqlite-diffable objects fixtures.db dump/sortable.ndjson --array"><pre class="notranslate"><code>sqlite-diffable objects fixtures.db dump/sortable.ndjson --array </code></pre></div> <p dir="auto">Output:</p> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="[ {"pk1": "a", "pk2": "a", "content": "a-a", "sortable": 63, "sortable_with_nulls": null, "sortable_with_nulls_2": 0.7364712141640124, "text": "$null"}, {"pk1": "a", "pk2": "b", "content": "a-b", "sortable": 51, "sortable_with_nulls": null, "sortable_with_nulls_2": 0.6020187290499803, "text": "$null"} ]"><pre class="notranslate"><code>[ {"pk1": "a", "pk2": "a", "content": "a-a", "sortable": 63, "sortable_with_nulls": null, "sortable_with_nulls_2": 0.7364712141640124, "text": "$null"}, {"pk1": "a", "pk2": "b", "content": "a-b", "sortable": 51, "sortable_with_nulls": null, "sortable_with_nulls_2": 0.6020187290499803, "text": "$null"} ] </code></pre></div> <h2 dir="auto"><a id="user-content-storage-format" class="anchor" aria-hidden="true" href="#user-content-storage-format"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Storage format</h2> <p dir="auto">Each table is represented as two files. The first, <code>table_name.metadata.json</code>, contains metadata describing the structure of the table. For a table called <code>redirects_redirect</code> that file might look like this:</p> <div class="highlight highlight-source-json notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="{ "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)" }"><pre>{ <span class="pl-ent">"name"</span>: <span class="pl-s"><span class="pl-pds">"</span>redirects_redirect<span class="pl-pds">"</span></span>, <span class="pl-ent">"columns"</span>: [ <span class="pl-s"><span class="pl-pds">"</span>id<span class="pl-pds">"</span></span>, <span class="pl-s"><span class="pl-pds">"</span>domain<span class="pl-pds">"</span></span>, <span class="pl-s"><span class="pl-pds">"</span>path<span class="pl-pds">"</span></span>, <span class="pl-s"><span class="pl-pds">"</span>target<span class="pl-pds">"</span></span>, <span class="pl-s"><span class="pl-pds">"</span>created<span class="pl-pds">"</span></span> ], <span class="pl-ent">"schema"</span>: <span class="pl-s"><span class="pl-pds">"</span>CREATE TABLE [redirects_redirect] (<span class="pl-cce">\n</span> [id] INTEGER PRIMARY KEY,<span class="pl-cce">\n</span> [domain] TEXT,<span class="pl-cce">\n</span> [path] TEXT,<span class="pl-cce">\n</span> [target] TEXT,<span class="pl-cce">\n</span> [created] TEXT<span class="pl-cce">\n</span>)<span class="pl-pds">"</span></span> }</pre></div> <p dir="auto">It is an object with three keys: <code>name</code> is the name of the table, <code>columns</code> is an array of column strings and <code>schema</code> is the SQL schema text used for tha table.</p> <p dir="auto">The second file, <code>table_name.ndjson</code>, contains <a href="http://ndjson.org/" rel="nofollow">newline-delimited JSON</a> 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.</p> <p dir="auto">That file for the <code>redirects_redirect.ndjson</code> table might look like this:</p> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="[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"]"><pre class="notranslate"><code>[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"] </code></pre></div> </article></div> | 1 | public | 0 | 0 |
Links from other tables
- 6 rows from repo in releases