Migrations
Changing a TableBase class does not change its table in Postgres. A migration
records the SQL needed to bring the database schema up to date. Iceaxe can
generate that migration by comparing your models with the database.
To run migrations from the command line, wrap Iceaxe's migration functions
with your CLI library. This example uses Click. Start with the project name
from pyproject.toml and a database connection helper. The credentials below
are examples; load yours from environment variables.
# cli.py
import asyncio
import asyncpg
from click import command, option
from iceaxe import DBConnection
from iceaxe.migrations.cli import handle_generate, handle_apply, handle_rollback
from myproject import models # noqa: F401
PROJECT = "myproject"
async def get_connection():
return DBConnection(
await asyncpg.connect(
host="localhost",
port=5432,
user="db_user",
password="yoursecretpassword",
database="your_db",
)
)
Add commands to generate a migration, apply pending migrations, and roll back the latest one:
@command()
@option("--message", help="A message to attach to the migration")
def generate(message: str | None):
async def _inner():
await handle_generate(PROJECT, await get_connection(), message=message)
asyncio.run(_inner())
@command()
def apply():
async def _inner():
await handle_apply(PROJECT, await get_connection())
asyncio.run(_inner())
@command()
def rollback():
async def _inner():
await handle_rollback(PROJECT, await get_connection())
asyncio.run(_inner())
Register those commands in pyproject.toml so you can call them with uv run:
[project.scripts]
migrate-generate = "myproject.cli:generate"
migrate-apply = "myproject.cli:apply"
migrate-rollback = "myproject.cli:rollback"
Automatic local schema sync
For local development, DBConnection.magic_migrate() combines generation and application into a
single call:
await conn.magic_migrate("myproject")
# Limit the sync to specific tables
await conn.magic_migrate("myproject", models=[Employee, Office])
# Add a message to the generated revision
await conn.magic_migrate("myproject", message="Add office capacity column")
magic_migrate() will:
- introspect the current database schema
- generate a new migration file only when the schema actually changed
- apply any pending migrations, including the one it just created
Use this for local branches and prototypes. Review and commit the generated migration files before applying them to production.
Ignoring externally managed tables
If your database contains tables that Iceaxe should leave alone, you can exclude them during migration generation:
await handle_generate(
PROJECT,
await get_connection(),
ignore_tables=["legacy_audit_log", "spatial_ref_sys"],
)
Ignored tables are removed from both the in-memory model comparison and the live database introspection step. This is useful when your application shares a database with extensions, third-party tools, or manually managed tables.
Generating migrations
Suppose the database already has an employee table with an auto-incrementing ID and a name:
| Column Name | Data Type | Constraints |
|---|---|---|
| id | int | PRIMARY KEY |
| name | varchar |
To add an age for each employee, update the TableBase definition:
from iceaxe import TableBase, Field
class Employee(TableBase):
id: int | None = Field(primary_key=True, default=None)
name: str
age: int # new field
Running migrate-generate will:
- Use the database connection to introspect the current schema
- Compare it with your model definitions
- Generate a migration file that will add the
agecolumn to theemployeetable
$ uv run migrate-generate
Generating migration to current schema
New migration added: rev_1729278706.py
It places the migration file in your myproject/migrations directory:
from iceaxe.migrations.migrator import Migrator
from iceaxe.migrations.migration import MigrationRevisionBase
from iceaxe.schemas.actions import ColumnType
class MigrationRevision(MigrationRevisionBase):
"""
Migration auto-generated on 2024-10-18T12:11:46.941324.
Context: None
"""
up_revision: str = "1729278608"
down_revision: str | None = None
async def up(self, migrator: Migrator):
await migrator.actor.add_column(table_name="employee", column_name="age", explicit_data_type=ColumnType.INTEGER, explicit_data_is_list=False, custom_data_type=None)
await migrator.actor.add_not_null(table_name="employee", column_name="age")
async def down(self, migrator: Migrator):
await migrator.actor.drop_column(table_name="employee", column_name="age")
up applies the schema change; down reverses it. Here, rolling back drops
the age column and its data.
Review the generated migration before applying it. Existing employees need an
age before the new column can become NOT NULL, so add your data update
between creating the column and adding the constraint:
async def up(self, migrator: Migrator):
await migrator.actor.add_column(table_name="employee", column_name="age", explicit_data_type=ColumnType.INTEGER, explicit_data_is_list=False, custom_data_type=None)
# Insert known ages for employees
await migrator.db_connection.exec(
...
)
await migrator.actor.add_not_null(table_name="employee", column_name="age")
Column Type Changes and Autocast
Changing a column's type generates a modify_column_type migration with
autocast=True. Iceaxe adds a SQL cast so Postgres can convert existing values
to the new type.
Automatic Type Conversion
For example, change a price stored as text to an integer:
# Before
class Product(TableBase):
id: int = Field(primary_key=True)
price: str # Originally stored as text
# After
class Product(TableBase):
id: int = Field(primary_key=True)
price: int # Now we want it as an integer
Iceaxe will generate a migration like this:
async def up(self, migrator: Migrator):
await migrator.actor.modify_column_type(
table_name="product",
column_name="price",
explicit_data_type=ColumnType.INTEGER,
autocast=True # Automatically added
)
autocast=True adds a USING clause to the type change. Supported conversions
include:
- String to numeric types (
"123"→123) - String to boolean (
"true"→True) - String to date/time types (
"2023-01-01"→date(2023, 1, 1)) - String to UUID, JSON, and other specialized types
- Scalar to array types (
42→[42]) - Custom enum conversions
Customizing Autocast Behavior
Edit the generated migration when your data needs a custom conversion.
autocast=False omits the USING clause, so Postgres will accept only type
changes it can perform without an explicit cast:
async def up(self, migrator: Migrator):
# Disable autocast for manual control
await migrator.actor.modify_column_type(
table_name="product",
column_name="price",
explicit_data_type=ColumnType.INTEGER,
autocast=False
)
# Or add custom data transformation logic
await migrator.db_connection.conn.execute("""
UPDATE product
SET price = CASE
WHEN price ~ '^[0-9]+$' THEN price::integer
ELSE 0
END
""")
Supported Type Conversions
The autocast feature supports most common PostgreSQL type conversions:
| From Type | To Type | Example |
|---|---|---|
| VARCHAR/TEXT | INTEGER/BIGINT | "123" → 123 |
| VARCHAR/TEXT | BOOLEAN | "true" → true |
| VARCHAR/TEXT | DATE/TIMESTAMP | "2023-01-01" → 2023-01-01 |
| VARCHAR/TEXT | UUID | "550e8400-..." → UUID |
| VARCHAR/TEXT | JSON/JSONB | '{"key":"value"}' → JSON |
| VARCHAR/TEXT | Custom Enums | "ACTIVE" → enum value |
| Scalar Types | Array Types | 42 → [42] |
| Compatible numeric types | Direct conversion | 123 → 123.0 |
Migration Safety
Automatic casting does not guarantee that every stored value can be converted. Before a production type change:
- Review generated migrations before applying them to production
- Test migrations on a copy of your production data first
- Back up your database before applying type-changing migrations
- Consider data validation - autocast will fail if data cannot be converted
If you have data that cannot be automatically converted (e.g., non-numeric strings when converting to integers), the migration will fail and you'll need to clean up the data first or provide custom conversion logic.
Applying migrations
After reviewing the migration, run migrate-apply. It applies all pending
revisions in your migrations folder, in order:
$ uv run migrate-apply
🚀 Applied 1729278608 in 0.02s
Undoing migrations
Run migrate-rollback to execute the most recent migration's down method.
Each call rolls back one revision:
$ poetry run migrate-rollback
🪃 Rolled back migration to None in 0.01s
Advanced Migration Patterns
On a busy database, a schema change can block application queries while it waits for or holds a lock. Plan the migration around the locks it needs and how long its data updates will take.
When to Disable Transactions
Iceaxe wraps each migration in a transaction by default. If a step fails,
Postgres rolls back the transaction's changes. Set use_transaction = False
only when you need to run operations separately:
Busy tables: A schema change can hold locks until the transaction ends.
Separating operations can shorten that time, though each operation still
needs its own locks. An ACCESS EXCLUSIVE lock waiting behind a long query
can also block later queries in the lock queue.
Concurrent index creation: PostgreSQL does not allow CREATE INDEX CONCURRENTLY inside a transaction block.
Long data updates: Committing batches separately can avoid holding one transaction open for the entire update.
Set the flag on the migration class:
class MigrationRevision(MigrationRevisionBase):
up_revision: str = "1729278608"
down_revision: str | None = None
use_transaction: bool = False # No transaction wrapper
async def up(self, migrator: Migrator):
# This migration runs without a transaction
await migrator.actor.create_index_concurrently(
table_name="employee",
index_name="idx_employee_email",
columns=["email"]
)
Warning: Without the transaction wrapper, completed steps remain applied if a later step fails. Plan how to resume or undo those partial changes before running the migration.
The NOT NULL Constraint Problem
Adding a required field to a large users table needs two operations: filling
existing rows and checking that none are NULL. The check can block traffic.
Don't do this on a hot table:
async def up(self, migrator: Migrator):
# This will lock your entire table during validation!
await migrator.actor.add_column(table_name="users", column_name="department", explicit_data_type=ColumnType.TEXT)
await migrator.actor.add_not_null(table_name="users", column_name="department")
Unless an existing validated constraint proves the column has no NULL values,
adding NOT NULL requires a table scan under an ACCESS EXCLUSIVE lock. That
lock blocks reads and writes while Postgres checks the rows.
Do this instead:
class MigrationRevision(MigrationRevisionBase):
use_transaction: bool = False # We'll handle this manually
async def up(self, migrator: Migrator):
# Step 1: Add the column as nullable (fast)
await migrator.actor.add_column(
table_name="users",
column_name="department",
explicit_data_type=ColumnType.TEXT
)
# Step 2: Add a CHECK constraint without validation (very fast)
await migrator.raw_sql("""
ALTER TABLE users
ADD CONSTRAINT users_department_not_null
CHECK (department IS NOT NULL) NOT VALID
""")
# Step 3: Backfill existing data (can be done in batches)
await migrator.raw_sql("""
UPDATE users
SET department = 'Engineering'
WHERE department IS NULL
""")
# Step 4: Validate the constraint (scans table but allows reads/writes)
await migrator.raw_sql("""
ALTER TABLE users VALIDATE CONSTRAINT users_department_not_null
""")
# Step 5 (optional): Convert to a real NOT NULL constraint
# This step is only supported in PostgreSQL 12+
await migrator.raw_sql("""
ALTER TABLE users ALTER COLUMN department SET NOT NULL
""")
# Step 6: Clean up the check constraint
await migrator.raw_sql("""
ALTER TABLE users DROP CONSTRAINT users_department_not_null
""")
The steps separate the long validation from the exclusive lock:
- Step 1 adds a nullable column without rewriting existing rows
- Step 2 takes an
ACCESS EXCLUSIVElock but skips scanning existing rows - Step 3 can be done in batches if needed, and you control the data being inserted
- Step 4 scans the whole table but only needs a
SHARE UPDATE EXCLUSIVElock, so normal operations continue - Steps 5-6 are optional cleanup if you want a "real"
NOT NULLconstraint
Making Migrations Idempotent
If a migration runs without a transaction, a retry may encounter changes already applied by the first attempt. Make each step check or tolerate that state so rerunning it does not repeat the work.
Always check if changes already exist:
async def up(self, migrator: Migrator):
# Check if column already exists before adding it
result = await migrator.db_connection.conn.fetch("""
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'department'
""")
if not result:
await migrator.actor.add_column(
table_name="users",
column_name="department",
explicit_data_type=ColumnType.TEXT
)
Use IF NOT EXISTS when possible:
async def up(self, migrator: Migrator):
# PostgreSQL has built-in idempotency for many operations
await migrator.raw_sql("""
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email
ON users (email)
""")
await migrator.raw_sql("""
ALTER TABLE users
ADD CONSTRAINT IF NOT EXISTS users_email_unique
UNIQUE (email)
""")
Handle data migrations carefully:
async def up(self, migrator: Migrator):
# For data migrations, use UPDATE WHERE conditions
# that won't affect already-migrated rows
await migrator.raw_sql("""
UPDATE users
SET status = 'active'
WHERE status IS NULL -- Only update unmigrated rows
""")
# Or use INSERT ... ON CONFLICT for new data
await migrator.raw_sql("""
INSERT INTO user_preferences (user_id, theme)
SELECT id, 'dark' FROM users
WHERE id NOT IN (SELECT user_id FROM user_preferences)
""")
Migration Deployment Strategies
For high-traffic applications, consider splitting complex migrations across multiple deployments:
Deploy 1: Add new columns, populate them
async def up(self, migrator: Migrator):
await migrator.actor.add_column(table_name="orders", column_name="payment_method", explicit_data_type=ColumnType.TEXT)
# Populate from existing data
await migrator.raw_sql("UPDATE orders SET payment_method = 'credit_card' WHERE payment_method IS NULL")
Deploy 2: Add constraints once data is clean
async def up(self, migrator: Migrator):
# Data is already populated, safe to add constraint
await migrator.actor.add_not_null(table_name="orders", column_name="payment_method")
Deploy 3: Remove old columns after application is updated
async def up(self, migrator: Migrator):
# Application code no longer references old_payment_field
await migrator.actor.drop_column(table_name="orders", column_name="old_payment_field")
This "expand-migrate-contract" sequence lets old and new application versions coexist during deployment. Remove the old column only after every running version has stopped using it.
Monitoring Migration Performance
For a long data update, report progress between batches so you can see whether the migration is advancing:
async def up(self, migrator: Migrator):
# Log progress for long operations
await migrator.raw_sql("SELECT 'Starting user migration...'")
start_time = time.time()
# Batch large updates to avoid holding locks too long
batch_size = 1000
offset = 0
while True:
result = await migrator.db_connection.conn.execute("""
UPDATE users
SET last_login = COALESCE(last_login, created_at)
WHERE id >= $1 AND id < $2 AND last_login IS NULL
""", offset, offset + batch_size)
if result == "UPDATE 0":
break
offset += batch_size
elapsed = time.time() - start_time
print(f"Processed {offset} users in {elapsed:.2f}s")
You can also query PostgreSQL's pg_stat_progress_create_index view to monitor index creation progress, or pg_locks to see what locks your migration is holding.