Test database: SQLite in-memory vs real Postgres

The most important decision in the module. It sets the speed vs fidelity of your test suite. Get it wrong and you land in one of two hells: fast tests that don't catch real bugs (SQLite when you should have used Postgres), or slow tests the team stops running locally (Postgres when SQLite would have done).

Both options have cases where they're the right call. There's no absolute winner — it depends on which Postgres features your app uses, how big the suite is, and how much fidelity you need.

In this capsule you'll learn the concrete trade-offs of each option, see the full setup for both, get to know the Postgres features SQLite doesn't support (critical for the decision), and cover the middle-ground options (testcontainers, pytest-postgresql) that combine speed and fidelity. By the end, you'll know what to pick for your project and how to configure it.

The central trade-off

SQLITE IN-MEMORY                      REAL POSTGRES (TEST DB)

✅ Trivial setup                      ⚠️ Requires Postgres running
✅ Zero config (in-memory)            ⚠️ Configuration + test DB
✅ Speed: 5-10x faster                ⚠️ Slower (network + disk)
✅ Total isolation between tests      ⚠️ Tests can share state
                                         if rollback isn't applied right

❌ No support for Postgres features   ✅ Supports everything your prod uses
❌ Different behavior in some          ✅ Same behavior as production
   queries (ordering, types)
❌ Bugs SQLite doesn't catch:          ✅ Catches Postgres-specific
   - JSON queries                        bugs
   - Array operations
   - Complex window functions
   - Full-text search
   - Real triggers

The practical rule

If your app uses ANY of these Postgres features:
  - JSON / JSONB columns with queries
  - Arrays
  - Partial / GIN / GiST indexes
  - Window functions
  - Full-text search
  - Triggers / stored procedures
  - Row-Level Security (RLS)
  - Multiple schemas
  - Native UUIDs

→ TEST AGAINST REAL POSTGRES

If your app is basic CRUD with simple queries:
  - Only SELECT/INSERT/UPDATE/DELETE
  - Basic types (int, str, datetime, decimal)
  - No complex queries
  - No Postgres-specific features

→ SQLite IN-MEMORY can work

If in doubt → real Postgres

Option 1: SQLite in-memory

Minimal setup

# tests/integration/conftest.py
import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from app.db import Base


SQLITE_TEST_URL = "sqlite:///:memory:"


@pytest.fixture(scope="session")
def db_engine():
    engine = create_engine(SQLITE_TEST_URL, echo=False)
    Base.metadata.create_all(engine)
    yield engine
    engine.dispose()


@pytest.fixture
def db_session(db_engine):
    connection = db_engine.connect()
    transaction = connection.begin()
    SessionLocal = sessionmaker(bind=connection)
    session = SessionLocal()
    yield session
    session.close()
    transaction.rollback()
    connection.close()

Pros

✅ Zero setup — no Postgres installation required
✅ Zero contention — each xdist worker gets its own in-memory DB
✅ As fast as it gets (no disk, no network)
✅ Works on any laptop with no configuration
✅ Works in CI with no external services

Cons (serious ones)

❌ JSON queries: SQLite has JSON1, but the behavior differs
❌ Arrays: does NOT support native Postgres arrays
❌ Types: DateTime with timezone gets lost
❌ Constraints: some Postgres checks don't apply
❌ Locks: transaction behavior is different
❌ Full-text search: separate module (FTS5), unlike Postgres FTS
❌ UUID: not native (stored as TEXT)

When SQLite in-memory IS the right choice

✅ New app with simple CRUD
✅ ORM-only queries (no raw SQL)
✅ Learning to write integration tests for the first time
✅ Prototypes / proofs of concept
✅ Apps that may switch databases later

When NOT to use SQLite

❌ App with complex JSONB queries
❌ App with Postgres triggers (audit logs, etc.)
❌ App with RLS (multi-tenancy)
❌ App with Postgres extensions (pg_trgm, postgis, etc.)
❌ App with Postgres-specific migrations
❌ App where raw SQL queries have been tuned for Postgres

Option 2: real Postgres (test database)

Prerequisite: Postgres running

# Option A: local install (macOS)
brew install postgresql@16
brew services start postgresql@16

# Option B: Docker
docker run -d --name pg-test \
    -e POSTGRES_PASSWORD=test \
    -p 5432:5432 \
    postgres:16

# Verify
psql -U postgres -c "SELECT version();"

Create the test database

-- Create the test DB (once per project)
CREATE DATABASE myapp_test;

-- User with privileges over that DB
CREATE USER test_user WITH PASSWORD 'test_pass';
GRANT ALL PRIVILEGES ON DATABASE myapp_test TO test_user;

Setup in pytest

# tests/integration/conftest.py
import os
import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from app.db import Base


# Read from an env var for flexibility
TEST_DB_URL = os.getenv(
    "TEST_DATABASE_URL",
    "postgresql://test_user:test_pass@localhost:5432/myapp_test",
)


@pytest.fixture(scope="session")
def db_engine():
    engine = create_engine(TEST_DB_URL, echo=False)
    Base.metadata.create_all(engine)  # creates the schema once
    yield engine
    Base.metadata.drop_all(engine)  # cleans up at the end
    engine.dispose()


@pytest.fixture
def db_session(db_engine):
    connection = db_engine.connect()
    transaction = connection.begin()
    SessionLocal = sessionmaker(bind=connection)
    session = SessionLocal()
    yield session
    session.close()
    transaction.rollback()
    connection.close()

Configure it via .env.test

# .env.test
TEST_DATABASE_URL=postgresql://test_user:test_pass@localhost:5432/myapp_test

# Load it when running tests
export $(cat .env.test | xargs)
pytest

Pros

✅ Same behavior as production
✅ Supports ALL Postgres features
✅ Catches engine-specific bugs (ordering, NULLs, types)
✅ Real-world migrations (Alembic runs real SQL)
✅ Testing of constraints, triggers, RLS

Cons

⚠️ Requires Postgres installed/running
⚠️ Slower than SQLite (typically 2-5x)
⚠️ Tests under xdist must be careful with a shared DB
⚠️ More complex initial setup

Option 3: testcontainers (on-demand Postgres in Docker)

A hybrid: the setup automatically spins up a Postgres container for each test session and destroys it at the end. It combines SQLite's speed of setup (nothing to install beforehand) with Postgres fidelity.

Setup

[project.optional-dependencies]
test = [
    # ... other deps ...
    "testcontainers[postgres]>=4.0",
]

# tests/integration/conftest.py
import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from testcontainers.postgres import PostgresContainer

from app.db import Base


@pytest.fixture(scope="session")
def postgres_container():
    """Postgres container, auto-started and destroyed at the end of the session."""
    with PostgresContainer("postgres:16") as postgres:
        yield postgres


@pytest.fixture(scope="session")
def db_engine(postgres_container):
    """Engine connected to the container."""
    engine = create_engine(postgres_container.get_connection_url())
    Base.metadata.create_all(engine)
    yield engine
    engine.dispose()


@pytest.fixture
def db_session(db_engine):
    connection = db_engine.connect()
    transaction = connection.begin()
    SessionLocal = sessionmaker(bind=connection)
    session = SessionLocal()
    yield session
    session.close()
    transaction.rollback()
    connection.close()

Pros

✅ Real Postgres (all of its features)
✅ Zero manual setup — testcontainers drives Docker
✅ Works in CI wherever Docker is available (GitHub Actions, etc.)
✅ Every test session gets a clean container
✅ No conflict with your local development DB

Cons

⚠️ Requires Docker running (more overhead than local Postgres)
⚠️ Session setup takes 5-10s (container download + start)
⚠️ Whole suite is slightly slower (container overhead)
⚠️ If Docker breaks, the tests break

Recommendation

testcontainers is the best option for 2026 if:

• Your team has Docker
• Your CI can run Docker
• You want real Postgres without assuming Postgres is pre-installed

It's the sweet spot: real Postgres + automatic setup.

Option 4: pytest-postgresql

A plugin that manages a Postgres instance dedicated to tests. More control than testcontainers, less automation:

# tests/integration/conftest.py
import pytest
from pytest_postgresql import factories

# Creates an on-demand Postgres instance
postgresql_proc = factories.postgresql_proc(port=None, unixsocketdir="/tmp")
postgresql = factories.postgresql("postgresql_proc")


@pytest.fixture
def db_session(postgresql):
    # postgresql is an already-connected connector
    ...

Useful if: you want an "embedded" Postgres without Docker.

Less popular than testcontainers in 2026. Recommendation: testcontainers unless you have a specific reason.

Final comparison

Worked case: what should you pick for your project?

Case A: a simple notes app

Stack: FastAPI + SQLAlchemy + Postgres
Features: CRUD over notes, no JSONB, no triggers, no extensions
Team: 2 devs, no Docker locally

Recommendation: SQLite in-memory.

• Trivial setup
• No Docker requirement
• The basic features work fine in SQLite

Case B: a multi-tenant SaaS app

Stack: FastAPI + SQLAlchemy + Postgres
Features: RLS for multi-tenancy, JSONB, partial indexes
Team: 5 devs with Docker, CI on GitHub Actions

Recommendation: testcontainers.

• RLS doesn't work in SQLite
• Automatic containers = no friction
• A Postgres service in CI is unnecessary overhead

Case C: a legacy app with raw SQL

Stack: SQLAlchemy with raw queries tuned for Postgres
Features: window functions, recursive CTEs, full-text search
Team: 10+ devs

Recommendation: local Postgres + testcontainers in CI.

• Tuned queries need real Postgres
• Devs with local Postgres keep their speed
• CI with testcontainers for reproducibility

Advanced patterns

Pattern: ENV-based switching

import os
from sqlalchemy import create_engine

if os.getenv("USE_SQLITE_TESTS"):
    engine = create_engine("sqlite:///:memory:")
else:
    engine = create_engine(os.getenv("TEST_DATABASE_URL"))

Lets you switch via an env var. Useful when some tests are fine on SQLite and others need Postgres.

Pattern: skipif for Postgres-specific tests

import pytest


pytestmark = pytest.mark.skipif(
    os.getenv("USE_SQLITE_TESTS"),
    reason="requires Postgres features",
)


def test_jsonb_query(db_session):
    # Uses JSONB, which SQLite doesn't support
    ...

Pattern: cross-DB parametrize

@pytest.fixture(params=["sqlite", "postgres"])
def db_engine(request):
    if request.param == "sqlite":
        return create_engine("sqlite:///:memory:")
    return create_engine(POSTGRES_TEST_URL)

Each test runs against both DBs. Useful for libraries that must support both.

CI integration

GitHub Actions with testcontainers

# .github/workflows/test.yml
jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.12"
      - run: pip install -e ".[test]"
      - run: pytest tests/integration/

Docker comes pre-installed on GitHub runners. testcontainers works out of the box.

GitHub Actions with a Postgres service

# Alternative: Postgres as a service
jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_PASSWORD: test
          POSTGRES_DB: myapp_test
        ports: ["5432:5432"]
        options: --health-cmd "pg_isready"
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
      - run: pip install -e ".[test]"
      - env:
          TEST_DATABASE_URL: postgresql://postgres:test@localhost:5432/myapp_test
        run: pytest tests/integration/

The service-based approach is slightly faster than testcontainers (no per-session overhead). Trade-off: the tests then require a postgres service in CI.

Traps and common mistakes

Trap 1: testing against your development DB

# ❌ DATABASE_URL points at the real DB
DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://localhost/myapp")

If your test runs Base.metadata.drop_all(engine), you wipe your dev data. Catastrophic.

Fix: a different ENV var for tests:

TEST_DATABASE_URL = os.getenv("TEST_DATABASE_URL", "postgresql://localhost/myapp_test")
# Validate that it is NOT prod
assert "test" in TEST_DATABASE_URL.lower(), "TEST_DATABASE_URL must contain 'test'"

Trap 2: forgetting drop_all in teardown

# ❌ No drop_all
@pytest.fixture(scope="session")
def db_engine():
    engine = create_engine(TEST_DB_URL)
    Base.metadata.create_all(engine)
    yield engine
    # No drop_all → data survives between suite runs

Every suite run piles on more data. Eventually: PK conflicts, slow queries, inconsistent behavior.

Fix: drop_all in teardown:

@pytest.fixture(scope="session")
def db_engine():
    engine = create_engine(TEST_DB_URL)
    Base.metadata.create_all(engine)
    yield engine
    Base.metadata.drop_all(engine)  # ← cleanup
    engine.dispose()

Trap 3: testcontainers without Docker running

$ pytest
testcontainers.core.exceptions.ContainerStartException: Docker daemon is not running

If your team has Docker but sometimes shuts it down, the tests fail. Document it in the README:

## Prerequisites for tests

Tests require Docker to be running:

    docker info  # should print the Docker version

Trap 4: SQLite with DateTime timezone

# Model
class Task(Base):
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))


# Test with SQLite
def test_task_timezone(db_session):
    task = Task(created_at=datetime.now(timezone.utc))
    db_session.add(task)
    db_session.commit()
    fetched = db_session.get(Task, task.id)
    # On SQLite: fetched.created_at has no tzinfo!

SQLite doesn't preserve the timezone of SQLAlchemy's DateTime(timezone=True). Postgres does. If you test with SQLite and production is Postgres, this bug only shows up in prod.

Trap 5: alembic vs metadata.create_all

# tests use create_all
Base.metadata.create_all(engine)

create_all builds the schema from the SQLAlchemy models. But it doesn't apply your migrations. If your migrations add indexes, constraints, or triggers — none of that is in the test DB.

Optional fix: run the migrations instead of create_all:

from alembic.config import Config
from alembic import command


@pytest.fixture(scope="session")
def db_engine():
    engine = create_engine(TEST_DB_URL)
    alembic_cfg = Config("alembic.ini")
    alembic_cfg.set_main_option("sqlalchemy.url", TEST_DB_URL)
    command.upgrade(alembic_cfg, "head")
    yield engine
    command.downgrade(alembic_cfg, "base")
    engine.dispose()

Slower (it runs every migration) but more faithful.

Trap 6: xdist with a shared Postgres

pytest -n auto  # 4 workers

If all 4 workers share the same test DB, you get race conditions. Each one creates and deletes data at the same time.

Fix options:

1. testcontainers — each session gets its own container
2. Schema per worker — myapp_test_worker_0, myapp_test_worker_1...
3. Don't use xdist with a shared DB — serial only

Trap 7: tests that create DB schemas

def test_x(db_session):
    db_session.execute("CREATE TABLE foo ...")
    ...

DDL statements commit automatically in Postgres. The per-test rollback doesn't undo them. Anti-pattern — don't create schemas dynamically inside tests.

Exercise: configure a test DB for your project

1. Decide SQLite vs Postgres vs testcontainers based on your features
2. Create the test DB (manual CREATE DATABASE or testcontainers config)
3. Implement the db_engine fixture with scope=session
4. Implement the db_session fixture with rollback per test
5. Write a simple test that creates and reads a User to verify the setup
6. Verify that pytest -n auto works (xdist compatible)

# pyproject.toml
[project.optional-dependencies]
test = [
    "pytest>=8.2",
    "pytest-mock>=3.12",
    "pytest-asyncio>=0.23",
    "pytest-sugar>=1.0",
    "pytest-randomly>=3.15",
    "factory-boy>=3.3",
    "testcontainers[postgres]>=4.0",
]

[tool.pytest.ini_options]
testpaths = ["tests"]
asyncio_mode = "auto"
markers = [
    "integration: tests that hit a real DB",
]

# tests/integration/conftest.py
import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from testcontainers.postgres import PostgresContainer

from app.db import Base


@pytest.fixture(scope="session")
def postgres_container():
    """Postgres container starts at session begin, stops at end."""
    with PostgresContainer("postgres:16-alpine") as pg:
        yield pg


@pytest.fixture(scope="session")
def db_engine(postgres_container):
    """Engine connected to the test container, with the schema created."""
    engine = create_engine(postgres_container.get_connection_url())
    Base.metadata.create_all(engine)
    yield engine
    engine.dispose()


@pytest.fixture
def db_session(db_engine):
    """Session per test, rolled back at the end."""
    connection = db_engine.connect()
    transaction = connection.begin()
    SessionLocal = sessionmaker(bind=connection)
    session = SessionLocal()
    yield session
    session.close()
    transaction.rollback()
    connection.close()


# tests/integration/test_setup.py
import pytest

from app.models import User


@pytest.mark.integration
def test_can_create_and_query_user(db_session):
    user = User(email="alice@example.com", name="Alice")
    db_session.add(user)
    db_session.commit()

    fetched = db_session.query(User).filter_by(email="alice@example.com").first()
    assert fetched is not None
    assert fetched.name == "Alice"


@pytest.mark.integration
def test_db_starts_clean_each_test(db_session):
    """If test_can_create ran first, this test must still start clean."""
    users = db_session.query(User).all()
    assert users == []

$ pytest tests/integration/ -v
========================= 2 passed in 8.43s =========================

Summary and next step

What you learned in this capsule:

• SQLite in-memory: fast, simple, but limited on Postgres features
• Local Postgres: high fidelity, requires setup
• testcontainers: real Postgres with automatic setup — the 2026 recommendation
• pytest-postgresql: an alternative, less popular
• The decision: Postgres features + Docker availability → testcontainers; simple CRUD → SQLite
• Traps: testing against the dev DB, schema vs migrations, xdist with a shared DB, SQLite and timezones

Checkpoint before moving on

Before continuing to the next capsule, you should:

• ✅ Have decided which option to use for your project
• ✅ Have the test DB configured (SQLite, Postgres, or a container)
• ✅ Have validated with a simple test that the setup works
• ✅ Know at least 3 Postgres features SQLite doesn't support

Bridge to the next capsule

Your test DB is ready. Now comes the canonical pattern — the one every professional SQLAlchemy 2.0 project uses: the engine + session duo with rollback per test, the right scopes, and async handling. Capsule 03 goes into that pattern in detail:

• session-scoped engine (once per suite)
• function-scoped session (one per test, with rollback)
• async support with AsyncEngine and AsyncSession
• patterns for tests that need an explicit commit (rare, but it happens)
• robust cleanup when tests fail

Resources

1. SQLAlchemy 2.0 — testing patterns — the official pattern
2. testcontainers Python — reference
3. pytest-postgresql — the alternative
4. SQLite vs Postgres differences — the canonical list
5. Postgres Docker images — for the Docker setup
6. Alembic — Running migrations programmatically — to run migrations in tests
7. GitHub Actions services — Postgres as a service