SQLAlchemy

SQLAlchemy#

SQLAlchemy is how Test Data Workbench finds out what is actually in a database it has never seen before. Nothing else in the pipeline has an opinion about schema until SQLAlchemy’s reflection reports one back.

Why SQLAlchemy#

The tool’s entire premise is that it does not know your schema ahead of time: point it at a connection string, and it has to discover tables, columns, types, primary keys, and foreign keys on its own, across whatever database engine is on the other end. Hand writing that discovery per database dialect is not a reasonable ask, and getting it wrong quietly (missing a foreign key, misreading a type) would corrupt everything downstream that depends on the discovery being correct.

SQLAlchemy solves exactly this. Its Core layer and reflection Inspector give one consistent API for reading a database’s own structure back, whether that database is the SQLite file tdw demo builds for you or a production PostgreSQL instance. It is listed as a direct dependency in pyproject.toml (sqlalchemy>=2.0.23), not an optional one, and it is imported in exactly one file in the whole project, src/test_data_workbench/adaptation/schema_analyzer.py. Once that file has done its job, nothing downstream needs SQLAlchemy again.

The idea underneath#

Relational data and Python objects do not fit together naturally. The mismatch is usually described along three axes:

  • Identity. A row’s identity in a relational database is its primary key value, nothing more. Two separate queries for the same row can hand back two distinct Python objects with no relationship to each other, unless something keeps track of which object already represents which row.

  • Navigation. A relational database has no pointers. Getting from an order to the user who placed it means a join or a second query keyed by a foreign key value, not attribute access. Anything that makes order.user behave like a normal object reference is doing work the relational model does not provide for free.

  • Lifetime. An in-memory object’s lifetime is scoped to the process that created it. A row persists independently of any object representing it, and something else in the source database can change the row while your object still holds a value that is now stale.

Martin Fowler names two patterns in Patterns of Enterprise Application Architecture that address these gaps, and SQLAlchemy’s ORM implements both: the Identity Map, which keeps one Python object per row identity within a session so that two queries for the same primary key return the same object, and the Unit of Work, which tracks every pending insert, update, and delete and flushes them together as one coordinated batch rather than as they happen.

This project uses neither. It never opens a Session, never declares a mapped class, and never writes a row back through SQLAlchemy at all, so there is no object identity to keep consistent and no pending write to batch. Once Inspector methods return, the result is translated straight into this project’s own plain dataclasses, Table, Column, and Relationship in src/test_data_workbench/core/models.py, which know nothing about SQLAlchemy.

What this project actually leans on is the other half of the idea: reflection, treating a database’s own catalog (the system tables or views where it describes its own structure, such as PostgreSQL’s information_schema and pg_catalog, or SQLite’s sqlite_master) as a machine-readable source of truth, rather than declaring a schema in Python and hoping it matches reality. This project’s own test suite makes that catalog boundary concrete: tests/test_metadata_only.py captures every SQL statement issued during an analysis and explicitly excludes lookups against sqlite_master and sqlite_temp_master from the “did this read table data” check, because those are catalog reads describing structure, not row reads.

How it fits this project#

SQLAlchemy’s job begins and ends inside SchemaAnalyzer.analyze_production_schema, in src/test_data_workbench/adaptation/schema_analyzer.py. That one method builds the engine, builds the inspector, and turns the result into a SchemaInfo made entirely of this project’s own dataclasses. Everything downstream, the GeneratorFactory in src/test_data_workbench/adaptation/generator_factory.py, entity classification, dependency ordering, and code emission, operates purely on those dataclasses and imports no SQLAlchemy at all. Structure comes in from SQLAlchemy at the front of the pipeline and nothing further back ever touches it again.

Core and the Inspector, not the ORM#

The analyzer uses SQLAlchemy Core and its reflection Inspector, never the ORM:

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()

There is no declarative_base(), no mapped class, no Session anywhere in this file, which is the correct choice for what it is doing: the ORM maps known Python classes onto tables declared ahead of time, and this project has nothing to declare ahead of time. Every Inspector call returns a plain dict or list of dicts, which _analyze_table and _detect_relationships translate directly into this project’s own dataclasses:

Inspector call

What it becomes

inspector.get_table_names()

The starting table list, capped at the first 20 entries per run.

inspector.get_columns(table_name)

One Column per entry: name, declared type (stringified), nullability, default.

inspector.get_pk_constraint(table_name)

Marks the matching columns with ConstraintType.PRIMARY_KEY.

inspector.get_foreign_keys(table_name)

Marks the matching columns with ConstraintType.FOREIGN_KEY, and separately feeds _detect_relationships, which turns each foreign key into an explicit Relationship(from_table, to_table, from_column, to_column).

Connection pooling and pool_pre_ping#

create_engine(connection_string, pool_pre_ping=True) asks SQLAlchemy to test every pooled connection with a lightweight probe immediately before handing it to the caller, rather than handing back whatever connection is next in the pool and finding out it is dead only when a real query fails on it.

The failure mode this guards against is ordinary and easy to hit: a connection can go stale while sitting idle in the pool, because the database restarted, a firewall or load balancer closed an idle connection underneath it, or the server enforced its own idle timeout. Without pool_pre_ping, the first sign of trouble is a driver-level error such as “server closed the connection unexpectedly” surfacing on whatever query happened to draw the dead connection, which looks like a query problem when it is really a pool-hygiene problem. With it, SQLAlchemy discards the dead connection and opens a fresh one transparently, at the cost of one small probe per checkout.

For a tool whose first move against an unfamiliar database is “connect and reflect,” that small cost is worth it: a connection string that is wrong should fail loudly and immediately, but a connection that was fine a moment ago and has since gone stale should not be mistaken for the same thing.

Metadata-only mode: the PII heuristic still runs#

metadata_only=True (exposed as tdw analyze --metadata-only and tdw deploy --metadata-only, see CLI Reference and Privacy and Data Access) is implemented as guards around the three call sites in the analyzer that issue a SELECT against table data: _get_sample_values and _estimate_row_count, both skipped inside _analyze_table, plus _detect_business_rules, which returns immediately instead of ever reaching its own query:

query = text(f'SELECT DISTINCT "{column_name}" FROM "{table_name}" LIMIT {limit}')
...
query = text(f'SELECT COUNT(*) FROM "{table_name}" LIMIT 1000')

When metadata_only is set, _analyze_table never calls either helper: sample_values stays an empty list and row_count is set to None, deliberately distinct from 0, since None means “not counted” and 0 would claim to know the table is empty. _detect_business_rules is skipped in the same mode for the same reason: its rule detection samples a dataframe of up to 100 rows per table, so there is nothing left for it to do once row reads are off the table. This is not just a docstring’s promise: tests/test_metadata_only.py attaches a SQLAlchemy before_cursor_execute event listener across every engine created during a run and asserts that none of the captured statements are SELECT or COUNT statements touching the analyzed tables.

PII column flagging is different. _detect_pii_columns calls classify_column_pii(column_name) for every column already collected, and that function takes a bare string and matches it against PII_NAME_PATTERNS with a regular expression. It never receives the engine, the inspector, or a connection, so it has no SQL to skip in the first place. It runs identically whether metadata_only is True or False, which is why the flagging survives metadata-only mode intact: it was never something metadata-only mode needed to guard against.

Foreign keys as a dependency graph#

Foreign keys discovered during reflection describe a directed graph: table A depends on table B if a row in A references a row in B. Generating data in a valid order (parents before the children that reference them) is a correctness property, not a nicety, and generator_factory.py implements it twice, once in _sort_by_dependencies (used to order the tables that get generator code emitted for them) and once in _calculate_dependency_order (used to order the actual generation run via RelationshipManager.dependency_order):

def visit(table: str):
    if table in visited:
        return
    if table in in_stack:
        return  # Cycle detected - break it
    in_stack.add(table)
    for dep in deps.get(table, []):
        visit(dep)
    in_stack.discard(table)
    visited.add(table)
    order.append(table)

Both are the same depth-first topological sort, and the cycle handling is the interesting part: in_stack marks vertices that are currently on the recursion stack, the classic “grey” state in a depth-first search. Revisiting a vertex that is still grey means the traversal has found a back edge, which is exactly what a cycle in the graph looks like, and the code breaks it by returning immediately instead of recursing further.

A self-referential foreign key, such as categories.parent_id -> categories.id (exercised directly in tests/test_postgres_integration.py), is the simplest possible case of this: it is a cycle of length one, caught by the same table in in_stack check on the very first recursive call. The ordering layer does not treat it as a special case; it just drops the back edge like any other and moves on, leaving the actual self-reference handling (drawing a value already generated earlier in the same batch, in _self_reference_id) to a different layer entirely.

Drivers and the DB-API layer: psycopg2#

SQLAlchemy Core builds SQL, manages the connection pool, and coordinates transactions, but it does not speak PostgreSQL’s or SQLite’s wire protocol itself. That work is delegated to a driver module, and every driver SQLAlchemy talks to implements PEP 249, the Python Database API Specification: a connect() function, a Connection object, a Cursor with execute() and the fetch methods.

For a postgresql:// connection string, SQLAlchemy’s PostgreSQL dialect defaults to psycopg2 as that driver; written out fully, the same URL reads postgresql+psycopg2://, exactly the form tests/test_postgres_integration.py uses. Nothing in src/ imports psycopg2 directly, and it does not need to: SQLAlchemy loads it by name when a Postgres engine is created, the same way any DBAPI driver is loaded for its matching dialect. psycopg2-binary is listed in pyproject.toml only under [project.optional-dependencies] as the postgres extra, not in the base dependencies list, so a plain install gets you SQLAlchemy and SQLite support (sqlite3 ships in the Python standard library, no driver install needed there) but not Postgres support until you install test-data-workbench[postgres] as well.

Sharp edges and limits#

A genuine cycle has no correct order, and the code does not pretend otherwise. The cycle-breaking in _sort_by_dependencies and _calculate_dependency_order guarantees termination and a defined order, not a correct one. A self-referential foreign key degrades gracefully because there was never a meaningful “before” for a table relative to itself. A genuine cycle across two or more tables (A references B, B references A) is a different case wearing the same code path: by definition no topological order can put A before B and B before A at the same time, so whichever order the traversal happens to emit will leave one of those foreign keys pointing at a row that does not exist yet when its generator runs. This is deliberate graceful degradation, not a bug fix waiting to happen; real production schemas do contain self-referential and mutual foreign keys, and refusing to generate anything for them would be worse than a best-effort order. The two shapes are simply not distinguished from each other in the result.

Per-table reflection failures are swallowed, not tracked. _analyze_table wraps its entire body in a bare except Exception: return None, and _detect_relationships does the same with except Exception: continue. A table that fails to reflect (an exotic column type, a permissions error on one table but not others) simply disappears from the result, and analysis_metadata never records which table it was or why. The only visible symptom is analyzed_tables being lower than total_tables, with no name attached. This is a real asymmetry with the rest of the pipeline: the Defensive Manager that runs the generator fallback ladder downstream tracks its errors rather than discarding them (see Architecture); the reflection layer does not offer the same visibility.

Reflection is silently capped at 20 tables. table_names[:20] (the comment reads “Limit for rapid analysis”) means a database with 30 tables reflects only the first 20 that get_table_names() returns, in whatever order the database or driver happens to return them, and nothing in the result flags that tables were left out.

The two topological sorts are independent copies, not one shared implementation. _sort_by_dependencies and _calculate_dependency_order reimplement the identical visited/in-stack depth-first search over two different input shapes. A correction to the cycle-handling logic in one does not automatically reach the other.

Nothing calls ``.dispose()`` on the engine. Each call to analyze_production_schema builds its own engine with sa.create_engine and never explicitly disposes it; only the connections opened inside with engine.connect() as conn: blocks are closed on exit. For a one-shot CLI invocation this is harmless, the process exits and takes the engine with it, but the same analyzer is also reachable through the FastAPI REST API (see Architecture), where a long-lived server process could call it repeatedly and simply accumulate un-disposed engines and pools between calls rather than reusing or closing one.

Postgres support fails late, not at install time. Because psycopg2-binary is an optional extra rather than a base dependency, installing the package without it and then pointing the analyzer at a postgresql:// URL does not fail until create_engine (or the first connection attempt) actually tries to load the driver, with a ModuleNotFoundError that gives no hint that a pip install test-data-workbench[postgres] would have fixed it in advance.

Learn more resources#

Official documentation#

Tutorials and blogs#

  • Martin Fowler, Identity Map: the pattern from Patterns of Enterprise Application Architecture that SQLAlchemy’s Session implements and this project has no use for, since it never maps a row to an object.

  • Martin Fowler, Unit of Work: the companion pattern for batching and flushing writes together, same book, same reason this project does not need it.

  • Talk Python to Me, episode 344, “SQLAlchemy 2.0”: an interview-format walkthrough of what changed in the 2.0 API line this project’s sqlalchemy>=2.0.23 pin targets.

  • SQLAlchemy Library: the project’s own curated index of talks, deep-dives, and community writing, useful once you know which sub-topic you want more of.

Videos#