Schema migrations: change a database safely

A migration is a versioned change to your database schema. This example adds a column to a Notes application without deleting existing records.

1. Create the original table

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);
INSERT INTO notes (id, title) VALUES (1, 'Read a book');

2. Write your first migration

The new application must track when a note was created:

ALTER TABLE notes ADD COLUMN created_at TEXT;
UPDATE notes SET created_at = '2026-11-01' WHERE created_at IS NULL;

The first statement changes the schema. The second statement backfills existing rows.

3. Verify the result

SELECT id, title, created_at FROM notes;

Expected columns: id, title, created_at. Your existing note remains.

4. Record applied migrations

CREATE TABLE IF NOT EXISTS schema_migrations (
    version TEXT PRIMARY KEY,
    applied_at TEXT NOT NULL
);
INSERT INTO schema_migrations (version, applied_at)
VALUES ('001_add_created_at', '2026-11-01');

The migration runner uses this table to skip changes already applied.

5. Production workflow

Back up important data before changing schemas. Review the generated SQL, apply changes in a transaction when supported by your database, and keep backward-compatible releases when multiple app versions may be running.

See SQLite and PostgreSQL for the database engines.