Follow the Postgres brick road: a journey of testing against a real database server
summary
Swapping SQLite tests for a real PostgreSQL server (run via Docker Compose) fixes type and dialect mismatches and moves test setup from fragile mocks to running migrations with Alembic and SQLAlchemy. The pipeline creates a template DB and caches it cache keyed by migration hash, cutting migration setup from ~3 seconds to ~0.5s (and individual tests to ~10ms). Tests use a session-scoped DB plus per-test transactions with rollback and an assertion that detects stray commits transactions + rollback with commit-detection, enabling parallel isolated pytest runs.
AI-written summary · about
chapters (13)
- 0:00Welcome
- 0:49Speaker and journey
- 2:14Effective database testing
- 3:37SQLite testing pitfalls
- 6:45Real Postgres for tests
- 7:49Test DB with migrations
- 10:16Caching schemas
- 14:12Test independence with transactions
- 19:50Avoid accidental commits
- 23:19Parallel tests with unique DBs
- 25:06Models vs migrations
- 27:54Schema consistency checks
- 29:41Summary
related talks
37mYes, You Can Test SQL
40mIntegration tests & testContainers
25mCourting Mongo, committing to Postgres: Our data love story!
36mAutomated Performance Testing of Postgres
44mBenchmarking your commits: The Postgres Performance Farm
52mManaging PostgreSQL changes for the Agile DBA
25mBig Bad World of Postgres Dev Environments
32mA Journey to Postgres Productivity with Go