SQLAlchemy Basics: ORM and Core

June 7, 2026 · 9 min read ·Updated June 7, 2026 ·intermediate

pythonsqlalchemyormdatabasedata

What SQLAlchemy actually is

SQLAlchemy is a Python SQL toolkit plus an object-relational mapper, organized as two layers that share a single foundation. The lower layer, called Core, is the SQL Expression Language plus a connection and transaction management system. It lets you build tables, generate DDL, and compose select, insert, update, and delete statements as Python objects. The upper layer, the ORM, builds on Core by mapping those Table definitions onto Python classes so you can read and write rows as instances.

Both layers use the same select() function, the same Engine, and the same dialect system that talks to SQLite, PostgreSQL, MySQL, Oracle, SQL Server, and others. You are not forced to pick one. Most applications use Core for bulk work and the ORM for CRUD over domain objects.

Engines and database URLs

Everything starts with sqlalchemy.create_engine. The engine holds a connection pool, a dialect, and the URL you point it at:

from sqlalchemy import create_engine

engine = create_engine("sqlite:///:memory:")
engine = create_engine("sqlite:///app.db")                   # relative file
engine = create_engine("postgresql+psycopg://user:pw@localhost/mydb")
engine = create_engine("mysql+pymysql://user:pw@localhost/mydb")

The URL format is dialect+driver://username:password@host:port/database. SQLite is the odd one out: sqlite:///:memory: gives an in-memory database, and sqlite:///relative/path.db opens a file relative to the current working directory. Four leading slashes mean an absolute path (sqlite:////var/data/app.db).

echo=True logs every emitted SQL statement to stdout. It’s great while you’re learning, painful in production, so leave it off. Create the engine once per process and share it.

The engine is lazy. Calling create_engine does not actually open a connection; that happens the first time you run a query or call engine.connect(). Internally the engine manages a connection pool. For non-SQLite databases the default is QueuePool, which keeps a small set of connections open and lends them out as needed. You can tune the pool with pool_size, max_overflow, and pool_timeout, or swap it out entirely: NullPool disables pooling (handy for short-lived scripts and some serverless setups), StaticPool keeps a single shared connection (useful for SQLite in-memory tests), and AsyncAdaptedQueuePool is what the asyncio extension uses. When your process is shutting down, call engine.dispose() to close out pooled connections cleanly.

The Core: tables, DDL, and the SQL expression language

With Core you describe schema in Python and let MetaData issue DDL for you:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, select

engine = create_engine("sqlite:///:memory:", echo=True)

metadata = MetaData()
users = Table(
    "users",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(30), nullable=False),
)
metadata.create_all(engine)

with engine.begin() as conn:
    conn.execute(users.insert().values(name="ada"))
    conn.execute(users.insert().values(name="alan"))

with engine.connect() as conn:
    for row in conn.execute(select(users)).all():
        print(row)
# output: (1, 'ada')
# output: (2, 'alan')

A few things to notice. First, select(users) builds a SELECT * FROM users statement as a Python object. You can chain .where(), .order_by(), .limit(), and .join() to compose it. Second, engine.begin() opens a connection inside a transaction and commits when the with block exits cleanly. If you use engine.connect() instead, you must call conn.commit() yourself for any write statement; SQLAlchemy 2.0 no longer auto-commits.

The result of conn.execute(select(...)) is a Result of Row objects. Rows are tuple-like (row[0], row[1]) and also indexable by column name (row.name). Iterate with for row in result: or materialize with .all().

MetaData is the container that knows about all your tables. You typically have one per database. Once tables are registered, metadata.create_all(engine) issues CREATE TABLE IF NOT EXISTS for each one, in dependency order. It will not alter existing tables; for schema migrations across the lifetime of an application, reach for Alembic. The expression language is not a string-templating system. Everything is a typed Python object, so you get attribute completion, you cannot mistype a column name without getting an error, and your statements are immune to SQL injection because values are bound, never interpolated.

The ORM: declarative models in 2.0 style

The modern ORM is built around a DeclarativeBase subclass and typed Mapped[] annotations. Each class attribute describes both the Python type and the column:

from __future__ import annotations
from typing import List, Optional
from sqlalchemy import ForeignKey, String, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "user_account"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    fullname: Mapped[Optional[str]]
    addresses: Mapped[List["Address"]] = relationship(
        back_populates="user", cascade="all, delete-orphan"
    )

    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r})"


class Address(Base):
    __tablename__ = "address"
    id: Mapped[int] = mapped_column(primary_key=True)
    email_address: Mapped[str]
    user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
    user: Mapped["User"] = relationship(back_populates="addresses")

    def __repr__(self) -> str:
        return f"Address(id={self.id!r}, email_address={self.email_address!r})"


engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)

Three things carry the 2.0 style:

Mapped[int] declares both the Python type and the column type. int becomes INTEGER, str becomes VARCHAR, float becomes FLOAT, bool becomes BOOLEAN. Use Mapped[Optional[X]] (or Mapped[X | None]) for nullable columns; the Optional form works on Python 3.9, the | form needs 3.10+ and from __future__ import annotations.
mapped_column(...) accepts a superset of Column arguments: primary_key=True, String(30), ForeignKey("user_account.id"), unique=True, index=True, default=..., server_default=....
relationship(back_populates="...") wires a two-way link between User.addresses and Address.user. The forward references "Address" and "User" are strings because the two classes are still being defined; from __future__ import annotations at the top of the file makes them lazy.

This is a real shift from the 1.4 style. Previously a column looked like id = Column(Integer, primary_key=True) and the Python type lived somewhere else (a comment, a type-checker plugin, or a separate Mapped[int] assignment). With 2.0 the type and the column are the same declaration, so a typed checker like mypy or pyright will flag a stray user.name.uppercase_garbage() because the IDE knows name is a str, not an Optional[str], not an int. It also means the class __init__ is generated for you, so you do not need a custom constructor to set fields. If you want to override that, pass init=False to mapped_column.

Sessions: where ORM meets the database

A Session is a unit-of-work container. It tracks new, dirty, and deleted objects and flushes them to the database when you call commit(). The simplest pattern is to use it as a context manager:

with Session(engine) as session:
    spongebob = User(
        name="spongebob",
        fullname="Spongebob Squarepants",
        addresses=[Address(email_address="[email protected]")],
    )
    patrick = User(name="patrick")
    session.add_all([spongebob, patrick])
    session.commit()

    for user in session.scalars(select(User).order_by(User.id)):
        print(user)
# output: User(id=1, name='spongebob')
# output: User(id=2, name='patrick')

    ada = session.get(User, 1)
    print(ada, ada.addresses)
# output: User(id=1, name='spongebob') [Address(id=1, email_address='[email protected]')]

session.scalars(stmt) returns a ScalarResult that yields ORM objects directly; use it whenever you’re selecting a single entity. session.execute(stmt) returns Row tuples; reach for it when you select multiple entities or specific columns. For primary-key lookups, session.get(User, 1) is the right call: it uses the identity map and skips the SELECT if the object is already loaded.

For web applications, build one factory at startup and call it per request:

from sqlalchemy.orm import sessionmaker

SessionLocal = sessionmaker(bind=engine, expire_on_commit=False)
# In a request handler:
with SessionLocal() as session:
    ...

expire_on_commit=False keeps attribute access working after commit() without an extra SELECT, useful when you’re about to serialize the object into a JSON response.

The unit-of-work pattern is the part that catches new users off guard. When you mutate ada.name = "Ada Lovelace", the session does not issue an UPDATE immediately. It marks the object as dirty and waits for the next flush, which is usually triggered by the next query, by an explicit session.flush(), or by session.commit(). The flush compares the current state of every tracked object to its original loaded state and emits the smallest set of changes needed: an UPDATE for ada, an INSERT for any newly added Address, a DELETE for any object you removed. That batching is what makes the ORM fast for typical CRUD. If you want to see the SQL, set echo=True on the engine, or wire event.listen(engine, "before_execute", ...) to log statements as they go out.

When to reach for Core vs the ORM

The two layers are not in opposition. The same select(User).where(...) runs against a Connection or a Session; both generate the same SQL. Use the ORM when you have a stable domain model, want identity tracking, and care about relationship traversal. Use Core for bulk operations, reporting queries that span many tables, dynamic schemas, or anywhere the unit-of-work pattern is more overhead than it’s worth.

The two mix freely. You can issue a Core insert(users) from a script and a few minutes later run an ORM session.scalars(select(User)) against the same engine against the same MetaData. That shared foundation is why SQLAlchemy is more than a database driver.

Common Pitfalls

Forgetting conn.commit(). A 2.0 engine.connect() does not auto-commit writes. Either use engine.begin() as a context manager or call conn.commit() explicitly. Without it, your INSERT runs in an open transaction that rolls back on connection close.

Mixing the legacy Query API. session.query(User).filter_by(name="x") still works in 2.0 but raises a LegacyAPIWarning. Use select(User).where(User.name == "x") and pass it to session.execute(...) or session.scalars(...).

Lazy loads after the session closes. Accessing user.addresses after the with Session(engine) as session: block raises DetachedInstanceError. Either keep the session open, set expire_on_commit=False, or load relationships eagerly with selectinload() or joinedload().

N+1 queries. Iterating for u in session.scalars(select(User)): print(u.addresses) issues one SELECT per user. Load all addresses in one round trip with select(User).options(selectinload(User.addresses)).

Bulk inserts through the ORM are slow. session.add_all(list_of_10k_dicts) emits one INSERT per object. For large batches use Core: conn.execute(insert(users), list_of_dicts). SQLAlchemy 2.0’s insertmanyvalues optimization applies to that path automatically. A rough rule of thumb: switch to Core’s bulk insert once you cross a few thousand rows, or once you measure a noticeable flush time.

Don’t share a Session across threads. Session is not thread-safe. Build one per thread, or wrap sessionmaker in scoped_session to manage thread-local instances for you.

Comparing to None with == or !=. Inside the ORM, User.name == None compiles to IS NULL correctly, but User.name != None reads as “is not the None object” if the expression is ever evaluated outside a statement. Reach for User.name.is_(None) and User.name.is_not(None) to stay explicit and to avoid surprises when the same logic is used in regular Python code.