MSSQL testdb: rootless podman/CLI engine, generated-schema hook, snapshot reset, NAV-style object names #64

Context

I am building a mock of a Dynamics NAV (C/AL) application on a real SQL Server: ~1,950 tables generated from a metadata export (composite primary keys, varbinary keys, names with spaces/$/non-ASCII such as [Company$Detailed Cust_ Ledg_ Entry]), plus ~4,500 views over them. It is exercised by pytest with a reset between tests, and several agents/worktrees work in parallel.

pgdevkit.testdb.mssql looks like the right foundation (container lifecycle, per-worktree database names, orphan cleanup), but I could not use it as is. This issue lists what is missing. Note: I read the code (testdb/mssql/*, testdb/_docker.py, naming.py, mssql_introspect.py) and did not run every path; items marked (unverified) are suspicions to check, not confirmed bugs.

1. Start the SQL Server container without the Docker SDK / as root in rootless podman

• _docker.client() needs a Docker-compatible socket. In rootless Podman environments there is often no podman.sock (no systemd user session) and DOCKER_HOST is unset, so ensure_mssql_container() cannot start the container.
• The SQL Server image also does not start rootless unless the container runs as root: with the default image user, podman run fails with exec container process /opt/mssql/bin/launch_sqlservr.sh: Permission denied. Adding --user 0 fixes it (SQL Server 2022 image; the 2025 default was not tried).

Requested:

• PGDEVKIT_CONTAINER_ENGINE=docker|podman (or auto-detect) that shells out to the CLI when the SDK socket is not reachable.
• PGDEVKIT_TESTDB_MSSQL_CONTAINER_USER (default unset) passed as user= / --user.
• Document the rootless-podman recipe in the README.

2. A schema that is generated by code, and a fast reset

ensure_testdb() builds the database from the database/ script layout. My schema is produced by a Python generator (DDL from metadata). Writing ~1,950 .sql files into the layout works but is awkward, and re-generating them on each metadata change is noisy.

Requested:

• A schema-provider hook: ensure_testdb(..., apply=callable(conn)) (or an entry point / extra directory) that runs after or instead of the script apply, so generated DDL can be used without materialising files.
• A cheap reset primitive for MSSQL: create a template state once and restore it per test. SQL Server Developer edition supports database snapshots (CREATE DATABASE x ... AS SNAPSHOT OF y / RESTORE DATABASE y FROM DATABASE_SNAPSHOT = x), which should take ~100 ms instead of my current "delete all non-empty tables and re-seed" (~1 s per test). API sketch: snapshot_testdb(config, db_name, name) / restore_testdb(config, db_name, name), with snapshot files placed where the container can write them (tmpfs-aware, see PGDEVKIT_TESTDB_MSSQL_TMPFS).

3. MSSQL introspection/compare on NAV-style schemas (unverified)

I would like to use pgdb compare --dialect mssql and pgdb stats to diff the generated schema against a real reporting database. Things worth checking/adding:

• Object names with spaces, $, .→_ replacements and accented characters (bracket quoting everywhere, file names for the database/ layout when a name contains $/spaces/é).
• Views (CREATE VIEW dbo.[Company$Table] AS SELECT * FROM ...) next to tables: are they introspected/compared?
• Performance and memory with ~2k tables and ~4.5k views, and composite PKs including varbinary(900) columns.
• Column types nvarchar(n), decimal(38,20), datetime, uniqueidentifier, tinyint flags and NOT NULL DEFAULT constraints round-trip without false diffs.

4. Per-worktree database names for agent worktrees (unverified)

naming.py derives database names per worktree/branch and cleans orphans — exactly what I need for parallel agent workers (I currently pick a database name per worker by hand through an environment variable). Please confirm this works when worktrees live under a nested directory (e.g. <repo>/.claude/worktrees/<id>) and that clean_testdb(orphaned=True) does not drop a database whose worktree is locked/in use.

Acceptance

1 and 2 are what block me; 3 and 4 are verification requests. Happy to test a branch or send a PR for 1 (CLI engine fallback + container user) if that direction is acceptable.