SQLAlchemy Query Correctness Work Tightens ORM and Dialect SQL

SQLAlchemy had a small but useful week for data engineering code that builds SQL through Python instead of string templates. The recent work is mostly about clearer ORM errors, safer dialect rendering, and documentation that reduces bad assumptions in concurrent SQLite tests and jobs.

The most visible change is the aliased change, which handles aliased() calls against select() and union() constructs through normal coercion rules. Before this, the failure mode could be an obscure missing .mapper attribute. That is not the error you want while debugging a query factory inside an ETL framework.

The implementation touches lib/sqlalchemy/orm/util.py and adds direct coverage in test/orm/test_utils.py. The behavior is also version aware. SQLAlchemy 2.1 raises and points users toward .subquery(). SQLAlchemy 2.0 emits a deprecation warning while coercing the construct into a subquery.

For pipeline code, that matters because generated queries tend to hide the call site. A mapper error can send you toward model setup, while the actual issue is a selectable being passed where a FROM object is expected. This change makes the fix more mechanical: create the subquery explicitly, then alias that.

The migration note on subqueries sits in the same theme. It is documentation rather than runtime behavior, but it lowers upgrade risk for teams moving older query construction patterns into current SQLAlchemy.

The PostgreSQL WITH options work is the kind of dialect detail that matters in data systems because DDL often sits inside migration runners, warehouse setup jobs, and integration tests. The activity mentions proper bool and None rendering, plus CreateView support.

The likely center of that work is lib/sqlalchemy/dialects/postgresql/base.py, with shared compiler behavior in lib/sqlalchemy/sql/compiler.py. That pairing is important. Dialect support should not live only as a special case if the compiler layer also needs to understand the shape of the generated object.

There is a practical point here for operators. If you generate views or table options from config, bool and null rendering bugs can produce SQL that is syntactically close but semantically wrong. Those are expensive failures because they often show up during deploy, not during local model tests.

The collation rendering fix moves string type collations through the identifier preparer. That sounds narrow, but data systems often carry names from source systems that were not designed with portable SQL identifiers in mind.

The changed files include lib/sqlalchemy/sql/sqltypes.py and coverage in test/sql/test_types.py. This is the right level for the fix. Collation is type metadata, but the final SQL still needs dialect aware quoting rules.

Another small correctness patch is the Numeric result processing change, which uses _effective_decimal_return_scale in Numeric.result_processor. Decimal scale details are easy to ignore until an aggregation result changes shape between database drivers. ETL code that compares extracted decimal values, serializes them to JSON, or writes them into analytical stores benefits from boring consistency here.

None of this is a broad type system rewrite. It is the maintenance work that keeps generated SQL and returned Python values close to what users asked for. That is more useful than a feature flag when you are moving millions of records through code paths that only fail on one backend.

The SQLite in memory documentation rewrite is documentation, but it deserves attention. SQLite in memory databases are common in tests, examples, and small batch jobs. They are also easy to misunderstand once threads, async wrappers, and connection pools enter the picture.

The top changed files include lib/sqlalchemy/dialects/sqlite/pysqlite.py and lib/sqlalchemy/dialects/sqlite/aiosqlite.py. That suggests the docs are close to the dialect behavior rather than isolated in a generic guide.

For data engineering teams, the risk is false confidence. A local test suite using an in memory SQLite database can pass while the production path uses a pooled PostgreSQL or MySQL connection. Clearer concurrency documentation does not remove that gap, but it makes the gap visible. That is a good outcome for test design.

Scan query builder code for aliased(select(...)) and aliased(union(...)). The runtime direction is clear: make the subquery step explicit before aliasing.

If your migrations generate PostgreSQL views or options from config, add regression tests around bool and null values. This is especially useful where config values flow from YAML or environment variables.

For test suites that use SQLite in memory databases, reread the current dialect guidance before treating concurrency failures as application bugs. The recent activity is a reminder that database test doubles have behavior, not just convenience.