<story><title>Simple declarative schema migration for SQLite</title><url>https://david.rothlis.net/declarative-schema-migration-for-sqlite/</url></story><parent_chain></parent_chain><comment><author>simonw</author><text>For anyone who needs to implement the 12 step procedure (&lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;www.sqlite.org&amp;#x2F;lang_altertable.html#otheralter&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;www.sqlite.org&amp;#x2F;lang_altertable.html#otheralter&lt;/a&gt;) from the SQLite documentation for applying complex alters, I&amp;#x27;ve built a CLI tool and Python library that can apply that for you.&lt;p&gt;CLI example: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;sqlite-utils.datasette.io&amp;#x2F;en&amp;#x2F;stable&amp;#x2F;cli.html#cli-transform-table&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;sqlite-utils.datasette.io&amp;#x2F;en&amp;#x2F;stable&amp;#x2F;cli.html#cli-tra...&lt;/a&gt;&lt;p&gt;&lt;pre&gt;&lt;code&gt;    sqlite-utils transform fixtures.db roadside_attractions \
      --rename pk id \
      --default name Untitled \
      --type longitude float \
      --type latitude float \
      --drop address
&lt;/code&gt;&lt;/pre&gt;
Python example: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;sqlite-utils.datasette.io&amp;#x2F;en&amp;#x2F;stable&amp;#x2F;python-api.html#python-api-transform&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;sqlite-utils.datasette.io&amp;#x2F;en&amp;#x2F;stable&amp;#x2F;python-api.html#...&lt;/a&gt;&lt;p&gt;&lt;pre&gt;&lt;code&gt;    table.tranform(
        rename={&amp;quot;pk&amp;quot;:&amp;quot;id&amp;quot;},
        defaults={&amp;quot;name&amp;quot;: &amp;quot;Untitled&amp;quot;},
        types={
            &amp;quot;latitude&amp;quot;: float,
            &amp;quot;longitude&amp;quot;: float
        },
        drop={&amp;quot;address&amp;quot;}
    )
&lt;/code&gt;&lt;/pre&gt;
Wrote more about those here: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;simonwillison.net&amp;#x2F;2020&amp;#x2F;Sep&amp;#x2F;23&amp;#x2F;sqlite-advanced-alter-table&amp;#x2F;&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;simonwillison.net&amp;#x2F;2020&amp;#x2F;Sep&amp;#x2F;23&amp;#x2F;sqlite-advanced-alter-...&lt;/a&gt;&lt;p&gt;(Just noticed this is already mentioned at the bottom of the blog entry!)</text></comment>
<story><title>Simple declarative schema migration for SQLite</title><url>https://david.rothlis.net/declarative-schema-migration-for-sqlite/</url></story><parent_chain></parent_chain><comment><author>simonw</author><text>This is an interesting approach to this problem.&lt;p&gt;I like the idea of building the new schema as an in-memory database. You could even go a step further and compare them using a SQL query that joins across the two databases (SQLite supports this, it&amp;#x27;s pretty neat).&lt;p&gt;I&amp;#x27;m a bit nervous about how edge-cases might screw things up, but a nice thing about SQLite is that backups are really cheap so I guess you can protect against any risks by creating a backup before running this script.&lt;p&gt;My preferred approach to database migrations is the one implemented by Django: migrations are a sequence of transformations stored in files on disk, and the database includes a table that says which of those migrations have been applied. This keeps everything in version control and means there&amp;#x27;s no chance of the wrong migration being applied in the wrong way.&lt;p&gt;It&amp;#x27;s quite a bit more work to setup though. I don&amp;#x27;t have my own Django-style migration system for SQLite yet and I really need one.&lt;p&gt;I really love how clean and short the implementation of this is! &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;david.rothlis.net&amp;#x2F;declarative-schema-migration-for-sqlite&amp;#x2F;migrator.py&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;david.rothlis.net&amp;#x2F;declarative-schema-migration-for-s...&lt;/a&gt;</text></comment>