Fastcrud

FastCRUD

FastCRUD generates async CRUD methods (and optionally CRUD endpoints) for SQLAlchemy 2.0 models inside a FastAPI app. The two entry points are:

• FastCRUD(Model) — the data-access class. Use this for per-row CRUD operations (single-record get / create / update / delete, paginated lists, joins with relationships). Real codebases use this in services, workers, and custom endpoints.
• crud_router(...) — returns an APIRouter with create/read/update/delete endpoints auto-wired. Use this only when you want generated endpoints; many apps skip it and hand-roll FastAPI routes on top of FastCRUD instances.

This skill covers the public API across fastcrud >= 0.22. SQLAlchemy is the default ORM in examples; SQLModel works the same way except where noted.

Before reaching for FastCRUD, check the When NOT to use FastCRUD section below. Aggregate roll-ups, CTEs, GROUP BY projections, and bulk writes are SQLAlchemy's job — FastCRUD is for per-row CRUD.

Canonical setup

The minimal viable pattern. Always start here, then add filters/joins/relationships as needed.

# models.py
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import DeclarativeBase, relationship

class Base(DeclarativeBase): pass

class Tier(Base):
    __tablename__ = "tier"
    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)

class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String, unique=True)
    tier_id = Column(Integer, ForeignKey("tier.id"))
    tier = relationship("Tier")

# schemas.py
from pydantic import BaseModel, ConfigDict

class UserCreate(BaseModel):
    name: str
    email: str
    tier_id: int

class UserRead(BaseModel):
    model_config = ConfigDict(from_attributes=True)
    id: int
    name: str
    email: str
    tier_id: int

class UserUpdate(BaseModel):
    name: str | None = None
    email: str | None = None

# main.py
from fastapi import FastAPI
from fastcrud import crud_router
from .db import get_async_session
from .models import User
from .schemas import UserCreate, UserRead, UserUpdate

app = FastAPI()

user_router = crud_router(
    session=get_async_session,    # callable returning AsyncSession (FastAPI dependency)
    model=User,
    create_schema=UserCreate,
    update_schema=UserUpdate,
    select_schema=UserRead,        # used for read responses
    path="/users",
    tags=["users"],
)
app.include_router(user_router)

This produces POST /users, GET /users/{id}, PATCH /users/{id}, DELETE /users/{id}, DELETE /users/db_delete/{id} (hard delete), and GET /users (paginated list).

Choosing the right method

When using FastCRUD(Model) directly, pick the method that matches the data shape you actually need. Do not loop over get_multi() and call get_joined() per row — that's the canonical N+1.

Avoiding N+1 — the most common mistake

FastCRUD has two mechanisms to prevent N+1 when fetching related data. Use one of them — never iterate.

1. Auto-detect relationships (zero config, default for v0.21+)

Set include_relationships=True on crud_router (or auto_detect_relationships=True on the FastCRUD method directly). FastCRUD inspects the SQLAlchemy mapper, builds the joins, and threads them through a single query.

crud_router(
    session=get_async_session,
    model=User,
    create_schema=UserCreate,
    update_schema=UserUpdate,
    select_schema=UserWithTier,           # schema includes nested tier field
    include_relationships=True,           # auto-include all relationships
    path="/users",
)

Or pass a list to include only specific relationships:

include_relationships=["tier", "department"]   # whitelist

Gotcha: One-to-many relationships are excluded by default because they can return unbounded data. Opt in explicitly:

include_relationships=True,
include_one_to_many=True,
default_nested_limit=10,    # cap nested rows per parent (uses SQL window function)

default_nested_limit uses row_number() OVER (PARTITION BY ...) at the database level — it does not fetch everything and slice in Python.

2. Manual JoinConfig (when you need explicit control)

For self-joins, aliases, custom join conditions, or per-join schemas:

from fastcrud import FastCRUD, JoinConfig

orders_with_user = await crud.get_multi_joined(
    db=session,
    joins_config=[
        JoinConfig(
            model=User,
            join_on=Order.user_id == User.id,
            join_prefix="user_",         # avoid column collisions
            schema_to_select=UserRead,
        ),
    ],
    offset=0,
    limit=20,
)

For one-to-many with per-parent capping:

JoinConfig(
    model=Article,
    join_on=Article.author_id == Author.id,
    relationship_type="one-to-many",
    sort_columns="created_at",
    sort_orders="desc",
    nested_limit=5,         # 5 most recent articles per author, computed in SQL
)

See references/joins.md for the full JoinConfig spec, polymorphic inheritance, and the auto-detection rules.

limit=None — the second biggest footgun

get_multi(...) / get_multi_joined(...) accept limit=None to fetch every matching row. This is the single biggest cause of latency cliffs and OOMs in real-world FastCRUD code.

Rule: limit=None is only safe when a WHERE clause domain-bounds the result to a known-small set. Otherwise, always pass an explicit numeric limit.

Safe — scoped by a WHERE clause

# OK: scoped to a single project → bounded by N(clips per project)
clips = await crud_clips.get_multi(db, limit=None, project_id=project.id)

# OK: scoped to a small known set of IDs
tiers = await crud_tiers.get_multi(db, limit=None, id__in=tier_ids)

Unsafe — unbounded growth

# BAD: grows linearly with the user table forever
users = await crud_users.get_multi(db, limit=None)

# GOOD: HTTP endpoint with a hard ceiling
limit = min(requested_limit, MAX_PAGE_LIMIT)
users = await crud_users.get_multi(db, offset=offset, limit=limit)

Pattern: sanity-check limit=None results

When limit=None is justified, log when results unexpectedly explode so drift is caught before it becomes an incident:

def handle_query_sanity(items: list, threshold: int, context: str) -> None:
    if len(items) > threshold:
        logger.warning("query growth: %d > %d threshold (%s)",
                       len(items), threshold, context)

entitlements = await crud_entitlements.get_multi(db, limit=None, user_id=user.id)
handle_query_sanity(entitlements["data"], threshold=100, context="user_entitlements")

Three-tier default

When NOT to use FastCRUD

FastCRUD is the right tool for per-row CRUD on a single model, optionally with relationship joins. Drop to raw SQLAlchemy when the query shape is anything else. In a typical app the split is roughly 80/20 — most data access is FastCRUD's territory, but the 20% that isn't is firmly SQLAlchemy's.

crud_router itself is optional. Production codebases often use FastCRUD instances as the data-access layer behind hand-written FastAPI routes, skipping crud_router entirely so they can compose auth, validation, and business logic without subclassing EndpointCreator.

Anti-patterns to refactor on sight

1. Per-ID loop calling crud.get() — classic N+1.

   # BAD
   for tier_id in tier_ids:
       tier = await crud_tiers.get(db, id=tier_id)
   # GOOD — one query
   tiers = await crud_tiers.get_multi(db, id__in=tier_ids, limit=None)

2. db.scalar(select(Model).where(...).exists()) — use await crud.exists(**filters).
3. get_multi(limit=None) then Python filtering — push the filter into kwargs so the database does the work.
4. crud.get() in a hot path without schema_to_select — selects every column. Pass a minimal schema to cut payload size.
5. Loop of crud.create() — calls go one-at-a-time. For >10 rows use crud.upsert_multi(...) or raw db.execute(insert(Model), records).

Filter syntax

FastCRUD accepts filters as keyword arguments on every read/update/delete method, and via FilterConfig for crud_router's GET /list endpoint.

Operator suffix on the field name

The operator goes after a double underscore:

await crud.get_multi(db, price__gte=10, name__ilike="%admin%", id__in=[1, 2, 3])