Manual Schema Migrations

I have been working with a small SQLite database recently and while I am using an ORM, I haven't yet committed to automating schema migrations. What does it take to manage those by hand?

Manual? What is the alternative?

A common approach is to use a migration tool to handle changes to table schemas. A few popular options include:

• Alembic
• sqlalchemy-migrate
• South/Django migrations

Most people aren't managing schema updates by hand. The reason for this is simple, it can be a bear to deal with. So let's get started!

Limitations of SQLite

A few of the issues here are in fact limitations of SQLite itself, rather than of SQL or relational databases. Because I am so enamored with the little database, I'll be working around those issues.

The Data Model

I began with three tables, below is the approximate data model:

Request

Client

Product Area

Changes

As I develop the application it has become clear my initial data model was lacking. I will be re-working things to meet the following criteria:

Request

The Request table needs two fields added (completed, priority_id), needs a field removed (url) and needs a foreign key from priority_id to the Priority table (which needs to be created). Client and Product Area remain unchanged.

Priority

Starting Somewhere

The above can be broken down into a few component parts

• Add a field
• Delete a field
• Add a foreign key
• Add a table

There is a small hitch in the above break-down that I only discovered after getting started. SQLite cannot add a foreign key to an existing table, you are instead required to recreate the entire existing table with the new foreign key added (before you migrate your data in other words). I need a foreign key in the Requests table from the new field (priority_id) referencing the new table (Priority).

Add a table

This is the most familiar, it is no different than how the schema was initially defined:

  CREATE TABLE priority (
         id    INTEGER NOT NULL,
         name  VARCHAR,
         PRIMARY KEY (id)
  );

Add a field, delete a field, and add a foreign key

Here we get to a more cumbersome step, SQLite does not support deleting columns from a table, instead the standard advice is to recreate the table as necessary and migrate the data between a temporary table. I am not using a temporary table here because I needed to additionally add a foreign key (which relies on a similar process of redefining the entire table).

I do a combination INSERT and SELECT into a new table which is eventually renamed to the existing table (Request).

  BEGIN TRANSACTION;
    CREATE TABLE modified_request (
            id INTEGER NOT NULL,
            title VARCHAR,
            description TEXT,
            target_date DATETIME,
            completed BOOLEAN DEFAULT 0,
            product_area_id INTEGER,
            client_id INTEGER,
            priority_id INTEGER,
            PRIMARY KEY (id),
            FOREIGN KEY(product_area_id) REFERENCES product_area (id),
            FOREIGN KEY(client_id) REFERENCES client (id),
            FOREIGN KEY(priority_id) REFERENCES priority (id)
    );

    INSERT INTO modified_request (
           id,
           title,
           description,
           target_date,
           product_area_id,
           client_id
    ) SELECT id, title, description, target_date, product_area_id, client_id FROM request;

    DROP TABLE request;
    ALTER TABLE modified_request RENAME TO request;
  COMMIT;

That wasn't so bad...

Doing the above took a little while for me, I kept referencing the documentation to make sure I had the right syntax. However, the results aren't very surprising; coming from a SQL background. It is declarative and very straight-forward, with little-to-no "magic" to confuse things.

I may be biased, I already liked SQL to begin with. But I must confess, I would much rather work with plain-old SQL than learn and re-learn tools that change each time you replace an ORM or framework. It probably isn't feasible to completely detach from some of these trends, but it sure is refreshing to get to the heart of how something like a database migration actually works.