SQLAlchemy 2.0.0 was released on 26 January 2023, and it moves type information from the right-hand to the left-hand side of a column declaration: the ORM mapping now derives nullability and Python type from PEP 484 annotations, no longer from the parameters passed to Column. This is the change with the widest effect on existing code, more so than async or the new INSERT machinery.

Context

SQLAlchemy is a SQL toolkit and ORM for Python, written by Mike Bayer since 2006 and released under the MIT licence. Version 2.0 wants at least Python 3.7 and closes a path opened with 1.4 (March 2021), which had introduced, in transitional form, both asyncio and the query style built on select(). The transition was meant to be gradual: code already written in “2.0 style” under 1.4 with the SQLALCHEMY_WARN_20 flag enabled reached 2.0 with few remaining changes.

Version 2.0 drops Python 2, clears away most of the deprecated elements, and downgrades the Query object — the session.query(...) used for years — to legacy status: it still works, but is no longer documented as the primary form.

The typing problem in the mapping

In earlier versions a column was declared like this:

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    email = Column(String, nullable=True)

To a type checker such as mypy or pyright, User.name had no sensible type: the information “it is a string, it cannot be NULL” sat inside the arguments to Column, out of reach of static analysis. For years that gap was plugged by SQLAlchemy’s official mypy plugin, an external dependency that had to chase the checker’s own releases.

Version 2.0 introduces a declarative mapping in which the type is the annotation itself:

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    email: Mapped[str | None]

Three things are derived from the annotation held inside the Mapped[...] generic:

  • the column type (Mapped[int] → Integer, Mapped[str] → String), via a configurable Python-type → SQL-type map;
  • nullability: the presence of Optional[...] (or | None) means NULL, its absence means NOT NULL. The default is the reverse of raw SQL, where a column is nullable unless stated otherwise;
  • the static type of the attribute: User.name is now Mapped[str], so an expression such as select(User.name) is typed down to the value extracted from the result row.

mapped_column() stays on the right-hand side for everything an annotation cannot express: primary_key, ForeignKey, server_default, an explicit String length. When none of that is needed, it disappears — email: Mapped[str | None] on its own is already a complete mapping.

The critical point

The risk sits in the nullability default. In SQL a column with no constraints is nullable; in 2.0’s annotated syntax a Mapped[str] without Optional produces NOT NULL. Anyone migrating an existing mapping column by column has to check each nullable= before and after, because the mechanical translation Column(String) → Mapped[str] changes the constraint that ends up in the DDL: the first form is nullable, the second is not. On an already-populated database the difference only shows up when the schema is regenerated or compared against a migration tool such as Alembic.

There is also a less obvious consequence. Once the annotation becomes the source of truth, a wrong annotation becomes a schema bug, no longer a mere checker warning. A Mapped[int] over a column the database treats as text is now a discrepancy that propagates into the generated DDL.

Async and INSERT

asyncio support does not begin with 2.0: it had arrived in 1.4 and is consolidated here. AsyncSession and AsyncConnection expose the same surface as the synchronous API over drivers such as asyncpg for PostgreSQL:

async with AsyncSession(engine) as session:
    result = await session.execute(select(User).where(User.name == "Anna"))
    users = result.scalars().all()

The performance change in 2.0 is insertmanyvalues: for dialects that have INSERT ... RETURNING, an ORM insert of many objects is rendered as a single statement with multiple value tuples inlined, so that RETURNING returns the generated primary keys and the rows respect the order of the parameters passed in. Previously, getting the generated keys for N objects meant N round trips; with insertmanyvalues they drop sharply. The mechanism holds where RETURNING exists: PostgreSQL and recent SQLite have it, MySQL does not (MariaDB does).

Operational implications

For anyone maintaining an application on SQLAlchemy 1.4 the documented path is incremental: first replace declarative_base() with DeclarativeBase, then Column with mapped_column(), then introduce Mapped[...] annotations where they pay off, and finally — with PEP 593 Annotated — package recurring directives (an integer primary key, say) into reusable type aliases. None of these steps forces you to rewrite the queries at the same time: session.query(...) stays valid as legacy while the mapping is converted.

The mypy plugin is no longer needed for the new declarative style, because typing is now part of how classes are defined and no longer asks you to teach the checker how to read Column calls. On mappings still in the old style the plugin keeps doing its job until the conversion happens.

Limits

The annotated syntax covers the common case; configurations that go beyond it — custom types, columns with particular behaviour, Mapped over non-trivial attributes — still want explicit arguments in mapped_column(), and it is worth reading the documentation on Python-type → SQL-type derivation for the cases where the default map is not what you expect (the precision of a Numeric, the length of a String). Version 2.0 does not make automatic what used to be explicit: it changes where the information is written, and leaves the programmer responsible for writing it correctly.

Cover image: Mike Bayer, the author of SQLAlchemy, talking about it at PyCon 2012 — photo by Taavi Burns, CC BY 2.0 — https://commons.wikimedia.org/wiki/File:Mike_Bayer_talking_about_SQLAlchemy_at_PyCon_2012_a.jpg