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)
¶
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
|
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
|
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 |
False
|
fail_on_pending
|
bool
|
Raise |
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 |
dict[str, Any]
|
|
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
¶
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
¶
PythonFileLoader
¶
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 |
Returns:
| Type | Description |
|---|---|
ExtensionMigrationMode
|
Effective validated migration mode, defaulting to |
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 |
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 |
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 |
list[str]
|
and reverse-dependency |
Raises:
| Type | Description |
|---|---|
SquashValidationError
|
If the statements cannot be folded faithfully. |