Change the schema with migrations

A migration is a versioned schema change. Keep each migration in source control and record which migrations ran.

Give migrations an immutable order, for example:

001_create_notes.sql
002_add_notes_owner.sql
003_index_notes_owner.sql

The application records completed versions in a migration table. At startup or deployment, it applies only the missing files in order. Never silently edit a migration that has already run in another environment. Add a new migration so an existing database and a fresh database follow the same history.

Separate the schema transition from application behavior when compatibility matters. For example, adding a required column safely may take several releases:

1. Add the column as nullable or with a safe default.
2. Deploy code that writes the new value.
3. Backfill old rows.
4. Start reading the new value.
5. Enforce the final constraint in a later migration.

SQLite supports operations such as renaming tables and columns and adding columns. More complex changes may require creating a replacement table, copying data, dropping the old table, and renaming the replacement. Preserve indexes, triggers, constraints, and foreign keys during that process; copying only the visible columns is a common data-integrity bug.

Wrap compatible schema and data changes in a transaction so a failure does not leave a half-migrated database. Before a risky migration, make a backup and verify that it can be restored. A migration’s rollback plan is often restoring that backup or deploying a forward fix, not automatically reversing every destructive statement.

Test two paths: building an empty database from migration 1, and upgrading a copy of realistic data from the currently deployed version. Measure long table rewrites too, because a migration that is correct on 20 rows may hold the only writer for too long on millions.

Try it: add a slug column to notes, backfill it, add a unique index, then run the complete migration sequence twice. The second run should have no pending work and should not damage data.