What’s New in SQLAlchemy 2.0 Miguel Grinberg

About Me ● Python & JavaScript Developer ● Living in ☘ Ireland since 2018 ● Principal Software Engineer at ● Blogger at https://blog.miguelgrinberg.com ● Book Author:

Integration With Type Checkers and IDEs

New Model Definition Syntax

New Model Syntax import sqlalchemy.orm as so import sqlalchemy as sa

New Model Syntax import sqlalchemy.orm as so import sqlalchemy as sa class Model(so.DeclarativeBase): pass class User(Model): pass

New Column Syntax import sqlalchemy.orm as so import sqlalchemy as sa class Model(so.DeclarativeBase): pass class User(Model): id: so.Mapped[int] name: so.Mapped[str] age: so.Mapped[Optional[int]]

New Column Syntax import sqlalchemy.orm as so import sqlalchemy as sa class Model(so.DeclarativeBase): pass class User(Model): id: so.Mapped[int] = so.mapped_column(primary_key=True) name: so.Mapped[str] = so.mapped_column(sa.String(50)) age: so.Mapped[Optional[int]]

Dataclass Integration import sqlalchemy.orm as so import sqlalchemy as sa class Model(so.DeclarativeBase): pass class User(so.MappedAsDataclass, Model): id: so.Mapped[int] = so.mapped_column(primary_key=True, init=False) name: so.Mapped[str] = so.mapped_column(sa.String(50)) age: so.Mapped[Optional[int]] = so.mapped_column(default=None)

New Query Syntax

Query Syntax import sqlalchemy.orm as so import sqlalchemy as sa query = sa.select(User).where(User.name == 'susan')

Query Syntax: Results Iterator import sqlalchemy.orm as so import sqlalchemy as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session: results = session.execute(query)

Query Syntax: All Result Rows import sqlalchemy.orm as so import sqlalchemy as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session: results = session.execute(query).all()

Query Syntax: First Result Row import sqlalchemy.orm as so import sqlalchemy as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session: results = session.execute(query).first()

Query Syntax: Exactly One Result Row import sqlalchemy.orm as so import sqlalchemy as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session: results = session.execute(query).one()

Query Syntax: At Most One Result Row import sqlalchemy.orm as so import sqlalchemy as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session: results = session.execute(query).one_or_none()

Query Syntax: First Item In Each Row import sqlalchemy.orm as so import sqlalchemy as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session: results = session.scalars(query) .all() .first() .one() .one_or_none()

Query Syntax: First Item In First Row import sqlalchemy.orm as so import sqlalchemy as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session: results = session.scalar(query)

Query Syntax: Explicit Commit import sqlalchemy.orm as so import sqlalchemy as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session: results = session.scalar(query) session.add(User(name='mary')) session.commit()

Query Syntax: Implicit Commit import sqlalchemy.orm as so import sqlalchemy as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session, session.begin(): results = session.scalar(query) session.add(User(name='mary'))

Relationship Improvements

Typed Foreign Keys class User(Model): # ... class Address(Model): # ... user_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey(User.id))

Typed Relationships class User(Model): # ... addresses: so.Mapped[list['Address']] = so.relationship(back_populates='user') class Address(Model): # ... user_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey(User.id)) user: so.Mapped[User] = so.relationship(back_populates='addresses')

Typed Relationships class User(Model): # ... addresses: so.Mapped[list['Address']] = so.relationship(back_populates='user') class Address(Model): # ... user_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey(User.id)) user: so.Mapped[User] = so.relationship(back_populates='addresses') with so.Session(engine) as session, session.begin(): # ... print(address.user) print(user.addresses) user.addresses.append(Address(...))

Write-Only Relationships class User(Model): # ... class Order(Model): # ... user_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey(User.id))

Write-Only Relationships class User(Model): # ... orders: so.WriteOnlyMapped[ 'Order'] = so.relationship(back_populates= 'user') class Order(Model): # ... user_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey(User.id)) user: so.Mapped[User] = so.relationship(back_populates= 'orders')

Write-Only Relationships class User(Model): # ... orders: so.WriteOnlyMapped[ 'Order'] = so.relationship(back_populates= 'user') class Order(Model): # ... user_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey(User.id)) user: so.Mapped[User] = so.relationship(back_populates= 'orders') with so.Session(engine) as session, session.begin(): # ... user.orders.add(Order(...)) orders = session.scalars(user.orders.select().limit(2)).all()

Backwards Compatibility

Backwards Compatibility ● Code written for legacy SQLAlchemy 1.x releases should work in 2.x with minor changes ● To access new features in 2.x you must upgrade your code to 2.x style ● SQLAlchemy 1.4 is a bridge between the 1.x and 2.x releases ○ Early release of some 2.x features ○ Warnings for features that will be deprecated or removed ● Migration path: 1. Upgrade to latest 1.4.x release, test and fix all issues and warnings 2. Upgrade to latest 2.x release (very minor issues expected, if any) 3. Upgrade legacy code to take advantage of new 2.x features

Asyncio Support

Asyncio Support ● No changes in models or queries ● Use a database driver compatible with Asyncio, for example: ○ aiosqlite for sqlite ○ aiomysql for MySQL ○ asyncpg for Postgres ● Use create_async_engine() instead of create_engine() ● Use AsyncSession instead of Session ● Use await for all blocking database calls ● Use stream() and stream_scalars() instead of execute() and scalars() for async iteration ● Ensure all relationships are preloaded or write-only to avoid implicit I/O

Using Alchemical

Introducing Alchemical ● Reduces boilerplate code to set up SQLAlchemy ● Applies recommended naming conventions to all constraints ● Supports sync and async interfaces ● Integrates nicely with Flask, FastAPI and other frameworks ● Integrates with Alembic for database migration support ● Greatly simplifies managing multiple databases ● Add to your project with pip install alchemical ● Disclaimer: I’m the author

Sync Alchemical Example from alchemical import Model class User(Model): # ...

Sync Alchemical Example from alchemical import Model class User(Model): # ... query = User.select().where(User.name == 'mary')

Sync Alchemical Example from alchemical import Model, Alchemical class User(Model): # ... query = User.select().where(User.name == 'mary') db = Alchemical( 'sqlite:///') db.create_all()

Sync Alchemical Example from alchemical import Model, Alchemical class User(Model): # ... query = User.select().where(User.name == 'mary') db = Alchemical( 'sqlite:///') db.create_all() with db.Session() as session: user = session.scalar(query)

Async Alchemical Example from alchemical.aio import Model, Alchemical class User(Model): # ... query = User.select().where(User.name == 'mary') async def main(): db = Alchemical( 'sqlite:///') await db.create_all() async with db.Session() as session: user = await session.scalar(query) asyncio.run(main())

Examples For simple examples of SQLAlchemy 2, see: https://github.com/miguelgrinberg/retrofun

Real-World Example For a non-trivial example using SQLAlchemy 2, Flask, Alchemical, Alembic and OpenAPI documentation, see: https://github.com/miguelgrinberg/microblog-api

Thanks! Questions?