SQLite Schema Drift Detection: A Complete Guide

SQLite is everywhere. It powers mobile apps, browser extensions, desktop applications, IoT devices, and local development environments. Because it is a file-based database, schema drift in SQLite looks different from PostgreSQL or MySQL — but it is just as dangerous.

When your app ships with a v1.db and your development environment has a v3.db, users with old database files experience crashes. When your test suite uses an in-memory SQLite database but production uses a file, subtle differences in PRAGMA settings break queries. This is SQLite schema drift, and because SQLite has no central server to enforce consistency, the burden of detection falls entirely on your team.

What SQLite Schema Drift Looks Like

SQLite schema drift is insidious because the database engine is so forgiving. It will happily open a file with an older schema and let your application crash at runtime. Here are the most common patterns:

• Migration gaps on user devices. A mobile app user skips two updates and reinstalls. The migration script from version 1 → 3 expects version 2 to exist. The app crashes on launch.
• Foreign key enforcement mismatch. One environment runs with PRAGMA foreign_keys = ON while another leaves it off. The same INSERT succeeds in testing and fails in production.
• Strict table differences. SQLite 3.37.0 introduced STRICT tables. A developer on a newer SQLite version creates a strict table, but CI or user devices run older versions that cannot parse it.
• Journal mode divergence. WAL mode behaves differently than DELETE mode under concurrency. If your test database uses WAL but your Electron app bundles DELETE mode, locking behavior differs silently.
• Missing IF NOT EXISTS guards. A CREATE INDEX statement without IF NOT EXISTS fails on the second app launch if the index already exists. This pattern spreads across environments unpredictably.

Method 1: The .schema Diff (Fastest One-Off Check)

The quickest way to compare two SQLite databases is to dump their schemas and diff them. This works for any two .db files — a user backup vs your latest build, or your test database vs production export.

# Dump schema from both databases
sqlite3 prod.db ".schema" > prod.sql
sqlite3 staging.db ".schema" > staging.sql

# For a more complete dump including indexes and triggers
sqlite3 prod.db ".schema --indent" > prod.sql

Now compare them. A text diff is a good start, but SQLite's .schema output order can vary between versions, creating false positives:

# Basic text diff (can be noisy)
diff prod.sql staging.sql

# Better: normalize with SchemaLens
# Paste both files into SchemaLens for a semantic diff
# that ignores statement ordering and whitespace

For a semantic diff that understands tables, columns, indexes, and foreign keys — rather than treating the schema as text — paste both files into SchemaLens. It filters out noise and highlights only the structural changes.

Method 2: Query sqlite_schema (Programmatic)

SQLite stores all schema metadata in the sqlite_schema table (also accessible as sqlite_master). You can query this directly to build a programmatic drift detector.

Compare Tables

SELECT name, sql
FROM sqlite_schema
WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
ORDER BY name;

Compare Columns

SELECT m.name AS table_name,
       p.name AS column_name,
       p.type AS data_type,
       p."notnull" AS is_not_null,
       p.dflt_value AS default_value
FROM sqlite_schema m
JOIN pragma_table_info(m.name) p
WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite_%'
ORDER BY m.name, p.cid;

Compare Indexes

SELECT m.name AS index_name,
       m.tbl_name AS table_name,
       m.sql AS index_definition
FROM sqlite_schema m
WHERE type = 'index' AND name NOT LIKE 'sqlite_autoindex_%'
ORDER BY m.tbl_name, m.name;

Compare Foreign Keys

SELECT m.name AS table_name,
       p."from" AS column_name,
       p."table" AS references_table,
       p."to" AS references_column
FROM sqlite_schema m
JOIN pragma_foreign_key_list(m.name) p
WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite_%'
ORDER BY m.name, p.id, p.seq;

Export these queries from both databases and diff the results. This approach is deterministic and easy to automate in a CI pipeline or an in-app health check.

Method 3: Automated Drift Detection in CI/CD

For teams shipping SQLite in desktop or mobile apps, schema drift should be caught before release. Here is a minimal GitHub Actions workflow that diffs your canonical schema against the schema generated by your migration scripts:

name: SQLite Schema Drift Check

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

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

      - name: Install SQLite
        run: sudo apt-get update && sudo apt-get install -y sqlite3

      - name: Build database from migrations
        run: |
          sqlite3 built.db < migrations/init.sql
          for f in migrations/*.sql; do
            sqlite3 built.db < "$f"
          done
          sqlite3 built.db ".schema --indent" > built.sql

      - name: Diff against canonical schema
        run: |
          npx schemalens-cli built.sql schema.sql --format markdown \
            > drift_report.md

      - name: Comment PR if drift detected
        uses: actions/github-script@v7
        with:
          script: |
            const fs = require('fs');
            const report = fs.readFileSync('drift_report.md', 'utf8');
            if (report.includes('change')) {
              github.rest.issues.createComment({
                issue_number: context.issue.number,
                owner: context.repo.owner,
                repo: context.repo.repo,
                body: '## ⚠️ SQLite Schema Drift Detected\\n\\n' + report
              });
            }

This workflow uses the SchemaLens CLI to generate a semantic diff and posts it as a PR comment. If the diff is empty, your migration scripts produce the expected schema.

Method 4: In-App Schema Validation

For mobile and desktop apps, the most critical drift check happens at runtime. Your application should validate the user's database schema on startup and either migrate or warn.

// Node.js / Electron example
const fs = require('fs');
const { execSync } = require('child_process');

function validateSchema(dbPath, canonicalSchemaPath) {
  const userSchema = execSync(`sqlite3 "${dbPath}" ".schema --indent"`).toString();
  const canonicalSchema = fs.readFileSync(canonicalSchemaPath, 'utf8');

  if (userSchema.trim() !== canonicalSchema.trim()) {
    console.warn('Schema drift detected. Running migration...');
    // Run your migration logic here
    return false;
  }
  return true;
}

A more robust approach stores a schema hash rather than comparing the full SQL:

// Generate a schema fingerprint
const crypto = require('crypto');

function schemaHash(dbPath) {
  const schema = execSync(`sqlite3 "${dbPath}" ".schema"`).toString();
  return crypto.createHash('sha256').update(schema).digest('hex').slice(0, 16);
}

Ship the expected hash with your app. On startup, compare. If they differ, run migrations or show a "database needs update" dialog.

Common SQLite-Specific Drift Traps

-- Expand: add new column as nullable
ALTER TABLE orders ADD COLUMN status_v2 TEXT;

-- Backfill
UPDATE orders SET status_v2 = status;

-- Contract: create new table, copy, swap, drop
CREATE TABLE orders_new (
  id INTEGER PRIMARY KEY,
  status_v2 TEXT NOT NULL
);

INSERT INTO orders_new (id, status_v2)
SELECT id, status_v2 FROM orders;

DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;