/advanced-alchemy

name: advanced-alchemy
description: "Auto-activate for advanced_alchemy imports, alembic/, SQLAlchemyAsyncRepositoryService, SQLAlchemyAsyncConfig, repository_type, service_class, filters, or storage. Not for raw SQLAlchemy without Advanced Alchemy — use SQLAlchemy guidance."

Advanced Alchemy

Code Style Rules

• Use `Mapped[...]` for columns and `T | None` for optional fields.
• Keep business transformations in service lifecycle hooks.
• Prefer the inner `Repo` service pattern and `advanced_alchemy.*` imports.
• Use `from __future__ import annotations` when it matches the project; 1.11

supports it in model modules.

Match-Your-Framework — read first

advanced-alchemy ships first-party extensions for five web frameworks. If your project uses one of these, **jump directly to the matching integration guide and skip the others**:

• **Litestar** — `SQLAlchemyPlugin` with full DI, session store, CLI. The rest of this SKILL.md covers Litestar by default; also see [`references/litestar_plugin.md`](references/litestar_plugin.md).
• **FastAPI** → [`references/fastapi-integration.md`](references/fastapi-integration.md) — `AdvancedAlchemy(config=..., app=app)`, `Depends(alchemy.provide_session())` DI, `provide_service()`/`provide_filters()`, Alembic CLI via `assign_cli_group`.
• **Flask** → [`references/flask-integration.md`](references/flask-integration.md) — `AdvancedAlchemy(config=..., app=app)` or `init_app()` factory, pull-based `alchemy.get_sync_session()`, async-via-portal.
• **Sanic** → [`references/sanic-integration.md`](references/sanic-integration.md) — `AdvancedAlchemy(sqlalchemy_config=..., sanic_app=app)` (note: `sqlalchemy_config=` kwarg, not `config=`), sanic-ext DI, `request.ctx` sessions.
• **Starlette** → [`references/starlette-integration.md`](references/starlette-integration.md) — `AdvancedAlchemy(config=..., app=app)`, `request.state` session access, lifespan wrapping.

Transaction configuration is framework-specific. Litestar uses `before_send_handler`; FastAPI, Flask, Starlette, and Sanic use `commit_mode="manual"`, `"autocommit"`, or `"autocommit_include_redirect"`. Read the matching framework guide, then [`references/commit-modes.md`](references/commit-modes.md) and [`references/multi-database.md`](references/multi-database.md).

The rest of this SKILL.md covers framework-agnostic topics: base classes, repositories, services, filters, custom types, caching, replicas, operations, and Alembic migrations.

Overview

Advanced Alchemy is NOT a raw ORM — it is a **service/repository layer** built on top of SQLAlchemy 2.0+ with opinionated base classes, audit mixins, and deep framework integrations (Litestar, FastAPI, Flask, Starlette, Sanic). It provides:

• **Base models** with automatic `id`, `created_at`, `updated_at` fields
• **Repository pattern** for type-safe async CRUD
• **Service layer** with lifecycle hooks (`to_model_on_create`, `to_model_on_update`)
• **Framework plugins** for automatic session/transaction management
• **Custom types**: `EncryptedString`, `FileObject`, `DateTimeUTC`, `GUID`, `Bool`, `Vector`, `TOTPSecret`, `OneTimeCode`
• **Alembic integration** for migrations via CLI

Quick Reference

Base Classes

| Base Class | PK Type | Audit Columns | When to Use | | --- | --- | --- | --- | | `UUIDAuditBase` | UUID v4 | `created_at`, `updated_at` | Default choice for most models | | `UUIDBase` | UUID v4 | None | Lookup tables, tags, no audit needed | | `UUIDv7AuditBase` | UUID v7 | `created_at`, `updated_at` | Time-ordered IDs when `uuid-utils` is installed or Python supplies UUIDv7 | | `BigIntAuditBase` | BigInt auto-increment | `created_at`, `updated_at` | Legacy systems, integer PKs | | `NanoIDAuditBase` | NanoID string | `created_at`, `updated_at` | URL-friendly short IDs | | `IdentityAuditBase` | database identity | `created_at`, `updated_at` | Native IDENTITY columns | | `DefaultBase` | None (define yourself) | None | Custom primary keys with AA table naming |

Repository Pattern

| Repository | Purpose | | --- | --- | | `SQLAlchemyAsyncRepository[Model]` | Standard async CRUD | | `SQLAlchemyAsyncSlugRepository[Model]` | CRUD + automatic slug generation | | `SQLAlchemyAsyncQueryRepository` | Complex read-only queries (no model_type) |

Service Layer

| Service | Purpose | | --- | --- | | `SQLAlchemyAsyncRepositoryService[Model]` | Full CRUD with lifecycle hooks | | `SQLAlchemyAsyncRepositoryReadService[Model]` | Read-only (get_many, get, count, exists) |

Key lifecycle hooks: `to_model_on_create`, `to_model_on_update`, `to_model_on_upsert`.

Custom Types

| Type | Purpose | Notes | | --- | --- | --- | | `FileObject` | Object storage with lifecycle hooks | Tracks file state across session; auto-deletes on row delete via `StoredObject` tracker | | `PasswordHash` | Hashed password storage | Supports Argon2, Passlib, and Pwdlib backends; hashes on assignment | | `EncryptedString` | Transparent encryption at rest | Pass a stable key explicitly; the random default is deprecated | | `UUID6` / `UUID7` | Time-sortable UUID variants | UUID7 preferred for standardized timestamp-ordered identifiers | | `DateTimeUTC` | Timezone-aware UTC datetime | Stores as UTC; raises on naive datetimes | | `Bool` | Dialect-aware boolean | Uses Oracle 23c native `BOOLEAN` when SQLAlchemy exposes it; falls back to stock SQLAlchemy `Boolean` | | `Vector` | Dialect-aware vector storage and distance operators | Oracle 23ai `VECTOR`, PostgreSQL/CockroachDB `pgvector`, JSON fallback without distance operators | | `TOTPSecret` / `OneTimeCode` | MFA and single-use code storage | `TOTPSecret` encrypts shared secrets; `OneTimeCode` hashes codes and requires an explicit hashing backend |

Repository Service Layer

`SQLAlchemyAsyncRepositoryService` is the primary service base class. Key behaviors:

• **Dict-to-model conversion**: pass raw `dict` to `create()`, `update()`, `upsert()` — the service converts via `to_model_on_create` / `to_model_on_update