SQLite Patterns¶

wlite implements the same SQLite patterns as dbwarden. This page explains each pattern, why it exists, and when it triggers.

Table rebuilds¶

Why rebuilds exist¶

SQLite's ALTER TABLE is limited. It can rename a table, add a column, or rename a column (SQLite 3.25+). That is all. It cannot change a column type, change nullability, modify a default, drop a column (until 3.35), or alter constraints.

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.

When rebuilds trigger¶

A rebuild is triggered when any of these change:

When rebuilds do NOT trigger¶

These changes use SQLite's ALTER TABLE directly:

Rebuild SQL¶

A table rebuild generates this sequence:

-- 1. Disable foreign key checks
PRAGMA foreign_keys = OFF;

-- 2. 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
);

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

-- 4. Drop old table
DROP TABLE users;

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

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

-- 7. Re-enable foreign key checks
PRAGMA foreign_keys = ON;

Data preservation¶

During a rebuild, wlite preserves all data. The INSERT INTO ... SELECT copies every row. If a new NOT NULL column is added without a DEFAULT, wlite uses a reasonable fallback:

Collapse logic¶

The problem¶

If you change two things about a table in the same migration (e.g., change a column type AND add a new column), a naive tool would rebuild the table twice. That is wasteful and slow.

The solution¶

wlite collapses multiple rebuilds on the same table into a single rebuild. The collapse happens during the planning stage, after the diff is computed.

Example¶

-- Model changes:
--   1. field name text  -->  field name text { not_null }
--   2. add field bio text

-- Without collapse (2 rebuilds):
CREATE TABLE users__wlite_new (...);
INSERT INTO users__wlite_new (...) SELECT ... FROM users;
DROP TABLE users;
ALTER TABLE users__wlite_new RENAME TO users;
CREATE TABLE users__wlite_new (...);
INSERT INTO users__wlite_new (...) SELECT ... FROM users;
ALTER TABLE users__wlite_new RENAME TO users;

-- With collapse (1 rebuild):
CREATE TABLE users__wlite_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    bio TEXT
) AS SELECT id, name FROM users;
DROP TABLE users;
ALTER TABLE users__wlite_new RENAME TO users;

When collapse does NOT apply¶

• Changes to different tables are never collapsed
• Adding a column (without other changes) does not trigger a rebuild, so there is nothing to collapse
• Drops and rebuilds on the same table are handled separately

Type normalization¶

Why normalization exists¶

SQLite stores types as strings and uses affinity rules. The type INT and the type INTEGER are the same thing to SQLite, but a naive diff tool would see them as different and trigger an unnecessary migration.

wlite normalizes types before comparison so equivalent types do not trigger migrations.

Normalization rules¶

Example¶

-- Model says:  field age INTEGER
-- Database:    age INT

-- wlite sees these as equivalent. No migration generated.

-- Model says:  field age INTEGER
-- Database:    age REAL

-- wlite sees a type change. Migration generated:
-- CREATE TABLE users__wlite_new (...);
-- INSERT INTO users__wlite_new (...) SELECT ... FROM users;
-- DROP TABLE users;
-- ALTER TABLE _staging RENAME TO ...;

Default handling¶

Semantic comparison¶

Default values are compared semantically, not textually. These pairs are equivalent:

When defaults trigger rebuilds¶

Changing a default value does NOT trigger a rebuild in most cases. SQLite supports ALTER TABLE ... ALTER COLUMN ... SET DEFAULT in recent versions. wlite uses this when available.

A rebuild is only triggered if the default change is combined with other changes that require a rebuild (type change, nullability change, etc.).

Constraint diffing¶

Primary keys¶

Primary keys are compared column-by-column. If the columns in the primary key change, a rebuild is triggered.

-- Model:  primary_key (id)
-- DB:     PRIMARY KEY (id, name)
-- Result: rebuild to change PK

Unique constraints¶

Unique constraints are compared by their column sets. Adding or removing a unique constraint triggers a rebuild.

-- Model:  unique (user_id, email)
-- DB:     (no unique constraint)
-- Result: rebuild to add UNIQUE (user_id, email)

Foreign keys¶

Foreign keys are compared by their target table, target column, and actions (ON DELETE, ON UPDATE). Any change triggers a rebuild.

-- Model:  references User.id on delete cascade
-- DB:     references User.id on delete restrict
-- Result: rebuild to change FK action

Check constraints¶

Check constraints are compared by their expression. Any change triggers a rebuild.

-- Model:  check (end_time > start_time)
-- DB:     check (end_time >= start_time)
-- Result: rebuild to change CHECK expression

STRICT mode¶

When a model has the strict option, wlite generates:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
) STRICT;

STRICT mode enforces that every column must have a type and that values match the declared type. Enabling or disabling STRICT triggers a rebuild.

WITHOUT ROWID¶

When a model has the without rowid option, wlite generates:

CREATE TABLE user_preferences (
    user_id INTEGER,
    key TEXT,
    value TEXT,
    PRIMARY KEY (user_id, key)
) WITHOUT ROWID;

WITHOUT ROWID tables are smaller and faster for tables with a composite primary key and no integer primary key. Enabling or disabling WITHOUT ROWID triggers a rebuild.

Partial indexes¶

Partial indexes include a WHERE clause that limits which rows are indexed:

index active_users {
    on users(email)
    where active = true
}

Generates:

CREATE INDEX active_users ON users (email) WHERE active = true;

Partial indexes are smaller and faster because they only index rows matching the WHERE clause.

Generated columns¶

Generated columns are computed from an expression. They can be stored (computed on write) or virtual (computed on read).

field search_name text {
    generated (lower(name)) stored
}

Generates:

ALTER TABLE users ADD COLUMN search_name TEXT AS (lower(name)) STORED;

Changing a generated column's expression or type triggers a rebuild.

What wlite does NOT do¶

• Data transformations: wlite only changes schema, not data. If you rename a column, the data is preserved. If you change a type, the data is copied as-is.
• Backups: wlite does not back up your database before migration. Use your own backup strategy.
• Incremental migrations: each migration is the full diff from current state to desired state. There is no chain of revision scripts.
• Migration history: the schema state is the source of truth. wlite does not track which migrations have been applied.
• Data validation: wlite does not validate that existing data matches new constraints. If you add NOT NULL to a column with existing NULLs, the rebuild will use a fallback value.