5.3 ORM

FastAPI does not come with a built-in ORM, but it works well with any ORM of your choice.

1. Common ORMs Used with FastAPI:

1. SQLAlchemy (most popular)

Mature, flexible, widely used in Python.
FastAPI works great with both:
- SQLAlchemy Core (manual SQL-style queries)
- SQLAlchemy ORM (class-based models and queries)

2. Tortoise ORM

Async-native ORM.
Simple and easy to use.
Good fit if your entire FastAPI app is async.

3. Gino

Async ORM designed for PostgreSQL.
Lightweight, with a similar API to SQLAlchemy.

4. Pony ORM

Unique syntax using Python generators.
Less common, but can be used with FastAPI.

2. Example with SQLAlchemy:

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

DATABASE_URL = "sqlite:///./test.db"

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(bind=engine)
Base = declarative_base()

# Define model
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)

FastAPI itself doesn’t manage the database layer — you manually create the session, models, and dependency injection.

====================================================

Here’s a complete working example of using FastAPI with SQLAlchemy based on your model. This includes:

Database setup
Model definition
Dependency injection
Create/read endpoints

📁 Project Structure (flat, simple)

main.py
test.db (auto-created by SQLite)

🐍 `main.py`

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, Session
from pydantic import BaseModel

# -------------------------------
# Database setup
# -------------------------------
DATABASE_URL = "sqlite:///./test.db"

engine = create_engine(DATABASE_URL, connect_args={"check_same_thread": False})
SessionLocal = sessionmaker(bind=engine, autocommit=False, autoflush=False)
Base = declarative_base()

# -------------------------------
# SQLAlchemy model
# -------------------------------
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)

# Create the table(s)
Base.metadata.create_all(bind=engine)

# -------------------------------
# Pydantic schemas
# -------------------------------
class UserCreate(BaseModel):
    name: str

class UserOut(BaseModel):
    id: int
    name: str

    class Config:
        orm_mode = True

# -------------------------------
# FastAPI app
# -------------------------------
app = FastAPI()

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

# -------------------------------
# Routes
# -------------------------------

# Create a new user
@app.post("/users/", response_model=UserOut)
def create_user(user: UserCreate, db: Session = Depends(get_db)):
    db_user = User(name=user.name)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user

# Get a user by ID
@app.get("/users/{user_id}", response_model=UserOut)
def read_user(user_id: int, db: Session = Depends(get_db)):
    user = db.query(User).filter(User.id == user_id).first()
    if user is None:
        raise HTTPException(status_code=404, detail="User not found")
    return user

# Get all users
@app.get("/users/", response_model=list[UserOut])
def read_users(db: Session = Depends(get_db)):
    return db.query(User).all()

▶️ Run the app:

uvicorn main:app --reload

🔍 Access the API Docs

Go to: http://127.0.0.1:8000/docs

FastAPI will generate interactive Swagger docs automatically!

📌 Summary

This example shows:

How to use SQLAlchemy with FastAPI.
How to define models and schemas.
How to connect routes with database operations using dependency injection.

Let me know if you want this with async support or with PostgreSQL instead.

=========================================

Sure! Let’s go through the FastAPI + SQLAlchemy code line by line and explain every section in detail. This will help you understand how it all fits together to create a fully working REST API.

🔧 1. Imports

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, Session
from pydantic import BaseModel

FastAPI: The main class for creating the API app.
Depends: Used for dependency injection.
HTTPException: Used to raise errors with HTTP status codes.
Column, Integer, String: SQLAlchemy column types.
create_engine: Creates the database engine.
declarative_base: Used to define the base class for models.
sessionmaker: Creates database sessions.
Session: Type for database session.
BaseModel: Pydantic class for request/response data validation.

🛢️ 2. Database Configuration

DATABASE_URL = "sqlite:///./test.db"

Defines the database URL.
Using SQLite (file-based DB) stored as test.db in the current directory.

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

create_engine: Connects SQLAlchemy to the database.
connect_args={"check_same_thread": False}: Required for SQLite to allow multiple threads to access the DB (needed for FastAPI).

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

SessionLocal: A factory for creating new DB sessions.
autocommit=False, autoflush=False: Makes session more controlled (explicit commits).

Base = declarative_base()

Base class used to define SQLAlchemy models.

👤 3. Define SQLAlchemy Model

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)

Defines a User table with:
- id: Primary key (auto-increment).
- name: String field.
__tablename__ = "users": The actual table name in the database.

🏗️ 4. Create Table(s)

Base.metadata.create_all(bind=engine)

Automatically creates the tables in the DB based on your model definitions.

📦 5. Define Pydantic Schemas

Pydantic models validate input and control output structure.

class UserCreate(BaseModel):
    name: str

This is the input schema used when a user is being created (POST).

class UserOut(BaseModel):
    id: int
    name: str

    class Config:
        orm_mode = True

This is the response schema (what the API returns).
orm_mode = True: Allows FastAPI to return SQLAlchemy objects directly (they’re not dicts by default).

🚀 6. Create the FastAPI App

app = FastAPI()

This is the API app object. You’ll pass this to uvicorn to run the server.

🔁 7. Database Dependency

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

A dependency function used with Depends.
It creates a new DB session for each request and closes it after use.

🌐 8. API Routes

🔸 Create a User

@app.post("/users/", response_model=UserOut)
def create_user(user: UserCreate, db: Session = Depends(get_db)):
    db_user = User(name=user.name)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user

POST endpoint at /users/.
Accepts a JSON body with a name field.
Creates a User object, adds it to the session, commits, refreshes (gets the id), and returns the new user.

🔸 Get a User by ID

@app.get("/users/{user_id}", response_model=UserOut)
def read_user(user_id: int, db: Session = Depends(get_db)):
    user = db.query(User).filter(User.id == user_id).first()
    if user is None:
        raise HTTPException(status_code=404, detail="User not found")
    return user

GET endpoint at /users/{user_id}.
Queries for the user by ID.
If not found, returns a 404 error.

🔸 Get All Users

@app.get("/users/", response_model=list[UserOut])
def read_users(db: Session = Depends(get_db)):
    return db.query(User).all()

GET endpoint at /users/.
Returns all users in the DB as a list.

🧪 9. Run the App

uvicorn main:app --reload

Starts the FastAPI server.
--reload means auto-restart on code change (useful during development).

📊 10. Interactive API Docs

Visit: http://127.0.0.1:8000/docs

FastAPI generates Swagger UI automatically.
You can test all endpoints here directly in your browser.

✅ Summary

This app:

Connects to a SQLite database.
Defines a User model/table.
Provides API endpoints to create and fetch users.
Uses SQLAlchemy for ORM and Pydantic for input/output validation.
Automatically generates interactive documentation.

Let me know if you’d like to add PUT, DELETE, or async support.