Release: 2.1.0b1 | Release Date: not released yet

# SQLAlchemy 2.1 Documentation

# 2.1 Changelog

## 2.1.0b1

no release date

### platform

- [platform] [feature]

  Free-threaded Python versions are now supported in wheels released on Pypi. This integrates with overall free-threaded support added as part of #12881 for the 2.0 and 2.1 series, which includes new test suites as well as a few improvements to race conditions observed under freethreading.

  References: #12881

- [platform] [change]

  Updated the setup manifest definition to use PEP 621-compliant pyproject.toml. Also updated the extra install dependency to comply with PEP-685. Thanks for the help of Matt Oberle and KOLANICH on this change.

- [platform] [change]

  The `greenlet` dependency used for asyncio support no longer installs by default. This dependency does not publish wheel files for every architecture and is not needed for applications that aren’t using asyncio features. Use the `sqlalchemy[asyncio]` install target to include this dependency.

  See also

  Asyncio “greenlet” dependency no longer installs by default

  References: #10197

- [platform] [change]

  Python 3.10 or above is now required; support for Python 3.9, 3.8 and 3.7 is dropped as these versions are EOL.

  References: #10357, #12029, #12819

### orm

- [orm] [feature]

  Added new hybrid method `hybrid_property.bulk_dml()` which works in a similar way as `hybrid_property.update_expression()` for bulk ORM operations. A user-defined class method can now populate a bulk insert mapping dictionary using the desired hybrid mechanics. New documentation is added showing how both of these methods can be used including in combination with the new `from_dml_column()` construct.

  See also

  New Hybrid DML hook features

  References: #12496

- [orm] [feature]

  Added `RegistryEvents` event class that allows event listeners to be established on a `registry` object. The new class provides three events: `RegistryEvents.resolve_type_annotation()` which allows customization of type annotation resolution that can supplement or replace the use of the `registry.type_annotation_map` dictionary, including that it can be helpful with custom resolution for complex types such as those of PEP 695, as well as `RegistryEvents.before_configured()` and `RegistryEvents.after_configured()`, which are registry-local forms of the mapper-wide version of these hooks.

  See also

  New RegistryEvents System for ORM Mapping Customization

  References: #9832

- [orm] [feature]

  The `relationship.back_populates` argument to `relationship()` may now be passed as a Python callable, which resolves to either the direct linked ORM attribute, or a string value as before. ORM attributes are also accepted directly by `relationship.back_populates`. This change allows type checkers and IDEs to confirm the argument for `relationship.back_populates` is valid. Thanks to Priyanshu Parikh for the help on suggesting and helping to implement this feature.

  See also

  ORM Relationship allows callable for back_populates

  References: #10050

- [orm] [feature]

  Added support for per-session execution options that are merged into all queries executed within that session. The `Session`, `sessionmaker`, `scoped_session`, `_ext.asyncio.AsyncSession`, and `_ext.asyncio.async_sessionmaker` constructors now accept an `Session.execution_options` parameter that will be applied to all explicit query executions (e.g. using `Session.execute()`, `Session.get()`, `Session.scalars()`) for that session instance.

  References: #12659

- [orm] [feature]

  Added new parameter `composite.return_none_on` to `composite()`, which allows control over if and when this composite attribute should resolve to `None` when queried or retrieved from the object directly. By default, a composite object is always present on the attribute, including for a pending object which is a behavioral change since 2.0. When `composite.return_none_on` is specified, a callable is passed that returns True or False to indicate if the given arguments indicate the composite should be returned as None. This parameter may also be set automatically when ORM Annotated Declarative is used; if the annotation is given as `Mapped[SomeClass|None]`, a `composite.return_none_on` rule is applied that will return `None` if all contained columns are themselves `None`.

  See also

  New rules for None-return for ORM Composites

  References: #12570

- [orm] [feature]

  Session autoflush behavior has been simplified to unconditionally flush the session each time an execution takes place, regardless of whether an ORM statement or Core statement is being executed. This change eliminates the previous conditional logic that only flushed when ORM-related statements were detected, which had become difficult to define clearly with the unified v2 syntax that allows both Core and ORM execution patterns. The change provides more consistent and predictable session behavior across all types of SQL execution.

  See also

  Session autoflush behavior simplified to be unconditional

  References: #9809

- [orm] [usecase]

  Added `DictBundle` as a subclass of `Bundle` that returns `dict` objects.

  References: #12960

- [orm] [usecase]

  Improvements to the use case of using Declarative Dataclass Mapping with intermediary classes that are unmapped. As was the existing behavior, classes can subclass `MappedAsDataclass` alone without a declarative base to act as mixins, or along with a declarative base as well as `__abstract__ = True` to define an abstract base. However, the improved behavior scans ORM attributes like `mapped_column()` in this case to create correct `dataclasses.field()` constructs based on their arguments, allowing for more natural ordering of fields without dataclass errors being thrown. Additionally, added a new `unmapped_dataclass()` decorator function, which may be used to create unmapped mixins in a mapped hierarchy that is using the `mapped_dataclass()` decorator to create mapped dataclasses.

  See also

  Using mixins and abstract superclasses

  References: #12854

- [orm] [usecase]

  The `aliased()` object now emits warnings when an attribute is accessed on an aliased class that cannot be located in the target selectable, for those cases where the `aliased()` is against a different FROM clause than the regular mapped table (such as a subquery). This helps users identify cases where column names don’t match between the aliased class and the underlying selectable. When `aliased.adapt_on_names` is `True`, the warning suggests checking the column name; when `False`, it suggests using the `adapt_on_names` parameter for name-based matching.

  References: #12838

- [orm] [usecase]

  The `Session.flush.objects` parameter is now deprecated.

  References: #10816

- [orm] [usecase]

  Added the utility method `Session.merge_all()` and `Session.delete_all()` that operate on a collection of instances.

  References: #11776

- [orm] [usecase]

  Added default implementations of `ColumnOperators.desc()`, `ColumnOperators.asc()`, `ColumnOperators.nulls_first()`, `ColumnOperators.nulls_last()` to `composite()` attributes, by default applying the modifier to all contained columns. Can be overridden using a custom comparator.

  References: #12769

- [orm] [usecase]

  Added support for using `with_expression()` to populate a `query_expression()` attribute that is also configured as the `polymorphic_on` discriminator column. The ORM now detects when a query expression column is serving as the polymorphic discriminator and updates it to use the column provided via `with_expression()`, enabling polymorphic loading to work correctly in this scenario. This allows for patterns such as where the discriminator value is computed from a related table.

  References: #12631

- [orm] [change]

  A sweep through class and function names in the ORM renames many classes and functions that have no intent of public visibility to be underscored. This is to reduce ambiguity as to which APIs are intended to be targeted by third party applications and extensions. Third parties are encouraged to propose new public APIs in Discussions to the extent they are needed to replace those that have been clarified as private.

  References: #10497

- [orm] [change]

  Removed legacy signatures dating back to 0.9 release from the `SessionEvents.after_bulk_update()` and `SessionEvents.after_bulk_delete()`.

  References: #10721

- [orm] [change]

  The `first_init` ORM event has been removed. This event was non-functional throughout the 1.4 and 2.0 series and could not be invoked without raising an internal error, so it is not expected that there is any real-world use of this event hook.

  References: #10500

- [orm] [changed]

  The “non primary” mapper feature, long deprecated in SQLAlchemy since version 1.3, has been removed. The sole use case for “non primary” mappers was that of using `relationship()` to link to a mapped class against an alternative selectable; this use case is now suited by the Relationship to Aliased Class feature.

  References: #12437

- [orm] [bug]

  Fixed issue where joined eager loading would fail to use the “nested” form of the query when GROUP BY or DISTINCT were present if the eager joins being added were many-to-ones, leading to additional columns in the columns clause which would then cause errors. The check for “nested” is tuned to be enabled for these queries even for many-to-one joined eager loaders, and the “only do nested if it’s one to many” aspect is now localized to when the query only has LIMIT or OFFSET added.

  References: #11226

- [orm] [bug]

  Removed the `ORDER BY` clause from queries generated by `selectin_polymorphic()` and the `Mapper.polymorphic_load` parameter set to `"selectin"`. The `ORDER BY` clause appears to have been an unnecessary implementation artifact.

  References: #13060

- [orm] [bug]

  A significant behavioral change has been made to the behavior of the `mapped_column.default` and `relationship.default` parameters, as well as the `relationship.default_factory` parameter with collection-based relationships, when used with SQLAlchemy’s Declarative Dataclass Mapping feature introduced in 2.0, where the given value (assumed to be an immutable scalar value for `mapped_column.default` and a simple collection class for `relationship.default_factory`) is no longer passed to the `@dataclass` API as a real default, instead a token that leaves the value un-set in the object’s `__dict__` is used, in conjunction with a descriptor-level default. This prevents an un-set default value from overriding a default that was actually set elsewhere, such as in relationship / foreign key assignment patterns as well as in `Session.merge()` scenarios. See the full writeup in the What’s New in SQLAlchemy 2.1? document which includes guidance on how to re-enable the 2.0 version of the behavior if needed.

  See also

  ORM Mapped Dataclasses no longer populate implicit default, collection-based default_factory in __dict__

  References: #12168

- [orm] [bug]

  A significant change to the ORM mechanics involved with both `with_loader_criteria()` as well as single table inheritance, to more aggressively locate WHERE criteria which should be augmented by either the custom criteria or single-table inheritance criteria; SELECT statements that do not include the entity within the columns clause or as an explicit FROM, but still reference the entity within the WHERE clause, are now covered, in particular this will allow subqueries using `EXISTS (SELECT 1)` such as those rendered by `Comparator.any()` and `Comparator.has()`.

  References: #13070

- [orm] [bug]

  The `relationship.secondary` parameter no longer uses Python `eval()` to evaluate the given string. This parameter when passed a string should resolve to a table name that’s present in the local `MetaData` collection only, and never needs to be any kind of Python expression otherwise. To use a real deferred callable based on a name that may not be locally present yet, use a lambda instead.

  References: #10564

- [orm] [bug]

  Revised the set “binary” operators for the association proxy `set()` interface to correctly raise `TypeError` for invalid use of the `|`, `&`, `^`, and `-` operators, as well as the in-place mutation versions of these methods, to match the behavior of standard Python `set()` as well as SQLAlchemy ORM’s “instrumented” set implementation.

  References: #11349

- [orm] [bug]

  The `SessionEvents.do_orm_execute()` event now allows direct mutation or replacement of the `ORMExecuteState.parameters` dictionary or list, which will take effect when the the statement is executed. Previously, changes to this collection were not accommodated by the event hook. Pull request courtesy Shamil.

  References: #12921

- [orm] [bug]

  Improved the behavior of standalone “operators” like `desc()`, `asc()`, `all_()`, etc. so that they consult the given expression object for an overriding method for that operator, even if the object is not itself a `ClauseElement`, such as if it’s an ORM attribute. This allows custom comparators for things like `composite()` to provide custom implementations of methods like `desc()`, `asc()`, etc.

  References: #12769

- [orm] [bug]

  A change in the mechanics of how Python dataclasses are applied to classes that use `MappedAsDataclass` or `registry.mapped_as_dataclass()` to apply `__annotations__` that are as identical as is possible to the original `__annotations__` given, while also adding attributes that SQLAlchemy considers to be part of dataclass `__annotations__`, then restoring the previous annotations in exactly the same format as they were, using patterns that work with PEP 649 as closely as possible.

  References: #13021

- [orm] [bug]

  ORM entities can now be involved within the SQL expressions used within `relationship.primaryjoin` and `relationship.secondaryjoin` parameters without the ORM entity information being implicitly sanitized, allowing ORM-specific features such as single-inheritance criteria in subqueries to continue working even when used in this context. This is made possible by overall ORM simplifications that occurred as of the 2.0 series. The changes here also provide a performance boost (up to 20%) for certain query compilation scenarios.

  References: #12843

- [orm] [bug]

  The behavior of `with_polymorphic()` when used with a single inheritance mapping has been changed such that its behavior should match as closely as possible to that of an equivalent joined inheritance mapping. Specifically this means that the base class specified in the `with_polymorphic()` construct will be the basemost class that is loaded, as well as all descendant classes of that basemost class. The change includes that the descendant classes named will no longer be exclusively indicated in “WHERE polymorphic_col IN” criteria; instead, the whole hierarchy starting with the given basemost class will be loaded. If the query indicates that rows should only be instances of a specific subclass within the polymorphic hierarchy, an error is raised if an incompatible superclass is loaded in the result since it cannot be made to match the requested class; this behavior is the same as what joined inheritance has done for many years. The change also allows a single result set to include column-level results from multiple sibling classes at once which was not previously possible with single table inheritance.

  References: #12395

- [orm]

  Ignore `Session.join_transaction_mode` in all cases when the bind provided to the `Session` is an `Engine`. Previously if an event that executed before the session logic, like `ConnectionEvents.engine_connect()`, left the connection with an active transaction, the `Session.join_transaction_mode` behavior took place, leading to a surprising behavior.

  References: #11163

- [orm]

  The `noload()` relationship loader option and related `lazy='noload'` setting is deprecated and will be removed in a future release. This option was originally intended for custom loader patterns that are no longer applicable in modern SQLAlchemy.

  References: #11045

### engine

- [engine] [usecase]

  Added new execution option `Connection.execution_options.driver_column_names`. This option disables the “name normalize” step that takes place against the DBAPI `cursor.description` for uppercase-default backends like Oracle, and will cause the keys of a result set (e.g. named tuple names, dictionary keys in `Row._mapping`, etc.) to be exactly what was delivered in cursor.description. This is mostly useful for plain textual statements using `text()` or `Connection.exec_driver_sql()`.

  References: #10789

- [engine] [change]

  An empty sequence passed to any `execute()` method now raised a deprecation warning, since such an executemany is invalid. Pull request courtesy of Carlos Sousa.

  References: #9647

- [engine] [change]

  The private method `Connection._execute_compiled` is removed. This method may have been used for some special purposes however the `SQLCompiler` object has lots of special state that should be set up for an execute call, which we don’t support.

- [engine] [bug]

  Adjusted URL parsing and stringification to apply url quoting to the “database” portion of the URL. This allows a URL where the “database” portion includes special characters such as question marks to be accommodated.

  See also

  URL stringify and parse now supports URL escaping for the “database” portion

  References: #11234

- [engine] [bug]

  Fixed issue in the `ConnectionEvents.after_cursor_execute()` method where the SQL statement and parameter list for an “insertmanyvalues” operation sent to the event would not be the actual SQL / parameters just emitted on the cursor, instead being the non-batched form of the statement that’s used as a template to generate the batched statements.

  References: #13018

- [engine] [bug]

  Fixed issue in “insertmanyvalues” feature where an INSERT..RETURNING that also made use of a sentinel column to track results would fail to filter out the additional column when `Result.unique()` were used to uniquify the result set.

  References: #10802

### sql

- [sql] [feature]

  Added the ability to create custom SQL constructs that can define new clauses within SELECT, INSERT, UPDATE, and DELETE statements without needing to modify the construction or compilation code of of `Select`, `Insert`, `Update`, or `Delete` directly. Support for testing these constructs, including caching support, is present along with an example test suite. The use case for these constructs is expected to be third party dialects for analytical SQL (so-called NewSQL) or other novel styles of database that introduce new clauses to these statements. A new example suite is included which illustrates the `QUALIFY` SQL construct used by several NewSQL databases which includes a cacheable implementation as well as a test suite.

  See also

  Extending Statements like SELECT, INSERT, etc

  References: #12195

- [sql] [feature]

  Added new Core feature `from_dml_column()` that may be used in expressions inside of `UpdateBase.values()` for INSERT or UPDATE; this construct will copy whatever SQL expression is used for the given target column in the statement to be used with additional columns. The construct is mostly intended to be a helper with ORM `hybrid_property` within DML hooks.

  References: #12496

- [sql] [feature] [core]

  > The Core operator system now includes the `matmul` operator, i.e. the `@` operator in Python as an optional operator. In addition to the `__matmul__` and `__rmatmul__` operator support this change also adds the missing `__rrshift__` and `__rlshift__`. Pull request courtesy Aramís Segovia.

  References: #12479

- [sql] [feature]

  Added support for Python 3.14+ template strings (t-strings) via the new `tstring()` construct. This feature makes use of Python 3.14 template strings as defined in PEP 750, allowing for ergonomic SQL statement construction by automatically interpolating Python values and SQLAlchemy expressions within template strings.

  See also

  Template String (t-string) Support for Python 3.14+ - in migration_21_toplevel

  References: #12548

- [sql] [usecase] [orm]

  The `Select.filter_by()`, `Update.filter_by()` and `Delete.filter_by()` methods now search across all entities present in the statement, rather than limiting their search to only the last joined entity or the first FROM entity. This allows these methods to locate attributes unambiguously across multiple joined tables, resolving issues where changing the order of operations such as `Select.with_only_columns()` would cause the method to fail.

  If an attribute name exists in more than one FROM clause entity, an `AmbiguousColumnError` is now raised, indicating that `Select.filter()` (or `Select.where()`) should be used instead with explicit table-qualified column references.

  See also

  filter_by() now searches across all FROM clause entities - Migration notes

  References: #8601

- [sql] [usecase]

  Added new generalized aggregate function ordering to functions via the `aggregate_order_by()` method, which receives an expression and generates the appropriate embedded “ORDER BY” or “WITHIN GROUP (ORDER BY)” phrase depending on backend database. This new function supersedes the use of the PostgreSQL `aggregate_order_by()` function, which remains present for backward compatibility. To complement the new parameter, the `aggregate_strings.order_by` which adds ORDER BY capability to the `aggregate_strings` dialect-agnostic function which works for all included backends. Thanks much to Reuven Starodubski with help on this patch.

  References: #12853

- [sql] [usecase]

  Changed the query style for ORM queries emitted by `Session.get()` as well as many-to-one lazy load queries to use the default labeling style, `SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`, which normally does not apply labels to columns in a SELECT statement. Previously, the older style `SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL` that labels columns as <tablename>_<columname> was used for `Session.get()` to maintain compatibility with `Query`. The change allows the string representation of ORM queries to be less verbose in all cases outside of legacy `Query` use. Pull request courtesy Inada Naoki.

  References: #12932

- [sql] [usecase]

  Added support for the pow operator (`**`), with a default SQL implementation of the `POW()` function. On Oracle Database, PostgreSQL and MSSQL it renders as `POWER()`. As part of this change, the operator routes through a new first class `func` member `pow`, which renders on Oracle Database, PostgreSQL and MSSQL as `POWER()`.

  References: #8579

- [sql] [usecase]

  Added method `TableClause.insert_column()` to complement `TableClause.append_column()`, which inserts the given column at a specific index. This can be helpful for prepending primary key columns to tables, etc.

  References: #7910

- [sql] [change]

  Added new implementation for the `Select.params()` method and that of similar statements, via a new statement-only `ExecutableStatement.params()` method which works more efficiently and correctly than the previous implementations available from `ClauseElement`, by associating the given parameter dictionary with the statement overall rather than cloning the statement and rewriting its bound parameters. The `ClauseElement.params()` and `ClauseElement.unique_params()` methods, when called on an object that does not implement `ExecutableStatement`, will continue to work the old way of cloning the object, and will emit a deprecation warning. This issue both resolves the architectural / performance concerns of #7066 and also provides correct ORM compatibility for functions like `aliased()`, reported by #12915.

  See also

  Improved params() implementation for executable statements

  References: #12915, #7066

- [sql] [change]

  The `.c` and `.columns` attributes on the `Select` and `TextualSelect` constructs, which are not instances of `FromClause`, have been removed completely, in addition to the `.select()` method as well as other codepaths which would implicitly generate a subquery from a `Select` without the need to explicitly call the `Select.subquery()` method.

  In the case of `.c` and `.columns`, these attributes were never useful in practice and have caused a great deal of confusion, hence were deprecated back in version 1.4, and have emitted warnings since that version. Accessing the columns that are specific to a `Select` construct is done via the `Select.selected_columns` attribute, which was added in version 1.4 to suit the use case that users often expected `.c` to accomplish. In the larger sense, implicit production of subqueries works against SQLAlchemy’s modern practice of making SQL structure as explicit as possible.

  Note that this is not related to the usual `FromClause.c` and `FromClause.columns` attributes, common to objects such as `Table` and `Subquery`, which are unaffected by this change.

  See also

  A SELECT statement is no longer implicitly considered to be a FROM clause - original notes from SQLAlchemy 1.4

  References: #10236

- [sql] [change]

  the `Numeric` and `Float` SQL types have been separated out so that `Float` no longer inherits from `Numeric`; instead, they both extend from a common mixin `NumericCommon`. This corrects for some architectural shortcomings where numeric and float types are typically separate, and establishes more consistency with `Integer` also being a distinct type. The change should not have any end-user implications except for code that may be using `isinstance()` to test for the `Numeric` datatype; third party dialects which rely upon specific implementation types for numeric and/or float may also require adjustment to maintain compatibility.

  References: #5252

- [sql] [bug]

  Fixed issue where anonymous label generation for `CTE` constructs could produce name collisions when Python’s garbage collector reused memory addresses during complex query compilation. The anonymous name generation for `CTE` and other aliased constructs like `Alias`, `Subquery` and others now use `os.urandom()` to generate unique identifiers instead of relying on object `id()`, ensuring uniqueness even in cases of aggressive garbage collection and memory reuse.

  References: #12990

- [sql] [bug]

  Fixed issue in name normalization (e.g. “uppercase” backends like Oracle) where using a `TextualSelect` would not properly maintain as uppercase column names that were quoted as uppercase, even though the `TextualSelect` includes a `Column` that explicitly holds this uppercase name.

  References: #10788

- [sql] [bug]

  Enhanced the caching structure of the `over.rows` and `over.range` so that different numerical values for the rows / range fields are cached on the same cache key, to the extent that the underlying SQL does not actually change (i.e. “unbounded”, “current row”, negative/positive status will still change the cache key). This prevents the use of many different numerical range/rows value for a query that is otherwise identical from filling up the SQL cache.

  Note that the semi-private compiler method `_format_frame_clause()` is removed by this fix, replaced with a new method `visit_frame_clause()`. Third party dialects which may have referred to this method will need to change the name and revise the approach to rendering the correct SQL for that dialect.

  References: #11515

- [sql] [bug]

  Updated the `over()` clause to allow non integer values in `over.range_` clause. Previously, only integer values were allowed and any other values would lead to a failure. To specify a non-integer value, use the new `FrameClause` construct along with the new `FrameClauseType` enum to specify the frame boundaries. For example:

      from sqlalchemy import FrameClause, FrameClauseType

      select(
          func.sum(table.c.value).over(
              range_=FrameClause(
                  3.14,
                  2.71,
                  FrameClauseType.PRECEDING,
                  FrameClauseType.FOLLOWING,
              )
          )
      )

  See also

  Non-integer RANGE window frame clauses now supported - in the migration guide

  References: #12596

- [sql] [bug]

  Added a new concept of “operator classes” to the SQL operators supported by SQLAlchemy, represented within the enum `OperatorClass`. The purpose of this structure is to provide an extra layer of validation when a particular kind of SQL operation is used with a particular datatype, to catch early the use of an operator that does not have any relevance to the datatype in use; a simple example is an integer or numeric column used with a “string match” operator.

  See also

  Operator classes added to validate operator usage with datatypes

  References: #12736

- [sql] [bug]

  Fixed an issue in `Select.join_from()` where the join condition between the left and right tables specified in the method call could be incorrectly determined based on an intermediate table already present in the FROM clause, rather than matching the foreign keys between the immediate left and right arguments. The join condition is now determined by matching primary keys between the two tables explicitly passed to `Select.join_from()`, ensuring consistent and predictable join behavior regardless of the order of join operations or other tables present in the query. The fix is applied to both the Core and ORM implementations of `Select.join_from()`.

  References: #12931

- [sql]

  Removed the automatic coercion of executable objects, such as `Query`, when passed into `Session.execute()`. This usage raised a deprecation warning since the 1.4 series.

  References: #12218

### schema

- [schema] [feature]

  Added support for the SQL `CREATE TABLE ... AS SELECT` construct via the new `CreateTableAs` DDL construct and the `Select.into()` method. The new construct allows creating a table directly from the results of a SELECT statement, with support for options such as `TEMPORARY` and `IF NOT EXISTS` where supported by the target database. Tables defined with `CreateTableAs` integrate with `MetaData` for automated DDL generation and provide a `Table` object for querying. Pull request courtesy Greg Jarzab.

  See also

  CREATE VIEW and CREATE TABLE AS SELECT Support

  References: #4950

- [schema] [feature]

  Added support for the SQL `CREATE VIEW` statement via the new `CreateView` DDL class. The new class allows creating database views from SELECT statements, with support for options such as `TEMPORARY`, `IF NOT EXISTS`, and `MATERIALIZED` where supported by the target database. Views defined with `CreateView` integrate with `MetaData` for automated DDL generation and provide a `Table` object for querying.

  See also

  CREATE VIEW and CREATE TABLE AS SELECT Support

  References: #181

- [schema] [usecase]

  The the parameter `DropConstraint.isolate_from_table` was deprecated since it has no effect on the drop table behavior. Its default values was also changed to `False`.

  References: #13006

- [schema] [bug]

  The `Float` and `Numeric` types are no longer automatically considered as auto-incrementing columns when the `Column.autoincrement` parameter is left at its default of `"auto"` on a `Column` that is part of the primary key. When the parameter is set to `True`, a `Numeric` type will be accepted as an auto-incrementing datatype for primary key columns, but only if its scale is explicitly given as zero; otherwise, an error is raised. This is a change from 2.0 where all numeric types including floats were automatically considered as “autoincrement” for primary key columns.

  References: #11811

- [schema]

  Deprecate Oracle only parameters `Sequence.order`, `Identity.order` and `Identity.on_null`. They should be configured using the dialect kwargs `oracle_order` and `oracle_on_null`.

  References: #10247

### typing

- [typing] [feature]

  The `Row` object now no longer makes use of an intermediary `Tuple` in order to represent its individual element types; instead, the individual element types are present directly, via new PEP 646 integration, now available in more recent versions of Mypy. Mypy 1.7 or greater is now required for statements, results and rows to be correctly typed. Pull request courtesy Yurii Karabas.

  See also

  Row now represents individual column types directly without Tuple

  References: #10635

- [typing] [orm]

  Removed the deprecated mypy plugin. The plugin was non-functional with newer version of mypy and it’s no longer needed with modern SQLAlchemy declarative style.

  References: #12293

- [typing]

  The default implementation of `TypeEngine.python_type` now returns `object` instead of `NotImplementedError`, since that’s the base for all types in Python3. The `python_type` of `JSON` no longer returns `dict`, but instead fallbacks to the generic implementation.

  References: #10646

- [typing] [orm]

  Deprecated the `declarative_mixin` decorator since it was used only by the now removed mypy plugin.

  References: #12346

### asyncio

- [asyncio] [feature]

  The “emulated” exception hierarchies for the asyncio drivers such as asyncpg, aiomysql, aioodbc, etc. have been standardized on a common base `EmulatedDBAPIException`, which is now what’s available from the `StatementException.orig` attribute on a SQLAlchemy `DBAPIError` object. Within `EmulatedDBAPIException` and the subclasses in its hierarchy, the original driver-level exception is also now available via the `EmulatedDBAPIException.orig` attribute, and is also available from `DBAPIError` directly using the `DBAPIError.driver_exception` attribute.

  References: #8047

- [asyncio] [change]

  Added an initialize step to the import of `sqlalchemy.ext.asyncio` so that `greenlet` will be imported only when the asyncio extension is first imported. Alternatively, the `greenlet` library is still imported lazily on first use to support use case that don’t make direct use of the SQLAlchemy asyncio extension.

  References: #10296

- [asyncio] [change]

  Adapted all asyncio dialects, including aiosqlite, aiomysql, asyncmy, psycopg, asyncpg to use the generic asyncio connection adapter first added in #6521 for the aioodbc DBAPI, allowing these dialects to take advantage of a common framework.

  References: #10415

- [asyncio] [change]

  Removed the compatibility `async_fallback` mode for async dialects, since it’s no longer used by SQLAlchemy tests. Also removed the internal function `await_fallback()` and renamed the internal function `await_only()` to `await_()`. No change is expected to user code.

- [asyncio] [bug]

  Refactored all asyncio dialects so that exceptions which occur on failed connection attempts are appropriately wrapped with SQLAlchemy exception objects, allowing for consistent error handling.

  References: #11956

### postgresql

- [postgresql] [feature]

  Adds a new `str` subclass `BitString` representing PostgreSQL bitstrings in python, that includes functionality for converting to and from `int` and `bytes`, in addition to implementing utility methods and operators for dealing with bits.

  This new class is returned automatically by the `postgresql.BIT` type.

  See also

  Addition of BitString subclass for handling postgresql BIT columns

  References: #10556

- [postgresql] [feature]

  Support for `VIRTUAL` computed columns on PostgreSQL 18 and later has been added. The default behavior when `Computed.persisted` is not specified has been changed to align with PostgreSQL 18’s default of `VIRTUAL`. When `Computed.persisted` is not specified, no keyword is rendered on PostgreSQL 18 and later; on older versions a warning is emitted and `STORED` is used as the default. To explicitly request `STORED` behavior on all PostgreSQL versions, specify `persisted=True`.

  References: #12866

- [postgresql] [feature]

  Support for storage parameters in `CREATE TABLE` using the `WITH` clause has been added. The `postgresql_with` dialect option of `Table` accepts a mapping of key/value options.

  See also

  WITH - in the PostgreSQL dialect documentation

  References: #10909

- [postgresql] [feature]

  Added additional emulated error classes for the subclasses of `asyncpg.exception.IntegrityError` including `RestrictViolationError`, `NotNullViolationError`, `ForeignKeyViolationError`, `UniqueViolationError` `CheckViolationError`, `ExclusionViolationError`. These exceptions are not directly thrown by SQLAlchemy’s asyncio emulation, however are available from the newly added `DBAPIError.driver_exception` attribute when a `IntegrityError` is caught.

  References: #8047

- [postgresql] [feature] [sql]

  Added support for monotonic server-side functions such as PostgreSQL 18’s `uuidv7()` to work with the “Insert Many Values” Behavior for INSERT statements feature. By passing `monotonic=True` to any `Function`, the function can be used as a sentinel for tracking row order in batched INSERT operations with RETURNING, allowing the ORM and Core to efficiently batch INSERT statements while maintaining deterministic row ordering.

  See also

  Support for Server-Side Monotonic Functions such as uuidv7() in Batched INSERT Operations

  Configuring Monotonic Functions such as UUIDV7

  PostgreSQL 18 and above UUID with uuidv7 as a server default

  References: #13014

- [postgresql] [feature]

  Added syntax extension `distinct_on()` to build `DISTINCT ON` clauses. The old api, that passed columns to `Select.distinct()`, is now deprecated.

  References: #12342

- [postgresql] [usecase]

  The PostgreSQL dialect now support reflection of table options, including the storage parameters, table access method and table spaces. These options are automatically reflected when autoloading a table, and are also available via the `Inspector.get_table_options()` and `Inspector.get_multi_table_optionsmethod()` methods.

  References: #10909

- [postgresql] [usecase]

  Added support for PostgreSQL 14+ HSTORE subscripting syntax. When connected to PostgreSQL 14 or later, HSTORE columns now automatically use the native subscript notation `hstore_col['key']` instead of the arrow operator `hstore_col -> 'key'` for both read and write operations. This provides better compatibility with PostgreSQL’s native HSTORE subscripting feature while maintaining backward compatibility with older PostgreSQL versions.

  Warning

  Indexes in existing PostgreSQL databases which were indexed on an HSTORE subscript expression would need to be updated in order to match the new SQL syntax.

  See also

  HSTORE subscripting now uses native PostgreSQL 14+ syntax - in the migration guide

  References: #12948

- [postgresql] [usecase]

  Added new parameter `Enum.create_type` to the Core `Enum` class. This parameter is automatically passed to the corresponding `ENUM` native type during DDL operations, allowing control over whether the PostgreSQL ENUM type is implicitly created or dropped within DDL operations that are otherwise targeting tables only. This provides control over the `ENUM.create_type` behavior without requiring explicit creation of a `ENUM` object.

  References: #10604

- [postgresql] [usecase]

  The default DBAPI driver for the PostgreSQL dialect has been changed to `psycopg` (psycopg version 3) instead of `psycopg2`. The `psycopg2` driver remains fully supported and can be explicitly specified in the connection URL using `postgresql+psycopg2://`.

  The `psycopg` (version 3) driver includes improvements over `psycopg2` including better performance when using C extensions and native support for async operations.

  See also

  Default PostgreSQL driver changed to psycopg (psycopg 3)

  References: #13010

- [postgresql] [change]

  The `Comparator.any()` and `Comparator.all()` methods for the `ARRAY` type are now deprecated for removal; these two methods along with `Any()` and `All()` have been legacy for some time as they are superseded by the `any_()` and `all_()` functions, which feature more intuitive use.

  References: #10821

- [postgresql] [change]

  Named types such as `ENUM` and `DOMAIN` (as well as the dialect-agnostic `Enum` version) are now more strongly associated with the `MetaData` at the top of the table hierarchy and are de-associated with any particular `Table` they may be a part of. This better represents how PostgreSQL named types exist independently of any particular table, and that they may be used across many tables simultaneously. The change impacts the behavior of the “default schema” for a named type, as well as the CREATE/DROP behavior in relationship to the `MetaData` and `Table` construct. The change also includes a new `CheckFirst` enumeration which allows fine grained control over “check” queries during DDL operations, as well as that the `SchemaType.inherit_schema` parameter is deprecated and will emit a deprecation warning when used. See the migration notes for full details.

  See also

  Changes to Named Type Handling in PostgreSQL - Complete details on PostgreSQL named type changes

  References: #10594, #12690

- [postgresql] [bug]

  A `CompileError` is raised if attempting to create a PostgreSQL `ENUM` or `DOMAIN` datatype using a name that matches a known pg_catalog datatype name, and a default schema is not specified. These types must be explicit within a schema in order to be differentiated from the built-in pg_catalog type. The “public” or otherwise default schema is not chosen by default here since the type can only be reflected back using the explicit schema name as well (it is otherwise not visible due to the pg_catalog name). Pull request courtesy Kapil Dagur.

  References: #12761

### mysql

- [mysql] [feature]

  Added new construct `limit()` which can be applied to any `update()` or `delete()` to provide the LIMIT keyword to UPDATE and DELETE. This new construct supersedes the use of the “mysql_limit” dialect keyword argument.

- [mysql] [mariadb] [reflection]

  Updated the reflection logic for indexes in the MariaDB and MySQL dialect to avoid setting the undocumented `type` key in the `ReflectedIndex` dicts returned by `get_indexes` method.

  References: #12240

### mariadb

- [mariadb] [usecase]

  Modified the MariaDB dialect so that when using the `Uuid` datatype with MariaDB >= 10.7, leaving the `Uuid.native_uuid` parameter at its default of True, the native `UUID` datatype will be rendered in DDL and used for database communication, rather than `CHAR(32)` (the non-native UUID type) as was the case previously. This is a behavioral change since 2.0, where the generic `Uuid` datatype delivered `CHAR(32)` for all MySQL and MariaDB variants. Support for all major DBAPIs is implemented including support for less common “insertmanyvalues” scenarios where UUID values are generated in different ways for primary keys. Thanks much to Volodymyr Kochetkov for delivering the PR.

  References: #10339

### sqlite

- [sqlite] [bug]

  Improved the behavior of JSON accessors `Comparator.as_string()`, `Comparator.as_boolean()`, `Comparator.as_float()`, `Comparator.as_integer()` to use CAST in a similar way that the PostgreSQL, MySQL and SQL Server dialects do to help enforce the expected Python type is returned.

  References: #11074

### mssql

- [mssql] [bug]

  The `Comparator.as_boolean()` method when used on a JSON value on SQL Server will now force a cast to occur for values that are not simple true/false JSON literals, forcing SQL Server to attempt to interpret the given value as a 1/0 BIT, or raise an error if not possible. Previously the expression would return NULL.

  References: #11074

- [mssql] [bug]

  Fix mssql+pyodbc issue where valid plus signs in an already-unquoted `odbc_connect=` (raw DBAPI) connection string are replaced with spaces.

  The pyodbc connector would unconditionally pass the odbc_connect value to unquote_plus(), even if it was not required. So, if the (unquoted) odbc_connect value contained `PWD=pass+word` that would get changed to `PWD=pass word`, and the login would fail. One workaround was to quote just the plus sign — `PWD=pass%2Bword` — which would then get unquoted to `PWD=pass+word`.

  References: #11250

### oracle

- [oracle] [feature]

  Added support for native BOOLEAN support in Oracle Database 23c and above. The Oracle dialect now renders `BOOLEAN` automatically when `Boolean` is used in DDL, and also now supports direct use of the `BOOLEAN` datatype, when 23c and above is in use. For Oracle versions prior to 23c, boolean values continue to be emulated using SMALLINT as before. Special case handling is also present to ensure a SMALLINT that’s interpreted with the `Boolean` datatype on Oracle Database 23c and above continues to return bool values. Pull request courtesy Yeongbae Jeon.

  See also

  Boolean Support

  References: #11633

- [oracle] [usecase]

  The default DBAPI driver for the Oracle Database dialect has been changed to `oracledb` instead of `cx_oracle`. The `cx_oracle` driver remains fully supported and can be explicitly specified in the connection URL using `oracle+cx_oracle://`.

  The `oracledb` driver is a modernized version of `cx_oracle` with better performance characteristics and ongoing active development from Oracle.

  See also

  Default Oracle driver changed to python-oracledb

  References: #13010

### tests

- [tests] [change]

  The top-level test runner has been changed to use `nox`, adding a `noxfile.py` as well as some included modules. The `tox.ini` file remains in place so that `tox` runs will continue to function in the near term, however it will be eventually removed and improvements and maintenance going forward will be only towards `noxfile.py`.

### misc

- [misc] [changed]

  Removed multiple api that were deprecated in the 1.3 series and earlier. The list of removed features includes:

  - The `force` parameter of `IdentifierPreparer.quote` and `IdentifierPreparer.quote_schema`;

  - The `threaded` parameter of the cx-Oracle dialect;

  - The `_json_serializer` and `_json_deserializer` parameters of the SQLite dialect;

  - The `collection.converter` decorator;

  - The `Mapper.mapped_table` property;

  - The `Session.close_all` method;

  - Support for multiple arguments in `defer()` and `undefer()`.

  References: #12441