Advanced Repository#

Repositories support composite primary keys, row-level SELECT ... FOR UPDATE locking, conditional bulk deletes, and cross-dialect exception translation.

Composite Primary Keys#

Models keyed by multiple primary-key columns (such as (user_id, role_id) on UserRole) accept composite keys in get and delete_many either as a column-name dictionary (recommended) or as an ordered tuple:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import Mapped, mapped_column
from advanced_alchemy.base import BigIntBase, DefaultBase
from advanced_alchemy.repository import SQLAlchemyAsyncRepository


class AdvancedUser(BigIntBase):
    __tablename__ = "advanced_user_account"
    username: Mapped[str]


class AdvancedRole(BigIntBase):
    __tablename__ = "advanced_role"
    name: Mapped[str]


class AdvancedUserRole(DefaultBase):
    __tablename__ = "advanced_user_role"

    user_id: Mapped[int] = mapped_column(ForeignKey("advanced_user_account.id"), primary_key=True)
    role_id: Mapped[int] = mapped_column(ForeignKey("advanced_role.id"), primary_key=True)
    permissions: Mapped[str] = mapped_column(default="member")


class AdvancedPost(BigIntBase):
    __tablename__ = "advanced_post"
    title: Mapped[str]
    published: Mapped[bool] = mapped_column(default=False)


class AdvancedUserRoleRepository(SQLAlchemyAsyncRepository[AdvancedUserRole]):
    model_type = AdvancedUserRole


class AdvancedUserRepository(SQLAlchemyAsyncRepository[AdvancedUser]):
    model_type = AdvancedUser


class AdvancedPostRepository(SQLAlchemyAsyncRepository[AdvancedPost]):
    model_type = AdvancedPost
async def get_role_by_dict(
    db_session: AsyncSession,
    user_id: int,
    role_id: int,
) -> AdvancedUserRole:
    repo = AdvancedUserRoleRepository(session=db_session)
    return await repo.get({"user_id": user_id, "role_id": role_id})
async def get_and_remove_roles(
    db_session: AsyncSession,
    user_id: int,
    role_id: int,
) -> Sequence[AdvancedUserRole]:
    repo = AdvancedUserRoleRepository(session=db_session)
    _ = await repo.get((user_id, role_id))
    return await repo.delete_many([{"user_id": user_id, "role_id": role_id}])

Row Locking#

Pass with_for_update=True (or a dictionary of FOR UPDATE options such as nowait and of) to acquire a row-level lock for the duration of the current transaction:

async def lock_user_for_update(db_session: AsyncSession, user_id: int) -> AdvancedUser:
    repo = AdvancedUserRepository(session=db_session)
    return await repo.get_one(id=user_id, with_for_update=True)


async def lock_user_without_waiting(db_session: AsyncSession, user_id: int) -> AdvancedUser:
    repo = AdvancedUserRepository(session=db_session)
    return await repo.get_one(
        id=user_id,
        with_for_update={"nowait": True, "of": AdvancedUser.id},
    )

Deleting Rows by Condition#

Call delete_where to delete matching rows directly by SQL predicate without loading every row first:

async def purge_unpublished_drafts(db_session: AsyncSession) -> Sequence[AdvancedPost]:
    repo = AdvancedPostRepository(session=db_session)
    return await repo.delete_where(AdvancedPost.published.is_(False))

Handling Database Exceptions#

Database drivers raise backend-specific DBAPI error codes when unique or foreign-key constraints fail. Repositories translate native constraint errors across PostgreSQL, CockroachDB, SQLite, MySQL, MariaDB, DuckDB, Oracle, SQL Server, and Spanner into typed Python exceptions:

from typing import Optional
from advanced_alchemy.exceptions import DuplicateKeyError, ForeignKeyError, NotFoundError


async def safe_assign_role(
    db_session: AsyncSession,
    user_id: int,
    role_id: int,
) -> Optional[AdvancedUserRole]:
    repo = AdvancedUserRoleRepository(session=db_session)
    try:
        return await repo.add(AdvancedUserRole(user_id=user_id, role_id=role_id), auto_commit=True)
    except (DuplicateKeyError, ForeignKeyError, NotFoundError):
        await db_session.rollback()
        return None
from advanced_alchemy.exceptions import DuplicateKeyError, ForeignKeyError, NotFoundError


async def safe_assign_role(
    db_session: AsyncSession,
    user_id: int,
    role_id: int,
) -> AdvancedUserRole | None:
    repo = AdvancedUserRoleRepository(session=db_session)
    try:
        return await repo.add(AdvancedUserRole(user_id=user_id, role_id=role_id), auto_commit=True)
    except (DuplicateKeyError, ForeignKeyError, NotFoundError):
        await db_session.rollback()
        return None