Asqueel Migration

Database schema migration engine — compare a desired database structure against a live database and generate (or apply) the SQL commands that align them.

ORM-agnostic by design: the library never reads your model. You describe the database in a normalized JSON contract — directly, or through the friendlier human JSON / XML external formats — and the engine does the rest: introspection, diff, DDL generation, execution.

Status: Alpha — API may change.

Features

• Four dialects: PostgreSQL (primary), SQLite, MySQL, MSSQL — one reader/writer/adapter trio per dialect, capability-gated.
• Two equivalent external formats: human JSON and XML with a packaged XSD; the same model in either compiles to the identical internal structure.
• Deterministic entity matching: FK/UNIQUE/index names are structural hashes computed from schema + table + columns, so renaming never causes spurious diffs.
• Additive compatibility check: success means the database can host the application without further additive changes, not that both structures are identical. Extra database objects are preserved; renames are never inferred.
• Safe by default: destructive commands (DROP) are disabled unless explicitly enabled; type conversions can be forced and backed up.
• Minimal runtime dependencies: dictdiffer, plus the DB driver of your dialect as an optional extra.

Install

Install from PyPI:

pip install "asqueel-migration[postgresql]"

Extras: postgresql (psycopg 3), mysql (PyMySQL), mssql (pymssql), validation (jsonschema), docs, dev, all. SQLite needs no extra (stdlib driver). all includes all the extras listed above.

Quickstart

from asqueel_migration import (
    JsonStructureProducer, PgDatabase, SqlMigrator, StructureValidator,
)

model = {
    "db": "mydb",
    "schemas": [
        {"name": "public", "tables": [
            {"name": "author", "pkey": "id", "columns": [
                {"name": "id", "dtype": "serial"},
                {"name": "name", "dtype": "A", "size": "0:120",
                 "notnull": True},
            ]},
        ]},
    ],
}

structure = JsonStructureProducer(model).get_json_struct()

db = PgDatabase({"dbname": "mydb", "host": "localhost"},
                application_schemas=["public"])
# Pre-create mydb when using public; see docs/known_limits.md.
migrator = SqlMigrator(db)
migrator.ormStructure = StructureValidator().validate(structure)
migrator.prepareMigrationCommands()
print(migrator.getChanges())        # review the SQL...
# migrator.applyChanges()           # ...or apply it

Swap PgDatabase for SqliteDatabase, MysqlDatabase or MssqlDatabase for the other dialects.

Describing the database

Three input formats, from the friendliest to the most technical:

1. Human JSON — readable JSON with names and lists, compiled by JsonStructureProducer (above).
2. XML — the same information described by the packaged XSD (sql_model-1.0.xsd), ideal for editors and GUIs; compiled by XmlStructureProducer. The inverse struct_to_xml() turns an introspected database back into editable XML. The producer parses XML; callers that need XSD validation perform it separately.
3. Normalized JSON — the internal contract, built directly with the new_*_item factories (the route an ORM extractor takes).

The complete format reference — columns, dtypes, foreign keys (including multi-column), constraints, indexes with per-column sort order and WITH options, extensions, event triggers — is in the Producer Guide, also available as docs/producer_guide.md.

How it works

your model ──(producer)──► normalized JSON ──┐
                                             ├──► diff ──► SQL commands ──► apply
live database ──(reader)──► normalized JSON ─┘

Both sides project to the same normalized JSON (hierarchy: root → schemas → tables → columns/relations/constraints/indexes, each entity carrying entity, entity_name, attributes). The diff engine (dictdiffer) emits added/changed/removed events; the command builder turns them into dialect-specific DDL; the executor assembles and applies it. The JSON Schema of the contract is packaged as structure-1.0.json.

Execution safety and recovery

applyChanges() is atomic by default where the generated DDL supports a transaction. PostgreSQL, SQLite and SQL Server execute extensions and all structural changes as one DDL unit. MySQL runs best-effort because its DDL can implicitly commit; applyChanges() records an explicit warning and returns best_effort: true.

Database creation is always a separate manager operation and cannot be part of the target database transaction. If creating a new database succeeds but its DDL later fails, the empty or partially initialized database remains, while the transactional DDL unit is rolled back where supported.

Every failure stops execution immediately and raises MigrationExecutionError. Its phase, rolled_back and partial_state_possible attributes describe the outcome. The same invocation does not try to repair or reanalyse the database. Run the migration again: it re-introspects the current database and generates only the residual additive changes. Objects already created are retained; no DROP or rename is inferred.

Database management service (0.2 migration)

The Kajenn application now lives in the separate asqueel-dbmanager repository. Install that application to use REST, generated OpenAPI, MCP, or PostgreSQL backup/restore. This engine no longer depends on Kajenn or genro-routes. The former [app] extra and asqueel_migration.app.EditorApp import are removed in 0.2. Python callers and the database CLI remain in this package.

from asqueel_migration import plan_migration, apply_migration

# db is a caller-owned PgDatabase/SqliteDatabase/etc.
# desired is normalized JSON text or a Python dictionary.
try:
    plan = plan_migration(db, desired)
    print(plan["sql"], plan["warnings"])
    # After reviewing the plan:
    result = apply_migration(db, desired, expected_plan=plan["plan_id"])
    assert result["verified"], result["remaining"]
finally:
    db.closeConnection()

SqlMigrator(db, structure=desired) validates and snapshots the input. The service functions require a desired schema: None is rejected before accessing the database. SqlMigrator(db) still supports deferred assignment. Malformed structure containers raise SqlValidationError with their field paths, including when the optional jsonschema dependency is absent. The legacy migrator.ormStructure = desired form remains available and now copies its input. Each preparation filters a fresh working copy; source data and repeated warnings are preserved. To change the desired schema, assign it again: edits to the exposed working dictionary do not update the source.

The connection's database name is the physical destination, including for CREATE DATABASE. The desired structure's root.entity_name is a model label and does not redirect creation to another database.

Plan identifiers cover the desired/current structure and generated commands. They detect drift before execution, but do not prevent concurrent external DDL. Unsupported operations block apply_migration; post-apply verification reports remaining differences without pretending that already applied DDL was undone. The legacy XML helpers in editor_service remain transport-independent.

Modules

Development checks

Install the development dependencies before running static checks:

python -m pip install -e ".[dev,postgresql]"
python -m ruff check src tests
python -m mypy src/asqueel_migration

The dev extra includes JSON Schema and PyMySQL stubs, and the pymssql driver with its bundled Python 3 stubs. These are development dependencies; using the migrator does not require installing every database driver. Mypy reads the installed dictdiffer source because it has no published stubs.

Documentation

Full documentation on Read the Docs. See the dialect matrix, known limits, and test/release instructions. Features marked unreleased are not in the published 0.2.1 package. Current milestones and historical designs are indexed in roadmap/.

License

Apache License 2.0 — Copyright Softwell S.r.l. See LICENSE and NOTICE.