News and Updates

SQLAlchemy 2.0.1 Released
permalink

SQLAlchemy release 2.0.1 is now available.

The release of 2.0 has been very well received, with about half of daily downloads (600K / day) going to the 2.0 series. The ORM Declarative Annotations feature is in particular receiving lots of attention, and the focus of 2.0 is improvements and fixes to this new system, as well as ongoing improvements and fixes to the general pep-484 typing now applied inline within the main source.

The release also repairs one issue within the new "bulk insert" mechanics, fixing compilation for complex INSERT statements with nested CTEs that may themselves also include INSERT statements.

The release has otherwise so far been very smooth with only a few trivial regressions which have been fixed.

Links to the detailed changelog for 2.0.1 is at Changelog.

SQLAlchemy 2.0.1 is available on the Download Page.

SQLAlchemy 2.0.0 Released
permalink

SQLAlchemy 2.0.0, the first production release of SQLAlchemy 2.0, is now available.

With this release, the default version of SQLAlchemy that will install when one runs pip install sqlalchemy will be version 2.0. Note that version 2.0 has significant API changes compared to the 1.4 series, so applications that run on the 1.x series of SQLAlchemy which have not gone through the migration process should seek to ensure requirements are set to maintain the desired major SQLAlchemy release series in place.

The history of the SQLAlchemy 2.0 series starts over four years ago on August 8, 2018, with some short ideas for how SQLAlchemy's notion of Core and ORM querying might be unified. The first plan for a real "SQLAlchemy 2.0" concept formed in November of that year, focused on the two areas of drastically simplifying Core execution and transactional APIs as well as seeking to unify querying across Core and ORM.

The changes to foundational concepts were dramatic enough that SQLAlchemy 2.0 was separated into two major phases. The first phase was the SQLAlchemy 1.4 series, which provided an entirely new unified Core / ORM SQL querying system, all while building on top of a new universal statement caching architecture. This phase provided the complete implementation for SQLAlchemy 2.0's SQL construction approach (minus pep-484 typing support), while maintaining the legacy Query API completely. Along with this release, a comprehensive migration path inspired by lessons learned from the Python 2->3 migration process was provided, which describes how to port applications so that they can continue to run in SQLAlchemy 1.4 while being entirely forwards compatible with SQLAlchemy 2.0.

The second phase is the SQLAlchemy 2.0 series, which removes the majority of deprecated elements, relegating the remaining ones (primarily Query) to long term "legacy" status, fully moves to Python 3 only, and at the same time adds many new features that build on top of the new architecture, taking full advantage of Python 3 features (including dataclasses, enums, inline annotations) as well as the new unified query architecture.

The key advantage to this approach is that the most significant and by far the riskiest architectural changes, that of the rewrite of Core/ORM querying on top of a new caching layer, have already been in production use with SQLAlchemy 1.4 for nearly two years. So while SQLAlchemy 2.0 will certainly have a lot of new issues once it is used by the full developer audience, they should not be of the "new cracks in the foundational approach" variety, as the architectural foundations are already in widespread use. We expect that the vast majority of issues will be related to the new typing system as well as issues of existing applications adjusting to use the new APIs.

SQLAlchemy 2.0 is a big enough release that it has two migration guides; the Major Migration Guide documents how to reach API compatibility for an application to be able to run in SQLAlchemy 1.4 or 2.0 equally; the What's New in SQLAlchemy 2.0? guide then provides for all the new features and APIs that are available once an application is running on SQLAlchemy 2.0.

With that introduction in mind, here's a top level bullet list of what's totally new in SQLAlchemy 2.0:

  • All new plugin-free pep-484 compatible ORM syntaxes - The ORM Declarative style of mapper configuration now has an all new look, borrowing heavily from systems such as Python dataclasses and SQLModel to provide for Annotation-driven ORM declarations, using runtime interpretation of pep-484 annotations to produce mapped classes that are fully typed and compatible with any type checker or IDE.
  • pep-484 support for both new-style and legacy ORM queries - using Annotated Declarative models, the SQL constructs one creates, such as a select() object or a legacy Query construct, are themselves typed as to the kind of columns returned within a row, and this typing carries forth all the way to the Python values extracted from the result object returned after executing the query. While Python typing still has limitations for doing this kind of thing, the new typing support draws from some techniques used by SQLModel so that it works without additional annotations for the vast majority of queries generated from ORM model classes, with varying degrees of support for result-set-typing of Core-oriented or hybrid Core/ORM queries as well. Some examples are at SQL Expression Typing - Examples.
  • Declarative fully integrated with Python Dataclasses - SQLAlchemy 2.0 introduces support for an Annotated Declarative mapped class to be generated as a Python dataclass directly; this will provide an ORM mapped class declared like any other that features auto-generated dataclasses methods such as __init__(), __repr__(), __eq__(), and all the rest. This new approach is vastly improved over the interim "hybrid" approaches introduced in SQLAlchemy 1.4.
  • An all-new, fully ORM-integrated approach to bulk INSERT that is typically an order of magnitude faster on most backends - Most of SQLAlchemy's supported databases and drivers have now added or improved their support for INSERT RETURNING syntax, and SQLAlchemy 2.0 has taken advantage of this by supporting and improving RETURNING support for all of its backends, with the sole exception of MySQL (MySQL only; MariaDB supports RETURNING). Part of this improvement is the ability to INSERT thousands of rows in one batched statement that also returns a complete result set of server generated values needed by the ORM, which was never possible before with the sole exception of an interim implementation that relied upon the psycopg2 driver. In 2.0, the work has been done to optimize INSERT for all backends so that thousands of ORM objects with or without primary keys can be INSERTed with one database round trip, and this capability is fully integrated into the ORM for all INSERT operations, both "regular" ORM unit of work operations as well as for "bulk" approaches, which are also newly revised in version 2.0.
  • An all-new bulk-optimized schema reflection architecture - operations that reflect full schemas as once such as MetaData.reflect(engine) and newly added schema-wide operations with the inspect(engine) construct now build on top of a foundation that assumes operations that work across hundreds or thousands of tables at once, rather than table-at-a-time. Dialects can "opt in" to the new architecture by providing for new reflection queries that work across many tables at once, allowing for very large schemas to be fully reflected much more efficently. The new architecture is enabled for the PostgreSQL and Oracle backends, which were the two backends that had the biggest performance issues for large schemas, where it grants a 250% improvement for PostgreSQL and a 900% improvement for Oracle. The SQL Server dialect is the next target for the new architecture. Any third party dialect can opt in to the new "many tables at once" system or remain on the previous "table at a time" approach that remains fully compatible without any changes.
  • Native extensions ported to Cython - SQLAlchemy's C extensions have been replaced with an updated approach using Cython. The Cython version of the extensions in most cases benches as fast as, and sometimes faster than, the previous C extensions, and allows SQLAlchemy to provide new native extensions in more areas of the library much more easily without risk of memory or stability issues.

SQLAlchemy 2.0 includes many more features and with new architectures and a lot of older baggage being shed, hopes to have plenty of room for the future.

Links to the detailed changelog for 2.0.0 is at Changelog - note that the 2.0 series changelog spans across four beta and three release candidate releases.

SQLAlchemy 2.0.0 is available on the Download Page.

SQLAlchemy 2.0.0rc3 Released
permalink

The third release candidate of SQLAlchemy 2.0 is now available.

Version 2.0.0rc3 releases a few more small enhancements and fixes, as well as serves as a test for an update of the wheel builder plugin on Pypi. Release 2.0.0 final is imminent, as early as next week. Applications that haven't tested within the 2.0 series should make sure they have pinned their requirements, as pip install will start to fetch the 2.0 series by default instead of 1.4 when the release occurs.

Links to the detailed changelog for 2.0.0rc3 is at Changelog.

SQLAlchemy 2.0.0rc3 is available on the Download Page.

SQLAlchemy 2.0.0rc2 Released
permalink

The second release candidate of SQLAlchemy 2.0 is now available.

Version 2.0.0rc2 includes a small number of relatively minor changes. In particular the MySQL/MariaDB dialect repair a regression that lasted throughout the 1.4 series where the Inspector.has_table() method would not report on the existence of temp tables.

2.0 final is still anticipated for sometime this month.

Links to the detailed changelog for 2.0.0rc2 is at Changelog.

SQLAlchemy 2.0.0rc2 is available on the Download Page.

SQLAlchemy 1.4.46 Released
permalink

SQLAlchemy 1.4.46 is now available.

The first release of the new year, SQLAlchemy 1.4.46 fixes a few fairly important issues, including an issue which could in unlikely circumstances impact connection pool stability for applications that use gevent or eventlet.

Release 1.4.46 is also a step towards the first release of SQLAlchemy 2.0 final, expected this month, introducing a new deprecation warning that emits exactly once when the application in use makes use of an API removed from 2.0, when the SQLALCHEMY_WARN_20 environment variable is not otherwise set. This deprecation warning is intended to give users that may not be following the progress of 2.0 that their requirements files should ensure SQLAlchemy 2.0 won't be installed prematurely.

The complete changelog for 1.4.46 is at Changelog.

SQLAlchemy 1.4.46 is available on the Download Page.