Litestar app with advanced-alchemy and DTOs on SQLite: working versions (tested 2026-10-15)

litestar==2.24.0
advanced-alchemy==1.11.0
aiosqlite==0.22.1
pytest==9.1.1

from advanced_alchemy.base import BigIntAuditBase
from advanced_alchemy.extensions.litestar import (
    SQLAlchemyAsyncConfig,
    SQLAlchemyDTO,
    SQLAlchemyDTOConfig,
    SQLAlchemyPlugin,
)
from litestar import Litestar, get, post
from litestar.exceptions import NotFoundException
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import Mapped, mapped_column


class Author(BigIntAuditBase):
    __tablename__ = "author"

    name: Mapped[str]
    email: Mapped[str] = mapped_column(unique=True)


class AuthorWriteDTO(SQLAlchemyDTO[Author]):
    # Clients may not set server-managed fields.
    config = SQLAlchemyDTOConfig(exclude={"id", "created_at", "updated_at"})


class AuthorReadDTO(SQLAlchemyDTO[Author]):
    config = SQLAlchemyDTOConfig(exclude={"created_at", "updated_at"})


@post("/authors", dto=AuthorWriteDTO, return_dto=AuthorReadDTO, status_code=201)
async def create_author(data: Author, db_session: AsyncSession) -> Author:
    db_session.add(data)
    # Commit explicitly so the row is visible to other requests. Relying on the
    # plugin's commit timing is fragile across versions and response paths.
    await db_session.commit()
    # Reload server-managed columns (id, timestamps) that the commit expired,
    # so the response DTO does not trigger a lazy load in async code.
    await db_session.refresh(data)
    return data


@get("/authors/{author_id:int}", return_dto=AuthorReadDTO)
async def get_author(author_id: int, db_session: AsyncSession) -> Author:
    author = await db_session.get(Author, author_id)
    if author is None:
        raise NotFoundException(f"author {author_id} not found")
    return author


def create_app(url: str) -> Litestar:
    # create_all runs on the app lifespan, so TestClient must be used as a context manager.
    db = SQLAlchemyAsyncConfig(connection_string=url, create_all=True)
    return Litestar(
        route_handlers=[create_author, get_author],
        plugins=[SQLAlchemyPlugin(config=db)],
    )

import pytest
from litestar.testing import TestClient

from app import create_app


@pytest.fixture
def client(tmp_path):
    # A file-backed SQLite DB: each pooled connection to ":memory:" would see its own empty database.
    app = create_app(f"sqlite+aiosqlite:///{tmp_path / 'test.db'}")
    # The context manager runs the lifespan, which creates the tables.
    with TestClient(app) as c:
        yield c


def test_create_returns_201_without_audit_fields(client):
    r = client.post("/authors", json={"name": "Ada", "email": "ada@example.com"})
    assert r.status_code == 201
    body = r.json()
    assert body["id"] and body["name"] == "Ada"
    assert "created_at" not in body and "updated_at" not in body


def test_created_author_is_visible_to_later_requests(client):
    # A new request gets a new session, so this only passes if the create was committed.
    new_id = client.post("/authors", json={"name": "Grace", "email": "g@example.com"}).json()["id"]
    r = client.get(f"/authors/{new_id}")
    assert r.status_code == 200
    assert r.json()["email"] == "g@example.com"


def test_missing_field_is_400(client):
    r = client.post("/authors", json={"name": "No Email"})
    assert r.status_code == 400


def test_unknown_id_is_404(client):
    assert client.get("/authors/999").status_code == 404

[pytest]
pythonpath = .