SQLAltery

A migration library for SQLAlchemy. A cleaner frontend to Alembic.

Guide

Set up management script

SQLAltery provides the "functional core". To use it in your project, you'll need to provide your own "imperative shell", but this is easy. Here's a starter managedb.py:

import sqlalchemy as sa
from sqlaltery import SQLAltery

def cmd_diff() -> None:
	# Show pending changes to schema
	salt = _get_sqlaltery()
	print(salt.diff(model.Base.metadata))

def cmd_commit(*, data: bool = False) -> None:
	# Create a new migration file from pending changes (if any)
	salt = _get_sqlaltery()
	salt.generate(model.Base.metadata, data = data)

def cmd_sync(*, revision: int | None = None, fake: bool = False) -> None:
	# Migrate your DB to `revision`. If `None`, this means the latest.
	# `revision == 0` is a special revision that means an empty DB.
	salt = _get_sqlaltery()
	engine = _get_engine()
	with salt.connect_to(engine) as migrator:
		migrator.migrate(revision, fake = fake)

def _get_sqlaltery() -> SQLAltery:
	# choose where to store migration files
	from pathlib import Path
	migration_dir = Path(__file__).parent / 'migration'
	return SQLAltery(migration_dir)

def _get_engine() -> sa.Engine:
	# an engine pointing to the DB you want to migrate
	return sa.create_engine(sa.URL.create(...))

def _get_metadata() -> sa.MetaData:
	# return your model metadata
	from my_app import my_model
	return my_model.Base.metadata

if __name__ == '__main__':
	import funcli
	funcli.run({ 'diff': cmd_diff, 'commit': cmd_commit, 'sync': cmd_sync })

Terminology used here

You can name the operations anything, but this guide will use these terms:

• commit: create a new migration (Django: py manage.py makemigrations)
• sync: apply migrations (Django: py manage.py migrate)
• sync --fake: fake migrations (Django: py manage.py migrate --fake); this updates only the migration metadata (table sqlaltery_migration)

Reset migrations

Assuming your database(s) are synced to latest, delete your migration dir, create a new migration, and do a fake sync:

rm -rf migration
py managedb.py commit
py managedb.py sync --fake

Data migrations

Data migrations usually involve a three-step process: expand, project, contract.

• Expand the schema: T_old to T_exp \superset T_new, where T_exp is such that the underlying database can automatically project the data onto it (e.g., adding only nullable columns)
• Project the data: apply a function T_exp -> T_new to the data; schema stays the same
• Contract the schema: T_exp to T_new

SQLAltery can automatically split the migration into these steps. Run:

py managedb.py commit --data

Now, modify the newly created migration to write data migration code in the forwards and backwards methods in the generated file. You can access the current sa.MetaData in self.md. Once you're done, sync:

py managedb.py sync

Status

• MetaData diffing (sqlaltery/compare.py)
• apply ops to MetaData (sqlaltery/ops.py:apply)
• apply ops to Connection (sqlaltery/ops.py:apply); all the heavy lifting is passed to Alembic's DDL
• write migration to a file (sqlaltery/command.py:_save_migration)
• allow multiple migrations per file (OPS or OPS_{\d+})
• generate(data: bool) that creates a new migration with expand/project/contract phases
• tests (95%)
• docs
  • document basic usage and show example script

Why not use Alembic directly?

• Alembic diffs the head MD against current DB, not against latest migration
• Alembic has split upgrade/downgrade
• Alembic requires you to manually manage metadata in migrations (for doing queries)
• Alembic creates a bunch of files (alembic.ini, env.py). The default case should be simple: just create an SQLAltery and use it; customize by passing arguments.

Internal use

Set up:

uv sync --no-install-workspace

Run checks:

py -m ruff check sqlaltery tests
py -m mypy sqlaltery tests
py -m pytest

How to publish:

bash build.sh
py -m twine upload artifact/dist/<file>