SQLAlchemy 2.0 AsyncPG & Alembic Migration Playbook

Write non-blocking SQLAlchemy 2.0 async sessions, eager loading queries, and verified Alembic migrations.

---
name: sqlalchemy-2-asyncpg-patterns
description: Write non-blocking SQLAlchemy 2.0 async sessions, eager loading queries, and verified Alembic migrations. Use when working with SQLAlchemy 2.0, AsyncPG, Alembic.
triggers:
  - sqlalchemy 2 asyncpg patterns
  - sqlalchemy 2.0
  - asyncpg
---

# SQLAlchemy 2.0 AsyncPG & Alembic Migration Playbook

## 1. When to Use This Skill
Load `skill-sqlalchemy-2-asyncpg-patterns` whenever an engineering, architecture, or operations task involves **SQLAlchemy 2.0, AsyncPG, Alembic**. Specifically:
- Write non-blocking SQLAlchemy 2.0 async sessions, eager loading queries, and verified Alembic migrations.
- Designing, reviewing, refactoring, or debugging production configurations without guesswork or deprecated flags.
- Running deterministic pre-flight validation and post-execution verification checks before reporting completion.

## 2. Prerequisites & Required Environment
- **Target Stack:** SQLAlchemy 2.0, AsyncPG, Alembic
- **Permissions Boundary:** Read-only inspection by default; ask for explicit human confirmation before executing destructive mutations or production deployments.
- **Reference Specification:** `https://github.com/sqlalchemy/sqlalchemy`

## 3. Step-by-Step Execution Playbook
1. **Context & Configuration Discovery:**
   Inspect existing project manifests (`package.json`, `tsconfig.json`, `pyproject.toml`, `Cargo.toml`, `wrangler.jsonc`, or schema declarations) before proposing changes. Never overwrite existing bindings or credentials blindly.
2. **Deterministic Implementation:**
   Apply the canonical production patterns for **SQLAlchemy 2.0**:
   - Enforce strict schema and type validation at every external boundary.
   - Include explicit error handling, structured telemetry, and idempotency keys on all mutating calls.
   - Keep secrets in environment variables or managed secret stores—never hardcode tokens in source files.
3. **Verification & Regression Gate:**
   Run the verification commands below and inspect both exit codes and output payloads before marking the task complete.

## 4. Reference Commands & CLI Workflow
```bash
alembic check
pytest
```

## 5. Edge Cases & Anti-Hallucination Guardrails
- **Zero Unverified Claims:** Never claim a build, migration, test run, or API call succeeded without inspecting the command output or HTTP status code.
- **Rollback Readiness:** Always record the previous state or migration version so changes can be reverted cleanly if downstream health checks fail.

Synthesized Engineering Blueprint & Setup Guide