Repository Basics#
Think of a Repository as a dedicated helper for one database table. Instead of writing raw SQLAlchemy select() and session.add() calls throughout your app, you point a repository at your Post model once and call clear methods like repo.add(post), repo.get(post_id), or repo.get_many().
Choosing a Repository Class#
Every async repository has an identical synchronous version—pick async when using AsyncSession and sync when using Session:
Repository Type |
When to Use It |
|---|---|
|
Everyday tables: create, read, update, delete, filter, paginate, and bulk upsert. |
|
Tables with URL slugs (like |
|
Custom |
Creating and Reading Rows#
Suppose our blog has Post and Tag tables. Subclass SQLAlchemyAsyncRepository and set model_type = Post:
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import Mapped, mapped_column
from advanced_alchemy.base import BigIntAuditBase
from advanced_alchemy.mixins import SlugKey
from advanced_alchemy.repository import SQLAlchemyAsyncRepository
class Post(BigIntAuditBase):
__tablename__ = "repo_basics_post"
title: Mapped[str]
content: Mapped[str]
published: Mapped[bool] = mapped_column(default=False)
class Tag(BigIntAuditBase, SlugKey):
__tablename__ = "repo_basics_tag"
name: Mapped[str]
class PostRepository(SQLAlchemyAsyncRepository[Post]):
model_type = Post
async def create_and_fetch_post(db_session: AsyncSession) -> Post:
repo = PostRepository(session=db_session)
saved = await repo.add(
Post(title="Getting Started", content="Repositories keep queries simple."),
auto_commit=True,
)
return await repo.get(saved.id)
Saving, Updating, and Deleting in Bulk#
When importing a batch of drafts or publishing ten posts at once, looping one query at a time is slow. Use the *_many methods to handle whole batches in a single step:
from collections.abc import Sequence
from sqlalchemy.ext.asyncio import AsyncSession
async def create_drafts(
db_session: AsyncSession,
drafts: list[tuple[str, str]],
) -> Sequence[Post]:
repo = PostRepository(session=db_session)
return await repo.add_many(
[Post(title=title, content=body) for title, body in drafts],
auto_commit=True,
)
from sqlalchemy.ext.asyncio import AsyncSession
async def publish_posts(db_session: AsyncSession, post_ids: list[int]) -> list[Post]:
repo = PostRepository(session=db_session)
posts = await repo.get_many(Post.id.in_(post_ids), published=False)
for post in posts:
post.published = True
return await repo.update_many(posts, auto_commit=True)
from collections.abc import Sequence
from sqlalchemy.ext.asyncio import AsyncSession
async def remove_posts(db_session: AsyncSession, post_ids: list[int]) -> Sequence[Post]:
repo = PostRepository(session=db_session)
return await repo.delete_many(post_ids, auto_commit=True)
Syncing External Data with Upserts#
Imagine syncing posts from an external CMS: some posts are brand new, and others are updates to rows already in your database.
upsert_many inserts new rows and updates matching ones in one call. Behind the scenes, it uses your database’s native upsert command (such as ON CONFLICT DO UPDATE on PostgreSQL/SQLite or MERGE on Oracle/SQL Server) whenever match_fields points to a unique column, and safely falls back to a standard lookup-and-save flow otherwise:
from collections.abc import Sequence
from sqlalchemy.ext.asyncio import AsyncSession
async def sync_imported_posts(db_session: AsyncSession, incoming: list[Post]) -> Sequence[Post]:
repo = PostRepository(session=db_session)
return await repo.upsert_many(
incoming,
match_fields=["id"],
auto_commit=True,
)
URL Slugs and Summary Queries#
Need to look up tags by URL slug (like /tags/python) or run a custom GROUP BY count? Use the slug or query repository:
from sqlalchemy.ext.asyncio import AsyncSession
from advanced_alchemy.repository import SQLAlchemyAsyncSlugRepository
class TagRepository(SQLAlchemyAsyncSlugRepository[Tag]):
model_type = Tag
async def find_tag(db_session: AsyncSession, slug: str) -> Tag:
repo = TagRepository(session=db_session)
return await repo.get_by_slug(slug)
from typing import Any
from sqlalchemy import Row, func, select
from sqlalchemy.ext.asyncio import AsyncSession
from advanced_alchemy.repository import SQLAlchemyAsyncQueryRepository
async def count_posts_by_status(db_session: AsyncSession) -> list[Row[Any]]:
query_repo = SQLAlchemyAsyncQueryRepository(session=db_session)
return await query_repo.get_many(
select(Post.published, func.count(Post.id)).group_by(Post.published),
)
Next Steps#
Filtering and Pagination — Search text, filter by lists or null values, and paginate results.
Repositories — Complete repository API reference.