sqlalchemy
SQLAlchemy is Python's standard database toolkit, and it is really two libraries. Core is a SQL expression language plus engine layer: connection pooling, dialects for every major database, and Python objects that compose into real SQL. The ORM sits on top with the unit-of-work pattern: you map classes to tables, mutate objects, and a Session flushes the changes as SQL inside a transaction. Its philosophy is that an ORM should not hide the relational model; you can drop to raw SQL or Core at any point. Version 2.0 unified the query style around select() and added a typed declarative system with Mapped annotations, plus asyncio support.
The most capable database layer in Python and the safe default outside Django. Budget real time to learn the Session model, or it will feel like the library is fighting you.
Use it if
- You need one data layer that works across PostgreSQL, MySQL, SQLite, SQL Server, and Oracle with the same code
- Your queries outgrow simple CRUD: window functions, CTEs, subqueries, and complex joins are all expressible without falling back to strings
- You want migrations done properly; Alembic is built by the same author and autogenerates from your models
- You use FastAPI or any non-Django stack; it is the assumed ORM in most of that ecosystem
- You are on Django; its built-in ORM is integrated with everything else and running SQLAlchemy alongside it doubles your mental load for little gain
- Your app is a simple script or small service with one database and basic queries; the Session lifecycle, expire-on-commit, and identity map are real learning curve for the value you get
- You want an async-first, minimal-API ORM; the asyncio layer works but rides on greenlet and inherits the same Session rules, and lazy loading raises errors under async unless you plan eager loading
- Your team keeps writing raw SQL anyway; a thin layer like a driver plus dataclasses may serve you better than a half-adopted ORM
Setup reality
pip install sqlalchemy is only step one: it ships no database drivers, so you also pick and install a DBAPI (psycopg2 or psycopg for PostgreSQL, pymysql for MySQL, and so on), each with its own quirks; psycopg2 wants build tools or the -binary package. On common platforms it pulls greenlet, which occasionally lacks wheels on new Python releases. The real cost is conceptual: engine vs connection vs session, autoflush, expire_on_commit, and detached-instance errors all bite newcomers. Migrations mean installing and configuring Alembic separately. The 1.x to 2.0 style change also means a lot of tutorials you will find are outdated.
Patterns
Create an engine and run raw SQLengine-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())Create the engine once per process; it owns the connection pool. Plain strings are not accepted, wrap SQL in text().
Declare typed 2.0-style modelsdeclare-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[str | None] makes the column nullable automatically; the old Column() style still works but new code should not mix them.
Create tables from metadatacreate-tables
Base.metadata.create_all(engine)Fine for tests and toys; real projects should use Alembic migrations because create_all never alters existing tables.
Insert rows with a Sessioninsert-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 commitAfter commit() attributes are expired and reload on next access; set expire_on_commit=False if you read objects after the session closes.
Query with select() and where()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() returns model instances; session.execute() returns Row tuples, which trips up everyone once.
One-to-many relationshiprelationships
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 must be declared on both sides with matching names; the old backref shortcut is discouraged in 2.0.
Avoid N+1 with selectinloadeager-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 queriesDefault loading is lazy (one query per access); selectinload issues one IN query per relationship and is the usual right answer.
Bulk update and deleteupdate-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()These run as single SQL statements and skip ORM events and in-session objects; use session.get() plus attribute mutation when you need those.
Fetch by primary keyget-by-pk
user = session.get(User, 1)
if user is None:
raise ValueError("not found")session.get() checks the identity map first, so it can return a cached instance without hitting the database.
Raw SQL with bound parametersraw-sql-params
from sqlalchemy import text
with engine.connect() as conn:
rows = conn.execute(
text("SELECT id, email FROM users WHERE email = :email"),
{"email": "a@b.com"},
).all()Always use :named parameters; f-string interpolation into text() is the classic injection hole.
Async engine and sessionasync-engine
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
engine = create_async_engine("postgresql+asyncpg://user:pass@localhost/mydb")
async def main():
async with AsyncSession(engine) as session:
users = (await session.scalars(select(User))).all()Requires an async driver (asyncpg, aiosqlite) and greenlet; lazy loads raise MissingGreenlet under async, so eager-load everything you touch.
Commit-or-rollback transaction blocktransaction-block
with Session(engine) as session:
with session.begin():
session.add(User(email="x@y.com"))
session.add(User(email="y@z.com"))
# committed here, rolled back on exceptionsession.begin() removes the need to call commit() manually and guarantees rollback on error.
Alternatives
| Package | Registry | Pick it when |
|---|---|---|
| django | PyPI | You are building on Django anyway; its ORM plus migrations are built in and integrated |
| peewee | PyPI | You want a small, readable ORM for simple apps and are fine with fewer databases and features |
| sqlmodel | PyPI | You use FastAPI and want Pydantic models and SQLAlchemy tables defined once; it is SQLAlchemy underneath |
| tortoise-orm | PyPI | You want an async-native ORM with a Django-like query API |