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:

from pathlib import Path

import sqlalchemy as sa
from sqlaltery import CLI as SQLAlteryCLI

from my_app import my_model

class MyCLI(SQLAlteryCLI):
	# where to store migration files
	migration_dir = Path(__file__).parent / 'migration'
	# your model metadata
	metadata = my_model.Base.metadata
	# URL of the DB you want to migrate
	db_url = 'sqlite:///somedb.sqlite3'

if __name__ == '__main__':
	import funcli
	cli = MyCLI()
	funcli.run({
		# show pending changes to schema
		'diff': cli.diff,
		# create a new migration file from pending changes (if any)
		# `commit [--data]`
		'commit': cli.commit,
		# migrate DB to a revision
		# `sync [--revision <revision>] [--fake]`
		'sync': cli.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

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:

bash check.sh

How to publish:

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