FastCRUD 0.23.0 Release Notes

FastCRUD 0.23.0 lets SQLAlchemy conditions reach every part of a query and puts access control inside FastCRUD itself. where= takes any SQLAlchemy condition on every read and write, JoinConfig.where chooses which joined rows attach to each parent, and PolicyConfig restricts every operation, join, count and upsert to the rows and columns a caller may see. Along the way, a set of nested_limit bugs that returned wrong or missing children is fixed.

Documentation:

• Row-Level Policies — PolicyConfig, AccessDenied, column rules, server_data, upserts and endpoints
• SQLAlchemy Conditions with where= — OR groups, column comparisons and subqueries on every method
• Choosing Joined Rows with JoinConfig.where — JoinConfig.where and CountConfig.where
• Row-Level Policies in Endpoints — per-request policies in crud_router

Summary

What's New for Users:

• where= on Every Method: Pass SQLAlchemy conditions to any read or write, AND-ed with the keyword filters: OR groups, column-to-column comparisons, .any() / .has() / exists() subqueries
• JoinConfig.where and CountConfig.where: Choose which joined rows attach to each parent, or which rows a count counts, without dropping any parents
• Row-Level Policies: PolicyConfig with read, create, update, delete, create_check and update_check rules, plus hidden_columns and read_only_columns
• Per-Request Policies in Endpoints: crud_router(policy=...) builds each request's policy from its user; refusals become 403s and rows outside the policy 404s
• server_data: create and update take values server code writes, such as an owner from the request's user
• Safer Writes: Updates without filters are refused, and conditions on unjoined models raise instead of multiplying rows
• nested_limit Fixes: Children no longer go to the wrong parents, get duplicated, or disappear through aliases, self-joins, joined-table inheritance and non-key links

Fixes from contributors:

• Nested related objects in create() no longer crash, by @Bhavana257 (closes #282)
• Filters on pre-parsed values such as UUIDs no longer fail in generated endpoints, by @avyuga

New Features

SQLAlchemy Conditions with where=

Docs: SQLAlchemy Conditions with where=

Keyword filters compare a column with a value. Everything else used to mean leaving FastCRUD for raw SQLAlchemy. Now every read and write (get, exists, count, get_multi, get_multi_by_cursor, get_joined, get_multi_joined, select, update, delete, db_delete, upsert_multi) takes where=: one SQLAlchemy condition, or a list of them that must all hold, AND-ed with the keyword filters.

from sqlalchemy import func, true

posts = await post_crud.get_multi(
    db,
    is_deleted=False,
    where=((Post.author_id == me) & (Post.status == "draft")) | (Post.region == "EU"),
)

late = await task_crud.count(db, where=[Task.finished_at > Task.due_at, func.lower(Task.owner) == "ann"])

authors = await author_crud.get_multi(db, where=Author.articles.any(Article.published))

await post_crud.update(db, {"archived": True}, allow_multiple=True, where=true())

where= takes expressions, boolean columns on their own such as User.active, and text(). Anything else raises TypeError, which catches a comparison Python evaluated itself, such as "draft" == status with plain strings on both sides, that SQLAlchemy's own .where() would turn into WHERE true. SQLModel users wrap fields in col() for type checkers.

JoinConfig.where and CountConfig.where

Docs: Choosing Joined Rows with JoinConfig.where

JoinConfig.where chooses which joined rows attach to each parent, never which parents come back:

result = await author_crud.get_multi_joined(
    db,
    joins_config=[
        JoinConfig(
            model=Article,
            join_on=Author.id == Article.author_id,
            join_prefix="articles_",
            relationship_type="one-to-many",
            where=Article.published,
        )
    ],
    nest_joins=True,
)

Every author comes back, each with only their published articles. On a left join the conditions go into the ON clause, so a parent without a match keeps None or []; on an inner join it's left out; on a nested_limit join they go into the separate per-parent query. total_count always agrees with the data. JoinConfig.filters is unchanged and still chooses parents.

CountConfig.where applies inside the count subquery: every parent is kept, and only matching rows are counted.

CountConfig(model=Article, join_on=Article.author_id == Author.id, alias="published_count", where=Article.published)

A one-to-many join with nested_limit also applies the extra conditions of its join_on now, such as (Author.id == Article.author_id) & Article.published, and its filters take the keyword filter operators like any other join.

Row-Level Policies

Docs: Row-Level Policies

A PolicyConfig on a FastCRUD instance restricts every operation through it:

from fastcrud import FastCRUD, PolicyConfig

post_crud = FastCRUD(Post)

policy = PolicyConfig(
    read=Post.published | (Post.owner_id == me),
    create=True,
    create_check=Post.owner_id == me,
    update=Post.owner_id == me,
    delete=Post.owner_id == me,
    read_only_columns={"owner_id"},
    hidden_columns={"internal_notes"},
)

mine = await post_crud.with_policy(policy).get_multi(db)

• Everything is denied unless granted. read, update and delete take True, False or conditions; create takes True or False, and conditions on created rows go in create_check.
• Rows outside the policy behave as absent. Reads leave them out, including every total_count, and writes raise NoResultFound. Updates and deletes reach only rows matching read and their own rule, as Postgres does.
• Callers can't widen it. where= and keyword filters are AND-ed with the policy, and where=true() means every row in reach.
• Checks run in a savepoint. create_check and update_check (which defaults to the update condition) are checked after a flush; a failing row is rolled back without discarding the caller's earlier work in the session, even with commit=False.
• Column rules. Hidden columns are left out of every result, and the caller may not filter, sort, write or name them in where=. Read-only columns may be read but not written.
• AccessDenied, exported from fastcrud, reports a refusal with reason ("operation", "check", "columns" or "row"), operation, model and columns.
• with_policy returns a copy under another policy and never changes the original, so a shared instance can serve each request its own policy.
• Upserts work under a policy on SQLite, PostgreSQL and MySQL, with conflicting rows outside the update's reach refused.

The policy covers its own model only: joined rows are filtered by JoinConfig.where, and related records are written through the related model's own FastCRUD and policy.

Per-Request Policies in Endpoints

Docs: Row-Level Policies in Endpoints

crud_router and EndpointCreator take policy=, a FastAPI dependency returning the request's policy, or None for no restriction on that request:

from fastapi import Depends
from fastcrud import CreateConfig, PolicyConfig, crud_router

def post_policy(user: User = Depends(get_current_user)) -> PolicyConfig | None:
    if user.is_admin:
        return None
    return PolicyConfig(
        read=Post.published | (Post.owner_id == user.id),
        create=True,
        update=Post.owner_id == user.id,
        delete=Post.owner_id == user.id,
        read_only_columns={"owner_id"},
    )

def current_user_id(user: User = Depends(get_current_user)) -> int:
    return user.id

router = crud_router(
    session=get_session,
    model=Post,
    create_schema=PostCreate,
    update_schema=PostUpdate,
    path="/posts",
    tags=["posts"],
    policy=post_policy,
    create_config=CreateConfig(auto_fields={"owner_id": current_user_id}),
)

AccessDenied becomes a 403 with its message as the detail, and rows outside the policy become 404s. auto_fields are written as server_data, so a read-only owner_id can still be set from the current user.

server_data

create and update take a keyword-only server_data: column values server code writes. They win over the caller's values for the same field and skip a policy's column rules, but they're part of the row create_check and update_check see.

await post_crud.with_policy(policy).create(db, PostCreate(title="Hello"), server_data={"owner_id": me})

Bug Fixes

Nested Related Objects in create() — closes #282

Creating a record from a schema with nested related objects crashed with AttributeError: 'dict' object has no attribute '_sa_instance_state', because model_dump() turns them into plain dicts. Relationship fields are now built as their models, to any depth. The wrong shape (something other than a list for a to-many relationship, or a list for a to-one) raises a ValueError naming the field, and a relationship set to None is treated as nothing to relate. Thanks @Bhavana257!

Filters on Values FastAPI Has Already Parsed

A generated endpoint's filter on a UUID column, among others, failed because the filter dependency converted a value FastAPI had already parsed, and UUID() can't take a UUID. A value that's already of the filter's type is now passed through, and so is one that converting would fail or mangle. Thanks @avyuga!

upsert of an Existing Record

upsert ran its update with no filters, so on any table with more than one row it raised MultipleResultsFound. It now updates only that record.

nested_limit Joins

• total_count counted the rows of the separately fetched join, so its filters changed the count of parents returned.
• Aliased joins returned every child twice.
• Self-referential joins returned each row as its own child.
• Joins linked by a column other than the primary key gave children to the wrong parents, or to none. Children are now matched by the column join_on names, and parents sharing a value share their children.
• Children went missing when the join's schema_to_select left out the foreign key, or the foreign key's attribute is named differently from its column, as through a named alias of a joined-table inheritance model.

Auto-Detected Relationships Between Inheritance Siblings

Auto-detected one-to-many relationships between joined-table inheritance models were silently skipped, logged as has no attribute 'c'. They're now joined through the flat alias auto-detection builds for models that share tables.

Changed

update With No Filters Is Refused

# Before: updated every row
await crud.update(db, {"archived": True}, allow_multiple=True)

# After: ValueError("No filters provided..."); say so explicitly
await crud.update(db, {"archived": True}, allow_multiple=True, where=true())

update, delete and db_delete also refuse filters that produce no conditions, such as a where=[] built by a loop that added nothing.

Conditions on Models the Query Doesn't Join Raise ValueError

A condition naming a model the query doesn't join pairs every row with every row of that model. SQLAlchemy only warned, while FastCRUD returned the wrong rows, or updated and deleted them. It now raises a ValueError naming the tables. Join the model, name its alias, or test it in a subquery such as Author.articles.any(...).

db_delete Raises NoResultFound When Nothing Matches

Like delete and update, with or without a policy. It used to succeed without deleting anything, and its endpoint now answers 404 instead of 200.

Errors on the nested_limit Path Propagate

A join condition or filter the separate query can't apply used to leave the nested lists empty; it now raises ValueError. That includes an unknown column in JoinConfig.filters, which used to be ignored. A one-to-many JoinConfig with nested_limit and join_type="inner" is refused too, since the inner join never removed parents.

Reserved Keyword Arguments

where on every method, and server_data on update, are now arguments of their own: a model column with either name can no longer be filtered by keyword. Use where=Model.where == value.

fetch_and_merge_one_to_many

This fastcrud.core function no longer takes pk_column_name: each join's join_on names the parent column its children are matched by.

Improved

• SQLModel tests on PostgreSQL and MySQL: the SQLModel conftest couldn't start either database (a missing psycopg2 driver, a masked MySQL password, and a teardown that hung on PostgreSQL), so the SQLModel suite now runs on all three databases like the SQLAlchemy one.
• Library Skill: the bundled skill for AI coding agents describes where=, JoinConfig.where and policies, with a new references/policies.md.
• Docs: the Library Skill link on the docs index pointed at a page that didn't exist.

What's Changed

• fix by @igorbenav in #335
• fix: support nested related object creation in FastCRUD.create() by @Bhavana257 in #336
• fix: dynamic filter type coercion when FastAPI pre-parses query params by @avyuga in #337
• where=: SQLAlchemy conditions on every read and write by @igorbenav in #338
• JoinConfig.where and CountConfig.where by @igorbenav in #339

New Contributors

• @Bhavana257 made their first contribution in #336
• @avyuga made their first contribution in #337

Full Changelog: v0.22.3...v0.23.0