sqlc — Testing#

Test metrics#

• Test files: 36 *_test.go files (excluding generated testdata files)
• Ratio (test files / source files): ~1:92 against raw Go file count, but misleading — 3,329 .go files include ~hundreds of generated files in internal/endtoend/testdata/; the ratio against hand-written source files is much higher
• Test frameworks: stdlib testing only; github.com/google/go-cmp/cmp for diffing (no testify, no gomock, no ginkgo)

Test organization#

• Placement: Both same-package tests (e.g., internal/engine/postgresql/catalog_test.go in package postgresql) and black-box _test packages (e.g., internal/endtoend/endtoend_test.go in package main)
• Helper packages:
  • internal/sqltest/docker — starts PostgreSQL and MySQL containers via Docker; polls for readiness with a select {} + time.After timeout loop
  • internal/sqltest/native — starts native (apt-installed) PostgreSQL/MySQL on Linux; used as Docker fallback in CI
  • internal/sqltest/local — high-level helper that resolves a live DB via: env var → Docker → native install → t.Skip. Implements schema caching keyed on FNV-64 hash for read-only databases; uses singleflight.Group to deduplicate concurrent DB creation; uses pgx/poolcache for connection reuse. Cleanup via t.Cleanup.
  • internal/sqltest/pgx.go, postgres.go, sqlite.go, mysql.go — lower-level DB helpers used by example tests
• Fixtures: internal/endtoend/testdata/ contains 379 named subdirectories. Each is a self-contained test case with a sqlc config file (sqlc.json/.yaml/.yml), SQL schema + query files, and committed expected generated output. Optionally an exec.json for non-default commands (vet, diff) and a stderr.txt for expected error output. Tests are discovered by filesystem walk, not enumeration.

Test patterns#

Table-driven tests#

• Prevalence: Occasional — used in focused unit tests, not universally
• Style: Anonymous struct slice with name, stmt/json, err fields; iterated with for i, tc := range []struct{...}{} and t.Run
• Example: internal/engine/postgresql/catalog_test.go:14 — TestUpdateErrors iterates 10+ SQL DDL strings and verifies the exact *sqlerr.Error returned; internal/config/config_test.go:26 — TestBadConfigs tests 4 malformed config variants

Snapshot / golden-file testing (primary strategy)#

• Prevalence: The dominant end-to-end strategy — 379 test cases, each a committed snapshot
• Style: TestReplay (and TestExamples) calls cmd.Generate in-process, collects map[string]string (filename → content), then cmpDirectory compares actual output against committed files on disk using go-cmp. A failure shows a unified diff of every changed file.
• Example: internal/endtoend/endtoend_test.go:231 — TestReplay walks testdata/, runs generate/vet/diff, compares output; internal/endtoend/endtoend_test.go:38 — TestExamples does the same for the examples/ tree
• Update workflow: To update golden files, regenerate with sqlc generate; the committed output becomes the new baseline. No explicit -update flag — contributors simply regenerate and commit.

Mocking approach#

• Strategy: No mocking. Unit tests test pure functions (parsers, config, utility packages). Integration-level tests use real database connections provisioned by internal/sqltest/local.
• Rationale: sqlc’s output correctness depends on real SQL parsing and DB catalog state; mocking SQL semantics would be unreliable.

Integration tests#

• Present: Yes
• How: Real PostgreSQL and MySQL databases, obtained via (in priority order): environment variable URI → Docker container (auto-started) → native apt installation. Connection URIs are wired into config.Server structs at test time via MutateConfig hooks. Example tests in examples/*/db_test.go connect to the provisioned DB and run the generated query methods against it.
• Separation: Two mechanisms: (1) build tag //go:build examples gates all examples/ tests; (2) TestReplay’s managed-db context is gated on testctx.Enabled() which returns false if no database URI was resolved. Tests that require a specific external executable (e.g., a WASM runtime) use t.Skipf("executable not found: %s", ...).

Schema validation tests#

• Present: Yes — internal/endtoend/ddl_test.go:TestValidSchema
• How: For every endtoend testcase whose engine is PostgreSQL or MySQL, applies the SQL schema to a live database (via local.PostgreSQL / local.MySQL) in parallel subtests. Validates DDL correctness independently of codegen.

Benchmarks#

• Present: Yes — BenchmarkExamples and BenchmarkReplay in internal/endtoend/endtoend_test.go
• Use: Run cmd.Generate b.N times per test case; measures full compiler throughput. Useful for detecting performance regressions in the parse + codegen pipeline.

Test quality observations#

What’s done well:

• Snapshot corpus is enormous and disciplined: 379 endtoend cases cover edge cases across PostgreSQL, MySQL, and SQLite for Go, Python, Kotlin, and JSON output. Each case is a self-documenting regression test; the committed output serves as both expected value and documentation of what sqlc produces.
• Database provisioning strategy is production-quality: The three-tier local helper (env var → Docker → native) with singleflight deduplication and schema hash-keyed caching is sophisticated engineering. Read-only databases are shared across parallel tests (keyed on schema hash); read-write databases are isolated per test and cleaned up via t.Cleanup. This prevents both redundant setup overhead and test interference.
• No mocking: The decision to always test against real parsers and real databases catches real SQL semantic bugs that a mock would hide. This is the right call for a tool whose correctness guarantee is about SQL semantics.
• go-cmp for diffs: Unified diffs on generated file content produce immediately actionable failure messages, not just “got X, want Y”.
• Build tag gating: The examples build tag cleanly separates DB-dependent tests from pure unit tests, so go test ./... (no tag) always works without infrastructure.
• Parallelism: t.Parallel() is used throughout endtoend tests; errgroup.SetLimit(GOMAXPROCS) in production code has a parallel counterpart in test parallelism. sync.Once in internal/sqltest/local/mysql.go ensures one-time DB setup even under concurrent test goroutines.

What could improve:

• Low unit test coverage in core packages: The compiler pipeline (internal/compiler/), code generators (internal/codegen/), and dialect parsers (internal/engine/dolphin/, internal/engine/postgresql/) have almost no direct unit tests — correctness is verified indirectly via the endtoend snapshot corpus. A mutation or regression in an intermediate compilation step may only be caught by the full generate pipeline, making diagnosis harder.
• Test discovery is implicit: The endtoend test discovery (walk filesystem for config files) means there is no authoritative list of test cases. Adding a case requires knowing the naming convention; there is no TestMain or registry.
• replay variable capture: internal/endtoend/ddl_test.go:35 still uses the j, pkg := j, pkg loop-variable capture idiom from pre-Go-1.22. The replay := replay pattern appears in endtoend_test.go:55. These are safe but will become noise once the codebase fully adopts Go 1.22+ loop semantics.

Patterns worth emulating:

• Three-tier database provisioning with graceful skip — the env var → Docker → native → t.Skip chain is a reusable pattern for any project that needs live DBs in tests but must also run in constrained environments.
• Schema-hash-keyed read-only database caching — hashing migration files to derive a stable DB name allows heavy schema migrations to be applied once and shared across all parallel tests that use the same schema, dramatically reducing test setup time.
• exec.json + stderr.txt per testcase — encoding the expected command and stderr output as structured files alongside the testcase data (rather than in Go code) makes it trivial to add new test cases without touching Go source.
• Benchmarks alongside endtoend tests — BenchmarkReplay enables go test -bench=. to measure full-pipeline throughput, providing a concrete regression signal for codegen performance.