Database Integration (SQLAlchemy)

🧑‍🏫 Sabse pehle — simple mein samjho#

FastAPI kisi ek specific database pe depend nahi karta (unlike Django jo apna ORM leke aata hai). Tumhari marzi hai tum MongoDB (Motor) use karo ya Postgres. Lekin FastAPI ke sath standard combination SQLAlchemy ka hai. SQLAlchemy Python ka sabse powerful SQL ORM hai. Tum tables ko Python classes ke roop mein define karte ho, aur SQLAlchemy unhe real database tables mein badal deta hai. Schema changes handle karne ke liye hum Alembic use karte hain (jise migrations kehte hain).

1. Setting up the Connection (database.py)#

First, we create the engine that communicates with the database and a session maker.

from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base, sessionmaker

# Example for SQLite (For Postgres, use: postgresql://user:password@localhost/dbname)
SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db"

# The Engine handles the actual connection to the DB
engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False} # Only needed for SQLite
)

# The SessionLocal will be instantiated per request (via Dependency Injection)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# Base class for our models to inherit from
Base = declarative_base()

2. Defining Models (models.py)#

Here we define the actual shape of the SQL tables. Do not confuse this with Pydantic!

• SQLAlchemy Models: Determine how data is stored in the database.
• Pydantic Models: Determine how data is validated and serialized in the API request/response.

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

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String, unique=True, index=True)
    hashed_password = Column(String)

3. Dependency Injection in Routes (main.py)#

We use the yield dependency pattern discussed earlier to give each route its own independent database session.

from fastapi import FastAPI, Depends
from sqlalchemy.orm import Session
import models, database

# This command creates the tables in the DB (usually replaced by Alembic in production)
models.Base.metadata.create_all(bind=database.engine)

app = FastAPI()

# Dependency to get DB session
def get_db():
    db = database.SessionLocal()
    try:
        yield db
    finally:
        db.close()

@app.get("/users/")
def get_users(db: Session = Depends(get_db)):
    # Query the database using SQLAlchemy
    users = db.query(models.User).all()
    return users

Migrations with Alembic#

In development, models.Base.metadata.create_all() works fine. But if you add a new column to a table later, that command won't update the existing table.

For production, you use Alembic. It generates a history of files (migrations) tracking every change made to your models (just like git commit), and applies those changes to the live database cleanly without losing data.