How to Use FastCRUD with SQLModel: A Complete Async CRUD Guide

FastCRUD is a generic, async-first CRUD helper that works seamlessly with SQLModel because SQLModel models are SQLAlchemy declarative classes under the hood, requiring only that your model defines table=True and you provide an AsyncSession from SQLAlchemy.

FastCRUD (from the benavlabs/fastcrud repository) eliminates boilerplate when building FastAPI applications with SQLModel. Since SQLModel is built directly on SQLAlchemy 2.0, FastCRUD can introspect your models using standard SQLAlchemy inspection while still leveraging SQLModel's Pydantic integration for type-safe request and response schemas.

Why FastCRUD Works with SQLModel

SQLModel models are fully compatible with FastCRUD because they provide the same __table__ attribute and column objects that standard SQLAlchemy declarative models expose. According to the source code in [fastcrud/core/introspection.py](https://github.com/benavlabs/fastcrud/blob/main/fastcrud/core/introspection.py), FastCRUD uses functions like get_primary_key_columns and get_model_column to introspect models, which work identically for SQLModel because it uses SQLAlchemy's declarative base underneath.

The FastCRUD class in [fastcrud/crud/fast_crud.py](https://github.com/benavlabs/fastcrud/blob/main/fastcrud/crud/fast_crud.py) is a generic engine that builds SELECT, INSERT, UPDATE, and DELETE statements using SQLAlchemy core expressions. Since SQLModel fields are SQLAlchemy columns, filter operations like age__gt=30 are parsed by the FilterProcessor in [fastcrud/core/filtering/processor.py](https://github.com/benavlabs/fastcrud/blob/main/fastcrud/core/filtering/processor.py) without requiring any SQLModel-specific logic.

Setting Up SQLModel Classes for FastCRUD

Before using FastCRUD, define your SQLModel model with table=True and optional Pydantic-compatible schemas for create and update operations. You can reuse the same SQLModel class for schemas or create separate ones for clarity.

# fastcrud/examples/item/sqlmodel.py

from datetime import datetime
from sqlmodel import Field, SQLModel, func

class Item(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str | None = None
    description: str | None = None
    category: str | None = None
    price: float | None = None
    last_sold: datetime | None = None
    created_at: datetime = Field(nullable=False, sa_column_kwargs={"default": func.now()})

class CreateItemSchema(SQLModel):
    name: str | None = None
    description: str | None = None
    category: str | None = None
    price: float | None = None
    last_sold: datetime | None = None

class ReadItemSchema(SQLModel):
    id: int
    name: str | None = None
    description: str | None = None
    category: str | None = None
    price: float | None = None
    last_sold: datetime | None = None
    created_at: datetime

class UpdateItemSchema(SQLModel):
    name: str | None = None
    description: str | None = None
    category: str | None = None
    price: float | None = None
    last_sold: datetime | None = None

The critical requirement is that your model class includes table=True, which ensures SQLModel generates the underlying SQLAlchemy __table__ attribute that FastCRUD expects during initialization.

Initializing FastCRUD with SQLModel

Create a FastCRUD instance by passing your SQLModel class as a type parameter along with your create, update, and return schemas. FastCRUD uses these generics for type hinting but does not require the schemas to be SQLModel instances—standard Pydantic models work as well.

from fastcrud import FastCRUD
from fastcrud.examples.item.sqlmodel import Item, CreateItemSchema, UpdateItemSchema, ReadItemSchema

item_crud = FastCRUD[Item, CreateItemSchema, UpdateItemSchema, None, None](Item)

The generic parameters correspond to [ModelType, CreateSchemaType, UpdateSchemaType, None, None] where the last two parameters are reserved for internal use. You only need to provide the model and schema types.

Performing CRUD Operations

All FastCRUD methods are async and require an AsyncSession from sqlalchemy.ext.asyncio. Typically, you inject this session via FastAPI dependencies.

Creating Records

Use create() with return_as_model=True and schema_to_select to return a typed SQLModel instance instead of a raw dictionary:

@router.post("/", response_model=ReadItemSchema)
async def create_item(
    payload: CreateItemSchema,
    db: AsyncSession = Depends(get_db),
):
    return await item_crud.create(
        db, 
        payload, 
        schema_to_select=ReadItemSchema, 
        return_as_model=True
    )

Reading Single Records

The get() method retrieves a single record by primary key or arbitrary filters:

@router.get("/{item_id}", response_model=ReadItemSchema)
async def read_item(item_id: int, db: AsyncSession = Depends(get_db)):
    return await item_crud.get(
        db, 
        id=item_id, 
        schema_to_select=ReadItemSchema, 
        return_as_model=True
    )

Reading Multiple Records with Pagination

The get_multi() method supports offset-based pagination and returns a dictionary with data and total_count:

@router.get("/", response_model=list[ReadItemSchema])
async def list_items(
    db: AsyncSession = Depends(get_db),
    offset: int = 0,
    limit: int = 20,
):
    result = await item_crud.get_multi(
        db,
        offset=offset,
        limit=limit,
        schema_to_select=ReadItemSchema,
        return_as_model=True,
    )
    return result["data"]

Updating Records

Use update() to modify records, then fetch the updated row to return the complete object:

@router.patch("/{item_id}", response_model=ReadItemSchema)
async def update_item(
    item_id: int,
    payload: UpdateItemSchema,
    db: AsyncSession = Depends(get_db),
):
    await item_crud.update(db, payload, id=item_id)
    return await item_crud.get(
        db, 
        id=item_id, 
        schema_to_select=ReadItemSchema, 
        return_as_model=True
    )

Deleting Records

The delete() method removes records matching the provided filters:

@router.delete("/{item_id}")
async def delete_item(item_id: int, db: AsyncSession = Depends(get_db)):
    await item_crud.delete(db, id=item_id)
    return {"detail": "deleted"}

Advanced Filtering and Sorting

FastCRUD supports SQLAlchemy-style filter operators through keyword arguments parsed by [fastcrud/core/filtering/processor.py](https://github.com/benavlabs/fastcrud/blob/main/fastcrud/core/filtering/processor.py). Because SQLModel columns are SQLAlchemy columns, operators like __gt, __lt, __ne, and __in work automatically:

# Fetch expensive items in the 'books' category, sorted by price descending

items_page = await item_crud.get_multi(
    db,
    offset=0,
    limit=25,
    sort_columns="price",
    sort_orders="desc",
    price__gt=10.0,
    category="books",
    schema_to_select=ReadItemSchema,
    return_as_model=True,
)

After query execution, [fastcrud/core/data/formatting.py](https://github.com/benavlabs/fastcrud/blob/main/fastcrud/core/data/formatting.py) handles the conversion of raw database rows into your specified Pydantic or SQLModel schema when return_as_model=True is set.

Summary

• FastCRUD treats SQLModel as a first-class citizen because SQLModel models inherit from SQLAlchemy's declarative base and expose the required __table__ attribute.
• Initialize FastCRUD using generic type parameters [Model, CreateSchema, UpdateSchema, None, None] where Model is your SQLModel class with table=True.
• All CRUD methods (create, get, get_multi, update, delete, upsert) accept an AsyncSession and optional schema_to_select with return_as_model=True for typed responses.
• Advanced filtering using operators like __gt, __lt, and __ne works automatically through the FilterProcessor in fastcrud/core/filtering/processor.py.
• Response formatting into SQLModel schemas is handled by fastcrud/core/data/formatting.py, ensuring type safety without manual mapping.

Frequently Asked Questions

Does FastCRUD require SQLModel specifically, or does it work with regular SQLAlchemy models?

FastCRUD works with any SQLAlchemy 2.0 compatible model, including regular declarative models and SQLModel. The library inspects models using standard SQLAlchemy introspection via fastcrud/core/introspection.py, which detects primary keys and column types regardless of whether you use pure SQLAlchemy or SQLModel's Pydantic-enhanced classes.

Can I use the same SQLModel class for both the database model and the Pydantic schema?

Yes. Since SQLModel inherits from Pydantic's BaseModel, you can use the same class for your database model (with table=True) and as a request/response schema. However, for security and validation purposes, it is often better to define separate schemas for Create and Update operations that exclude sensitive fields like id or created_at, as shown in the fastcrud/examples/item/sqlmodel.py example.

Why am I getting an introspection error when initializing FastCRUD with my SQLModel class?

This typically occurs when you forget to add table=True to your SQLModel class definition. The FastCRUD constructor checks for the __table__ attribute (accessed via inspect(model) in fastcrud/core/introspection.py) to verify that the model is actually mapped to a database table. Without table=True, SQLModel creates a Pydantic model only, not a SQLAlchemy table mapping.

How does FastCRUD handle UUID primary keys with SQLModel?

FastCRUD automatically detects UUID primary keys through the is_uuid_type function in fastcrud/core/introspection.py. When your SQLModel uses uuid.UUID as a primary key type (declared via Field(default=None, primary_key=True) or similar), FastCRUD adjusts its internal handling to accommodate UUID string conversion and comparison operations without requiring additional configuration.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}