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.