Metadata-Version: 2.5
Name: csrd-migration
Version: 0.8.0
Summary: Versioned schema migrations for csrd database adapters
Project-URL: Repository, https://github.com/csrd-api/fastapi-common
Project-URL: Documentation, https://github.com/csrd-api/fastapi-common/tree/main/packages/migration
Project-URL: Changelog, https://github.com/csrd-api/fastapi-common/blob/main/CHANGELOG.md
License: MIT
Requires-Python: >=3.12
Requires-Dist: csrd-repository
Description-Content-Type: text/markdown

# csrd-migration

Versioned, idempotent schema migrations for `csrd.repository` database adapters.

## Quick start

```python
from csrd.migration import Migration, MigrationRunner
from csrd.repository import SQLiteAdapter

MIGRATIONS = [
    Migration(
        version="001",
        description="create users table",
        up="CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT NOT NULL)",
        down="DROP TABLE users",
    ),
]

async def setup_db():
    adapter = SQLiteAdapter("app.db")
    await adapter.connect()
    runner = MigrationRunner()
    await runner.apply_all(adapter, MIGRATIONS)
```

## API

### `Migration`

Frozen dataclass representing a single schema change.

| Field | Type | Description |
|---|---|---|
| `version` | `str` | Unique version identifier (e.g. `"001"`, `"002"`) |
| `description` | `str` | Human-readable description |
| `up` | `str \| Sequence[str]` | SQL to apply the migration |
| `down` | `str \| Sequence[str]` | SQL to revert the migration |

A `str` is split into statements by `split_sql` (below). A list or tuple is
executed verbatim, one element per statement — use it when you want full
control (lists are stored as tuples, so the dataclass stays hashable).
`up_statements()` / `down_statements()` return exactly what the runner executes.

### `split_sql(sql, *, nested_comments=True) -> list[str]`

Splits at top-level semicolons only. A `;` does not end a statement inside:

- single-quoted strings (`'it''s; ok'`) and `E'...'` strings (`E'it\'s; ok'`);
- double-quoted identifiers and MySQL backtick identifiers;
- dollar quoting: `$$ ... $$` and `$tag$ ... $tag$` (PostgreSQL function / `DO` bodies);
- `--` line comments and `/* */` block comments (nested, PostgreSQL-style;
  pass `nested_comments=False` for first-`*/`-wins);
- the `BEGIN ... END` body of a `CREATE TRIGGER` / `FUNCTION` / `PROCEDURE`
  statement — SQLite triggers, PostgreSQL `BEGIN ATOMIC`, MySQL procedures
  (`CASE ... END`, `END IF`, `END LOOP`, `END WHILE`, `END REPEAT`, `END CASE` handled).

Statements that are empty or comment-only are dropped; each statement is
stripped and has no trailing `;`.

```python
Migration(
    version="007",
    description="remove shares when a collection is deleted",
    up="""
        CREATE FUNCTION remove_shares() RETURNS trigger AS $$
        BEGIN
            DELETE FROM shares WHERE collection_id = OLD.id;
            RETURN OLD;
        END
        $$ LANGUAGE plpgsql;
        CREATE TRIGGER collections_remove_shares AFTER DELETE ON collections
            FOR EACH ROW EXECUTE FUNCTION remove_shares();
    """,
    down="DROP TRIGGER collections_remove_shares ON collections; DROP FUNCTION remove_shares();",
)
```

Statements run without bind parameters, so `PGAdapter` / `MariaAdapter` send
them verbatim — a literal `%` (`RAISE NOTICE '%'`, `format('%I')`) or `:word`
needs no escaping.

### `MigrationRunner`

Applies and tracks migrations against any `ABCDatabaseAdapter`.

| Method | Description |
|---|---|
| `apply_all(adapter, migrations)` | Apply all pending migrations in order |
| `rollback(adapter, migrations, *, to_version=None)` | Roll back applied migrations in reverse order. Pass `to_version` to stop at a specific version, or `None` to roll back all |
| `reapply_all(adapter, migrations)` | Roll back all then re-apply — clean schema reset without dropping the database |
| `current_version(adapter)` | Return the latest applied version, or `None` |
| `pending(adapter, migrations)` | Return migrations not yet applied |

The runner creates a `_csrd_migrations` table automatically to track
applied versions. Re-running is idempotent — already-applied migrations
are skipped.

## Design

- **Adapter-agnostic** — works with `SQLiteAdapter`, `PGAdapter`, `MariaAdapter`
- **Idempotent** — safe to call on every startup
- **Ordered** — migrations are applied in list order
- **Future-ready** — the `Migration` dataclass is the stable interface for
  a future model→SQL translation layer (see `docs/AUGMENT_PLAN.md`)
