Successfully Altering Tables in SQLite with Attached Views

Previous Post: Re-tagging docker images easily

Back to all posts.

SQLite is a neat database that is often used in client applications and development, but with some configuration, can absolutely be used to run production website larger than you expect. With the proper settings, you can even support multiple concurrent users, reading and writing to the same database (as long as it’s all on a single server).

In my case, I’m specifically using SQLite as the backing database for a Django application that I use to track expenses. Using SQLite’s power, I built a couple of views to summerize and aggregate the data (monthly, yearly, & overall totals, etc.). Django provides a facility, django.db.migrations.RunSQL, to run arbitrary SQL as part of the migration. Using this, I was able to get the django migration to automatically generate the view as part of normal operations.

Things went along great until I needed to alter one of the tables. All of a sudden, the migration with the table alteration failed, with the view complaining that the table didn’t exist:

django.db.utils.OperationalError: error in view view_name_here: no such table: main.table_name_here

However, as I could see, the table clearly existed! What was going on?

Investigation

The first clue came once I started collecting the SQL commands that django generated.

To add a field to the table, instead of simply altering the table, django first created a new table, then dropped the existing table, and finally renamed the new table to the old name. This is the recommended method as it ensures that the database maintains compatibility with SQLite pre-3.13.

CREATE TABLE temp_table_name (existing fields and old fields);
INSERT INTO temp_table_name (existing fields) SELECT existing fields FROM table_name;
-- Here's the issue
DROP TABLE table_name;
ALTER TABLE temp_table_name RENAME TO table_name;

The issue is that SQLite verifies the integrity of the schema after each command. If the integrity is violated, such as when a VIEW (or TRIGGER) refers to a table that does not exist, it fails and aborts the change.

Solution

Looking at SQLite’s ALTER TABLE documentation, we see two options, a proper one and a quicker but more dangerous one.

The proper method involves:

  1. disabling foreign key constraints if enabled;

  2. starting a transaction;

  3. storing the schema for any index, trigger, or view associated with the table to be deleted;

  4. create the new table (with a new name) and transfer the data to it;

  5. drop the indexes, triggers, views associated with the old table and the old table itself;

  6. rename the new table to the old name;

  7. recreate the indexes, triggers, views;

  8. if foreign key constraints were enabled, run a foreign key check;

  9. commit the transaction;

  10. if foreign key constraints where enabled, re-enable them.

This is quite a lengthy procedure for a rather simple change.

Instead, I chose to go with the riskier but quicker solution. SQLite >= 3.38.0 includes a PRAGMA setting called writable_schema. When this setting is set to ON, SQLite silently ignores any errors in the schema such as a missing table. Setting it to RESET both enables the error checking and forces a schema reload, triggering validation.

In other words, I would prepend a raw SQL command to disable schema validation and append another SQL command to re-enable it to any migration that altered the tables that had dependent views.

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ("module_name", "####_previous_migration_name"),
    ]

    operations = [
        migrations.RunSQL("PRAGMA writable_schema=ON"),
        # Existing migrations here
        migrations.RunSQL("PRAGMA writable_schema=RESET"),
    ]