🗄️ FastAPI Database Integration: Building Your Data Kingdom

The Story of Your Digital Filing Cabinet

Imagine you’re running a library. You have thousands of books, and you need a way to:

• Store them so they don’t get lost
• Find them when someone asks for a specific book
• Add new ones when they arrive
• Remove old ones that are damaged
• Keep track of changes over time

That’s exactly what Database Integration does for your FastAPI app! Your database is like a super-organized filing cabinet that never forgets anything.

🏗️ Part 1: Database Setup with SQLAlchemy

What is SQLAlchemy?

Think of SQLAlchemy as your friendly librarian robot. Instead of you walking through dusty shelves to find books, you just tell the robot what you want, and it fetches it for you!

Without SQLAlchemy:

You: "Go to shelf 42, row 3,
      find the blue book..."

With SQLAlchemy:

You: "Get me the book about cats"
Robot: "Here you go! 🐱"

Setting Up Your Database Connection

First, let’s tell our robot where the filing cabinet is!

Step 1: Install SQLAlchemy

pip install sqlalchemy

Step 2: Create the Connection

# database.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import declarative_base

# Where is our filing cabinet?
DATABASE_URL = "sqlite:///./myapp.db"

# Create the connection to our cabinet
engine = create_engine(
    DATABASE_URL,
    connect_args={"check_same_thread": False}
)

# Our robot helper for each request
SessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine
)

# Blueprint for our filing folders
Base = declarative_base()

Understanding Each Part

graph LR
    A[🏠 DATABASE_URL] --> B[📍 Where database lives]
    C[⚙️ engine] --> D[🔌 Connection to database]
    E[🤖 SessionLocal] --> F[📝 Handles conversations]
    G[📋 Base] --> H[🗂️ Template for tables]

Creating Your First Table (Model)

Tables are like folders in your filing cabinet. Let’s create a folder for “Users”:

# models.py
from sqlalchemy import Column, Integer, String
from database import Base

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    email = Column(String(100), unique=True)
    age = Column(Integer)

What does this mean?

Connecting to FastAPI

Now let’s introduce our robot to FastAPI:

# main.py
from fastapi import FastAPI, Depends
from sqlalchemy.orm import Session
from database import SessionLocal, engine
import models

# Create all the folders (tables)
models.Base.metadata.create_all(bind=engine)

app = FastAPI()

# Get our robot helper
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

The get_db function is like:

1. 📂 Opening the filing cabinet
2. 📝 Doing your work
3. 📂 Closing it when done

🔄 Part 2: CRUD Operations

CRUD is not a monster under your bed! It’s your four superpowers for handling data:

graph TD
    C[🟢 CREATE] --> CC[Add new stuff]
    R[🔵 READ] --> RR[Find stuff]
    U[🟡 UPDATE] --> UU[Change stuff]
    D[🔴 DELETE] --> DD[Remove stuff]

🟢 CREATE - Adding New Data

Like putting a new book on the shelf:

@app.post("/users/")
def create_user(
    name: str,
    email: str,
    db: Session = Depends(get_db)
):
    # Make a new user folder
    new_user = User(name=name, email=email)

    # Put it in the cabinet
    db.add(new_user)

    # Save changes
    db.commit()

    # Get the updated info
    db.refresh(new_user)

    return new_user

Step by step:

1. User(...) → Create the folder
2. db.add() → Place it in cabinet
3. db.commit() → Lock in the changes
4. db.refresh() → Get the latest info

🔵 READ - Finding Data

Like asking the librarian for a book:

# Get ALL users
@app.get("/users/")
def get_all_users(db: Session = Depends(get_db)):
    return db.query(User).all()

# Get ONE user by ID
@app.get("/users/{user_id}")
def get_user(
    user_id: int,
    db: Session = Depends(get_db)
):
    user = db.query(User).filter(
        User.id == user_id
    ).first()

    if not user:
        raise HTTPException(
            status_code=404,
            detail="User not found!"
        )
    return user

Query tricks:

🟡 UPDATE - Changing Data

Like erasing and rewriting in pencil:

@app.put("/users/{user_id}")
def update_user(
    user_id: int,
    name: str,
    db: Session = Depends(get_db)
):
    # Find the user
    user = db.query(User).filter(
        User.id == user_id
    ).first()

    if not user:
        raise HTTPException(
            status_code=404,
            detail="User not found!"
        )

    # Change the name
    user.name = name

    # Save changes
    db.commit()

    return {"message": "Updated!"}

🔴 DELETE - Removing Data

Like taking a book off the shelf permanently:

@app.delete("/users/{user_id}")
def delete_user(
    user_id: int,
    db: Session = Depends(get_db)
):
    # Find the user
    user = db.query(User).filter(
        User.id == user_id
    ).first()

    if not user:
        raise HTTPException(
            status_code=404,
            detail="User not found!"
        )

    # Remove from cabinet
    db.delete(user)

    # Save changes
    db.commit()

    return {"message": "Deleted!"}

CRUD Summary Table

🦎 Part 3: Database Migrations with Alembic

Why Do We Need Migrations?

Imagine your library has been running for a year. Now you want to add a “phone number” column to your Users folder. But you can’t just destroy everything and start over!

Migrations let you change your database structure without losing data. It’s like renovating your house while still living in it! 🏠

What is Alembic?

Alembic is your time-traveling construction worker. It:

• 📸 Takes snapshots of your database structure
• 🔧 Makes changes safely
• ⏪ Can undo changes if something goes wrong

Setting Up Alembic

Step 1: Install Alembic

pip install alembic

Step 2: Initialize Alembic

alembic init alembic

This creates a magical folder structure:

📂 alembic/
├── 📄 env.py         (configuration)
├── 📄 script.py.mako (template)
└── 📂 versions/      (your migrations)

Step 3: Configure Alembic

Edit alembic.ini:

# Point to your database
sqlalchemy.url = sqlite:///./myapp.db

Edit alembic/env.py:

# Import your models
from models import Base
target_metadata = Base.metadata

Creating Your First Migration

Scenario: You want to add a phone column to Users.

Step 1: Update your model

# models.py
class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    email = Column(String(100), unique=True)
    age = Column(Integer)
    phone = Column(String(20))  # NEW!

Step 2: Generate migration

alembic revision --autogenerate \
    -m "add phone column"

This creates a file like: versions/abc123_add_phone_column.py

Step 3: Apply the migration

alembic upgrade head

Understanding Migration Files

# versions/abc123_add_phone_column.py

def upgrade():
    """Go forward in time"""
    op.add_column(
        'users',
        sa.Column('phone', sa.String(20))
    )

def downgrade():
    """Go backward in time"""
    op.drop_column('users', 'phone')

Essential Alembic Commands

graph LR
    A[alembic revision] --> B[📝 Create migration]
    C[alembic upgrade head] --> D[⬆️ Apply all migrations]
    E[alembic downgrade -1] --> F[⬇️ Undo last migration]
    G[alembic current] --> H[📍 Show current version]
    I[alembic history] --> J[📜 Show all migrations]

Migration Best Practices

✅ DO:

• Always review auto-generated migrations
• Test migrations on a copy first
• Use descriptive migration names
• Keep migrations small and focused

❌ DON’T:

• Edit migrations after applying them
• Delete migration files manually
• Skip migrations in production

🎯 Putting It All Together

Here’s the complete flow:

graph TD
    A[📝 Define Models] --> B[🗄️ Create Tables]
    B --> C[🔄 CRUD Operations]
    C --> D[📈 Need Changes?]
    D --> E[🦎 Alembic Migration]
    E --> F[✅ Updated Database]

Complete Example Project

📂 my_fastapi_project/
├── 📄 main.py
├── 📄 database.py
├── 📄 models.py
├── 📄 alembic.ini
└── 📂 alembic/
    ├── 📄 env.py
    └── 📂 versions/

🌟 Key Takeaways

1. SQLAlchemy = Your friendly robot that talks to the database
2. Models = Blueprints for your data folders
3. Sessions = Conversations with your database
4. CRUD = Your four superpowers (Create, Read, Update, Delete)
5. Alembic = Your time-traveling renovation worker

Remember: Every big database started with one small table. You’ve got this! 🚀

📚 Quick Reference

# Create
db.add(item)
db.commit()

# Read
db.query(Model).all()
db.query(Model).filter(Model.id == 1).first()

# Update
item.field = new_value
db.commit()

# Delete
db.delete(item)
db.commit()

Alembic Workflow:

# Change model → Generate → Apply
alembic revision --autogenerate -m "description"
alembic upgrade head

Now go build something amazing! 🎉