# advanced-alchemy

Adopt this skill: https://proskills.md/skills/other/litestar-org-litestar-skills-skills-advanced-alchemy

Page: https://proskills.md/skills/other/litestar-org-litestar-skills-skills-advanced-alchemy
This document is the public skill bundle for that page. It contains SKILL.md and every helper file the catalog carries. No login is required.

## File: skills/advanced-alchemy/SKILL.md
size: 8030
upstream: https://github.com/litestar-org/litestar-skills/blob/main/skills/advanced-alchemy/SKILL.md

```text
---
name: advanced-alchemy
description: "Auto-activate for 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.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`, `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, `BINARY(16)` or `CHAR(32)` fallback elsewhere; pair with `UUIDv7AuditBase` / `UUIDv6AuditBase` for time-sortable UUIDs |
| `DateTimeUTC` | Timezone-aware UTC datetime | Stores as UTC; normalizes or raises on naive datetimes |
| `Bool` | Dialect-aware boolean | Uses Oracle 23ai native `BOOLEAN` when SQLAlchemy exposes it; falls back to stock SQLAlchemy `Boolean` |
| `JsonB` | Dialect-aware JSON / JSONB | Uses `JSONB` on PostgreSQL/CockroachDB, `JSON` on other backends, `BLOB` with `OSON` on Oracle |
| `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 and returns `TOTPProvider`; `OneTimeCode` hashes codes with TTL/attempt tracking and returns `HashedOneTimeCode` |

## 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)`, `delete_many(item_ids)` — batched in a single transaction; `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

| Mixin | Fields Added | When to Use |
| --- | --- | --- |
| `AuditColumns` | `created_at`, `updated_at` | Add timestamps to a model with a custom primary key |
| `SlugKey` | unique `slug` column | Pair with a slug repository; the mixin does not generate values |
| `UniqueMixin` | `as_unique_async()` / `as_unique_sync()` | Session-cached select-or-create after defining `unique_hash(
```

## File: README.md
upstream: https://github.com/litestar-org/litestar-skills/blob/main/README.md

````text
# litestar-skills

> Opinionated, first-party agent skills, plugins, subagents, hooks, and rules for the **Litestar** framework and its ecosystem — published from a single-root repository across Google Antigravity, Claude Code, Codex CLI, Cursor, OpenCode, and VS Code / Copilot.

`litestar-skills` teaches AI coding agents how the Litestar team builds software. Dedicated package skills cover Litestar, SQLSpec, Advanced Alchemy, msgspec, Polyfactory, pytest-databases, Litestar Security, Litestar Autowire, Litestar Granian, Litestar SAQ, Litestar Queues, Litestar Vite, Litestar MCP, Litestar Email, Litestar HTMX, Litestar Docs (Sphinx & Zensical), Litestar Build, Litestar Deployment, Litestar Testing, and the Litestar Styleguide.

## Status

**v0.12.0** — Single-root multi-harness packaging, 20 skills. The plugin and namespace identifier is `litestar`. The Python package and repository remain `litestar-skills`.

## Host Artifacts

| Host | Entry Point |
| --- | --- |
| **Google Antigravity** | `plugin.json` + `hooks.json` + `rules/litestar-antigravity.md` + `agents/*.md` + `skills/` |
| **Claude Code** | `.claude-plugin/plugin.json` + `.claude-plugin/marketplace.json` + `hooks/hooks-claude.json` + `agents/*.md` + `skills/` |
| **Codex CLI** | `.codex-plugin/plugin.json` + `.agents/plugins/marketplace.json` + `.codex/agents/*.toml` + `skills/` |
| **OpenCode V2** | `.opencode/plugins/litestar.js` + `skills/` |

## Harness Names

| Concept | Canonical Value |
| --- | --- |
| Plugin / marketplace identity | `litestar` |
| Skill directory names | `skills/<skill-name>/SKILL.md`, e.g. `skills/sqlspec/SKILL.md` |
| Hook and policy namespace | `litestar:<skill-name>`, e.g. `litestar:sqlspec` |
| Reviewer subagent | `agents/litestar-reviewer.md` and `.codex/agents/litestar-reviewer.toml` |

| Harness | Skill Trigger | Reviewer Agent Trigger |
| --- | --- | --- |
| **Google Antigravity** | Skills load from the `litestar` plugin or `.agents/skills/`. `SessionStart` in `hooks.json` and `rules/litestar-antigravity.md` inject `litestar:<skill-name>` context when project signals are detected. | `litestar-reviewer` from `agents/`. |
| **Claude Code** | `/litestar:litestar` for the hub skill; `/litestar:sqlspec` for focused skills. Plugin policy uses `Skill(litestar:<skill-name>)`. | `litestar-reviewer` from `agents/`. |
| **Codex CLI** | Force the hub with `$litestar:litestar` and focused skills with `$litestar:<skill-name>`, or use natural language. | `$agent litestar-reviewer` from `.codex/agents/`. |
| **OpenCode V2** | `.opencode/plugins/litestar.js` injects `litestar:<skill-name>` system reminders and registers `litestar-reviewer`; skills load from `.agents/skills/`. | `litestar-reviewer` registered via plugin. |

## Install

### Google Antigravity

```bash
agy plugin install https://github.com/litestar-org/litestar-skills
```

### Claude Code

```bash
claude plugin marketplace add litestar-org/litestar-skills
claude plugin install litestar@litestar
```

### Codex CLI

```bash
codex plugin marketplace add litestar-org/litestar-skills
codex plugin add litestar@litestar
```

To install the optional reviewer subagent in Codex:

```bash
cp .codex/agents/litestar-reviewer.toml ~/.codex/agents/
```

### OpenCode V2

```bash
opencode plugin add litestar-org/litestar-skills
```

## Optional: Google Developer Knowledge MCP

Google publishes an MCP server returning Firebase, Google Cloud, Android, and Maps documentation at `developerknowledge.googleapis.com`.

```bash
claude mcp add google-dev-knowledge --transport http \
  https://developerknowledge.googleapis.com/mcp \
  --header "X-Goog-Api-Key: YOUR_API_KEY"
```

Full reference: [`skills/litestar-styleguide/references/google-developer-knowledge-mcp.md`](skills/litestar-styleguide/references/google-developer-knowledge-mcp.md).

## Uninstall

- **Google Antigravity**: `agy plugin uninstall litestar` (or `rm -rf ~/.gemini/config/plugins/litestar`)
- **Claude Code**: `claude plugin uninstall litestar`
- **Codex CLI**: `codex plugin remove litestar@litestar && codex plugin marketplace remove litestar`
- **OpenCode V2**: `opencode plugin remove litestar-org/litestar-skills`

## Skill Catalog

20 skills with focused reference guides:

| Category | Skills |
| --- | --- |
| **Core** | `litestar` (routing, DTOs, OpenAPI, DI/Dishka, guards, middleware, exceptions, plugins, Channels/SSE/WebSockets, settings, data services, filters & pagination) |
| **Foundation** | `litestar-styleguide` |
| **Data & Serialization** | `sqlspec`, `advanced-alchemy`, `msgspec` |
| **Security & Auth** | `litestar-security` |
| **Tasks & Queues** | `litestar-queues`, `litestar-saq` |
| **Frontend** | `litestar-vite` (Vite + Inertia), `litestar-htmx` |
| **Server** | `litestar-granian` |
| **Integrations & Protocols** | `litestar-mcp`, `litestar-email`, `litestar-autowire` |
| **Documentation** | `litestar-docs` (Sphinx & Zensical) |
| **Packaging & Deployment** | `litestar-build`, `litestar-deployment` |
| **Testing** | `litestar-testing`, `pytest-databases`, `polyfactory` |

## Project Documents

- [Roadmap](docs/roadmap.md) — deferred items with explicit graduation triggers.
- [Policy & permissions](docs/policy.md) — per-host allow/ask/deny grammar and managed-settings paths. Drop-in template at [templates/managed-settings/claude-code.json](templates/managed-settings/claude-code.json).

## Contributing

See [`CONTRIBUTING.md`](CONTRIBUTING.md) for skill authoring conventions, manifest sync rules, and validation gates.

## License

MIT — see [`LICENSE`](LICENSE).
````
