/advanced-alchemy

name: advanced-alchemy
description: "Use when working with advanced_alchemy, alembic/, SQLAlchemyAsyncRepositoryService, SQLAlchemyAsyncConfig, repository_type, service_class, filters, or storage. Not for raw SQLAlchemy — use sqlspec."

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.12

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`, `to_model_on_upsert`, `to_model_on_delete`)
• **Framework plugins** for automatic session/transaction management
• **Custom types**: `EncryptedString`, `EncryptedText`, `PasswordHash`, `FileObject`, `StoredObject`, `DateTimeUTC`, `GUID`, `Bool`, `JsonB`, `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` / `UUIDv7Base` | UUID v7 | `created_at`, `updated_at` / None | Time-ordered IDs when `uuid-utils` is installed or Python supplies UUIDv7 | | `UUIDv6AuditBase` / `UUIDv6Base` | UUID v6 | `created_at`, `updated_at` / None | Time-ordered UUIDv6 IDs | | `BigIntAuditBase` / `BigIntBase` | BigInt auto-increment | `created_at`, `updated_at` / None | Legacy systems, integer PKs | | `NanoIDAuditBase` / `NanoIDBase` | NanoID string | `created_at`, `updated_at` / None | URL-friendly short IDs | | `IdentityAuditBase` / `IdentityBase` | database identity | `created_at`, `updated_at` / None | Native IDENTITY columns | | `DefaultBase` | None (define yourself) | None | Custom or composite primary keys with AA table naming | | `SQLQuery` | None | None | Custom mapped read-only SQL query projections |

Repository Pattern

| Repository | Purpose | | --- | --- | | `SQLAlchemyAsyncRepository[Model]` / `SQLAlchemySyncRepository[Model]` | Standard async/sync CRUD | | `SQLAlchemyAsyncSlugRepository[Model]` / `SQLAlchemySyncSlugRepository[Model]` | CRUD + slug generation helpers | | `SQLAlchemyAsyncQueryRepository` / `SQLAlchemySyncQueryRepository` | Complex read-only queries (no `model_type`) |

Service Layer

| Service | Purpose | | --- | --- | | `SQLAlchemyAsyncRepositoryService[Model]` / `SQLAlchemySyncRepositoryService[Model]` | Full CRUD with lifecycle hooks | | `SQLAlchemyAsyncRepositoryReadService[Model]` / `SQLAlchemySyncRepositoryReadService[Model]` | Read-only (`get_many`, `get`, `count`, `exists`) | | `SQLAlchemyAsyncQueryService` / `SQLAlchemySyncQueryService` | Service wrapper around arbitrary SQL queries |

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

Custom Types

| Type | Purpose | Notes | | --- | --- | --- | | `FileObject` / `StoredObject` | Object storage with lifecycle hooks | Tracks file state across session; auto-deletes on row delete via `StoredObject` tracker; use `FileObjectList` for multi-file JSON arrays | | `PasswordHash` | Hashed password storage | Supports Argon2, Passlib, and Pwdlib backends; returns `HashedPassword` with `.verify()` and `.verify_and_update()` | | `EncryptedString` / `EncryptedText` | Transparent encryption at rest | Supports `FernetBackend` (client-side) and `PGCryptoBackend` (server-side); pass a stable key explicitly | | `GUID` | Cross-dialect UUID column | Native `UUID` on PostgreSQL/DuckDB/CockroachDB, `B