Skip to content

Migrations

from sqlspec.config import (
    ExtensionMigrationMode,
    resolve_extension_migration_mode,
)
from sqlspec.migrations import (
    AsyncMigrationCommands,
    AsyncMigrationRunner,
    AsyncMigrationTracker,
    BaseMigrationLoader,
    MigrationSquasher,
    PythonFileLoader,
    SchemaEnsureResult,
    SchemaTarget,
    SQLFileLoader,
    SquashPlan,
    SyncMigrationCommands,
    SyncMigrationRunner,
    SyncMigrationTracker,
    create_migration_commands,
    create_migration_runner,
    ensure_schema_async,
    ensure_schema_sync,
    fold_migration_ddl,
)
from sqlspec.migrations.context import MigrationContext

Programmatic schema migration commands, SQL and Python migration loaders, version table trackers, unversioned extension reconciler contexts, declarative schema targets, and canonical DDL or concatenation squashing. See the Migrations Guide for end-to-end workflows.

Migration Commands

SyncMigrationCommands

Bases: MigrationCommandsCore['SyncConfigT', SyncMigrationRunner]

Synchronous migration commands.

init(directory, package=True)

Initialize migration directory structure.

Parameters:

Name Type Description Default
directory str

Directory to initialize migrations in.

required
package bool

Whether to create init.py file.

True

current(verbose=False)

Show current migration version.

Parameters:

Name Type Description Default
verbose bool

Whether to show detailed migration history.

False

Returns:

Type Description
str | None

The current migration version or None if no migrations applied.

revision(message, file_type=None, *, from_schema=False, from_data=None, schema=None, target_table=None)

Create a new migration file with timestamp-based versioning.

Generates a unique timestamp version (YYYYMMDDHHmmss format) to avoid conflicts when multiple developers create migrations concurrently.

Parameters:

Name Type Description Default
message str

Description for the migration.

required
file_type str | None

Type of migration file to create ('sql' or 'py').

None
from_schema bool

When True, populate the migration with the current database schema DDL.

False
from_data str | Path | None

Data file path or staging table name to infer DDL from. See MigrationCommandsCore._resolve_data_source for the detection rules.

None
schema str | None

Optional database schema name.

None
target_table str | None

Optional target table name when bootstrapping from data.

None

Returns:

Type Description
Path

Path to the created migration file.

Raises:

Type Description
MigrationError

When both sources are given or DDL cannot be inferred.

upgrade(revision='head', allow_missing=False, auto_sync=True, dry_run=False, *, use_logger=False, echo=None, summary_only=None)

Upgrade to a target revision.

Validates migration order and warns if out-of-order migrations are detected. Out-of-order migrations can occur when branches merge in different orders across environments.

Parameters:

Name Type Description Default
revision str

Target revision or "head" for latest.

'head'
allow_missing bool

If True, allow out-of-order migrations even in strict mode. Defaults to False.

False
auto_sync bool

If True, automatically reconcile renamed and squashed migrations in the tracking table. Defaults to True. Can be disabled via --no-auto-sync flag. A partially applied squash raises SquashValidationError either way.

True
dry_run bool

If True, report the reconciliation and migrations that would run without creating the tracking table or writing any tracking rows.

False
use_logger bool

If True, output to logger instead of Rich console. Defaults to False. Can be set via MigrationConfig for persistent default.

False
echo bool | None

Echo output to the console. Defaults to True when unset.

None
summary_only bool | None

Emit a single summary log entry when logger output is enabled.

None

downgrade(revision='-1', *, dry_run=False, use_logger=False, echo=None, summary_only=None)

Downgrade to a target revision.

Parameters:

Name Type Description Default
revision str

Target revision or "-1" for one step back.

'-1'
dry_run bool

If True, show what would be done without making changes.

False
use_logger bool

If True, output to logger instead of Rich console. Defaults to False. Can be set via MigrationConfig for persistent default.

False
echo bool | None

Echo output to the console. Defaults to True when unset.

None
summary_only bool | None

Emit a single summary log entry when logger output is enabled.

None

verify(*, strict=False, fail_on_pending=False, use_logger=False, echo=None)

Verify database migration tracking records against migration files on disk.

Verification is read-only: a missing tracking table is reported as every migration pending and is never created.

Parameters:

Name Type Description Default
strict bool

Raise MigrationError when checksum drift, orphaned records, or partial squashes exist.

False
fail_on_pending bool

Raise MigrationError when migrations are pending.

False
use_logger bool

Output verification messages to logger instead of Rich console.

False
echo bool | None

Echo output to the console.

None

Returns:

Type Description
dict[str, Any]

Verification report dictionary with valid, checksum_mismatches, orphaned_records,

dict[str, Any]

pending_versions, and partial_squash_errors.

dump_schema(output_path=None, *, schema=None, include_migrations_table=False, prefer_native=True, target_dialect=None, echo=None)

Dump the full current database schema DDL to a string and optional file path.

Parameters:

Name Type Description Default
output_path str | Path | None

Optional file path to write the rendered schema SQL.

None
schema str | None

Optional database schema name to inspect.

None
include_migrations_table bool

Whether to include the migration tracking table.

False
prefer_native bool

Prefer engine-native catalog DDL when available.

True
target_dialect str | None

Optional target SQLGlot dialect for cross-dialect transpilation.

None
echo bool | None

Echo output to the console.

None

Returns:

Type Description
str

Rendered schema DDL string.

stamp(revision)

Mark database as being at a specific revision without running migrations.

Parameters:

Name Type Description Default
revision str

The revision to stamp.

required

squash(start_version=None, end_version=None, description=None, *, dry_run=False, update_database=True, yes=False, allow_gaps=False, output_format='sql', strategy='concat', single_file=None)

Squash a range of migrations into a single file.

Combines multiple sequential or timestamped migrations into a single release migration.

Parameters:

Name Type Description Default
start_version str | None

First version in the range to squash (inclusive). When None, defaults to the first core migration found.

None
end_version str | None

Last version in the range to squash (inclusive). When None, defaults to the last core migration found.

None
description str | None

Description for the squashed migration file. When None, defaults to 'initial_schema' if yes=True or prompts interactively.

None
dry_run bool

Preview changes without applying.

False
update_database bool

Update migration records in database.

True
yes bool

Skip confirmation prompt.

False
allow_gaps bool

Allow gaps in version sequence.

False
output_format str

Output format ("sql" or "py").

'sql'
strategy str

Squash strategy: "concat" (default) keeps every statement in order, "ddl" folds DDL and refuses ranges it cannot fold faithfully, "schema" reflects the live database schema.

'concat'
single_file bool | None

Force a single squashed file when True, split by file type/directives when False or None.

None

fix(dry_run=False, update_database=True, yes=False)

Convert timestamp migrations to sequential format.

Implements hybrid versioning workflow where development uses timestamps and production uses sequential numbers. Creates backup before changes and provides rollback on errors.

Parameters:

Name Type Description Default
dry_run bool

Preview changes without applying.

False
update_database bool

Update migration records in database.

True
yes bool

Skip confirmation prompt.

False

AsyncMigrationCommands

Bases: MigrationCommandsCore['AsyncConfigT', AsyncMigrationRunner]

Asynchronous migration commands.

create_migration_commands(config)

Factory function to create the appropriate migration commands.

Returns:

Type Description
SyncMigrationCommands[SyncConfigT] | AsyncMigrationCommands[AsyncConfigT]

Appropriate migration commands instance.

Migration Runners & Trackers

SyncMigrationRunner

Bases: BaseMigrationRunner

Synchronous migration runner with pure sync methods.

AsyncMigrationRunner

Bases: BaseMigrationRunner

Asynchronous migration runner with pure async methods.

SyncMigrationTracker

Bases: BaseMigrationTracker['SyncDriverAdapterBase']

Synchronous migration version tracker.

AsyncMigrationTracker

Bases: BaseMigrationTracker['AsyncDriverAdapterBase']

Asynchronous migration version tracker.

create_migration_runner(migrations_path, extension_migrations, context, extension_configs, is_async=False, runtime=None, description_hints=None)

Factory function to create the appropriate migration runner.

Returns:

Type Description
SyncMigrationRunner | AsyncMigrationRunner

Appropriate migration runner instance.

Migration Loaders

BaseMigrationLoader

Abstract base class for migration loaders.

SQLFileLoader

Bases: BaseMigrationLoader

Loader for SQL migration files.

PythonFileLoader

Bases: BaseMigrationLoader

Loader for Python migration files.

Extension Reconciliation & Context

Built-in reconcilers (litestar, events, and adk) execute <migrations_path>/reconcile.py::reconcile(context) across all 22 unconditional built-in adapters (sqlite, aiosqlite, asyncpg, psycopg, psqlpy, cockroach_asyncpg, cockroach_psycopg, aiomysql, asyncmy, pymysql, mysqlconnector, duckdb, mssql_python, pymssql, oracledb, spanner, snowflake, bigquery, db2, sqlanywhere, sybase, teradata), arrow_odbc on mssql/tsql/db2, and adbc on backends with a resolved dialect (ADBC over FlightSQL requires an explicit statement_config dialect or gizmosql_backend). Inside reconcile(context), pass manage_schema=True, commit=False, strict=True to ensure_schema_sync or ensure_schema_async so the migration runner owns transaction boundaries and unsafe schema drift fails closed with MigrationError.

ExtensionMigrationMode = Literal['versioned', 'reconcile']

MigrationContext

Context object passed to migration functions.

Provides runtime information about the database environment to migration functions, allowing them to generate dialect-specific SQL.

resolve_extension_migration_mode(explicit_mode: ExtensionMigrationMode | None, settings: Mapping[str, Any] | None, *, extension_name: str) -> ExtensionMigrationMode

Resolve and validate the effective migration mode for an extension.

Raises:

Type Description
MigrationError

Either settings["migration_mode"] or explicit_mode is invalid.

Returns:

Type Description
ExtensionMigrationMode

Effective validated migration mode, defaulting to "reconcile".

Declarative Schema & Squashing

SchemaTarget

Describe one table's target schema.

Parameters:

Name Type Description Default
table_name str

Unqualified table name used for introspection and DDL.

required
create_table CreateTable

Builder containing the target columns and create DDL.

required
schema str | None

Optional schema containing the table.

None
create_statement str | CreateTable | None

Optional raw DDL or CreateTable executed on creation.

None
constraint_columns frozenset[str] | None

Extra case-folded column names governed by constraints.

None
unsafe_add_columns frozenset[str] | None

Extra case-folded column names unsafe for additive ALTER TABLE.

None

SchemaEnsureResult

Summarize changes made by an ensure operation.

ensure_schema_sync(driver: Any, targets: Sequence[SchemaTarget], *, manage_schema: bool = False, create_schema: bool = True, run_migrations: bool = False, migration_runner: Callable[[Any], None] | None = None, assume_existing: bool = False, commit: bool = True, strict: bool = False) -> SchemaEnsureResult

Apply additive target-schema changes with a synchronous driver.

Explicit migrations remain independent of automatic schema currency. When requested, migration_runner runs before schema introspection and owns its migration-ledger behavior. Automatic changes never write ledger rows.

Returns:

Type Description
SchemaEnsureResult

Summary of created tables, added columns, and deferred targets.

Raises:

Type Description
ValueError

If explicit migrations are enabled without a runner.

MigrationError

If strict=True detects non-reconcilable schema drift.

ensure_schema_async(driver: Any, targets: Sequence[SchemaTarget], *, manage_schema: bool = False, create_schema: bool = True, run_migrations: bool = False, migration_runner: Callable[[Any], Awaitable[None]] | None = None, assume_existing: bool = False, commit: bool = True, strict: bool = False) -> SchemaEnsureResult

Apply additive target-schema changes with an asynchronous driver.

Returns:

Type Description
SchemaEnsureResult

Summary of created tables, added columns, and deferred targets.

Raises:

Type Description
ValueError

If explicit migrations are enabled without a runner.

MigrationError

If strict=True detects non-reconcilable schema drift.

MigrationSquasher

Core squash engine for combining migrations.

Provides functionality to plan, validate, and execute migration squash operations. Combines multiple sequential or timestamped migrations into a single file.

SquashPlan

Represents a planned squash operation.

fold_migration_ddl(statements: Sequence[str], *, dialect: str | None = None, origins: Sequence[str] | None = None) -> tuple[list[str], list[str]]

Fold a chronological sequence of migration SQL statements into net canonical DDL.

Returns:

Type Description
list[str]

Tuple of (up_statements, down_statements) representing net schema DDL

list[str]

and reverse-dependency DROP statements.

Raises:

Type Description
SquashValidationError

If the statements cannot be folded faithfully.