Python database migrations: preview the SQL before it runs

The scary part of migrate isn't the command. It's not knowing exactly what it's about to send to your database. A typo in a file name, a migration you forgot was still pending, an index you didn't mean to build at 5 p.m. on a Friday.

DBLift is Flyway-style raw-SQL migrations for Python teams: no JVM, and you see the exact SQL before it runs. This first episode of DBLift features, explained covers that last part for Python database migrations: preview SQL with dblift migrate --dry-run --show-sql before you apply it. The install steps are in the five-minute install tutorial. Everything here is open source and needs no licence.

Everything below was run with DBLift 4.11.1, installed from PyPI into a fresh virtual environment, against SQLite 3.46.1 on 13 October 2026. You need Python 3.11 or newer. Terminal excerpts drop the log banner lines. The confirmation info also drops its closing status box. Everything else is unedited.

Step 1: A small project

python3 -m venv .venv && . .venv/bin/activate
pip install dblift==4.11.1
dblift --version

dblift version 4.11.1

Two migrations: a users table, then an orders table with an index.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    total_cents INTEGER NOT NULL
);
CREATE INDEX idx_orders_user_id ON orders (user_id);

With no --config, the CLI reads dblift.yaml from the current directory:

database:
  url: "sqlite:///shop.db"
migrations:
  directory: "./migrations"

Step 2: Where are we? dblift info

Before previewing anything, check what DBLift thinks is pending.

dblift info

=== Migration Summary ===
Total Migrations: 2
Applied Migrations: 0
Pending Migrations: 2
Failed Migrations: 0
=========================

╭────────────┬─────────┬────────────────────┬──────┬─────────────────────┬──────────────┬─────────┬───────────┬──────────╮
│ Category   │ Version │ Description        │ Type │ Installed On        │ Installed By │ State   │ Exec Time │ Undoable │
├────────────┼─────────┼────────────────────┼──────┼─────────────────────┼──────────────┼─────────┼───────────┼──────────┤
│ Versioned  │ 1.0.0   │ create_users_table │ SQL  │                     │              │ Pending │           │    No    │
│ Versioned  │ 1.1.0   │ add_orders         │ SQL  │                     │              │ Pending │           │    No    │
╰────────────┴─────────┴────────────────────┴──────┴─────────────────────┴──────────────┴─────────┴───────────┴──────────╯
Total migrations: 2
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ SUCCESS ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Command INFO completed successfully (Execution time: 57 ms)                  ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛

Two pending, nothing applied. Undoable: No means neither migration has an undo file yet; see Roll back a migration with undo files.

Step 3: Preview SQL with a dry run: --dry-run --show-sql

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

Found 2 pending migration(s)
DRY RUN: Would execute the following migrations:
  - V1_0_0__create_users_table.sql [SAFE]
  - V1_1_0__add_orders.sql [SAFE]
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ SUCCESS ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃   - Dry run — 2 migration(s) would be applied                                ┃
┃ Command MIGRATE completed successfully (Execution time: 57 ms)               ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
SQL Statements:
--------------------------------------------------------------------------------
-- V1_0_0__create_users_table.sql
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);
-- V1_1_0__add_orders.sql
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    total_cents INTEGER NOT NULL
);
CREATE INDEX idx_orders_user_id ON orders (user_id);
--------------------------------------------------------------------------------

You get the list of files in the order they'd run, then every statement, grouped under a comment naming its file. That's the whole review surface: if something in that block surprises you, you haven't touched the database yet.

Did the dry run really leave the database alone? Listing the SQLite objects right after it:

python3 -c "import sqlite3;print(sqlite3.connect('shop.db').execute('select name from sqlite_master').fetchall())"

[]

No tables, not even the history table. The dry run does create an empty shop.db if none exists, and it writes a log file under ./logs/.

What to look for in the preview

• The file list. Is anything pending that you thought was already applied, or the other way round? Is the order what you expect?
• The target. The banner (dropped above) names the database and URL the command connected to. Read it before you read the SQL.
• Heavy statements. Index builds and table rewrites on large tables are worth a second look, and a quieter time window.
• Anything you didn't write. If a statement in the block isn't one you remember adding, find out where it came from before you apply.

Step 4: Apply

dblift migrate

Found 2 pending migration(s)
Migration lock acquired successfully
Statement executed successfully 
Migration V1_0_0__create_users_table.sql executed successfully in 1ms
Successfully applied migration V1_0_0__create_users_table.sql
Migration V1_1_0__add_orders.sql executed successfully in 0ms
Successfully applied migration V1_1_0__add_orders.sql
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ SUCCESS ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Command MIGRATE completed successfully (Execution time: 55 ms)               ┃
┃ Schema Version: 1.1.0                                                        ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛

migrate takes a real lock on every engine before applying anything (for example, a PostgreSQL advisory lock, MySQL/MariaDB GET_LOCK, SQL Server sp_getapplock, or a SQLite lease row in dblift_migration_lock), waits up to 60 s, then fails; --dry-run takes no lock.

Step 5: Confirm with info

dblift info

=== Migration Summary ===
Total Migrations: 2
Applied Migrations: 2
Pending Migrations: 0
Failed Migrations: 0
=========================

╭────────────┬─────────┬────────────────────┬──────┬─────────────────────┬──────────────┬─────────┬───────────┬──────────╮
│ Category   │ Version │ Description        │ Type │ Installed On        │ Installed By │ State   │ Exec Time │ Undoable │
├────────────┼─────────┼────────────────────┼──────┼─────────────────────┼──────────────┼─────────┼───────────┼──────────┤
│ Versioned  │ 1.0.0   │ create_users_table │ SQL  │ 2026-10-13 21:25:27 │ box          │ Success │       1ms │    No    │
│ Versioned  │ 1.1.0   │ add_orders         │ SQL  │ 2026-10-13 21:25:27 │ box          │ Success │           │    No    │
╰────────────┴─────────┴────────────────────┴──────┴─────────────────────┴──────────────┴─────────┴───────────┴──────────╯
Total migrations: 2

Both versions applied, with who ran them and when. On SQLite, Installed On is stored in UTC: this run happened at 17:25 in Toronto. The blank Exec Time on 1.1.0 means 0 ms; info renders a zero as an empty cell.

Make it a habit: the Python database migrations dry-run loop

dblift info                         # where are we?
dblift migrate --dry-run --show-sql # what will run?
dblift migrate                      # apply

The preview also belongs in code review. Paste the SQL Statements block into the pull request, or run it in CI so reviewers see it next to the diff; the CI/CD guide shows the pipeline version. Placeholders are resolved in this output, so don't post --show-sql output to shared logs if a placeholder carries a secret.

What a dry run won't tell you

--dry-run shows the SQL; it doesn't execute it. Errors that depend on your data, such as a UNIQUE constraint that existing rows violate, only show up when the statement runs. Test against a copy of production data for those.

If you want more than a read-through, dblift validate-sql (open source since 4.11.0) checks your migration files without connecting: a SAFE/REVIEW/UNSAFE verdict per script, exit code 1 on UNSAFE, and --format json for CI. The [SAFE] tags in the dry run above come from the same checks.

FAQ

Does --dry-run create the history table?

No. A dry run never creates the history or lock table, on any engine.

Can I preview a single version?

migrate accepts --target-version, --versions and --exclude-versions. See the `migrate` reference.

Does it work on PostgreSQL?

Yes. Install dblift[postgresql], point database.url at your server, and supply the password in the URL or under database:. The dry run connects to the database but creates nothing, not even the history table.

Read next

• Next episode: info and validate, knowing exactly what's applied
• Your first migration, the docs version of this loop
• Install and the five-minute SQL and Python tutorial
• Flyway for Python: migrate versioned SQL with DBLift
• Database migration best practices for production teams
• Add migrations to an existing database
• DBLift on GitHub