README dbmapper

Read-only metadata extraction for Windows. db-extract reads the catalogues of SQL Server, PostgreSQL, Oracle and SQLite databases and writes one versioned JSON document per target in the Metadata Contract format: schemas, tables, columns and types, keys, constraints, indexes, views, routines, triggers, sequences, synonyms, user-defined types, partitioning, policies, dependencies, descriptions and extended properties, and optionally privileges. Implementation and verification status are recorded in VALIDATION.md. Metadata Contract 1.0 is the initial, unpublished contract. Linux and macOS are future phases.

Install

Python 3.14 or later is required. Install the extras for the databases you use:

python -m pip install "dbmapper[sqlite,mssql,postgresql,oracle]"

Core installs Pydantic, Click and tqdm. Core, dbmapper.contract, dbmapper.extract and db-extract --help work without database extras. SQLAlchemy and keyring are loaded only for selected targets. SQLite has no keyring dependency. The first release includes extraction and the contract only.

Native prerequisites:

Extra	Native prerequisite	Connection method
sqlite	Python's SQLite library, version 3.37+	Existing database file
mssql	Microsoft ODBC Driver 17 or 18 for SQL Server, same architecture as Python	Host/port, host/instance, or configured ODBC DSN
postgresql	None beyond the binary psycopg wheel	Host and database
oracle	Thin: none; thick: compatible Oracle Client/Instant Client libraries discoverable through PATH or oracle_client_lib_dir	Host with service name or SID, or TNS alias

When a matching binary wheel is unavailable, native driver builds also require Visual C++ Build Tools and a full Python installation with development headers. That build path was not available for the final Python 3.15 compatibility run.

The configured ODBC driver must be installed; options such as Encrypt=strict also require an ODBC driver/server combination supporting that option. Oracle Client mode and network configuration persist for the process lifetime. Sequential API calls may reuse them but cannot switch to an incompatible mode. The API does not support concurrent calls in one process.

Configure

Copy dbmapper.example.toml to dbmapper.toml and replace the example targets. Every target requires a dialect. Names are portable ASCII identifiers (1–64 characters, beginning with a letter or digit, followed by letters, digits, underscores or hyphens), unique without regard to ASCII case, excluding Windows device names. Output is <name>.json. No URL or password field is accepted.

Connection combinations:

• SQL Server: host, database, odbc_driver, optional port or instance; alternatively dsn, with optional database. Use username for password authentication or trusted_connection=true without a username for Windows authentication. DSN cannot be combined with host, port, instance or driver.
• PostgreSQL: host, database, username, optional port.
• Oracle: username and either host with exactly one of service_name or sid (optional port), or tns_alias alone. Top-level oracle_config_dir selects the directory containing tnsnames.ora. Easy Connect strings and full descriptors are not configuration inputs.
• SQLite: path to an existing regular database file. It accepts no credentials, connection timeout or driver options. Relative paths resolve against the TOML file; directly constructed TargetConfig paths resolve against cwd.

schemas is an optional exact, case-sensitive allowlist; exclude_schemas subtracts from it or from all visible schemas. Empty schemas=[] selects none. System schemas/objects are excluded. An unavailable requested schema is recorded as an error. include_row_counts=false skips statistics retrieval entirely. Counts are estimates from catalogue statistics; extraction never runs ANALYZE, COUNT(*) or application-table scans. SQLite statistics can be absent.

include_privileges (default false) adds the privileges facet: object, column and schema grants, including SQL Server DENY. On PostgreSQL an object with no access list reports its effective default (acldefault), including the owner's privileges. It is opt-in because it exposes role and user names, and because complete privileges can need broader catalogue access (see Privileges below). include_connected_user (default true) records the session user as extraction.connected_user; false records null and skips the query. Neither option is accepted on SQLite targets.

connect_timeout_seconds defaults to 30, query_timeout_seconds to 120; each is a positive integer no greater than 3600. Credentials have a separate 30-second lookup deadline. Booleans are not integers in configuration.

The optional [targets.options] table accepts only:

Dialect	Options and values
SQL Server	Encrypt: yes/no/mandatory/optional/strict; TrustServerCertificate: yes/no; HostNameInCertificate: nonempty string; ApplicationIntent: ReadOnly/ReadWrite; MultiSubnetFailover: yes/no
PostgreSQL	sslmode: disable/allow/prefer/require/verify-ca/verify-full; sslrootcert: certificate path or system; application_name: nonempty string; target_session_attrs: any/read-write/read-only/primary/standby/prefer-standby
Oracle	protocol: tcp/tcps; ssl_server_dn_match: boolean; ssl_server_cert_dn: nonempty string

SQL Server option names are case-insensitive, without duplicate aliases; PostgreSQL and Oracle names are exact. Enumerated values are case-insensitive. Authentication, secret-bearing, redirecting and timeout options are excluded. Oracle wallets are excluded, including certificate wallets; tcps requires a driver/client combination capable of using system certificate trust. TLS option coverage remains a release gate in VALIDATION.md.

Credentials and scheduling

Passwords come only from keyring. Store them separately, before running:

keyring set "localhost:5432" "metadata_reader"
db-extract --config dbmapper.toml --target local_postgresql

The service key is lowercased host:port, or lowercased host when no port is configured; no default port is substituted. SQL Server instances use host\instance, DSNs use the DSN exactly, and Oracle aliases use the lowercase alias. Username is the configured username. Database/service/SID do not affect the lookup. keyring_service and keyring_username override either component. SQL Server Windows authentication and SQLite never look up a password.

Production backends are Windows Credential Locker, macOS Keychain and Linux Secret Service; phase one runs on Windows only. Every member of a chained backend must be supported. A locked, denied, missing or unsupported backend fails pre-flight. No prompt, environment-variable password or file fallback is used. Schedule the task as the Windows identity that owns the credentials (or has SQL Server access for integrated authentication).

Credential lookups use daemon threads with a deadline. A lookup that times out is abandoned and not joined. In an API host its thread may remain until the backend returns, but it cannot keep the CLI process alive. Each invocation has its own credential cache. Test-only DBMAPPER_TEST_KEYRING=1 permits keyrings.alt and logs that the store is not an operating-system store; this setting is unsupported in production. Integration tests use temporary stores.

For the later Linux phase, a headless session needs an already unlocked Secret Service provider. For macOS, Keychain access must be granted interactively once before scheduling. These platform behaviours have not been verified here.

CLI

db-extract --help
db-extract --config dbmapper.toml --output-dir ./IO/json
db-extract --target local_sqlserver --database another_database --force
db-extract --config dbmapper.toml --compact

--target selects one exact configured name. --database requires --target and supports SQL Server/PostgreSQL only. --force enables atomic replacement. --compact writes the compact layout instead of the indented one; it changes only whitespace, never content or order. --log-level accepts DEBUG, INFO, WARNING, ERROR and CRITICAL; --verbose means DEBUG and conflicts with an explicitly selected non-DEBUG level.

The CLI appends to dbmapper.log in cwd. Progress and errors go to stderr, including plain completion lines when redirected. Stdout contains one final summary. Raw driver exceptions, credentials, parameter values, retrieved source and descriptions are not copied into diagnostics. DEBUG includes catalogue SQL templates and timings. Logging initialization failure is a pre-flight error; later logging failures mark the run unsuccessful without discarding output.

Exit code	Meaning
0	All targets committed without metadata errors
1	Partial/failed target, logging failure or unexpected internal fault
2	Configuration or pre-flight failure; no connection opened or final output changed
130	Operator cancellation after cleanup; earlier commits remain

All content-only configuration checks apply to every target. Environmental checks apply only to selected targets. Independent pre-flight problems are aggregated before any database connects.

Python API and contract

from dbmapper import extract
from dbmapper.contract import load_metadata

result = extract.run(
    "dbmapper.toml", target="local_sqlite", output_dir="IO/json", compact=False
)
metadata = extract.extract_target(extract.TargetConfig(
    name="local", dialect="sqlite", path="data/example.db"
))
compatible = load_metadata(metadata.model_dump_json())

run returns an immutable RunResult with ordered TargetResult records: name, complete/partial/failed status, absolute output path or None, and safe diagnostics. Its exit_code summarizes success. Expected operational failures are results; extract_target raises TargetError for fatal target failures. Both raise PreflightError for local problems. Exceptions expose respectively target_name/errors and problems. Cancellation and unexpected programming faults propagate after cleanup. Library calls add no logging handlers and extract_target writes no files or progress output.

configure_oracle(thick_mode=False, client_lib_dir=None, config_dir=None) sets process-wide Oracle configuration before Oracle use. Relative API paths resolve against cwd. Configure it once; incompatible subsequent changes fail pre-flight.

Models live in dbmapper.contract. The generated exact-version schema is shipped as schemas/dbmapper-contract-1.0.schema.json in that package and can be loaded with importlib.resources.files("dbmapper.contract"). Producer models forbid unknown fields and apply strict typing and cross-field validation. load_metadata accepts JSON text/UTF-8 bytes/mappings, rejects duplicate JSON members, validates known fields and ignores fields introduced by compatible 1.x minor versions. It preserves the supplied minor version. Unsupported majors raise UnsupportedContractVersionError; invalid content raises ContractValidationError. Integers, including sequence limits, are exact and may exceed 2^53; consumers must use arbitrary-precision parsing.

The JSON Schema is the contract's normative form. The models implement it, plus the cross-field invariants it cannot express, which its root $comment lists. A conformance corpus of valid and invalid documents, each with the expected outcome for the schema, the producer models and the loader, ships as the conformance/ directory of dbmapper.contract.

Facets, unsupported fields and document size

extraction.facets lists the optional groups of fields that were extracted:

Facet	Fields	Listed when
descriptions	every description	always, for the built-in providers
extended_properties	every properties map	always, for the built-in providers
dependencies	every references list	always, for the built-in providers
row_counts	row_count, row_count_kind, row_count_as_of	include_row_counts = true (the default)
privileges	every privileges list	include_privileges = true

When a facet is absent, its query is not issued and its fields hold their defaults (None, [], {}). Read them as "not extracted", never as facts.

extraction.unsupported lists the field paths the source DBMS cannot populate, for example schemas.synonyms on PostgreSQL. It depends on the dialect and the server version, and follows the capability matrix in requirements/01_extract/extraction_coverage.md §15. Values at or below an unsupported path also hold their defaults. An empty collection is therefore a fact only when its facet is listed, its path is supported and no error covers it.

Document size grows with the number of columns. Measured with SQLite; the server dialects carry similar per-column facts, and routine and view source adds its own length:

Database	Indented	Compact
200 tables, 1,399 columns, with keys, checks, indexes and 20 views	2.1 MB (about 10.5 KB per table)	1.0 MB
1,000 tables of 99 integer columns	82 MB (about 830 bytes per column)	39 MB

The compact layout is about half the size of the indented one. Extraction holds the whole document in memory before writing it.

Partial output, safety and timeouts

Recoverable object failures retain accessible metadata and attach structured errors. Missing required facts omit the affected item instead of inventing defaults. Failed categories are empty with enclosing errors. An unusable session or invalid root fails its target without output. Encrypted/wrapped source is null without error; detectable privilege gaps are errors. Catalogue visibility can hide objects silently, so an error-free document is not proof of sufficient privileges.

Each target is validated/serialized fully, written to a unique same-directory temporary file, flushed/closed, then committed atomically. Without force, Windows rename atomically refuses an existing destination. Force uses atomic replacement. Unsupported filesystem semantics fail the target. Cancellation removes temporary files; forced process termination can leave temporary files but no partially written final JSON. Earlier target commits remain, and failed targets do not return a path to an old file. Power-loss durability is not promised.

SQLite is opened with mode=ro, uri=true and PRAGMA query_only=ON. No attachments, extension loading or immutable mode are used. WAL-mode opening may fail if the WAL/shared-memory state cannot be read or initialized with the available permissions; that is a connection failure. The database and any required -wal/-shm files must be accessible. Filesystem access controls govern SQLite; SQLCipher and other encryption extensions are excluded.

SQL Server requests the ODBC read-only connection attribute; PostgreSQL sets default_transaction_read_only=on; Oracle uses read-only transactions and reestablishes that transaction mode after a recoverable rollback.

Driver	Connection mechanism	Query mechanism and accepted gaps
pyodbc	ODBC login timeout	DBAPI connection query timeout; post-execution fetching and some native cleanup/name-resolution phases may remain unbounded
psycopg	libpq connect_timeout	Server statement_timeout; DNS, native connection/cleanup work and multiple underlying address attempts may exceed a single timeout
oracledb	tcp_connect_timeout, retries disabled	call_timeout per round trip including fetch; this is not a whole-query or authentication-handshake deadline; name resolution, multiple addresses and native cleanup can add time
sqlite3	No connect timeout	Progress handler plus lock-wait timeout; filesystem I/O and work between progress callbacks may block beyond the deadline

There is no whole-run deadline or worker process/thread for database calls. Cancellation takes effect when the current call returns or its driver deadline ends it; cleanup has the same native-driver gaps. There are no automatic application retries. The SQLite broken-view/virtual-table fallback is explicitly allowed to retry individual objects after a failed bulk category.

Verbatim catalogue source and comments may contain sensitive database-authored text. They are preserved in JSON; control access to the output directory.

Privileges and verification

The project requires least-privilege evidence on minimum server versions before release. That matrix is not yet complete. Current controlled fixtures establish:

• SQL Server: Windows administrator access in an isolated test database. Start with CONNECT and database/schema/object VIEW DEFINITION for a reader; the minimum restricted-account grants are not yet verified. With include_privileges, sys.database_permissions shows only the permissions the connecting principal can see.
• PostgreSQL: a login with CONNECT (public default) and USAGE on the fixture schema can read the tested metadata, including privileges and policies, without application-table SELECT grants. Exact fixture grants are in tests/test_local_postgresql.py.
• Oracle: the isolated fixture owner has CREATE SESSION and owns its objects. Complete privileges need SELECT on SYS.DBA_TAB_PRIVS, SYS.DBA_COL_PRIVS and, on 23ai, SYS.DBA_SCHEMA_PRIVS. Without them extraction uses the ALL_ views, which show only grants involving the connecting user or its roles, records a PRIVILEGE_GAP on every schema and exits with code 1. Cross-schema least-privilege grants/source visibility are not established; SELECT_CATALOG_ROLE/SELECT ANY DICTIONARY are candidates, not verified minimum grants.
• SQLite: read access to the existing file and relevant WAL sidecars.

See VALIDATION.md for exact versions, acceptance coverage and remaining gates.

The recorded Windows/Python 3.14.7 server tests used SQL Server 17.0.1145.4 (pyodbc 5.3.0, ODBC Driver 18 DLL version 2018.0186.0001.01), PostgreSQL 18.3 (psycopg 3.3.6), and Oracle AI Database 26ai Free 23.26.1.0.0 (oracledb 3.4.2, thin and thick mode with Oracle Client 23.26.1.0.0). SQLite 3.37.0 and 3.53.1 passed the default suite on Python 3.14.7; final Python 3.15.0 passed with SQLite 3.53.4. SQLAlchemy was 2.1.6 and Pydantic 2.14.1. These results establish the tested fixtures; the complete supported-version/privilege matrix remains outstanding, including SQL Server/Oracle native drivers on Python 3.15.

Development

uv sync --all-extras
uv run ruff check src tests tools
uv run ruff format --check src tests tools
uv run mypy src
uv run pytest
uv run python tools/generate_schema.py --check
uv run python tools/build_conformance_corpus.py --check
uv run python tools/build_requirements_index.py --check
uv build

The default suite includes real SQLite fixtures and a 100,000-object correctness and query-count check. Canned mapping tests complement controlled server tests; they do not replace live SQL evidence. Local server fixtures are opt-in because they create isolated databases/accounts and require installed Windows services:

$env:DBMAPPER_LOCAL_MSSQL="1"
$env:DBMAPPER_LOCAL_POSTGRESQL="1"
$env:DBMAPPER_LOCAL_ORACLE="1"
uv run pytest -m local_db

SQL Server uses localhost Windows authentication; Oracle uses local OS SYSDBA only to create/drop a temporary user in FREEPDB1, while extraction itself uses the generated test password through a temporary keyring. PostgreSQL starts and stops a disposable localhost cluster using the installed server binaries (DBMAPPER_POSTGRES_BIN overrides their directory). Set DBMAPPER_ORACLE_THICK=1 in a separate test process to test Oracle thick mode. Fixtures do not inspect application databases or the operator's stored passwords. Containerized minimum-version CI and the full release matrix remain outstanding.

Bulk access uses fixed catalogue facets. SQL Server and SQLite issue no per-object catalogue statements during successful extraction. With SQLAlchemy 2.1.6, PostgreSQL constraints/indexes use batches of 3,000 and Oracle reflection uses batches of 500. Each such facet has one catalogue execution per batch plus fixed enumeration/setup queries. The SQLite fallback above is the only per-object-query exception. Test and record batch boundaries when changing SQLAlchemy versions; the complete live boundary matrix is still a release gate.