db-test

name

db_test

description

Run the storage conformance suite against real database engines (Postgres, MySQL, MSSQL, Oracle) in local Docker containers, end to end — start Docker Desktop, build the containers up, run the engine legs, tear everything down, quit Docker Desktop. Use when the user asks to "test against real databases", "run the postgres/mysql/mssql/oracle leg", "db test", or before landing a change that touches the database backend, the schema (models/rows.py), or a dialect profile.

Local real-engine database testing

The full cycle is: start Docker Desktop → build up → test → tear down → quit Docker Desktop. Leave the machine as you found it — no containers running, no Docker Desktop in the dock, no venv missing drivers the sqlite suite needs.

The single source of truth for images, credentials, ports, and health checks is docker/compose.test.yml (CI uses the same file; details and per-engine notes in docker/README.md). Engine legs live in tests/test_storage_conformance.py, gated on VFS_TEST_<ENGINE>_URL env vars — unset means the leg skips, so nothing here is needed for a plain uv run pytest.

1. Start Docker Desktop

Check whether Docker Desktop is already running before launching it — the user may have it open (or be mid-quit, which can take

30 s while the VM winds down; open -a Docker during a wind-down races the shutdown). Ask/confirm state first, then start:

pgrep -x "Docker Desktop" >/dev/null && echo "already running" || echo "not running"
open -a Docker
for i in $(seq 1 45); do docker info >/dev/null 2>&1 && break; sleep 2; done
docker info --format '{{.ServerVersion}}' || echo "Docker never came up"

If it was already running, skip open -a Docker and go straight to the docker info wait. If docker info still fails after the loop, stop and report — do not retry the suite against a dead daemon.

2. Build up

Postgres has no compose profile and starts by default; the heavier engines start when named (naming a service activates its profile):

docker compose -f docker/compose.test.yml up -d --wait                 # postgres only
docker compose -f docker/compose.test.yml up -d --wait mysql          # + mysql
docker compose -f docker/compose.test.yml up -d --wait mssql oracle   # heavyweights

--wait blocks on query-level healthchecks, not container liveness. Oracle's first start takes 30–60 s; MSSQL runs amd64-under-Rosetta and needs the host ODBC driver (brew install msodbcsql18, one-time).

Install the drivers for every engine you will test in one sync — uv sync makes the venv match exactly, so syncing one extra evicts the others' drivers:

uv sync --extra postgres --extra mysql --group dev   # add --extra mssql / oracle as needed

3. Test

One env var + one marker per engine (URLs use offset host ports so local installs never shadow the containers — a host Postgres on 5432 silently swallowing connections is a known failure mode):

VFS_TEST_POSTGRES_URL="postgresql+asyncpg://vfs:vfs@localhost:54320/vfs" \
  uv run pytest -m postgres --tb=short

VFS_TEST_MYSQL_URL="mysql+aiomysql://vfs:vfs@localhost:33061/vfs?charset=utf8mb4" \
  uv run pytest -m mysql --tb=short

VFS_TEST_MSSQL_URL="mssql+aioodbc://sa:vfsStr0ngPassw0rd@localhost:14330/master?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes" \
  uv run pytest -m mssql --tb=short

VFS_TEST_ORACLE_URL="oracle+oracledb_async://vfs:vfs@localhost:15210/?service_name=FREEPDB1" \
  uv run pytest -m oracle --tb=short

A healthy leg matches the sqlite leg's pass count, with the same capability skips (mkedge is the last classified stub). Keep ?charset=utf8mb4 on the MySQL URL — text bodies depend on it. Engine legs are reentrant: each harness run mints its own table namespace (vfs_<hex>), so concurrent runs against one engine — two terminals, parallel review agents on a shared stack — never tear each other down. A crashed run's leftover vfs_* tables are residue on an ephemeral-data stack; compose down clears them. Report failures as findings against the code, not the harness: a leg that fails on a real engine while sqlite passes is exactly the signal this setup exists to produce (that is how the InnoDB index-cap defect was caught).

Run the legs concurrently, and the sqlite CI leg beside them

Each run mints its own table namespace, so the four legs never collide, and each container is its own server — run them at the same time, not one after another (Clay, 2026-08-26): wall time is the slowest leg (~2.5 min, Oracle) instead of the sum (~7 min). The sqlite CI leg (scripts/ci.sh 3.13) is CPU-bound in one process while the legs mostly wait on their databases, so it runs alongside them.

( VFS_TEST_POSTGRES_URL=... uv run pytest -m postgres -q > leg_postgres.log 2>&1 ) &
( VFS_TEST_MYSQL_URL=...    uv run pytest -m mysql    -q > leg_mysql.log    2>&1 ) &
( VFS_TEST_MSSQL_URL=...    uv run pytest -m mssql    -q > leg_mssql.log    2>&1 ) &
( VFS_TEST_ORACLE_URL=...   uv run pytest -m oracle   -q > leg_oracle.log   2>&1 ) &
( scripts/ci.sh 3.13 > leg_ci.log 2>&1 ) &
wait; tail -n 1 leg_*.log

While iterating on a fix, run only the slice that touches it (-k "<names>") on the engine that failed — seconds, not minutes — and run the whole legs once, at the gate. Benchmarks are the exception: a timing run wants a quiet machine, so it runs alone, after the legs, and uses a smaller sample on the server engines (1,000 linux files; 4,000 on sqlite — the bulk-insert benchmark's defaults) because its job is the ratio between shapes, not the seconds.

4. Tear down

Plain down skips services behind profiles — name the profiles — and always pass -t 60: MSSQL runs amd64-under-Rosetta and routinely outlives compose's default 10 s grace, so a bare down SIGKILLs it (exit 137), which is what has corrupted the daemon's container store in the past (see the phantom-record note below):

docker compose -f docker/compose.test.yml --profile mysql --profile mssql --profile oracle down -t 60

If this session used a fresh -p vfs-test-<letter> project (the phantom-record workaround below), tear that project down too, with the same profile flags and grace — it is a separate compose project and the default down never touches it:

docker compose -p vfs-test-<letter> -f docker/compose.test.yml --profile mssql down -t 60

Data is tmpfs/ephemeral; nothing persists. Verify with docker ps -a --format '{{.Names}} {{.Status}}' — expect zero rows, not just zero running: an Exited (137) remnant is the phantom-record precursor and must not be left behind. One exception: a known, already-diagnosed phantom record (below) will still be listed until its removal step actually runs — surface it in the report every session it survives; never silently ignore it.

Phantom container records (seen 2026-07-24; workaround re-needed 2026-07-25)

Symptom: docker ps -a lists an exited vfs-test container that docker rm -f, docker system prune, and daemon restarts all bounce off ("No such container" / "no such object") — and docker compose up for that service fails trying to start the dead record. Recovery, in order:

1. Unblock the run now: start the affected service under a fresh project name — same image, same host port, tests unaffected: docker compose -p vfs-test-<letter> -f docker/compose.test.yml up -d --wait mssql (tear it down later with the same -p).
2. Actually remove the records — in the same session, not later. The record survives daemon restarts and sessions: until this step runs, step 1's -p workaround recurs every session (proven 2026-07-25 — the 2026-07-24 phantom was still there 36 hours later). It reaches inside the Docker Desktop VM with a privileged container, which the agent's permission mode may refuse — in that case hand the user the exact commands to run themselves (the ! prefix runs them in-session). List the records via docker run --rm --privileged --pid=host alpine nsenter -t 1 -m -u -n -i sh -c 'ls /var/lib/docker/containers' (the phantom's full id is also printed by the failing compose up), then rm -rf exactly the phantom-id directories (never the helper's own fresh id): docker run --rm --privileged --pid=host alpine nsenter -t 1 -m -u -n -i sh -c 'rm -rf /var/lib/docker/containers/<phantom-id>', then restart Docker Desktop and confirm docker ps -a is empty.

5. Quit Docker Desktop

osascript -e 'quit app "Docker"'        # stops the app and its Linux VM (the heavy part)
pkill -f "com.docker.backend" || true   # its "run in background" helpers (~400 MB) linger otherwise

Only after teardown succeeded — quitting the app kills the daemon out from under any still-running container. The quit can take >30 s while the VM winds down; docker info failing is the "VM is gone" signal. com.docker.vmnetd remaining is normal — that is a system-wide privileged launchd helper, not part of the session; leave it alone.