Skip to content

SQLite Migration strategy

Jump to bottom
Rakshith Ravi edited this page Sep 16, 2024 · 1 revision

Sqlite Migrations

Ok so sqlite migrations are a bit weird, as discussed, they don’t support the full fleet of migration features provided by something like Postgres, but it offers a smaller subset of the ALTER commands. The commands given to us are

  • Rename Table
  • Rename Column
  • Add Column
  • Drop Column

Further more each of these columns have several restrictions on them as well, for example the ALTER TABLE DROP COLUMN will fail if any one of the following is true:

Django’s official docs mention this and give a small set of steps on how to overcome this limitation:

  • Creating a new table with the new schema
  • Copying the data across
  • Dropping the old table
  • Renaming the new table to match the original name

This is essentially a boiled down version of what's already explained in the official SQLite docs, which I'm going to try and encapsulate here. This should enable us to perform migrations, albeit with a bit more complexity than I would've preferred, but I've seen this same pattern use everywhere (I haven't yet checked gitea's codebase, I'll update this when I get to that)

The Migration Process

  1. Disable the Foreign Key Constraints using PRAGMA foreign_keys=OFF
  2. Start a transaction
  3. Store all the indices, triggers, and all that stuff of table <table_name> by running something like SELECT type,sql FROM sqlite_schema WHERE tbl_name='<table_name>';
  4. Create a new table, CREATE TABLE new_<table_name> that contains the desired changes.
  5. Copy all the data from the old table to the new one using something like
INSERT INTO new_<table_name> (column1, column2, ...)
SELECT  column1, column2, ...
FROM <table_name>;
  1. DROP TABLE <table_name>
  2. Change the name of the new table to the original one ALTER TABLE new_<table_name> RENAME <table_name>
  3. Use CREATE INDEX, CREATE TRIGGER, and CREATE VIEW to reconstruct indexes, triggers, and views associated with table X. Perhaps use the old format of the triggers, indexes, and views saved from step 3 above as a guide, making changes as appropriate for the alteration.
  4. Verify the foreign key constraints using PRAGMA foreign_key_check to make sure that the new schema changes didn't violate any foreign key constraints.
  5. Commit the transaction
  6. Re-enable foreign key constraints using PRAGMA foreign_keys=ON

I'm also outlining another method for making alterations that do not affect the content, but does stuff like removing a CHECK, FOREIGN KEY, NOT NULL constraints, or modify the DEFAULT values on a column. This is also taken from the sqlite docs, and is faster than the previous process, but this can't do a lot stuff like adding a FOREIGN KEY constraint.

  1. Start a transaction
  2. Enable schema editing using PRAGMA writable_schema=ON.
  3. Update the definition of the table in the sqlite_schema table using:
UPDATE sqlite_schema 
SET sql='<new_sql_for_the_table>'
WHERE type='table' AND
name='<table_name>';

CAUTION: Making a change to the sqlite_schema table like this will render the database corrupt and unreadable if the change contains a syntax error. It is suggested that careful testing of the UPDATE statement be done on a separate blank database prior to using it on a database containing important data

  1. If the changes affect some other tables, make corresponding changes to those tables as well, again making sure that the changes are tested on a blank db beforehand.
  2. Disable schema editing using PRAGMA writable_schema=OFF.
  3. Run PRAGMA integrity_check to verify that the schema changes did not damage the database
  4. Commit the transaction

Clone this wiki locally