Migration Internals¶

This page explains how libwlite converts a schema diff into executable SQL. Understanding this helps you predict what libwlite will do and why.

The problem¶

SQLite's ALTER TABLE is limited. It can:

Rename a table
Add a new column
Rename a column (SQLite 3.25+)

It cannot:

Change a column type
Change a column's NULL/NOT NULL constraint
Change a column's DEFAULT value
Drop a column
Add or modify table-level constraints (PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY)

When you need to do any of these, you must rebuild the table: create a new table with the correct schema, copy the data, drop the old table, and rename the new one.

How libwlite handles this¶

Step 1: Classify each difference¶

When libwlite compares the model against the database, each difference is classified:

Classification	Example	Action
Additive	New column, new table	`ALTER TABLE ... ADD COLUMN` or `CREATE TABLE`
Subtractive	Drop column, drop table	`ALTER TABLE ... DROP COLUMN` or `DROP TABLE`
Rebuild	Change column type, change DEFAULT, change constraints	Full table rebuild

The classification is done by the diff engine. The planner uses these classifications to decide which SQL to generate.

Note: there is no separate "Alterable" path for changing a column's DEFAULT value. SQLite does not support ALTER COLUMN ... SET DEFAULT, so any change to a column's default always triggers a full table rebuild.

Step 2: Expand rebuilds¶

A table rebuild is expanded into a sequence of SQL statements:

-- 1. Create staging table with correct schema
CREATE TABLE users__wlite_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 2. Copy data from old table
INSERT INTO users__wlite_new (id, name, email, created_at)
SELECT id, name, email, created_at FROM users;

-- 3. Drop old table
DROP TABLE IF EXISTS users;

-- 4. Rename staging to final name
ALTER TABLE users__wlite_new RENAME TO users;

-- 5. Recreate indexes
CREATE INDEX IF NOT EXISTS users_email ON users (email);

The staging table name is derived from the original table name with a __wlite_new suffix. This avoids collisions with other tables and marks the table as temporary during migration.

The planner generates a separate CREATE TABLE statement and INSERT INTO ... SELECT statement. It does not use the CREATE TABLE ... AS SELECT shorthand. This is intentional: the CREATE TABLE must define the full schema (constraints, defaults, collations), while INSERT INTO ... SELECT copies the data.

The column list in the INSERT INTO is computed by intersecting the columns of the old and new table schemas. Columns that exist in both schemas are included, excluding generated columns (which SQLite computes automatically).

Step 3: Plan ordering¶

The planner orders statements to respect dependencies:

Create new tables (no foreign key references yet)
Add columns to existing tables
Rebuild tables that need schema changes
Create indexes (after tables exist)
Create views and triggers

Foreign key dependencies affect ordering. If table B references table A, table A is created first. The planner performs a topological sort on the dependency graph.

Step 4: Collapse rebuilds¶

Multiple rebuilds on the same table are collapsed into one. If you change both the type of a column and add a new column in the same migration, libwlite does one rebuild instead of two.

Before collapse:
  ALTER TABLE users ADD COLUMN created_at ...;
  ALTER TABLE users ADD COLUMN bio ...;     -- another rebuild?

After collapse:
  ALTER TABLE users ADD COLUMN created_at ...;
  -- single rebuild with all changes
  CREATE TABLE users__wlite_new (...);
  INSERT INTO users__wlite_new (...) SELECT ...;
  DROP TABLE IF EXISTS users;
  ALTER TABLE users__wlite_new RENAME TO users;

The collapse happens during planning, after the diff is computed. The planner groups all rebuild-triggering changes for each table and produces a single rebuild sequence.

Foreign key handling¶

When rebuilding a table with foreign keys, libwlite:

Disables foreign key checks during the rebuild (PRAGMA foreign_keys = OFF)
Creates the staging table with the correct foreign key constraints
Copies the data
Drops the old table
Renames the staging table
Re-enables foreign key checks

This prevents cascading deletes from firing during the rebuild.

Cascading tables¶

If the rebuilt table is referenced by other tables with foreign keys, those references remain valid after the rebuild. SQLite tracks tables by name, and the rename at the end preserves the name. The foreign key references continue to point to the correct table.

However, if the rebuild changes the primary key columns, any foreign keys that reference those columns will break. libwlite does not automatically fix these. You must update the referencing tables in the same migration.

Index handling¶

Indexes are rebuilt as part of the migration. If a table is rebuilt, its indexes are dropped and recreated. New indexes are created after the table they reference exists.

-- Table rebuild
CREATE TABLE users__wlite_new (...);
INSERT INTO users__wlite_new (...) SELECT ...;
DROP TABLE IF EXISTS users;
ALTER TABLE users__wlite_new RENAME TO users;

-- Indexes recreated
CREATE INDEX IF NOT EXISTS users_email ON users (email);
CREATE INDEX IF NOT EXISTS users_username ON users (username);

Index creation uses IF NOT EXISTS to avoid errors if the index already exists. This is a safety measure, not a normal case.

Comment handling¶

Table and column comments are stored using SQLite's COMMENT ON syntax (when supported) or as metadata in the schema. Changes to comments do not trigger table rebuilds.

Rollback¶

Every migration is wrapped in a transaction. If any step fails, the entire migration is rolled back. The database returns to its pre-migration state.

// libwlite_migrate wraps everything in:
BEGIN;
  CREATE TABLE users__wlite_new (...);
  INSERT INTO users__wlite_new (...) SELECT ...;
  DROP TABLE IF EXISTS users;
  ALTER TABLE users__wlite_new RENAME TO users;
  CREATE INDEX ...;
COMMIT;
-- If any step fails: ROLLBACK

The transaction ensures atomicity. Either all changes are applied or none are. There is no partial state.

Every plan step also includes rollback SQL. For a rebuild, the rollback SQL reverses the operation by rebuilding the table back to its original schema. This means rollback is always possible without data loss.

Checksums¶

After a successful migration, libwlite computes a checksum of the schema state. This checksum is used by wl_schema_verify to verify the database matches the model without re-introspecting the entire schema.

The checksum is an FNV-1a hash of the normalized schema structure (table names, column names, types, constraints, defaults). FNV-1a is used because it is fast, has good distribution, and requires no external dependencies.

The checksum is computed from the introspected schema after migration, not from the model. This ensures the checksum reflects the actual database state.

What libwlite does NOT do¶

It does not generate data transformations (only schema changes)
It does not back up data before migration (use your own backup strategy)
It does not apply migrations incrementally (each migration is the full diff from current state to desired state)
It does not track migration history (the schema state is the source of truth, not a chain of migrations)
It does not validate data against new constraints (if you add NOT NULL to a column with existing NULLs, the rebuild uses a fallback value)

Examples¶

Adding a column¶

-- model adds: field bio text
ALTER TABLE users ADD COLUMN bio TEXT;

Changing a column type¶

-- model changes: field age integer -> field age real
CREATE TABLE users__wlite_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age REAL
);
INSERT INTO users__wlite_new (id, name, age)
SELECT id, name, age FROM users;
DROP TABLE IF EXISTS users;
ALTER TABLE users__wlite_new RENAME TO users;

Changing a column DEFAULT¶

-- model changes: field role text default 'user' -> default 'member'
CREATE TABLE users__wlite_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'member'
);
INSERT INTO users__wlite_new (id, name, role)
SELECT id, name, role FROM users;
DROP TABLE IF EXISTS users;
ALTER TABLE users__wlite_new RENAME TO users;

SQLite does not support ALTER COLUMN ... SET DEFAULT, so this triggers a full table rebuild.

Adding a NOT NULL column without DEFAULT¶

-- model adds: field email text { not_null }
-- SQLite cannot add NOT NULL without DEFAULT via ALTER TABLE
CREATE TABLE users__wlite_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL
);
INSERT INTO users__wlite_new (id, name, email)
SELECT id, name, COALESCE(email, '') FROM users;
DROP TABLE IF EXISTS users;
ALTER TABLE users__wlite_new RENAME TO users;

Dropping a table¶

-- model removes: model OldTable { ... }
DROP TABLE old_table;

Adding an index¶

-- model adds: index users_email { on users(email) }
CREATE INDEX IF NOT EXISTS users_email ON users (email);

Changing a primary key¶

-- model changes: primary_key (id) -> primary_key (id, tenant_id)
CREATE TABLE users__wlite_new (
    id INTEGER,
    tenant_id INTEGER,
    name TEXT NOT NULL,
    PRIMARY KEY (id, tenant_id)
);
INSERT INTO users__wlite_new (id, tenant_id, name)
SELECT id, tenant_id, name FROM users;
DROP TABLE IF EXISTS users;
ALTER TABLE users__wlite_new RENAME TO users;

Adding a foreign key¶

-- model adds: field org_id integer { references Organization.id }
-- This requires a rebuild because SQLite cannot add foreign keys via ALTER TABLE
CREATE TABLE users__wlite_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    org_id INTEGER REFERENCES Organization(id)
);
INSERT INTO users__wlite_new (id, name, org_id)
SELECT id, name, org_id FROM users;
DROP TABLE IF EXISTS users;
ALTER TABLE users__wlite_new RENAME TO users;

Enabling STRICT mode¶

-- model adds: strict
CREATE TABLE users__wlite_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
) STRICT;
INSERT INTO users__wlite_new (id, name)
SELECT id, name FROM users;
DROP TABLE IF EXISTS users;
ALTER TABLE users__wlite_new RENAME TO users;

Edge cases¶

Self-referencing foreign keys¶

If a table has a foreign key that references itself, the rebuild handles it correctly. The foreign key checks are disabled during the rebuild, so the self-reference does not cause a constraint violation.

Circular foreign keys¶

If table A references table B and table B references table A, both tables must be rebuilt in a specific order. libwlite handles this by rebuilding tables in topological order, with foreign key checks disabled during the rebuild.

Tables with no columns¶

SQLite allows tables with no columns. libwlite handles this edge case. The staging table has the same column list as the original (empty), and the data copy is a no-op.

Views that reference rebuilt tables¶

Views are not affected by table rebuilds. Views are defined by SQL queries, not by table structure. If a view references a table that is rebuilt, the view continues to work after the rebuild because the table name is preserved.

Triggers on rebuilt tables¶

Triggers that reference a rebuilt table are not affected. SQLite stores triggers by name and SQL text, not by table structure. The trigger continues to fire after the rebuild.

Materialized views¶

libwlite does not support materialized views. SQLite does not have native materialized view support. If you need materialized views, implement them in your application layer.

Tables with generated columns¶

Generated columns are handled correctly during rebuilds. The staging table includes the generated column definition, and SQLite computes the values during the INSERT. The data copy does not include the generated column in the SELECT list.

Tables with STRICT mode¶

When rebuilding a STRICT table, the staging table includes the STRICT keyword. This ensures the rebuild preserves the strict type enforcement.

Tables with WITHOUT ROWID¶

When rebuilding a WITHOUT ROWID table, the staging table includes the WITHOUT ROWID keyword. The primary key columns are included in the INSERT SELECT.

Recovery from failed migration¶

If a migration fails, the transaction is rolled back and the database returns to its pre-migration state. There is no partial state. The error message includes the failing SQL and the SQLite error, making it straightforward to diagnose and fix the issue.

Performance of rebuilds¶

Table rebuilds are the most expensive operation in a migration. The cost is proportional to the number of rows in the table. For large tables (millions of rows), a rebuild can take seconds to minutes. libwlite does not optimize this because SQLite's own INSERT ... SELECT is the fastest way to copy data.

Multi-table migrations¶

When multiple tables need changes, libwlite processes them in dependency order. Tables with no foreign key dependencies are processed first. Tables that are referenced by other tables are processed before the tables that reference them. This ensures foreign key constraints are never violated during the migration.

Summary¶

The migration pipeline is straightforward: classify, expand, order, collapse, execute. Each step is deterministic and produces predictable SQL. The transaction wrapper ensures atomicity. The checksum ensures verifiability. libwlite does not do anything surprising.