litestar-org/litestar-skills/advanced-alchemy

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`.
• FastAPI → `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` — AdvancedAlchemy(config=..., app=app) or init_app() factory, pull-based alchemy.get_sync_session(), async-via-portal.
• Sanic → `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` — 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` and `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

Repository Pattern

Service Layer

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

Custom Types

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 lifecycle hooks before persistence
• Bulk operations: create_many(data), update_many(data), upsert_many(data, match_fields=..., no_merge=False, chunk_size=None), delete_many(item_ids) — batched in a single transaction; upsert_many() dispatches to native dialect primitives (ON CONFLICT, ON DUPLICATE KEY UPDATE, MERGE, Spanner INSERT OR UPDATE) via resolve_upsert_strategy() when uniqueness is provable, and delete_many() accepts raw primary keys, composite-key tuples/dicts, model instances, or mixed lists
• Lifecycle hooks: to_model_on_create, to_model_on_update, to_model_on_upsert — override to transform input data, hash passwords, normalize strings, etc.

Mixins

Litestar Integration

Use SQLAlchemyPlugin (composite of SQLAlchemyInitPlugin + SQLAlchemySerializationPlugin) for full integration:

• `SQLAlchemyPlugin`: registers engine/session providers, a Litestar

before_send hook, and ORM type encoders in one call

• `SQLAlchemyDTO`: generates Litestar DTOs directly from ORM models with include/exclude field control
• Type encoders: automatic serialization of datetime, UUID, Decimal, Enum, and custom column types
• Exception handling: set_default_exception_handler=True (the default)

registers RepositoryError: exception_to_http_response on the app — unless Litestar(exception_handlers={...}) contains any integer status-code key (500, 404, etc.), in which case SQLAlchemyInitPlugin skips default registration and you must register RepositoryError: exception_to_http_response explicitly

<workflow>

Workflow

Step 1: Define the Model

Choose the appropriate base class from the quick reference table. Use UUIDAuditBase unless you have a specific reason not to. Define columns with Mapped[] typing.

Step 2: Create the Repository

Create a repository class with model_type set to your model. Use SQLAlchemyAsyncRepository for standard CRUD, SQLAlchemyAsyncSlugRepository if the model uses SlugKey.

Step 3: Build the Service

Create a service class with an inner Repo class. Set match_fields for upsert logic. Add lifecycle hooks (to_model_on_create, to_model_on_update) for business logic transformations.

Step 4: Wire into Framework

Use the framework plugin (Litestar, FastAPI, Flask, Sanic) to inject sessions and register the service as a dependency.

Step 5: Generate Migration

With Litestar, run litestar database make-migrations -m "description" and then litestar database upgrade. With the standalone CLI, put the required config option before the command: alchemy --config path.to.config make-migrations -m "description".

</workflow>

<guardrails>

Guardrails

• Always use the service layer for business logic — never put validation, hashing, or transformation logic directly in route handlers or repositories
• Repositories are for data access only — no business rules, no side effects beyond database operations
• Never bypass the service layer to call repository methods directly from handlers
• Always set `match_fields` on services that use upsert() to avoid duplicate-key errors
• Use `schema_dump()` / `schema_dump_config` for explicit dump behavior — services already convert Pydantic/msgspec/attrs/dataclass inputs during model conversion
• Prefer `UUIDAuditBase` as default base class — only deviate when you have a concrete reason
• Use `advanced_alchemy.*` imports — the old litestar.plugins.sqlalchemy paths are deprecated
• Pass stable keys to `EncryptedString` and `EncryptedText`. Omitting

key= emits a 1.11 deprecation warning and produces data that cannot survive a process restart.

• Use `get_many()` and `get_many_and_count()`. list() and

list_and_count() are deprecated until 2.0.

• Explicitly register `RepositoryError: exception_to_http_response` when using integer status-code `exception_handlers` in Litestar. SQLAlchemyInitPlugin skips its default RepositoryError handler whenever any int key (500, 404, etc.) is present in Litestar(exception_handlers={...}).

</guardrails>

<validation>

Validation Checkpoint

Before delivering code, verify: