SQLite Schema Migration Best Practices

SQLite powers more applications than almost any other database — iOS apps, Android apps, Chrome internals, Python prototypes, and embedded IoT devices. But SQLite's ALTER TABLE is famously limited. You can rename a table and add a column. That is it.

Want to drop a column? Change a TEXT to an INTEGER? Add a UNIQUE constraint? Rename a column? You must rebuild the entire table. Get this wrong and you corrupt data, break foreign keys, or lose rows silently.

This guide shows you the safe table-rebuild pattern, common mistakes that trip up even experienced developers, and how to automate SQLite schema diffs in your workflow.

What SQLite ALTER TABLE Actually Supports

SQLite's ALTER TABLE has exactly two operations:

-- 1. Rename a table
ALTER TABLE old_name RENAME TO new_name;

-- 2. Add a column (with restrictions)
ALTER TABLE users ADD COLUMN age INTEGER;

The ADD COLUMN has additional limits:

• The column cannot have a PRIMARY KEY or UNIQUE constraint.
• The column cannot have a default value of CURRENT_TIME, CURRENT_DATE, or CURRENT_TIMESTAMP.
• If NOT NULL is specified, it must have a default value (otherwise the existing rows would have NULL values).
• You cannot add a column to a table that has a WITHOUT ROWID clause unless the column is part of the primary key.

Everything else — dropping columns, changing types, adding constraints, renaming columns — requires a table rebuild.

The Safe Table-Rebuild Pattern

Because SQLite is transactional even for schema changes, you can wrap the entire rebuild in a single transaction. If anything fails, the database rolls back to its original state. Here is the canonical six-step pattern:

Step 1: Begin a Transaction

BEGIN TRANSACTION;

Step 2: Create the New Table

CREATE TABLE users_new (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

Step 3: Copy Data From Old to New

INSERT INTO users_new (id, name, email, created_at)
SELECT id, name, email, created_at
FROM users;

If you dropped a column, simply omit it from the INSERT and SELECT lists. If you added a column with a default, SQLite handles it automatically.

Step 4: Drop the Old Table

DROP TABLE users;

Step 5: Rename the New Table

ALTER TABLE users_new RENAME TO users;

Step 6: Commit

COMMIT;

Critical: Always verify row counts before and after. If users had 10,000 rows, users_new must also have 10,000 rows. A mismatch means data loss.

Handling Indexes, Triggers, and Views

When you drop the old table, SQLite automatically drops indexes and triggers attached to it. Views that reference the table survive but may become invalid. You must recreate indexes and triggers after the rename:

-- Recreate indexes
CREATE INDEX idx_users_email ON users(email);

-- Recreate triggers
CREATE TRIGGER users_updated
AFTER UPDATE ON users
BEGIN
  UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

Use sqlite3_schema or PRAGMA index_list(users) before the migration to capture the exact index and trigger definitions you need to recreate.

Common SQLite Migration Mistakes

1. Forgetting Foreign Key Constraints High Risk

If the table you are rebuilding is referenced by foreign keys in other tables, dropping it breaks those relationships. You must either:

• Temporarily disable foreign key checks: PRAGMA foreign_keys = OFF;
• Rebuild the referencing tables in the correct dependency order.

Disabling foreign keys is faster but dangerous — you lose referential integrity validation during the migration. Only do this if you are certain the data relationships are intact.

2. Losing Auto-Increment Sequences High Risk

SQLite stores AUTOINCREMENT sequence state in the sqlite_sequence table. When you drop and recreate a table with INTEGER PRIMARY KEY AUTOINCREMENT, the sequence resets. You must manually preserve it:

-- Before dropping
SELECT seq FROM sqlite_sequence WHERE name = 'users';

-- After recreating and renaming
UPDATE sqlite_sequence SET seq = 12345 WHERE name = 'users';

3. Changing Column Affinity Unintentionally Medium Risk

SQLite uses type affinity, not strict types. A column declared INTEGER can still store text. When you rebuild a table and change the declared type, SQLite may change how it coerces values on insert. Test with real data before deploying.

4. Forgetting Views Medium Risk

Views referencing the rebuilt table do not update automatically. If the column names changed, the view may return incorrect results or fail entirely. Run PRAGMA integrity_check; after any schema migration to catch orphaned views.

5. Not Wrapping in a Transaction High Risk

Without a transaction, a failed step leaves your database in an inconsistent state — the old table might be gone while the new one is half-populated. Always use BEGIN...COMMIT or run the migration inside a savepoint if you need partial rollback.

Schema Diffing for SQLite

Before running any migration, you should diff your old schema against your new schema. SQLite does not have a built-in schema diff tool, but you have two solid options:

Option 1: Use .schema and Text Diff

# Dump schema from both databases
sqlite3 old.db ".schema" > old.sql
sqlite3 new.db ".schema" > new.sql

# Text diff (noisy — includes index order, whitespace)
diff old.sql new.sql

Option 2: Use SchemaLens for Semantic Diff

Paste both .schema outputs into SchemaLens and get a semantic diff that understands tables, columns, indexes, and constraints — without the noise of text diffing. SchemaLens generates the exact table-rebuild script you need, including the transaction wrapper and index recreation.

-- SchemaLens output for a column rename in SQLite
BEGIN TRANSACTION;

CREATE TABLE users_new (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL
);

INSERT INTO users_new (id, full_name, email)
SELECT id, name, email FROM users;

DROP TABLE users;

ALTER TABLE users_new RENAME TO users;

CREATE INDEX idx_users_email ON users(email);

COMMIT;

Automating SQLite Schema Checks in CI

For teams using SQLite in testing or local development, add a schema diff step to your CI pipeline. This catches migration script errors before they reach production:

name: SQLite Schema Check

on:
  pull_request:
    paths:
      - 'schema.sql'
      - 'migrations/**'

jobs:
  diff:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Dump schema from reference database
        run: sqlite3 test.db ".schema" > baseline.sql

      - name: Diff against PR schema
        run: |
          npx schemalens-cli baseline.sql schema.sql --dialect sqlite \
            --format markdown > schema_report.md

      - name: Fail if schema changes are unaccounted for
        run: |
          if grep -q "change" schema_report.md; then
            echo "Schema drift detected. See schema_report.md"
            cat schema_report.md
            exit 1
          fi

When to Use SQLite vs. a Client-Server Database

SQLite's schema migration limitations are a trade-off for its simplicity. If you find yourself rebuilding tables weekly, consider whether your project has outgrown SQLite:

• Stay on SQLite: Mobile apps, embedded devices, single-user desktop apps, small web apps with infrequent schema changes, testing fixtures.
• Consider PostgreSQL/MySQL: Multi-user web apps, frequent schema evolution, complex constraint logic, need for online schema changes without table locks.

Related Reading

• PostgreSQL Schema Drift Detection: A Complete Guide
• MySQL ALTER TABLE Cheatsheet for Developers
• How to Catch Schema Drift Before It Hits Production
• Database Schema Versioning Best Practices