Migrations

SQLSpec migration workflow demo

SQLSpec ships with a built-in migration system backed by the SQL file loader. Use it when you want a lightweight, code-first workflow without pulling in Alembic or a full ORM stack.

Core Concepts

  • Migrations are SQL or Python files stored in a migrations directory.

  • Each database configuration can include its own migration settings.

  • Extension migrations (ADK, events, Litestar sessions) are opt-in and versioned.

Common Commands

sqlspec init
sqlspec create-migration -m "add users"
sqlspec upgrade

Configuration

Set migration_config on your database configuration to customize script locations, version table names, and extension migration behavior.

The migration CLI resolves config from --config, SQLSPEC_CONFIG, or [tool.sqlspec] in pyproject.toml.

from sqlspec.adapters.duckdb import DuckDBConfig

config = DuckDBConfig(
    connection_config={"database": "/tmp/analytics.db"},
    migration_config={
        "script_location": "migrations/duckdb",
        "version_table": "_schema_versions",
    },
)

# Apply all pending migrations
config.migrate_up()

# Apply up to a specific revision
config.migrate_up(revision="003")

# Dry run to see what would happen
config.migrate_up(dry_run=True)

For async configs, migrate_up() returns an awaitable:

from sqlspec.adapters.asyncpg import AsyncpgConfig

config = AsyncpgConfig(
    connection_config={"dsn": "postgresql://localhost/app"},
    migration_config={"script_location": "migrations/postgres"},
)

await config.migrate_up()

Extension migrations are auto-included when the corresponding entry exists in extension_config. Use migration_config["exclude_extensions"] to skip a specific extension, migration_config["include_extensions"] to opt in explicitly by extension name, or migration_config["enabled"] = False to disable migrations entirely for a database config.

Logging and Echo Controls

Configure output behavior with migration_config or CLI flags:

  • use_logger to emit structured logs instead of console output.

  • echo to control console output when not using the logger.

  • summary_only to emit a single summary log entry when logger output is enabled.

The CLI equivalents are --use-logger, --no-echo, and --summary.