Yes, You Can Test SQL
summary
Covers concrete techniques: run raw SQL against an in-memory SQLite or DuckDB transpile to SQLite for fast transformation tests, or spin up a real Postgres with Docker Compose Postgres fixture and pytest fixtures to get true-dialect behavior. Shows injecting seed rows via a CTE-injected test seed so tests run without touching prod, using pytest parametrized cases, metadata reflection to copy schemas, and automating linters and tests with pre-commit and CI workflows.
AI-written summary · about
chapters (17)
- 0:00Introduction
- 1:10Why SQL testing is hard
- 3:18Manual vs automated testing
- 4:23Orders table example
- 5:05Arrange Act Assert pattern
- 7:12Testing logic with test doubles
- 8:57DB-API and Pytest fixtures
- 11:03SQLite and DuckDB testing
- 13:33Transpiling SQL dialects
- 14:56Testing with real DBs in containers
- 21:12Schema changes and data round trips
- 24:42Isolating tests and state
- 26:47Schema reflection and detection
- 28:31Tools beyond hand-rolled
- 29:38Formatters linters pre-commit hooks
- 31:23Data quality vs unit testing
- 32:05Key takeaways and next steps
related talks
30mFollow the Postgres brick road: a journey of testing against a real database server
56mDitch SQLite! Write Reliable EF Core Tests with Testcontainers
50mAdam Furmanek: You Must Test Your Databases And Here Is How
28mAdvanced Testing Techniques
27mDatabase Testing with pytest
13mSQL Test Kit, or how to unit test any SQL query
40mIntegration tests & testContainers
33mNo More SQLite - How to Write Tests With EF Core Using TestContainers