SQLModel Integration#
SQLModel classes configured with table=True work directly with Advanced Alchemy repositories and services.
Defining and Querying a SQLModel Table#
Mark your SQLModel class with table=True and point a repository at it just like any SQLAlchemy model:
from typing import Optional
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from sqlmodel import Field, SQLModel
from advanced_alchemy.repository import SQLAlchemyAsyncRepository
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
secret_name: str
age: Optional[int] = None
class HeroRepository(SQLAlchemyAsyncRepository[Hero]):
model_type = Hero
async def create_and_list_heroes() -> list[Hero]:
engine = create_async_engine("sqlite+aiosqlite:///:memory:")
session_factory = async_sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
async with engine.begin() as conn:
await conn.run_sync(SQLModel.metadata.create_all)
try:
async with session_factory() as session:
repo = HeroRepository(session=session)
await repo.add(Hero(name="Spider-Man", secret_name="Peter Parker"))
await session.commit()
return await repo.get_many()
finally:
await engine.dispose()
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from sqlmodel import Field, SQLModel
from advanced_alchemy.repository import SQLAlchemyAsyncRepository
class Hero(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str
secret_name: str
age: int | None = None
class HeroRepository(SQLAlchemyAsyncRepository[Hero]):
model_type = Hero
async def create_and_list_heroes() -> list[Hero]:
engine = create_async_engine("sqlite+aiosqlite:///:memory:")
session_factory = async_sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
async with engine.begin() as conn:
await conn.run_sync(SQLModel.metadata.create_all)
try:
async with session_factory() as session:
repo = HeroRepository(session=session)
await repo.add(Hero(name="Spider-Man", secret_name="Peter Parker"))
await session.commit()
return await repo.get_many()
finally:
await engine.dispose()
Metadata Configuration#
SQLModel registers tables on SQLModel.metadata rather than Advanced Alchemy’s orm_registry.metadata. Pass metadata=SQLModel.metadata to SQLAlchemyAsyncConfig or SQLAlchemySyncConfig so Alembic migrations and create_all target your SQLModel tables.