PyPIDataupdated 19 Sept 2026

sqlalchemy review

Q: Does sqlalchemy install cleanly?

Yes. In a fresh container with an empty cache, pip install sqlalchemy finished in 0.6s, leaving 3 packages and 16 MB on disk. pip-audit reported no known vulnerabilities.

SQLAlchemy 2.0.52 is both a Python SQL expression toolkit and an object-relational mapper. Core supplies Engines, pools, transactions, dialects, schema metadata, and composable SQL. The ORM adds typed declarative models, identity maps, relationships, loading strategies, and Session-based units of work. Database drivers and schema migrations are separate choices. The current patch adds Python 3.15 support and fixes concurrent UPDATE returning-column alignment, stuck Sessions after failed bulk work, cross-process unpickling of wildcard loader options, several quoting paths, and costly SQLite reflection.

sqlalchemy · v2.0.54 · 74.9M/wk · ★ 12,166 · pushed 15 Sept 2026 · sqlalchemy/sqlalchemy · docs · reviewed by Keyoor

Verdict

SQLAlchemy 2.0.52 installed in 0.6 seconds and occupied 16 MB across 3 packages in our sandbox, with typed metadata and 0 audit findings. Install it when a team needs both SQL composition and a disciplined ORM; choose a smaller layer if nobody will own Session lifetime, loading policy, and migrations.

We installed it

uv pip install sqlalchemy (v2.0.52) on 22 Aug 2026, fresh sandbox (3 CPUs, 8 GB, Python 3.12, empty cache). How we test →

Lab card: what happened when we installed sqlalchemy

Install	✓ · 0.6s	3 packages on disk · 16 MB
Import	✓	import sqlalchemy in 0.69s · compiled extensions · py.typed · requires Python >=3.7
Known vulns	0	(pip-audit)

Answers from our run

Does sqlalchemy install cleanly?

Yes. In a fresh container with an empty cache, pip install sqlalchemy finished in 0.6s, leaving 3 packages and 16 MB on disk. pip-audit reported no known vulnerabilities.

What does sqlalchemy need to run?

Python >=3.7, and a platform wheel with compiled extensions. In our run import sqlalchemy succeeded in 0.69s, and the package ships py.typed for type checkers.

sqlalchemy or sqlmodel: which should you use?

sqlmodel: Choose it for Pydantic-style typed models over SQLAlchemy in a smaller application. SQLAlchemy 2.0.52 installed in 0.6 seconds and occupied 16 MB across 3 packages in our sandbox, with typed metadata and 0 audit findings.

When should you not use sqlalchemy?

A small script only runs a few SQLite statements; the standard sqlite3 module has less state and setup

API stability4/5The 2.0 API has a consistent select, Engine, Connection, Result, DeclarativeBase, Mapped, and Session model. Moving from legacy Query and implicit transaction patterns was a real migration, and 2.1 deprecations are already documented alongside 2.0 fixes. Applications that stay on public 2.0 constructs and pin their database driver get a steadier surface than code built around legacy coercions or dialect internals.

Docs5/5The official 2.0 documentation has unified Core and ORM tutorials plus deep references for engines, pools, transactions, mappings, relationships, loaders, asyncio, events, types, and individual dialects. Migration material explains the 1.4 to 2.0 behavioral shifts. The documentation is unusually precise about Session state and emitted SQL, though its size means developers must follow a focused path rather than browse randomly.

Maintenance5/5GitHub reported an unarchived repository pushed on 2026-08-24 with 209 open issues and pull requests. Version 2.0.52 shipped on 2026-08-11 and fixes Python 3.15 compatibility, ORM concurrency, failed bulk-state cleanup, multiprocessing unpickling, quoting, reflection, and dialect behavior. Detailed release notes and concurrent 2.1 beta work show sustained maintenance across the database matrix.

Ecosystem5/5The measured weekly count was 96,267,323 downloads, and GitHub showed 12,109 stars. Alembic supplies migrations, frameworks integrate Session management, and separate dialects and DBAPI drivers cover major relational databases in sync and async modes. SQLModel builds on this layer rather than replacing it. The breadth is valuable, while each chosen driver still brings its own release and deployment risks.

Discussed on

Use it if

One service needs composable SQL against a supported relational database without giving up direct SQL control
Domain objects benefit from relationship loading, identity tracking, and a transaction-scoped unit of work
The codebase wants Core queries and ORM mappings over the same Engine and dialect layer
Sync and async applications need related APIs while retaining an explicit driver choice

Skip it if

A small script only runs a few SQLite statements; the standard sqlite3 module has less state and setup
The team will not define Session lifetime, rollback rules, and relationship loading policy
Migrations are expected inside the ORM package; production schema history requires Alembic or another tool
The project wants a narrow SQL-first builder and no identity map or unit-of-work behavior
Async code cannot prevent implicit relationship I/O; unloaded attributes are a common source of unexpected awaits and errors

Setup reality

Our Python 3.12 sandbox installed SQLAlchemy 2.0.52 in 0.6 seconds. Three packages occupied 16 MB, and pip-audit found 0 known vulnerabilities. Inspection reported 32 direct dependencies, a Python 3.7 minimum, MIT licensing, compiled .so extensions, and py.typed metadata. import sqlalchemy completed in 0.69 seconds. A database driver such as psycopg, asyncpg, or a MySQL client is still a separate installation and compatibility decision.

Create one Engine for each database configuration and reuse it. The Engine owns the connection pool, though construction does not eagerly open every connection. A URL selects both dialect and driver. Credentials containing special characters should go through URL.create() or correct URL escaping. Pool size, timeout, pre-ping, and recycle settings depend on the database, proxy, and worker count; copied defaults can exhaust a small server or retain dead connections.

A Session is a mutable transaction and identity-map boundary. Open it per request or job, commit deliberately, roll back failures, and close it. Commit expires attributes by default, so later access may emit SQL. Lazy relationships can cause N+1 queries or fail after the Session closes. Pick selectinload, joinedload, or raiseload based on cardinality and verify query counts against the real database. create_all creates missing objects but records no migration history.

AsyncEngine needs an async DBAPI driver and AsyncSession; it cannot turn a synchronous driver into nonblocking I/O. Avoid implicit lazy loads in async code and close sessions on cancellation. Version 2.0.52 fixes ORM UPDATE result-column mix-ups most visible under concurrency and resets the flushing flag after failed legacy bulk calls. It also makes wildcard loader state safe to unpickle under spawn and forkserver. Those fixes warrant an upgrade for worker-heavy services.

Patterns

Execute a bound statement through an Engine engine-connect

from sqlalchemy import create_engine, text

engine = create_engine("postgresql+psycopg2://user:pass@localhost/mydb")

with engine.connect() as conn:
    result = conn.execute(text("SELECT 1"))
    print(result.scalar())

Keep one Engine per database configuration. Bound parameters belong in text() rather than string interpolation.

Declare typed ORM attributes declare-models

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)
    email: Mapped[str] = mapped_column(unique=True)
    name: Mapped[str | None]

Mapped annotations inform both mapping and type checking. Python Optional types should agree with database nullability.

Create missing tables in a prototype create-tables

Base.metadata.create_all(engine)

create_all has no ordered migration history or downgrade path. Use Alembic for reviewed production schema changes.

Commit one unit of work insert-session

from sqlalchemy.orm import Session

with Session(engine) as session:
    user = User(email="a@b.com", name="Ada")
    session.add(user)
    session.commit()
    print(user.id)  # populated after commit

The context closes the Session. Commit normally expires loaded attributes, and reading one afterward may issue another query.

Return mapped rows from a select select-where

from sqlalchemy import select

with Session(engine) as session:
    stmt = (
        select(User)
        .where(User.email.like("%@b.com"))
        .order_by(User.id)
        .limit(10)
    )
    users = session.scalars(stmt).all()

session.scalars unwraps ORM objects from Result rows. Add deterministic ordering before applying a limit.

Connect both sides of a relationship relationships

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    author: Mapped["User"] = relationship(back_populates="posts")

# on User:
# posts: Mapped[list["Post"]] = relationship(back_populates="author")

back_populates synchronizes Python-side attributes. Foreign keys and database delete rules remain separate schema choices.

Preload a collection with selectinload eager-loading

from sqlalchemy.orm import selectinload

stmt = select(User).options(selectinload(User.posts))
users = session.scalars(stmt).all()
for u in users:
    print(u.name, len(u.posts))  # no extra queries

selectinload sends another SELECT for related rows and often suits collections. Assert query count in representative tests.

Apply set-based update and delete statements update-delete

from sqlalchemy import update, delete

with Session(engine) as session:
    session.execute(
        update(User).where(User.name == None).values(name="unknown")
    )
    session.execute(delete(Post).where(Post.user_id == 42))
    session.commit()

Bulk DML bypasses normal per-object changes and may leave objects already present in the Session out of date.

Roll back a failed transaction rollback-on-error

from sqlalchemy.orm import Session

with Session(engine) as session:
    try:
        session.add(User(email=email))
        session.commit()
    except:
        session.rollback()
        raise

A failed flush leaves the Session transaction unusable until rollback. Re-raise the original exception after cleanup.

Run a query with AsyncSession use-async-session

from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
from sqlalchemy import select

engine = create_async_engine("postgresql+asyncpg://user:pass@localhost/app")
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)

async with SessionLocal() as session:
    users = (await session.scalars(select(User))).all()

The URL must select an async driver such as asyncpg. Avoid unloaded relationship access that would trigger implicit I/O.

Join-load a collection and deduplicate rows join-load-collection

from sqlalchemy.orm import joinedload

stmt = select(User).options(joinedload(User.posts))
users = session.scalars(stmt).unique().all()

A joined collection repeats parent rows in SQL. unique() is required before materializing ORM parents from this result.

Lock a selected row inside a transaction lock-row-for-update

with Session(engine) as session, session.begin():
    job = session.scalar(
        select(Job)
        .where(Job.id == job_id)
        .with_for_update()
    )
    job.state = "running"

Lock syntax and wait behavior differ by database. Keep the transaction short and test competing workers against the production dialect.

Alternatives

Package	Registry	Pick it when
sqlmodel	PyPI	Choose it for Pydantic-style typed models over SQLAlchemy in a smaller application.
peewee	PyPI	Choose it for a compact active-record-like ORM with a narrower learning surface.
django	PyPI	Use Django ORM when the application already adopts Django migrations, admin, and request stack.

More data guides

numpy · fsspec · pandas · pyarrow · s3fs · lxml · the whole shelf →

How this guide is made: grounded in the library's documentation, release notes, changelog, and issue history, on a fixed rubric — not a hands-on install of every release. The 50 most-downloaded entries are additionally install-verified in clean containers. Corrections: contact the desk.