Testcontainers pattern for PostgreSQL integration tests

# tests/conftest.py
import pytest
import pytest_asyncio
from testcontainers.postgres import PostgresContainer
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
from app.models.base import Base

@pytest.fixture(scope='session')
def postgres_container():
    # Starts a real PostgreSQL container
    with PostgresContainer('pgvector/pgvector:pg17') as container:
        # Wait for container to be ready (handled by testcontainers)
        yield container

@pytest.fixture(scope='session')
def db_url(postgres_container):
    # Get the connection URL (uses random ephemeral port)
    return postgres_container.get_connection_url().replace(
        'postgresql://', 'postgresql+asyncpg://'
    )

@pytest_asyncio.fixture(scope='session')
async def engine(db_url):
    eng = create_async_engine(db_url, echo=False)
    async with eng.begin() as conn:
        # Create all tables including pgvector extension
        await conn.execute(text('CREATE EXTENSION IF NOT EXISTS vector'))
        await conn.run_sync(Base.metadata.create_all)
    yield eng
    await eng.dispose()

@pytest_asyncio.fixture
async def session(engine):
    # Each test gets a transaction that rolls back
    async with engine.begin() as conn:
        session_factory = async_sessionmaker(
            bind=conn, expire_on_commit=False
        )
        async with session_factory() as session:
            yield session
            await conn.rollback()  # Rollback after each test

# Usage
async def test_create_trace(session):
    trace = Trace(
        title='Test trace',
        context_text='Context',
        solution_text='Solution',
        status='pending',
    )
    session.add(trace)
    await session.flush()
    assert trace.id is not None
    # Rolled back automatically

# Install
uv add testcontainers --dev

# Run tests (Docker must be running)
pytest tests/integration/