SQLAlchemy Architecture — Notes from a Beginner Learning Backend

Akash — Sat, 17 Jan 2026 07:17:15 +0000

I am still new to backend development and databases. While trying to understand SQLAlchemy, I wrote these notes in simple words so I don’t fool myself into thinking I understand more than I actually do. This is not a guide. It is a learning reflection.

What SQLAlchemy Is (in simple terms)

SQLAlchemy is a Python library used to work with relational databases.

At first, I thought ORM means “no SQL”. SQLAlchemy proved that idea wrong.

With SQLAlchemy:

SQL is still important
Tables and relations still matter
Python objects are optional, not mandatory

It does not hide the database. It exposes it carefully.

The Real Issue with “Database Abstraction”

Many tools try to make databases feel invisible:

No SQL
No schema thinking
No joins or constraints visible

This feels comfortable but breaks reality.

Relational databases are powerful because they are strict:

Schemas exist for a reason
Relations are explicit
SQL has rules

Objects want flexibility. Databases demand accuracy. This conflict always exists.

SQLAlchemy does not try to escape this conflict.

SQLAlchemy’s Core Philosophy

SQLAlchemy does not try to protect the developer from the database.

Its thinking:

You must understand relations
You must see SQL
Automation should reduce repetition, not thinking

That is why SQLAlchemy calls itself a toolkit, not a framework.

It allows abstraction to leak:

You can see what’s happening
You can control behavior
Nothing is forced or hidden

Core vs ORM (This Took Time to Click)

SQLAlchemy is split into two layers.

Core

Core is the foundation.

It handles:

Database driver communication
SQL construction
Table and column definitions
Connections and execution

Important points:

Core works without ORM
Core stays close to SQL
Core is always available

This cleared my confusion that SQLAlchemy equals ORM.

ORM

ORM is built on top of Core.

It adds:

Mapping Python classes to tables
Identity tracking
Change tracking and persistence

Key idea:

ORM is optional
ORM never blocks access to Core
You can drop down anytime

This separation feels honest.

Cost of This Design

Because everything is layered:

Many function calls happen
Python function calls are slow

SQLAlchemy reduces this by:

Optimizing hot paths
Inlining logic
Using C extensions where needed

The design stays clean instead of being rewritten in low-level code.

Why DBAPI Alone Is Not Enough

DBAPI is only a loose specification.

Different drivers behave differently:

Unicode handling differs
Binary handling differs
Parameter styles differ
Getting last inserted ID differs

This inconsistency is dangerous.

SQLAlchemy exists to normalize these differences.

Engine, Connection, Result (Mental Model)

SQLAlchemy wraps raw database access into clear layers.

Engine
Entry point. Knows which database and driver to use.
Connection
Represents one actual database connection.
Result
Wraps returned rows and metadata cleanly.

Direct DBAPI usage disappears.

Dialect (The Key Concept)

Dialect was the hardest idea to understand.

A dialect defines:

How SQL should look for a database
Driver-specific behavior
Type conversions
Parameter formatting

Each dialect targets:

One database
One driver

This explains how SQLAlchemy supports many databases cleanly.

ExecutionContext

Every time a query runs:

A short-lived execution object is created
Runtime details are handled there
SQL, parameters, and results are connected

Dialect defines rules.
ExecutionContext applies them at runtime.

Handling Drivers That Support Many Databases

Some drivers talk to many databases.

SQLAlchemy handles this by:

Sharing common behavior
Adding database-specific logic only where required

No duplication. No guessing.

Final Understanding

The main thing I learned:

Relational databases are strict by nature.
SQLAlchemy does not pretend otherwise.

Instead, it:

Makes rules visible
Automates repetitive work
Keeps control with the developer
Scales from raw SQL to ORM smoothly

This design feels realistic, not magical.

DEV Community: Akash