Understanding the Relationship Between FastAPI, SQLAlchemy, ORM, and Database Connections

FastAPI is taking the Python web development world by storm specially for developer who wants to develop AI based application. To master it, we need a solid understanding of the core components that make it so fast and popular. This article dives into the essential, fundamental concepts—the must-learn connectors—every developer needs to know to build robust applications with FastAPI. This is mine journey of learning fastapi. This is our first tutorial notes on FASTAPI.

🧠 1. What Is SQLAlchemy?

SQLAlchemy is a powerful Python library that provides a consistent way to interact with relational databases such as:

• PostgreSQL
• MySQL
• SQLite
• SQL Server

It acts as an abstraction layer or Interface kind of thing — meaning, your code doesn’t care which database engine is used. You just change the database URL, and SQLAlchemy takes care of the rest. Example:

from sqlalchemy import create_engine

# Connect to PostgreSQL
engine = create_engine("postgresql://user:password@localhost/mydb")

# Change to SQLite without touching other code
# engine = create_engine("sqlite:///./test.db")

✅ You use the same API for any relational database. That’s why SQLAlchemy is called an abstraction layer — it hides DB-specific complexity behind a common Python interface.

🧩 2. SQLAlchemy ORM — The Object-Relational Mapper

ORM stands for Object-Relational Mapping. It allows you to treat database tables like Python classes and rows like Python objects. Example (models.py):

from sqlalchemy import Column, Integer, String, Float
from database import Base

class Product(Base):
    __tablename__ = "products"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String)
    description = Column(String)
    price = Column(Float)

Instead of writing SQL queries like:

SELECT * FROM products WHERE id = 1;

You can write in Python:

product = db.query(Product).filter(Product.id == 1).first()

✅ The ORM automatically translates this Python query into SQL.
✅ This improves readability, security, and portability.

Where the from database import Base came from ? So, it’s imported from your local database.py file, not directly from SQLAlchemy. But inside that database.py, you’ll find something like this:

from sqlalchemy.ext.declarative import declarative_base.

Base = declarative_base()

⚙️ What Does declarative_base() Do?
The declarative_base() is a SQLAlchemy function that creates a base class for all your ORM models. Think of it like this: It’s the parent class that tells SQLAlchemy — “Hey, any class that inherits from me is a table in the database.” So when you write:

class Product(Base):
    __tablename__ = "products"
    id = Column(Integer, primary_key=True, index=True)

✅ SQLAlchemy automatically understands:

• “This is a table named products.”
• “These are its columns and types.”

Component	Description
declarative_base()	SQLAlchemy function that returns a Base class
Base	Your app’s root class for all ORM models
Product(Base)	Inheriting from Base registers this model as a database table

⚙️ 3. What Is create_engine()?

create_engine() is the core entry point to the database. It creates a connection engine, which:

• Knows how to talk to your specific database (PostgreSQL, SQLite, etc.)
• Manages the connection pool
• Is created once when your app starts

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql://user:password@localhost/mydb",
    pool_size=10,
    max_overflow=20,
)

🏊‍♂️ 4. Understanding Connection Pools

A connection pool is a collection of ready-to-use database connections managed by SQLAlchemy.

❌ Without Pooling

Every API request would:

1. Open a new database connection
2. Authenticate
3. Run a query
4. Close the connection

That’s very slow (opening a DB connection can take hundreds of milliseconds).

✅ With Pooling

When your app starts:

• SQLAlchemy opens a few database connections (e.g., 5–10)
• Keeps them alive in memory
  Each API request:
• Borrows a connection from the pool
• Runs its query
• Returns the connection to the pool when done

So your app reuses connections instead of creating new ones every time — this makes it faster and more scalable.

🔄 5. How FastAPI Manages Sessions per Request

In a FastAPI app, each API request gets its own SQLAlchemy session, created from a global SessionLocal.

from sqlalchemy.orm import sessionmaker

SessionLocal = sessionmaker(bind=engine)

In main.py, you define a dependency:

from fastapi import Depends

def get_db():
    db = SessionLocal()  # get a session (borrows connection)
    try:
        yield db
    finally:
        db.close()  # return connection to pool

Each route uses it:

@app.get("/products")
def get_products(db: Session = Depends(get_db)):
    return db.query(Product).all()

✅ Each API request → gets its own independent session
✅ Each session → borrows a connection from the shared pool
✅ When done → connection is returned to pool for reuse

🧱 6. Why Do We Need Connection Pools?

Without Pool	With Pool
Each API call opens a new DB connection	Reuses existing connections
Slow performance	Fast, low latency
High DB load	Efficient use of resources
Risk of hitting DB connection limit	Stable, controlled concurrency

💡 In high-traffic APIs, pooling can make the difference between handling 100 requests/second vs. 5 requests/second.

⚡ 7. Summary of Relationships

Component	Purpose
SQLAlchemy	Abstraction layer for relational DBs
ORM	Maps Python classes to database tables
create_engine()	Creates engine + connection pool
Session	Temporary workspace that borrows a connection
Connection Pool	Keeps connections ready for reuse
FastAPI Request	Creates a new session for each request, which uses the pool

---

🧩 8. The Full Flow (Visual Diagram)

Here’s a simplified diagram showing how these pieces connect:

+------------------+
|  API Request     |
+--------+---------+
         |
         v
+------------------+
|  get_db()       |  ← creates session
+--------+---------+
         |
         v
+------------------+
|  SessionLocal()  |  ← borrows connection from pool
+--------+---------+
         |
         v
+------------------+
|  Connection Pool |  ← created & managed by SQLAlchemy engine
+--------+---------+
         |
         v
+------------------+
|   Database       |
+------------------+

---

Would you like me to generate this as a high-quality image diagram (with colors, arrows, and labels) that you can save and reuse in your notes?

🧩 9. Understanding SessionLocal, Session, and Connection Pools

When using SQLAlchemy with FastAPI, database communication happens through three layers:

create_engine() → SessionLocal() → Session (per request)

⚙️ 1. create_engine() — Global Engine & Connection Pool

create_engine() creates the engine that knows how to talk to the database and also initializes a global connection pool.
This pool keeps a small number of active database connections ready for reuse.

engine = create_engine(
    "postgresql://user:password@localhost/mydb",
    pool_size=10,
    max_overflow=20
)

---

🧰 2. SessionLocal — The Session Factory

✅ SessionLocal is not an actual session — it’s a session factory (a kind of constructor or blueprint) created using sessionmaker(). You use it to create new Session objects whenever needed (for each API request). It doesn’t connect to the database directly — it just knows how to create a session that connects using the existing engine (and therefore, the existing connection pool).

SessionLocal is a factory that produces individual Session objects. It’s bound to the global engine (and thus to its connection pool):

SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False)

It doesn’t hold connections itself — it just defines how sessions are created.

---

🔄 3. Session — Per-Request Database Session

The SQLAlchemy session exists only for database operations — things like reading, writing, updating, or deleting records.

✅ When a session is used

• When your API endpoint queries data from the DB (e.g., db.query(models.Todo).all())
• When you add, update, or delete ORM objects
• When FastAPI uses Depends(get_db) to inject the session

❌ When a session is NOT needed

• When serving static files (like HTML, CSS, or images)
• When returning computed values or text responses
• When calling external APIs (like fetching weather data)
• When doing authentication/token verification (if stored outside DB)

So — you only create a session when your route talks to the database.

FastAPI is smart: it doesn’t automatically use a session for all requests — you control it with Depends(get_db) only in routes that need it. Each API request gets its own Session, created by calling SessionLocal() inside a dependency:

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

• Each Session borrows a single connection from the shared pool.
• Once the request completes, the session is closed and the connection is returned to the pool.

SessionLocal → (factory)
Session = SessionLocal() → (actual session that borrows connection from pool)

Think of it like:

🧱 SessionLocal = class definition
🧍 SessionLocal() = creating an object (session instance)

⚙️ sessionmaker() — The Function That Creates the Factory

sessionmaker() is a SQLAlchemy helper function that returns a customized Session class with your preferred settings.

Example:

from sqlalchemy.orm import sessionmaker

SessionLocal = sessionmaker(
    bind=engine,
    autoflush=False,
    autocommit=False
)

🔍 Explanation of Parameters

Parameter	Meaning
bind=engine	Tells the session which database engine (and its connection pool) to use. Without it, the session wouldn’t know where to connect.
autoflush=False	Prevents automatic flushing (sending pending changes to DB) before every query. You control when to flush/commit — gives more predictability.
autocommit=False	Ensures SQLAlchemy uses transaction mode — you must explicitly call db.commit(). This avoids accidental partial saves.

🧠 In short:

SessionLocal = sessionmaker(bind=engine, ...)
→ creates a factory that knows how to build new Sessions
→ each Session borrows a connection from the engine’s pool,
→ runs queries, commits/rolls back, then returns the connection when closed.

---

🔍 Summary

Component	Scope	Purpose
create_engine()	Global	Creates engine and manages the connection pool
Connection Pool	Global (inside engine)	Holds a set of open database connections
SessionLocal	Global	Factory for creating new Session objects
Session	Per request	Borrows and returns connections from the pool

In short:

1. The engine owns the connection pool.
2. SessionLocal is a global session factory.
3. Each API request creates its own temporary Session, which borrows a connection from the shared pool.