1 hour 59 min

Create PostgreSQL-Powered APIs with SQLAlchemy

Shrink

Lab 2 – Create PostgreSQL-Powered APIs with SQLAlchemy

Welcome to Lab 2! In this lab, you'll level up from a simple API to a real database-backed application. You'll run PostgreSQL in a Docker container, use SQLAlchemy
ORM to interact with the database through Python objects, manage schema changes with Alembic migrations, and build a complete CRUD API for user management.

4/14/26, 10:26 AM 1/49 [Link]

 Clients Alembic Migrations
alembic revision --autogenerate
-> creates migration files
Curl Commands SwaggerUI Postman SQL-ALchemy
ORM
alembic upgrade head
-> Apply changes

SQL DDL
HTTP Request
Commands Comm

FastAPI Application Server Docker Container

(lab2_postgres)
API
endpoints
- GET Route FastAPI
- POST Request routes
- PUT
- DELETE

Business Validation
Logic Layer PostgreSQL 16
Port 5432
Pydantic
- Route Handlers
- Session provider Schemas
(get_db)

Calls ORM

Objectives
Set up PostgreSQL database using Docker Compose
4/14/26, 10:26 AM 2/49 [Link]
Connect FastAPI to PostgreSQL using SQLAlchemy ORM
 Define database models (tables) as Python classes
Create Pydantic schemas for request/response validation
Implement full CRUD API for user management
Use Alembic to manage database schema migrations
Test API endpoints with multiple methods
Understand database sessions and connection management

Background
Why Do We Need Databases?

In Lab 1, our API returned data directly from the code. But what happens when:

The server restarts? All data is lost!

Multiple users need to access the same data?
You need to store millions of records?
You need to search, filter, or analyze data?

Databases solve these problems by providing:

Persistent storage - Data survives server restarts

Concurrent access - Multiple users can read/write simultaneously
Query capabilities - Search, filter, sort, aggregate data efficiently
Data integrity - Enforce rules (unique emails, required fields, etc.)
Relationships - Connect related data (users and their posts)

What is PostgreSQL?

PostgreSQL (often called "Postgres") is one of the world's most advanced open-source relational databases.

Relational Database means data is organized in tables (like Excel spreadsheets):

users table:
┌────┬──────────────────────┬─────────────┐
│ id │ email │ username │
├────┼──────────────────────┼─────────────┤
│ 1 │ alice@[Link] │ alice │
│ 2 │ bob@[Link] │ bob │
│ 3 │ charlie@[Link] │ charlie │
└────┴──────────────────────┴─────────────┘

4/14/26, 10:26 AM 3/49 [Link]

Why PostgreSQL?
 Reliable: Battle-tested for over 30 years
Feature-rich: Supports JSON, full-text search, geospatial data
Standards-compliant: Follows SQL standards strictly
Open-source: Free to use, large community
Scalable: Handles small to massive datasets
Used by: Instagram, Spotify, Reddit, Uber

What is an ORM (SQLAlchemy)?

ORM = Object-Relational Mapping

Instead of writing raw SQL queries like this:

SELECT * FROM users WHERE id = 1;

INSERT INTO users (email, username) VALUES ('ada@[Link]', 'ada');

An ORM lets you use Python objects:

user = [Link](User).filter([Link] == 1).first()

new_user = User(email="ada@[Link]", username="ada")
[Link](new_user)

Benefits of using SQLAlchemy:

Pythonic: Work with objects instead of SQL strings

Type-safe: Catch errors before runtime
Database-agnostic: Switch from PostgreSQL to MySQL with minimal changes
Less error-prone: ORM handles SQL escaping, preventing SQL injection
Relationships: Easy to define and query related data
Migrations friendly: Works seamlessly with Alembic

SQLAlchemy Components:

4/14/26, 10:26 AM 4/49 [Link]

 Session
Application Code get_db Dependency Use Session
(Created)
Route Handlers

Application Layer

Request Close Session

Session

Parent Class

SessionLocal User Model

Declarative Base
(sessionmaker) class User (Base)

Configures Model Layer

Engine
Column Definitions Constraints
(create_engine)
(id, email, username) (unique, nullable, index)

SQLAlchemy Core Components

Manages
Spawns Connection Pool DB-API Driver
(Reuses Connections) Reuses (psycopg2)

populates

Session Instance
Connection Layer
4/14/26, 10:26 AM 5/49 [Link] Transmits via
 users Table Database PostgreSQL Server PostgreSQL Protocol

Maps to
1. Declarative Base:

Parent class for all models

Maintains registry of all tables
Provides metadata about schema

2. Engine:

Manages connection pool

Handles database dialect
Entry point to database

3. SessionLocal:

Factory for creating sessions

Configured with engine
Settings: autocommit=False, autoflush=False

4. Session:

Your "workspace" for database operations

Tracks changes to objects
Manages transactions (commit/rollback)

5. Connection Pool:

Maintains pool of database connections

Reuses connections for efficiency
Handles connection timeout and recycling

6. Models:

Python classes representing database tables

Connection
4/14/26, 10:26 AM Pool Management
6/49 [Link]
 Connection Lifecycle
Reuse Existing
Connection

Yes

Pool Has No, Under Max Create New Use Connection R

New Request Request Complete
Available? Connection Execute Query

No, At Max
Wait for Available
or Timeout

Application Requests

Request 1 Request 2 Request 3 Request 4

GET /users POST /users PUT /users/1 DELETE /users/2

FastAPI Dependency
get_db Dependency
Session Factory

4/14/26, 10:26 AM 7/49 [Link]

Connection Pool Slots
 Connection 1 Connection 2 Connection 3 Connection 4 Connection 5
In Use In Use Available Available Available

PostgreSQL Database

PostgreSQL Server
Active Connections: 5
Max Connections: 100

What is a Connection Pool?

A connection pool is a cache of database connections maintained by SQLAlchemy's Engine. Instead of creating a new connection for every database request (which is slow
and expensive), SQLAlchemy reuses existing connections from the pool.

Why Connection Pooling Matters:

Without connection pooling, every database operation would:

1. Establish a new TCP connection to PostgreSQL (~50-100ms)

2. Authenticate with username/password (~20-50ms)
3. Execute the query (~5-20ms)
4. Close the connection (~10-20ms)

Total: 85-190ms per request

With connection pooling:

1. Get existing connection from pool (~1ms)

2. Execute the query (~5-20ms)
3. Return connection to pool (~1ms)

Total: 7-22ms per request (10-20x faster!)

How Connection Pool Works:

4/14/26, 10:26 AM 8/49 [Link]
When you create an Engine in app/[Link]:
 engine = create_engine(
DATABASE_URL,
echo=False,
future=True
)

SQLAlchemy automatically creates a connection pool with default settings:

pool_size=5: Maintains 5 persistent connections

max_overflow=10: Can create 10 additional temporary connections when needed
pool_timeout=30: Wait up to 30 seconds for an available connection untill raising an error
pool_recycle=3600: Recycle connections after 1 hour (prevents stale connections)

Connection Pool Lifecycle:

1. Application Starts

Engine created with connection pool

Pool is initially empty (connections created on demand)

2. First Request Arrives

get_db() dependency creates a Session

Session requests a connection from pool
Pool is empty, so it creates a new connection to PostgreSQL
Connection used for query
Connection returned to pool (not closed!)

3. Second Request Arrives

Session requests connection from pool

Pool has 1 available connection from previous request
Reuses existing connection (very fast!)
Connection returned to pool

4. Pool Grows Over Time

As more concurrent requests arrive, pool creates more connections

Pool grows up to (5 connections)
4/14/26, 10:26 AM 9/49 [Link]
pool_size
These 5 connections are permanent and always kept alive
 5. Traffic Spike (More than 5 concurrent requests)

Pool is at max size (5 connections all in use)

6th request needs a connection
Pool creates an overflow connection (temporary)
Can create up to max_overflow (10) additional connections
Total possible concurrent connections: pool_size + max_overflow = 15

6. Overflow Connections Cleanup

When request completes, overflow connection is closed

Only the core pool_size connections are kept persistent
This prevents connection leaks during traffic spikes

Connection States:

Available: Connection is in the pool, ready to be used

In Use: Connection is currently executing a query
Overflow: Temporary connection created during high traffic (discarded after use)
Stale: Connection that's been idle too long, will be recycled

Connection Pool Configuration Example:

For production environments with higher traffic, you might configure:

engine = create_engine(
DATABASE_URL,
pool_size=20, # Keep 20 connections always ready
max_overflow=40, # Allow 40 more during spikes (total: 60)
pool_timeout=30, # Wait 30s for connection before error
pool_recycle=3600, # Recycle connections every hour
pool_pre_ping=True, # Test connection before using (catch stale connections)
echo=False
)

The connection pool is invisible to your route handlers but provides massive performance benefits automatically!
4/14/26, 10:26 AM 10/49 [Link]
What are Database Migrations (Alembic)?
The Problem: Your database schema changes over time:

Initially: Users have email and username

Later: You add created_at timestamp
Later: You add is_active boolean

How do you safely update production databases without losing data?

Database Migrations are version-controlled schema changes:

Initial state: Migration 001: Migration 002:

users table Add created_at Add is_active
- id - id - id
- email - email - email
- username - username - username
- created_at - created_at
- is_active

4/14/26, 10:26 AM 11/49 [Link]

 Migration Process

alembic revision
Developer Modifies Models Alembic Detects Changes Create Migrat
--autogenerate
app/[Link] Compares [Link] vs DB alembic/version
-m 'description'

Execute upgrade function Check Current Version Connect to PostgreSQL

alembic upgrade head
Run DDL statements alembic_version table Using DATABASE_URL
Appy migration

CREATE TABLE users

WITH columns and
CREATE TABLE constraints

ALTER TABLE users

ADD COLUMN Update alembic_version Migration Complet
Operation Type ADD COLUMN
new_field Record new version Database Schema Upd

ALTER COLUMN ALTER TABLE users

ALTER COLUMN field
TYPE

Rollback Process

4/14/26, 10:26 AM 12/49 [Link]

Execute downgrade function
Need to Rollback? alembic downgrade -1
 Reverse changes
Go back one version

Alembic is a migration tool for SQLAlchemy that:

Tracks all schema changes in version files

Applies migrations in order (upgrade)
Reverses migrations if needed (downgrade)
Auto-generates migration code from model changes
Works across different environments (dev, staging, production)

Think of it like Git for your database schema.

CRUD Operations
CRUD is an acronym for the four basic database operations:

Operation HTTP Method SQL Command Purpose

Create POST INSERT Add new records
Read GET SELECT Retrieve records
Update PUT/PATCH UPDATE Modify existing records
Delete DELETE DELETE Remove records

Every data-driven application implements these operations.

Docker and Docker Compose

Docker packages applications in "containers" - isolated environments that run consistently everywhere.

Docker Compose defines multi-container applications in a simple YAML file.

Why use Docker for PostgreSQL?

No need to install PostgreSQL on your machine

Consistent setup across all developers
Easy to start/stop/reset the database
Isolated from other projects
Production-like environment locally
4/14/26, 10:26 AM 13/49 [Link]
Key Concepts
Database Schema: The structure of your database (tables, columns, types, constraints)

Table: A collection of related data (like a spreadsheet)

Row/Record: A single entry in a table (one user)

Column/Field: A specific attribute (email, username)

Primary Key: Unique identifier for each row (id)

Foreign Key: Links to another table (user_id in posts table)

Index: Speeds up queries on specific columns

Constraint: Rules enforced by the database (UNIQUE, NOT NULL)

Session: A workspace for database operations (like a transaction)

Connection Pool: Reuses database connections for efficiency

Pydantic Schema: Defines data structure for API requests/responses (validation)

Project Structure

Lab-2/
├── [Link] # PostgreSQL container configuration
├── .env # Environment variables (database URL, app name)
├── .[Link] # Template for environment variables
├── [Link] # Python dependencies
├── [Link] # Alembic configuration file
├── .gitignore # Files to exclude from version control
│
├── app/ # Main application package
│ ├── __init__.py # Makes 'app' a Python package
│ ├── [Link] # FastAPI app + CRUD route handlers
│ ├── [Link] # Database engine, session, and Base class
│ ├── [Link] # SQLAlchemy models (database tables)
│ └── [Link] # Pydantic schemas (request/response validation)
│
└── alembic/ # Database migrations
├── [Link] # Alembic environment configuration
├── [Link] # Template for new migrations
├── README # Alembic documentation
4/14/26, 10:26 AM 14/49 [Link]
 └── versions/ # Migration version files
└── xxxx_create_users_table.py # First migration (created by you)

Architecture Components:

1. Client Layer

Web browsers, API testing tools (cURL, Postman), and Python scripts
Send HTTP requests with JSON payloads

2. FastAPI Application Layer

API Routes: Define endpoints for CRUD operations

Pydantic Schemas: Validate incoming requests and serialize responses
Business Logic: Route handlers that orchestrate database operations
Dependency Injection: Manages database sessions lifecycle
Auto-Documentation: Automatically generated from code

3. Database Layer

SQLAlchemy Engine: Manages connection pool to PostgreSQL

Sessions: Handle database transactions and queries
Models: Python classes representing database tables
Alembic: Manages database schema migrations and versioning

4. Data Storage Layer

Docker Compose: Orchestrates PostgreSQL container

PostgreSQL: Stores persistent data in tables

5. Configuration

.env: Application and database connection settings

[Link]: Docker container configuration

Request Flow Example (Creating a User):

1. Client sends: POST /users with JSON {"email": "ada@[Link]", "username": "ada"}
2. FastAPI receives request and validates JSON using Pydantic schema
3. FastAPI calls the create_user function with validated data
4. Function creates SQLAlchemy User object
5. SQLAlchemy translates to SQL: INSERT INTO users ...
6. PostgreSQL executes SQL and returns new user ID
7. SQLAlchemy creates Python User object with ID
4/14/26, 10:26 AM 15/49 [Link]
8. FastAPI serializes object to JSON using Pydantic schema
 9. Client receives: {"id": 1, "email": "ada@[Link]", "username": "ada"}

Step-by-Step Implementation Guide

Step 1: Create Project Directory Structure

Create the following directory structure:

mkdir app

Your directory should look like:

code/
└── app/

Step 2: Create [Link]

Create a file named [Link] with these dependencies:

fastapi==0.115.5
uvicorn[standard]==0.32.0
python-dotenv==1.0.1
SQLAlchemy==2.0.23
psycopg2-binary==2.9.10
pydantic==2.9.2
alembic==1.13.2
pydantic[email]

What each package does:

fastapi: The web framework

4/14/26, 10:26 AM 16/49 [Link]
uvicorn: ASGI server to run FastAPI
 python-dotenv: Load environment variables from .env
SQLAlchemy: ORM for database operations
psycopg2-binary: PostgreSQL database adapter for Python
pydantic: Data validation using Python type hints
alembic: Database migration tool

Step 3: Create .env File

Create .env file to store configuration:

APP_NAME=FastAPI Lab 2
DATABASE_URL=postgresql+psycopg2://postgres:postgres@localhost:5432/lab2_db

Explanation:

postgresql+psycopg2:// - Use PostgreSQL with psycopg2 driver

postgres:postgres - username:password
@localhost:5432 - host and port
/lab2_db - database name

Step 4: Create [Link]

Create [Link] to run PostgreSQL in Docker:

version: "3.9"

services:
db:
image: postgres:16
container_name: lab2_postgres
environment:
POSTGRES_USER: postgres
4/14/26, 10:26POSTGRES_PASSWORD:
AM 17/49 [Link]
postgres
 POSTGRES_DB: lab2_db
ports:
- "5432:5432"
volumes:
- pgdata_lab2:/var/lib/postgresql/data
healthcheck:
test: ["CMD-SHELL", "pg_isready -U postgres -d lab2_db"]
interval: 5s
timeout: 5s
retries: 20

volumes:
pgdata_lab2:

Step 5: Start PostgreSQL Container

# Start PostgreSQL in the background

docker compose up -d

# Check if container is running and healthy

docker compose ps

# View logs if needed

docker compose logs -f db

Expected output: alt text

Verify database is ready:

4/14/26, 10:26 AM 18/49 [Link]

 docker compose exec db psql -U postgres -d lab2_db -c "SELECT version();"

Step 6: Create Virtual Environment and Install Dependencies

# Create virtual environment

python -m venv .venv

# Activate it (Linux/Mac)
source .venv/bin/activate

# Install all dependencies

pip install -r [Link]

Step 7: Create app/__init__.py

Create an empty file to make app a Python package:

touch app/__init__.py

Step 8: Create app/[Link]

4/14/26, 10:26 AM 19/49 [Link]
touch app/[Link]

This file sets up the database connection:

import os
from dotenv import load_dotenv
from sqlalchemy import create_engine
from [Link] import sessionmaker, DeclarativeBase

# Load environment variables from .env file

load_dotenv()
DATABASE_URL = [Link]("DATABASE_URL")

# Base class for all models

class Base(DeclarativeBase):
pass

# Create database engine

# echo=True would print all SQL queries (useful for debugging)
engine = create_engine(
DATABASE_URL,
echo=False, # Set to True to see SQL queries
future=True # Use SQLAlchemy 2.0 style
)

# Create session factory

# Sessions are your "workspace" for database operations
SessionLocal = sessionmaker(
bind=engine,
autoflush=False, # Don't automatically flush changes
autocommit=False, # Don't automatically commit
4/14/26, 10:26 AM 20/49 [Link]
 future=True # Use SQLAlchemy 2.0 style
)

Key Concepts:

Engine: Maintains a connection pool to the database

SessionLocal: Factory that creates database sessions
Base: All your models will inherit from this class
autocommit=False: We manually control when to save changes
autoflush=False: We manually control when to send changes to DB

Step 9: Create app/[Link]

touch app/[Link]

Define the database table structure:

from sqlalchemy import Column, Integer, String, UniqueConstraint

from .database import Base

class User(Base):
__tablename__ = "users"

# Primary key column

id = Column(
Integer,
primary_key=True, # Unique identifier
index=True # Create index for faster queries
)

# Email column
4/14/26, 10:26 AM 21/49 [Link]
email = Column(
 String(255), # Maximum 255 characters
nullable=False, # Cannot be NULL
unique=True, # Must be unique across all users
index=True # Create index for faster lookups
)

# Username column
username = Column(
String(50), # Maximum 50 characters
nullable=False, # Cannot be NULL
unique=True, # Must be unique across all users
index=True # Create index for faster lookups
)

# Table-level constraints
__table_args__ = (
UniqueConstraint("email", name="uq_users_email"),
UniqueConstraint("username", name="uq_users_username"),
)

def __repr__(self):
"""String representation of User object."""
return f"<User(id={[Link]}, email='{[Link]}', username='{[Link]}')>"

What this creates in PostgreSQL:

CREATE TABLE users (

id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
username VARCHAR(50) NOT NULL UNIQUE
4/14/26, 10:26 AM 22/49 [Link]
);
 CREATE INDEX ix_users_id ON users (id);
CREATE INDEX ix_users_email ON users (email);
CREATE INDEX ix_users_username ON users (username);

Step 10: Create app/[Link]

touch app/[Link]

Pydantic schemas for data validation:

from pydantic import BaseModel, EmailStr, Field

class UserCreate(BaseModel):
email: EmailStr # Validates email format
username: str = Field(
min_length=3, # Minimum 3 characters
max_length=50 # Maximum 50 characters
)

class UserUpdate(BaseModel):
email: EmailStr | None = None # Optional email
username: str | None = Field(
default=None,
min_length=3,
max_length=50
)

4/14/26, 10:26 AM 23/49 [Link]

 class UserOut(BaseModel):
id: int
email: EmailStr
username: str

class Config:
"""Pydantic configuration."""
from_attributes = True # Allow creating from ORM models

Why separate schemas?

UserCreate: Only accepts email + username (no ID)

UserUpdate: All fields optional, for partial updates
UserOut: Includes ID, used for responses (never for input)

Benefits:

Automatic validation
Clear API documentation
Type safety
Prevents sending unwanted data

Step 11: Create app/[Link]

touch app/[Link]

The main FastAPI application with all CRUD endpoints:

import os
from dotenv import load_dotenv
from fastapi import FastAPI, Depends, HTTPException, status
from [Link] import Session

from .database import SessionLocal

4/14/26, 10:26 AM 24/49 [Link]
from .models import User
from .schemas import UserCreate, UserUpdate, UserOut

# Load environment variables

load_dotenv()

# Create FastAPI application

app = FastAPI(
title=[Link]("APP_NAME", "FastAPI Lab 2"),
description="A CRUD API for user management with PostgreSQL",
version="1.0.0"
)

# Dependency: Database session

def get_db():
db = SessionLocal()
try:
yield db
finally:
[Link]()

# Health check endpoint

@[Link]("/ping")
def ping():
return {"status": "ok", "message": "pong"}

# CREATE: Add a new user

@[Link]("/users", response_model=UserOut, status_code=status.HTTP_201_CREATED)
def create_user(payload: UserCreate, db: Session = Depends(get_db)):
4/14/26, 10:26 AM 25/49 [Link]
# Check if email or username already exists
 existing_user = [Link](User).filter(
([Link] == [Link]) | ([Link] == [Link])
).first()

if existing_user:
raise HTTPException(
status_code=400,
detail="Email or username already exists"
)

# Create new user

user = User(email=[Link], username=[Link])
[Link](user) # Add to session
[Link]() # Save to database
[Link](user) # Reload from database (get the ID)

return user

# READ: Get all users

@[Link]("/users", response_model=list[UserOut])
def list_users(db: Session = Depends(get_db)):
return [Link](User).order_by([Link]()).all()

# READ: Get single user by ID

@[Link]("/users/{user_id}", response_model=UserOut)
def get_user(user_id: int, db: Session = Depends(get_db)):
user = [Link](User, user_id)

if not user:
raise HTTPException(
4/14/26, 10:26 AM 26/49 [Link]
status_code=404,
 detail="User not found"
)

return user

# UPDATE: Modify existing user

@[Link]("/users/{user_id}", response_model=UserOut)
def update_user(user_id: int, payload: UserUpdate, db: Session = Depends(get_db)):
# Get existing user
user = [Link](User, user_id)

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

# Update email if provided and different

if [Link] and [Link] != [Link]:
# Check if email already taken
if [Link](User).filter([Link] == [Link]).first():
raise HTTPException(
status_code=400,
detail="Email already in use"
)
[Link] = [Link]

# Update username if provided and different

if [Link] and [Link] != [Link]:
# Check if username already taken
if [Link](User).filter([Link] == [Link]).first():
4/14/26, 10:26 AM 27/49 [Link]
raise HTTPException(
 status_code=400,
detail="Username already in use"
)
[Link] = [Link]

[Link](user) # Mark as modified

[Link]() # Save changes
[Link](user) # Reload from database

return user

# DELETE: Remove a user

@[Link]("/users/{user_id}", status_code=status.HTTP_204_NO_CONTENT)
def delete_user(user_id: int, db: Session = Depends(get_db)):
user = [Link](User, user_id)

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

[Link](user) # Mark for deletion

[Link]() # Execute deletion

return None # 204 responses have no body

Key Patterns:

Dependency Injection: Depends(get_db) automatically provides database session

Session Management: Session is always closed, even if errors occur
Error Handling: Raises HTTPException with appropriate status codes
4/14/26, 10:26 AM 28/49 [Link]
Data Validation: Pydantic automatically validates input/output
 Database Operations: Always add() → commit() → refresh()

Step 12: Initialize Alembic

# Initialize Alembic in your project

alembic init alembic

This creates:

alembic/
├── [Link] # Environment configuration
├── README # Alembic documentation
├── [Link] # Template for new migrations
└── versions/ # Migration files go here

[Link] # Alembic configuration file

Step 13: Configure Alembic

Edit alembic/[Link] to connect Alembic to your database and models:

Find this line (around line 21):

target_metadata = None

Replace the entire section with:

# Import your models

from [Link] import Base
from [Link] import User # Import all models here
4/14/26, 10:26 AM 29/49 [Link]
 # Set target metadata from your models
target_metadata = [Link]

Also, find the run_migrations_offline() and run_migrations_online() functions and ensure they read from your .env file. Add this at the top of [Link] after imports:

from dotenv import load_dotenv

import os

# Load environment variables

load_dotenv()

# Override [Link] with DATABASE_URL from .env

config.set_main_option('[Link]', [Link]('DATABASE_URL'))

Edit [Link] - Verify the [Link] (line 63):

[Link] = postgresql+psycopg2://postgres:postgres@localhost:5432/lab2_db

(This will be overridden by .env, but it's good to have a default)

Step 14: Create and Apply Migration

# Generate migration from your models

alembic revision --autogenerate -m "Create users table"

# This creates a file like: alembic/versions/xxxx_create_users_table.py

4/14/26, 10:26 AM 30/49 [Link]
Review the generated migration file in alembic/versions/:

Apply the migration:

# Apply all pending migrations

alembic upgrade head
4/14/26, 10:26 AM 31/49 [Link]
Expected output:

Verify the table was created:

docker compose exec db psql -U postgres -d lab2_db -c "\dt"

docker compose exec db psql -U postgres -d lab2_db -c "\d users"

4/14/26, 10:26 AM 32/49 [Link]

Step 15: Run Your FastAPI Application

# Start the API server

uvicorn [Link]:app --reload --host [Link] --port 8000

Command breakdown:
4/14/26, 10:26 AM 33/49 [Link]
 [Link]: Import app from app/[Link]
app: The FastAPI instance
--reload: Auto-restart on code changes

4/14/26, 10:26 AM 34/49 [Link]

Expected output:

4/14/26, 10:26 AM 35/49 [Link]

Step 16: Access FastAPI Application using Poridhi's Loadbalancer
To access the FastAPI Application with poridhi's Loadbalancer, use the following steps:

Find the wt0 IP address for the Poridhi's VM currently you are running by using the command:

ifconfig

Note: The wt0 IP

in this image is wrong. It actually showed [Link] IP when this lab was being done by the poridhi team, which is why on the next load balancer image, you see
[Link] IP. When you start a vm, you might get a different wt0 IP.

4/14/26, 10:26 AM 36/49 [Link]

Go to Poridhi's LoadBalancer and Create a LoadBalancer with the wt0 IP and port 8000.

Step 17: Test Your API

Open Interactive Documentation

4/14/26, 10:26 AM 37/49 [Link]
Swagger UI: <|Load Balancer URL|>/docs

4/14/26, 10:26 AM 38/49 [Link]

4/14/26, 10:26 AM 39/49 [Link]
This is your best friend! You can:

See all endpoints

Try requests directly in the browser
View request/response schemas
See validation rules

How It Works (Code Basics)

[Link] creates an engine with DATABASE_URL and a SessionLocal for requests.
[Link] maps the User class to the users table with uniqueness on email and username.
[Link] defines UserCreate, UserUpdate, and UserOut for input/output validation.
[Link] wires routes to DB operations using a session dependency (get_db).
alembic/[Link] loads DATABASE_URL and migrates based on [Link].

Method 1: Using Swagger UI (Recommended for Beginners)

1. Open <|Load Balancer URL|>/docs

2. Find the POST /users endpoint
3. Click "Try it out"
4. Enter JSON:

{
"email": "alice@[Link]",
"username": "alice"
}

5. Click "Execute"
6. See response with status 201 and the created user with ID

Try all endpoints this way - it's interactive and visual!

Method 2: Using CURL (Command Line)

4/14/26, 10:26 AM 40/49 [Link]

# CREATE: Add a new user
curl -X POST "<|Load Balancer URL|>/users" \
-H "Content-Type: application/json" \
-d '{"email": "bob@[Link]", "username": "bob"}'

# READ: Get all users

curl -X GET "<|Load Balancer URL|>/users"

# READ: Get specific user

curl -X GET "<|Load Balancer URL|>/users/1"

4/14/26, 10:26 AM 41/49 [Link]

 # UPDATE: Modify user
curl -X PUT "<|Load Balancer URL|>/users/1" \
-H "Content-Type: application/json" \
-d '{"email": "[Link]@[Link]"}'

# DELETE: Remove user

curl -X DELETE "<|Load Balancer URL|>/users/1"

Method 3: Using Python Requests

Create test_users.py:

import requests

BASE_URL = "[Link]

4/14/26, 10:26 AM 42/49 [Link]

# CREATE a user
 response = [Link](
f"{BASE_URL}/users",
json={"email": "charlie@[Link]", "username": "charlie"}
)
print(f"CREATE: {response.status_code}")
print([Link]())
user_id = [Link]()["id"]

# READ all users

response = [Link](f"{BASE_URL}/users")
print(f"\nREAD ALL: {response.status_code}")
print([Link]())

# READ single user

response = [Link](f"{BASE_URL}/users/{user_id}")
print(f"\nREAD ONE: {response.status_code}")
print([Link]())

# UPDATE user
response = [Link](
f"{BASE_URL}/users/{user_id}",
json={"email": "[Link]@[Link]"}
)
print(f"\nUPDATE: {response.status_code}")
print([Link]())

# DELETE user
response = [Link](f"{BASE_URL}/users/{user_id}")
print(f"\nDELETE: {response.status_code}")

Run it:
4/14/26, 10:26 AM 43/49 [Link]
 pip install requests
python test_users.py

Verify Data in Database

You can directly query the PostgreSQL database:

# Connect to database
docker compose exec db psql -U postgres -d lab2_db

# List all users

SELECT * FROM users;

# Count users
SELECT COUNT(*) FROM users;

# Exit psql
\q

Step 18: Modify Schema with Another Migration

Now let's demonstrate a real-world scenario: adding new columns to an existing table. This shows how Alembic helps you evolve your database schema over time.

Why Another Migration? In production, you'll often need to:

Add new features (new columns)

Change existing columns
Add indexes for performance
Maintain backward compatibility

Let's add created_at and is_active columns to track when users were created and whether they're active.

18.1: Update
4/14/26, 10:26 AMthe User
44/49 Model
[Link]
Edit app/[Link]:

from sqlalchemy import Column, Integer, String, UniqueConstraint, DateTime, Boolean

from [Link] import func
from .database import Base

class User(Base):
__tablename__ = "users"

id = Column(Integer, primary_key=True, index=True)

email = Column(String, unique=True, index=True, nullable=False)
username = Column(String, unique=True, index=True, nullable=False)

# New columns
created_at = Column(DateTime(timezone=True), server_default=[Link](), nullable=False)
is_active = Column(Boolean, default=True, nullable=False)

What changed:

Added DateTime and Boolean imports

Added func from [Link] for [Link]()
created_at: Automatically set to current timestamp when row is created
is_active: Defaults to True, allows soft-deletion (marking users inactive instead of deleting)

18.2: Generate Migration

alembic revision --autogenerate -m "Add created_at and is_active to users"

Expected output:
4/14/26, 10:26 AM 45/49 [Link]
18.3: Review Generated Migration

Open the newly created file in alembic/versions/ (e.g., abc123_add_created_at_and_is_active_to_users.py):

Understanding the migration:

upgrade(): Adds the new columns

downgrade(): Removes them (allows rolling back)
server_default: Sets default values for existing rows
Alembic detected changes automatically!

18.4: Apply the Migration

4/14/26, 10:26 AM 46/49 [Link]
 alembic upgrade head

Expected output:

18.5: Verify New Columns in Database

# Check table structure

docker compose exec db psql -U postgres -d lab2_db -c "\d users"

Expected output:

4/14/26, 10:26 AM 47/49 [Link]

Verify existing data got default values:

docker compose exec db psql -U postgres -d lab2_db -c "SELECT id, username, created_at, is_active FROM users;"

You should see existing users now have created_at set to migration time and is_active = true.

4/14/26, 10:26 AM 48/49 [Link]

What You've Learned:

This second migration demonstrates the real power of Alembic. In production applications, your database schema constantly evolves:

New features require new columns

Performance optimization needs new indexes
Business requirements change over time

With Alembic, you can:

Modify your models in Python (add columns, change types, etc.)

Generate migrations automatically with alembic revision --autogenerate
Apply changes safely to any environment with alembic upgrade head
Rollback if needed using alembic downgrade -1
Track all changes in version control alongside your code

Real-World Impact: Imagine you have 10,000 users in production and need to add a last_login column. With Alembic:

1. Modify model locally

2. Generate migration
3. Test on staging database
4. Apply to production: alembic upgrade head
5. All 10,000 users get the new column with default values
6. Zero downtime, zero data loss!

Without migrations, you'd manually write SQL, risk inconsistencies between environments, and potentially lose data.

Key Takeaway: Alembic migrations are version control for your database schema. Just like Git tracks code changes, Alembic tracks schema changes, enabling safe
evolution across development, staging, and production environments.

Conclusion
Congratulations on completing Lab 2! You've built a production-ready CRUD API with FastAPI and PostgreSQL, learned how SQLAlchemy manages database connections
efficiently through connection pooling, and mastered Alembic migrations for evolving your database schema safely. These fundamentals form the backbone of modern web
applications—from handling thousands of concurrent users to deploying schema changes in production without downtime. You're now equipped to build robust, scalable
APIs that connect to real databases.

4/14/26, 10:26 AM 49/49 [Link]