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.
Top comments (0)