Databases

Redis Streams give you a light, self-hosted option versus Apache Kafka for event-driven data pipelines. You get append-only log semantics, consumer groups with ack tracking, and sub-millisecond latency on a single Redis 7.4+ instance. Producers XADD events to a stream. Consumer groups read with XREADGROUP in Python via redis-py . Manual XACK calls plus a pending entry list (PEL) give you at-least-once processing.

What follows covers stream basics, consumer groups with failure recovery, a full producer and consumer pipeline with a dead-letter queue, and the ops practices to keep Redis Streams healthy in production.

SQLite is the right default database for most apps. With WAL mode on, it gives you unlimited concurrent readers and one writer. That writer can sustain thousands of transactions per second on modern NVMe drives. SQLite also handles files up to 281 TB and needs zero config, zero extra processes, and zero network hops. Start with SQLite. Move to PostgreSQL only when you hit a real, measured limit, not a guess.

Testcontainers spins up real databases and services as Docker containers inside your test suite. Tests run against production-grade PostgreSQL, Redis, or Kafka instead of flaky mocks. The testcontainers-python v4.14.2 library works with pytest . It automates the container life cycle. You get isolated, reproducible integration tests that catch bugs unit tests miss.

Below: setup with pytest, testing services beyond databases, performance patterns, and CI/CD configuration.

Why Mocks and In-Memory Databases Are Not Enough

Mocking db.execute() only checks if your code calls the function. It does not check if the SQL is valid. It also misses schema errors and type mismatches. You might have passing tests while your queries fail in production.

Alembic is the standard migration tool for SQLAlchemy projects. You run alembic init, point it at your SQLAlchemy models, and use alembic revision --autogenerate to produce migration scripts. Alembic then applies those scripts in order with alembic upgrade head. You get repeatable, reviewable schema changes that work the same way everywhere your app runs. The latest stable release is Alembic 1.18.4. It supports SQLAlchemy 2.0 (now at 2.0.48) and its modern typed APIs.