Verify a PostgreSQL constraint with pytest and Testcontainers

• Use a PostgreSQL container and the production migration path.
• Create the engine from the container URL.
• Isolate test data with rollback or deliberate cleanup.
• Flush the conflicting insert inside the assertion.
• Dispose the engine before stopping the container.

1. Start one session-scoped PostgreSQL container.
2. Build an engine and run alembic upgrade head.
3. Open a fresh SQLAlchemy Session for the test.
4. Commit the first user, then flush the conflicting second user.
5. Roll back the failed transaction and release all resources.

import pytest
from sqlalchemy import create_engine, text
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session
from testcontainers.postgres import PostgresContainer

@pytest.fixture(scope="session")
def postgres_engine():
    with PostgresContainer("postgres:17-alpine") as postgres:
        engine = create_engine(postgres.get_connection_url())
        run_alembic_upgrade(engine, "head")
        yield engine
        engine.dispose()

def test_email_uniqueness_is_case_insensitive(postgres_engine):
    assert postgres_engine.dialect.name == "postgresql"
    with Session(postgres_engine) as session:
        session.add(User(email="User@Example.com"))
        session.commit()

        session.add(User(email="user@example.com"))
        with pytest.raises(IntegrityError):
            session.flush()
        session.rollback()

The test performs two inserts and one indexed uniqueness check. Container startup dominates runtime, so fixture reuse must be balanced with deterministic isolation.

• A failed flush leaves the session transaction failed until rollback.
• Parallel tests need unique databases, schemas, or coordinated cleanup.
• PostgreSQL collation and normalization rules must match the migration's intended rule.
• Application validation can improve feedback but cannot replace the constraint.

• Verify Alembic's version table reached the expected head.
• Remove application duplicate checks and prove the database still protects the rule.
• Insert a distinct email to rule out unrelated schema failure.
• Run the test twice without depending on leftover state.

• SQLite cannot prove PostgreSQL index and collation behavior.
• Creating test-only DDL leaves the migration untested.
• Without flush or commit, the exception may occur outside the assertion.
• Stopping the container before disposing pooled connections causes noisy teardown.