FastAPI Connecting to a SQL Database

Most real APIs store and retrieve data from a database. SQLAlchemy is the most popular Python library for working with SQL databases. It lets you write Python code instead of raw SQL for most operations, and it works with SQLite, PostgreSQL, MySQL, and other databases.

Install the Required Packages

pip install sqlalchemy

# For PostgreSQL, also install:
pip install psycopg2-binary

# For MySQL, also install:
pip install pymysql

SQLite needs no extra package — Python includes it by default. It is a great choice during development and testing.

The Database Connection Flow

FastAPI App
    │
    │ creates
    ▼
Engine  ← knows the database URL and manages the connection pool
    │
    │ creates
    ▼
Session ← a single conversation with the database
    │
    │ used inside
    ▼
Route Function ← runs queries, commits changes

Setting Up database.py

# database.py

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

DATABASE_URL = "sqlite:///./myapp.db"
# For PostgreSQL: "postgresql://user:password@localhost/dbname"

engine = create_engine(
    DATABASE_URL,
    connect_args={"check_same_thread": False}  ← SQLite only
)

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

Base = declarative_base()

What Each Part Does

DATABASE_URL  → tells SQLAlchemy where the database is
engine        → the connection pool (reuses connections efficiently)
SessionLocal  → a factory that creates new session objects
Base          → the parent class for all your database models

Creating a Session Dependency

Each request gets its own database session. The session opens at the start of the request and closes after the response is sent — even if an error occurs:

from database import SessionLocal

def get_db():
    db = SessionLocal()
    try:
        yield db       ← request runs here
    finally:
        db.close()     ← always closes, even on error

Using yield makes this a generator-based dependency. FastAPI runs the code before yield before the route, and the code after yield after the route finishes.

Using the Session in a Route

from fastapi import Depends
from sqlalchemy.orm import Session
from database import get_db

@app.get("/ping-db")
def ping_database(db: Session = Depends(get_db)):
    db.execute("SELECT 1")
    return {"database": "connected"}

Connection Pool Visualization

Engine manages a pool of connections:

  Connection 1 ──→ Request A (session)
  Connection 2 ──→ Request B (session)
  Connection 3 ──→ Request C (session)

When a request finishes:
  session closed → connection returned to pool
  next request reuses the same connection

Connection pooling means your app does not open a new database connection for every request. Opening connections is expensive. Reusing them is fast.

Supported Databases and Their URLs

Database      URL Format
─────────────────────────────────────────────────────
SQLite        sqlite:///./filename.db
PostgreSQL    postgresql://user:pass@host/dbname
MySQL         mysql+pymysql://user:pass@host/dbname

Environment Variables for Database URLs

Never hard-code passwords in your source code. Read the URL from an environment variable:

import os

DATABASE_URL = os.getenv("DATABASE_URL", "sqlite:///./dev.db")

Set the real URL in your server environment or in a .env file that is never committed to version control.

Key Points

• SQLAlchemy works with SQLite, PostgreSQL, MySQL, and more using a URL string.
• The engine manages the connection pool. The Session executes queries.
• Use a get_db() generator dependency to open and close sessions per request.
• Connection pooling reuses database connections for better performance.
• Store your database URL in an environment variable — never in source code.