Python database migrations: a SQL and Python tutorial

Python database migrations keep changes to your database alongside your application code. With DBLift, you write versioned SQL files for schema changes and Python files for data work, then apply them in order. A history table records what ran and a checksum helps detect edits to applied files.

This getting-started tutorial runs the full loop on a disposable SQLite file: install, create SQL and Python migrations, preview, apply, validate and undo. You need Python and a terminal; no database server or DBLift license is required. For an existing PostgreSQL or MySQL server, use the database quickstart.

The examples below were checked with DBLift 4.8.0 on 27 September 2026. Terminal excerpts omit headers, log timestamps and some table columns.

SQL files or Python migration scripts?

Use .sql for database statements you want to review directly. Use .py when a change needs Python logic; both formats share the same version history on relational engines. DBLift runs files you author, rather than generating them from ORM model differences. See DBLift vs Alembic if model-generated revisions are your starting point.

For document changes and indexes in MongoDB, follow the separate MongoDB migrations in Python tutorial.

Minute one: install and pick a file

pip install dblift
mkdir -p dblift-tutorial/migrations
cd dblift-tutorial
export DBLIFT_DB_URL="sqlite:///./app.db"

SQLite's driver ships with Python, so the bare package is enough. For any other engine you add a driver extra, and that is the only install difference: pip install "dblift[postgresql]".

The database file does not need to exist. DBLift creates it on first contact.

Minute two: three files

Migrations live in a migrations folder next to where you run the command. Three files show the three kinds.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    active INTEGER
);
INSERT INTO accounts (id, email, active) VALUES
    (1, 'alice@example.com', NULL),
    (2, 'bob@example.com', NULL);

def migrate(context):
    if context.dry_run:
        return
    context.execute("UPDATE accounts SET active = 1 WHERE active IS NULL")

DROP VIEW IF EXISTS active_accounts;
CREATE VIEW active_accounts AS SELECT id, email FROM accounts WHERE active = 1;

• V files are versioned. Applied versions are skipped on later runs; an undone version can be applied again.
• R files are repeatable. They run every time their content changes. Views and functions go here.
• U files undo a specific version. We add one in a minute.

The double underscore between version and description is mandatory. .sql and .py sit side by side; a Python file just needs a migrate(context) function.

Minute three: look before you run

dblift info

Total Migrations: 3
Applied Migrations: 0
Pending Migrations: 3

│ Category   │ Version │ Description     │ Type   │ State   │ Undoable │
├────────────┼─────────┼─────────────────┼────────┼─────────┼──────────┤
│ Versioned  │ 1       │ create_accounts │ SQL    │ Pending │    No    │
│ Versioned  │ 2       │ backfill_active │ Python │ Pending │    No    │
│ Repeatable │         │ active_accounts │ SQL    │ Pending │    No    │

No migration has been applied yet. Neither version has an undo file at this point. The repeatable has no version because it reruns when its checksum changes.

Preview the pending files and SQL before applying them:

dblift migrate --dry-run --show-sql

The Python file is listed, but its body is not executed by this CLI preview. A dry run cannot tell you which rows arbitrary Python code will change; review that code and test it on disposable data too.

Minute four: apply

dblift migrate

Found 3 pending migration(s)
Migration lock acquired successfully
Successfully applied migration V1__create_accounts.sql
Successfully applied migration V2__backfill_active.py
Successfully applied migration R__active_accounts.sql
Command MIGRATE completed successfully (Execution time: 23 ms)
Schema Version: 2

Run dblift info again and every row reads Success with a timestamp and your username. That record lives in a table named dblift_schema_history inside app.db, next to your own tables. Run dblift migrate a second time and it finds nothing to do.

Minute five: break it on purpose

Add a comment to the end of the file you already applied, then ask DBLift whether everything still lines up.

echo "-- edited after apply" >> migrations/V1__create_accounts.sql
dblift validate

ERROR: Migration script V1__create_accounts.sql has been modified
       since it was applied.
       Database checksum: -221499039, Filesystem checksum: 149915987
ERROR: Validation failed. Detected modified migration scripts.

This is the whole point of a history table. Every applied file is recorded with a checksum. Edit it afterwards and validate refuses, before any deploy job gets to run it. Fix by reverting the edit, or by shipping the change as a new versioned file. Run dblift validate in CI against a database with the relevant migration history; validating only a fresh empty database cannot detect edits to files previously applied elsewhere.

Delete the line you added, then check again:

dblift validate

Migration validation passed

One more minute: go backwards

Undo is opt-in, one companion file per version. Give V2 one:

This inverse is for the two seed rows in the disposable tutorial database, where both original values were NULL. For application data, record which rows changed and their previous values before designing an undo; setting every active account to NULL would not restore its earlier state.

def migrate(context):
    if context.dry_run:
        return
    context.execute("UPDATE accounts SET active = NULL WHERE id IN (1, 2) AND active = 1")

dblift undo --target-version 1

Found 1 migration(s) to undo
Successfully undone migration V2__backfill_active.py
Schema Version: 1

dblift info now shows V2 twice: the original row marked Undone, and a fresh Pending row underneath, because the file is still in the folder. dblift migrate applies it again. Undo never deletes history, it appends to it.

Same files, real database

The migration workflow also applies to PostgreSQL. Install its driver, change the URL, and name the schema. Use a disposable database here too, then check each SQL statement for your target dialect.

pip install "dblift[postgresql]"
export DBLIFT_DB_URL="postgresql+psycopg://app:secret@localhost:5432/app"
export DBLIFT_DB_SCHEMA="public"
dblift info

The example supplies explicit IDs, so it does not rely on SQLite's automatic row IDs. When the application should generate IDs, choose the appropriate identity syntax for your engine. For MySQL connection settings and engine-specific behavior, see the MySQL migration reference.

When the environment variables get old, the same settings go in a dblift.yaml at the project root. The Configuration page has the full list; the Flyway article shows a complete example.

Where this goes next

• CI — dblift/action@v1 installs the package and runs validate or migrate in a GitHub job. The best practices article shows the pinned form.
• Tests — pytest-dblift applies your migrations before the test session starts, so tests run against the real schema.
• Coming from Flyway — review the import workflow in Flyway for Python.
• Other engines — MySQL, SQL Server, Oracle, DB2, DuckDB, Cosmos DB, MongoDB and the PostgreSQL-compatible clouds are all a driver extra away. See Database engine coverage.

One housekeeping note: DBLift writes a log file per command into a logs folder in the working directory. Add it to .gitignore along with app.db.

The core commands you used, info, migrate, validate, undo, need no license. Offline SQL review (validate-sql) and release evidence (plan, preflight) are part of the commercial dblift-enterprise distribution; see Pricing when you get there.