sqlmodel-database_skill

Overview

This skill teaches how to design and implement database schemas with SQLModel, covering both synchronous Session and asynchronous AsyncSession patterns. It focuses on model hierarchies, relationships, FastAPI integration, Neon/PostgreSQL connections, and safe async transaction patterns to avoid common pitfalls like MissingGreenlet. Use it to build robust, validated ORM models and API endpoints with predictable behavior.

How this skill works

The skill shows how to split models into base, table, create/update/read variants and wire them into sync or async engines. It demonstrates session providers for dependency injection in FastAPI, query patterns (filters, joins, pagination), and relationship setups including one-to-many, many-to-many, extra-fields on link tables, and self-referential links. It emphasizes async-safe flows: extract primitives, flush to get generated IDs, then commit and refresh.

When to use it

• When building FastAPI endpoints backed by SQLModel models with Pydantic validation.
• When you need async database access to PostgreSQL (Neon) or SQLite for scalable APIs.
• When defining relationships: one-to-many, many-to-many, link tables with extra fields, or self-references.
• When implementing dependency-injected sessions and transaction-safe service layers.
• When writing tests using in-memory SQLite and overriding session dependencies for fast CI.

Best practices

• Keep separate models: Base (validation), Table (table=True), Create/Read/Update for API contracts.
• Prefer async engine and AsyncSession in FastAPI for non-blocking I/O and better concurrency.
• Avoid committing inside lower-level service functions; let the caller own the transaction.
• Use session.flush() to read generated IDs before commit, and extract primitives from related objects before commit to prevent MissingGreenlet.
• Use model_dump/exclude_unset for PATCH handlers so only provided fields are updated.

Example use cases

• Implement async /tasks endpoints with TaskCreate, TaskRead models and an AsyncSession dependency.
• Model projects and tasks with one-to-many relationships and eager-load with selectinload for performance.
• Create many-to-many worker assignments with a link table that stores assigned_at and role.
• Configure Neon PostgreSQL by converting sync URLs to postgresql+asyncpg and setting pool sizes.
• Write pytest fixtures using an in-memory SQLite engine and dependency_overrides for FastAPI tests.