SQLAlchemy¶
SQLAlchemy is how the system reads a database it has never seen before. It is the factual layer everything else in Schema Adaptation builds on: structure comes from SQLAlchemy’s reflection, not from guesswork.
Core and the Inspector, not the ORM¶
The analyzer, src/test_data_workbench/adaptation/schema_analyzer.py, uses
SQLAlchemy Core and its reflection Inspector, not the ORM. This is the
correct layer for the job: the ORM maps known Python classes onto tables you
declare ahead of time, and this tool’s entire premise is that it does not know
the schema ahead of time. Reflection is the mechanism built for exactly that:
read the structure that is already there, at runtime, without a mapped model
in sight.
engine = sa.create_engine(connection_string, pool_pre_ping=True)
inspector = inspect(engine)
db_name = engine.url.database or "unknown"
table_names = inspector.get_table_names()
pool_pre_ping=True is worth calling out: it makes the engine test each
pooled connection with a lightweight ping before handing it out, rather than
handing back a connection that silently died since it was last used. For a
tool whose first move against an arbitrary database is “connect and reflect,”
failing fast on a stale connection matters more than the small per-checkout
cost.
What gets read per table¶
_analyze_table calls four Inspector methods per table, each mapped
onto the system’s own Schema Adaptation model:
Inspector call |
What it becomes |
|---|---|
|
One |
|
Marks the matching columns with |
|
Marks the matching columns with |
|
The starting table list, capped at 20 tables per analysis run. |
None of this issues a SELECT against a single row. It is pure catalog
reflection, which is exactly why it is safe to run unconditionally, even in
Privacy and Data Access’s metadata-only mode.
Metadata-only mode is an SQLAlchemy usage boundary¶
--metadata-only (see CLI Reference and
Privacy and Data Access) is implemented as a straightforward branch: when
set, the analyzer simply never calls the two methods that issue row-reading
SQL, _get_sample_values and _estimate_row_count:
query = text(f'SELECT DISTINCT "{column_name}" FROM "{table_name}" LIMIT {limit}')
...
query = text(f'SELECT COUNT(*) FROM "{table_name}" LIMIT 1000')
Both use SQLAlchemy’s text() for a handful of small, deliberately simple
queries, rather than building them through Core’s expression language.
That is a reasonable trade for this codebase: the queries are trivial
(distinct sample, bounded count), and quoting the identifier keeps them
portable enough across the databases the tool targets without pulling in
Core’s fuller query-building machinery for something this small. Guarding
these two call sites is the entire metadata-only guarantee; there is no
separate “read-only mode” flag deeper in SQLAlchemy being toggled. The test
that backs the guarantee (see Testing and Quality) captures every SQL
statement executed during a metadata-only run and asserts that none of them
touch table data.
Engine lifetime¶
Each call to analyze_production_schema creates its own engine and does not
retain it; there is no long-lived connection pool shared across analyses.
For a CLI tool that runs one analysis and exits, or an API request that does
the same, this is the appropriate lifetime, an engine per operation rather
than a global one the process must manage. The one exception is
Testing and Quality’s cross-process determinism concerns, which are
about Faker and random, not about SQLAlchemy state.
What SQLAlchemy is not asked to do here¶
The generator factory does not use SQLAlchemy at all: once a Table and its
Column objects exist, everything downstream, entity classification,
generator code emission, is pure Python operating on the system’s own
dataclasses. SQLAlchemy’s job ends at reflection.