<story><title>Tracking SQLite Database Changes in Git</title><url>https://garrit.xyz/posts/2023-11-01-tracking-sqlite-database-changes-in-git</url></story><parent_chain><item><author>simonw</author><text>This approach works by storing the actual SQLite binary files in Git and then using a custom &amp;quot;diff&amp;quot; configuration to dump each file as SQL and compare the result.&lt;p&gt;It&amp;#x27;s a neat trick, but storing binary files like that in Git isn&amp;#x27;t as space efficient as using a plain text format.&lt;p&gt;I built my own tooling to solve this problem: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;datasette.io&amp;#x2F;tools&amp;#x2F;sqlite-diffable&quot; rel=&quot;nofollow noreferrer&quot;&gt;https:&amp;#x2F;&amp;#x2F;datasette.io&amp;#x2F;tools&amp;#x2F;sqlite-diffable&lt;/a&gt; - which outputs a “diffable” copy of the data in a SQLite database, precisely so you can store it in Git and look at the differences later.&lt;p&gt;I’ve been running that for a couple of years in this repo: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;simonw&amp;#x2F;simonwillisonblog-backup&quot;&gt;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;simonw&amp;#x2F;simonwillisonblog-backup&lt;/a&gt; - which provides a backup of my blog’s PostgreSQL Django database (first converted to SQLite and then dumped out using sqlite-diffable).&lt;p&gt;Here’s an example diff: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;simonw&amp;#x2F;simonwillisonblog-backup&amp;#x2F;commit&amp;#x2F;72e73b2cdd714fb1f3cd87d6a752971fc6398890&quot;&gt;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;simonw&amp;#x2F;simonwillisonblog-backup&amp;#x2F;commit&amp;#x2F;72...&lt;/a&gt;</text></item></parent_chain><comment><author>chlorion</author><text>I don&amp;#x27;t think there will be any huge difference between a text dump and binary formats here, git doesn&amp;#x27;t really care about the underlying data when it stores and packs it.&lt;p&gt;Maybe a text format that is sorted before saved would compress a lot better though, both with zlib compression and gits delta compression. You can&amp;#x27;t really sort a binary file and put it back together!</text></comment>
<story><title>Tracking SQLite Database Changes in Git</title><url>https://garrit.xyz/posts/2023-11-01-tracking-sqlite-database-changes-in-git</url></story><parent_chain><item><author>simonw</author><text>This approach works by storing the actual SQLite binary files in Git and then using a custom &amp;quot;diff&amp;quot; configuration to dump each file as SQL and compare the result.&lt;p&gt;It&amp;#x27;s a neat trick, but storing binary files like that in Git isn&amp;#x27;t as space efficient as using a plain text format.&lt;p&gt;I built my own tooling to solve this problem: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;datasette.io&amp;#x2F;tools&amp;#x2F;sqlite-diffable&quot; rel=&quot;nofollow noreferrer&quot;&gt;https:&amp;#x2F;&amp;#x2F;datasette.io&amp;#x2F;tools&amp;#x2F;sqlite-diffable&lt;/a&gt; - which outputs a “diffable” copy of the data in a SQLite database, precisely so you can store it in Git and look at the differences later.&lt;p&gt;I’ve been running that for a couple of years in this repo: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;simonw&amp;#x2F;simonwillisonblog-backup&quot;&gt;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;simonw&amp;#x2F;simonwillisonblog-backup&lt;/a&gt; - which provides a backup of my blog’s PostgreSQL Django database (first converted to SQLite and then dumped out using sqlite-diffable).&lt;p&gt;Here’s an example diff: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;simonw&amp;#x2F;simonwillisonblog-backup&amp;#x2F;commit&amp;#x2F;72e73b2cdd714fb1f3cd87d6a752971fc6398890&quot;&gt;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;simonw&amp;#x2F;simonwillisonblog-backup&amp;#x2F;commit&amp;#x2F;72...&lt;/a&gt;</text></item></parent_chain><comment><author>infamia</author><text>&amp;gt; I’ve been running that for a couple of years in this repo: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;simonw&amp;#x2F;simonwillisonblog-backup&quot;&gt;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;simonw&amp;#x2F;simonwillisonblog-backup&lt;/a&gt; - which provides a backup of my blog’s PostgreSQL Django database (first converted to SQLite and then dumped out using sqlite-&lt;p&gt;I&amp;#x27;m curious, what is the reason you chose not to use pgdump, but instead opted to convert to to sqlite and then dump the DB using sqlite-diffable?&lt;p&gt;On a project I&amp;#x27;m working on, I&amp;#x27;d like to dump our Postgres schema into individual files for each object (i.e., one file for each table, function, stored proc, etc.), but haven&amp;#x27;t spent enough time to see if pgdump could actually do that. We&amp;#x27;re just outputting files by object type for now (one tables, function, and stored procs files). It looks like sqlite-diffable does something similar to what we&amp;#x27;re looking for (separate files for each object).</text></comment>