> ## Documentation Index
> Fetch the complete documentation index at: https://genai.codewithsiva.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# 02-Student Management REST API using ORM

> Build a production-style Student Management REST API using FastAPI, SQLAlchemy ORM, SQLite, APIRouter, Repository Pattern, Service Layer, and Global Exception Handling.

# Capstone Project - Student Management REST API

**Problem Statement**

In this capstone project, you will build a **Student Management System** that exposes a set of RESTful APIs to manage student records.

The application should store student information in a **SQLite** database using **SQLAlchemy ORM** and follow a clean, modular architecture. Throughout this project, you will implement the application layer by layer, separating database operations, business logic, and API endpoints.

The application should support the following features:

* Add a Student
* View All Students
* Search Students using query parameters
* View Student Details
* Update Student Details
* Delete a Student

By the end of this project, you will have a complete backend application that demonstrates industry-standard practices for building REST APIs using FastAPI.

***

# Learning Objectives

After completing this project, you will be able to:

* Build RESTful APIs using FastAPI.
* Organize applications using a modular project structure.
* Configure and use SQLite with SQLAlchemy ORM.
* Design database models using SQLAlchemy.
* Validate requests and responses using Pydantic Schemas.
* Separate database operations using the Repository Pattern.
* Implement business logic using the Service Layer.
* Handle application errors using global exception handlers.
* Perform CRUD operations on a persistent database.
* Search records using query parameters.
* Test REST APIs using Swagger UI.

***

# Final Project Architecture

```text theme={null}
                        Client
                           │
                     HTTP Request
                           │
                           ▼
                     APIRouter
                           │
                           ▼
                    Service Layer
                           │
                           ▼
                   Repository Layer
                           │
                           ▼
                    SQLAlchemy ORM
                           │
                           ▼
                     SQLite Database
```

***

# Request Flow

```text theme={null}
Client
   │
   ▼
APIRouter
   │
   ▼
Service Layer
   │
   ▼
Repository Layer
   │
   ▼
SQLite Database
   │
   ▼
Repository Layer
   │
   ▼
Service Layer
   │
   ▼
APIRouter
   │
   ▼
Client
```

***

# Development Approach

Rather than building the entire application at once, we will develop it incrementally.

At the end of each step, we will **run and verify** the implementation before moving on to the next step. This approach makes debugging easier and helps us understand the purpose of every layer in the application.

Each step consists of:

* Objective
* Instructions
* Task
* Solution
* Run & Verify

***

# Step 1: Create the Project and Git Repository

**Objective**

Create a new FastAPI project directory, initialize Git, configure `.gitignore`, and prepare the development environment.

**Instructions**

* Create a new project directory named `student-management-api`.
* Navigate to the project directory.
* Initialize the project environment using **uv**.
* Initialize Git and set up `.gitignore` to avoid tracking virtual environments or databases.

**Task**

Create the project directory, initialize using **uv**, initialize Git, configure `.gitignore`, and commit.

<Accordion title="Solution">
  ```bash theme={null}
  # Create the project directory
  mkdir student-management-api

  # Navigate into the project
  cd student-management-api

  # Initialize the project
  uv init

  # Create a virtual environment
  uv venv

  # Activate the virtual environment
  # Windows
  .venv\Scripts\activate
  # macOS / Linux
  source .venv/bin/activate

  # Initialize Git
  git init

  # Create `.gitignore`
  cat <<EOT > .gitignore
  .venv/
  __pycache__/
  *.py[cod]
  *.db
  .vscode/
  .idea/
  .DS_Store
  EOT

  # Create the first Git commit
  git add .
  git commit -m "chore: initialize project and gitignore"
  ```
</Accordion>

**Run & Verify**

Verify that:

* The project directory has been created.
* The virtual environment is activated.
* A `pyproject.toml` and `.gitignore` files are present.
* `git status` reports a clean working tree after the first commit.

***

# Step 2: Install Dependencies

**Objective**

Install all the libraries required to build the Student Management REST API.

**Instructions**

Install the following dependencies:

* FastAPI
* Uvicorn
* SQLAlchemy
* Email Validator

**Task**

Install the required dependencies using **uv** and commit.

<Accordion title="Solution">
  ```bash theme={null}
  uv add fastapi uvicorn sqlalchemy email-validator

  # Commit the dependency files
  git add pyproject.toml uv.lock
  git commit -m "chore: install dependencies (fastapi, uvicorn, sqlalchemy, email-validator)"
  ```
</Accordion>

**Run & Verify**

Verify that:

* All packages are installed successfully.
* The `pyproject.toml` file contains the installed dependencies.

***

# Step 3: Create the Project Structure

**Objective**

Organize the application package directories to separate different layers.

**Instructions**

* Create the application package directory `app`.
* Create folders for `core`, `models`, `schemas`, `repositories`, `services`, and `routers` under `app`.
* Place empty `__init__.py` files inside each folder.

**Task**

Create the project structure and commit the new folders.

<Accordion title="Solution">
  **Project Structure**

  ```text theme={null}
  student-management-api/
  │
  ├── app/
  │   ├── __init__.py
  │   ├── core/
  │   │   ├── __init__.py
  │   │   ├── database.py
  │   │   ├── exceptions.py
  │   │   └── exception_handlers.py
  │   │
  │   ├── models/
  │   │   └── __init__.py
  │   │
  │   ├── schemas/
  │   │   └── __init__.py
  │   │
  │   ├── repositories/
  │   │   └── __init__.py
  │   │
  │   ├── routers/
  │   │   └── __init__.py
  │   │
  │   ├── services/
  │   │   └── __init__.py
  │   │
  │   └── main.py
  │
  ├── .gitignore
  ├── pyproject.toml
  └── uv.lock
  ```

  Commit the project structure to Git:

  ```bash theme={null}
  git add app/
  git commit -m "chore: add modular project directory structure"
  ```
</Accordion>

**Run & Verify**

Verify that the directories are present and contain initial package files.

***

# Step 4: Configure the Database

**Objective**

Configure SQLAlchemy so that the application can communicate with the SQLite database.

**Instructions**

Create a `database.py` file inside the **core** folder and implement:

* Database URL
* SQLAlchemy Engine
* Session Factory
* Declarative Base
* Database Dependency (`get_db()`)

**Task**

Configure SQLAlchemy for the SQLite database and commit.

<Accordion title="Solution">
  **File:** `app/core/database.py`

  ```python theme={null}
  from sqlalchemy import create_engine
  from sqlalchemy.orm import sessionmaker, DeclarativeBase

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

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

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

  class Base(DeclarativeBase):
      pass


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

  Commit the changes to Git:

  ```bash theme={null}
  git add app/core/database.py
  git commit -m "feat: configure SQLite database and session dependency"
  ```
</Accordion>

**Run & Verify**

Verify that:

* The project runs without import errors.
* The `database.py` file has no syntax errors.

***

# Step 5: Design the Database Schema

**Objective**

Design the database schema required for the Student Management System before implementing the database models.

**Instructions**

Design a database table named **students** with the following columns:

* Student ID
* Student Name
* Student Age
* Student Email Address

Identify:

* Primary Key
* Data Types
* Required Fields
* Unique Constraints

**Task**

Design the database schema for the Student Management System.

<Accordion title="Solution">
  **Students Table**

  | Column | Data Type | Constraints                 |
  | ------ | --------- | --------------------------- |
  | id     | Integer   | Primary Key, Auto Increment |
  | name   | String    | Not Null                    |
  | age    | Integer   | Not Null                    |
  | email  | String    | Not Null, Unique            |

  **Database Schema**

  ```text theme={null}
  +--------------------------------------+
  |              students                |
  +--------------------------------------+
  | id      INTEGER   PRIMARY KEY        |
  | name    VARCHAR   NOT NULL           |
  | age     INTEGER   NOT NULL           |
  | email   VARCHAR   UNIQUE NOT NULL    |
  +--------------------------------------+
  ```
</Accordion>

***

# Step 6: Create the Student ORM Model

**Objective**

Create the SQLAlchemy ORM model that maps the **students** table to a Python class.

**Instructions**

Create a `Student` model in `app/models/student.py` that:

* Maps to the `students` table.
* Defines the required columns using SQLAlchemy 2.0 `Mapped` and `mapped_column`.
* Inherits from the application's `Base` class.

**Task**

Create the Student ORM model and commit.

<Accordion title="Solution">
  **File:** `app/models/student.py`

  ```python theme={null}
  from sqlalchemy.orm import Mapped, mapped_column
  from app.core.database import Base

  class Student(Base):
      __tablename__ = "students"

      id: Mapped[int] = mapped_column(primary_key=True)
      name: Mapped[str]
      age: Mapped[int]
      email: Mapped[str] = mapped_column(unique=True)
  ```

  Commit the model:

  ```bash theme={null}
  git add app/models/student.py
  git commit -m "feat: create student database model"
  ```
</Accordion>

**Run & Verify**

Create the database tables by temporarily adding the following code to `main.py`:

```python theme={null}
from app.core.database import Base, engine
from app.models.student import Student

Base.metadata.create_all(bind=engine)
```

Run the application:

```bash theme={null}
uv run uvicorn app.main:app --reload
```

Verify that:

* A new file named **students.db** is created.
* The **students** table exists in the database.

***

# Step 7: Implement the Health Check Endpoint

**Objective**

Implement a Health Check endpoint to verify that the application can receive and respond to HTTP requests.

**Instructions**

* Open the `main.py` file.
* Implement a Health Check endpoint.
* Verify the endpoint using Swagger UI.

**Task**

Implement a Health Check endpoint and commit.

<Accordion title="Solution">
  **File:** `app/main.py`

  ```python theme={null}
  from fastapi import FastAPI
  from app.core.database import Base, engine
  from app.models.student import Student

  Base.metadata.create_all(bind=engine)

  app = FastAPI()


  @app.get("/health")
  def health_check():
      return {
          "status": "success",
          "message": "Student Management API is running."
      }
  ```

  Commit the entrypoint setup:

  ```bash theme={null}
  git add app/main.py
  git commit -m "feat: initialize main app with health check endpoint"
  ```
</Accordion>

**Run & Verify**

Open the following URL:

```text theme={null}
http://127.0.0.1:8000/health
```

Expected Response:

```json theme={null}
{
    "status": "success",
    "message": "Student Management API is running."
}
```

***

# Step 8: Create the Pydantic Schemas

**Objective**

Create Pydantic schemas to validate incoming requests and serialize outgoing responses.

**Instructions**

Create a `student.py` file inside the `schemas` folder and implement:

* `StudentCreate`
* `StudentUpdate`
* `StudentResponse`

Apply the following validation rules:

* **Name**: Required, min 2 chars, max 50 chars.
* **Age**: Required, between 1 and 120.
* **Email**: Required, valid email format.

**Task**

Define Pydantic schemas and commit.

<Accordion title="Solution">
  **File:** `app/schemas/student.py`

  ```python theme={null}
  from pydantic import BaseModel, EmailStr, Field

  class StudentBase(BaseModel):
      name: str = Field(..., min_length=2, max_length=50)
      age: int = Field(..., ge=1, le=120)
      email: EmailStr

  class StudentCreate(StudentBase):
      pass

  class StudentUpdate(BaseModel):
      name: str | None = Field(None, min_length=2, max_length=50)
      age: int | None = Field(None, ge=1, le=120)
      email: EmailStr | None = None

  class StudentResponse(StudentBase):
      id: int

      class ConfigDict:
          from_attributes = True
  ```

  Commit the schemas:

  ```bash theme={null}
  git add app/schemas/student.py
  git commit -m "feat: implement student validation schemas"
  ```
</Accordion>

**Run & Verify**

Verify that the validation schemas compile without errors.

***

# Step 9: Implement the Repository Layer

**Objective**

Abstract direct database operations away from business logic using the Repository Pattern.

**Instructions**

Create `app/repositories/student_repository.py` and implement operations:

* Retrieve all students.
* Search students using query parameters.
* Retrieve a student by ID/Email.
* Create, update, and delete student records.

**Task**

Implement StudentRepository and commit.

<Accordion title="Solution">
  **File:** `app/repositories/student_repository.py`

  ```python theme={null}
  from sqlalchemy import select
  from sqlalchemy.orm import Session
  from app.models.student import Student
  from app.schemas.student import StudentCreate, StudentUpdate

  class StudentRepository:
      def __init__(self, db: Session):
          self.db = db

      def get_all(self, name: str | None = None, email: str | None = None) -> list[Student]:
          stmt = select(Student)
          if name:
              stmt = stmt.where(Student.name.icontains(name))
          if email:
              stmt = stmt.where(Student.email.icontains(email))
          return list(self.db.execute(stmt).scalars().all())

      def get_by_id(self, student_id: int) -> Student | None:
          return self.db.get(Student, student_id)

      def get_by_email(self, email: str) -> Student | None:
          stmt = select(Student).where(Student.email == email)
          return self.db.execute(stmt).scalar_one_or_none()

      def create(self, student_in: StudentCreate) -> Student:
          db_student = Student(**student_in.model_dump())
          self.db.add(db_student)
          self.db.commit()
          self.db.refresh(db_student)
          return db_student

      def update(self, db_student: Student, student_in: StudentUpdate) -> Student:
          update_data = student_in.model_dump(exclude_unset=True)
          for key, value in update_data.items():
              setattr(db_student, key, value)
          self.db.commit()
          self.db.refresh(db_student)
          return db_student

      def delete(self, db_student: Student) -> None:
          self.db.delete(db_student)
          self.db.commit()
  ```

  Commit the repository file:

  ```bash theme={null}
  git add app/repositories/student_repository.py
  git commit -m "feat: implement StudentRepository data interfaces"
  ```
</Accordion>

**Run & Verify**

Verify that all Repository queries match standard SQLAlchemy syntax.

***

# Step 10: Configure Global Exception Handling

**Objective**

Define custom exception classes and globally catch errors inside the FastAPI application.

**Instructions**

* Create `app/core/exceptions.py`.
* Create `app/core/exception_handlers.py`.
* Define `AppException`, `StudentNotFoundError`, and register global handler functions.

**Task**

Implement custom exceptions, global handlers, and commit.

<Accordion title="Solution">
  **File:** `app/core/exceptions.py`

  ```python theme={null}
  class AppException(Exception):
      def __init__(self, message: str, status_code: int = 400):
          self.message = message
          self.status_code = status_code
          super().__init__(message)

  class StudentNotFoundError(AppException):
      def __init__(self, message: str = "Student not found"):
          super().__init__(message, status_code=404)
  ```

  **File:** `app/core/exception_handlers.py`

  ```python theme={null}
  from fastapi import Request
  from fastapi.responses import JSONResponse
  from app.core.exceptions import AppException

  async def app_exception_handler(request: Request, exc: AppException):
      return JSONResponse(
          status_code=exc.status_code,
          content={"detail": exc.message}
      )
  ```

  Commit exception modules:

  ```bash theme={null}
  git add app/core/exceptions.py app/core/exception_handlers.py
  git commit -m "feat: configure custom application exceptions and handlers"
  ```
</Accordion>

**Run & Verify**

Verify that custom exceptions extend the base `AppException`.

***

# Step 11: Implement the Service Layer

**Objective**

Coordinate business operations and enforce domain validation rules inside the Service Layer.

**Instructions**

* Create `app/services/student_service.py`.
* Implement registration, retrieval, updating, and deletion operations.
* Throw custom exceptions like `AppException` when an email already exists.

**Task**

Implement StudentService and commit.

<Accordion title="Solution">
  **File:** `app/services/student_service.py`

  ```python theme={null}
  from sqlalchemy.orm import Session
  from app.repositories.student_repository import StudentRepository
  from app.schemas.student import StudentCreate, StudentUpdate
  from app.core.exceptions import AppException, StudentNotFoundError
  from app.models.student import Student

  class StudentService:
      def __init__(self, db: Session):
          self.student_repo = StudentRepository(db)

      def register_student(self, student_in: StudentCreate) -> Student:
          existing = self.student_repo.get_by_email(student_in.email)
          if existing:
              raise AppException(message="Email already registered", status_code=400)
          return self.student_repo.create(student_in)

      def get_students(self, name: str | None = None, email: str | None = None) -> list[Student]:
          return self.student_repo.get_all(name=name, email=email)

      def get_student_by_id(self, student_id: int) -> Student:
          student = self.student_repo.get_by_id(student_id)
          if not student:
              raise StudentNotFoundError()
          return student

      def update_student(self, student_id: int, student_in: StudentUpdate) -> Student:
          student = self.get_student_by_id(student_id)
          if student_in.email:
              existing = self.student_repo.get_by_email(student_in.email)
              if existing and existing.id != student_id:
                  raise AppException(message="Email already registered", status_code=400)
          return self.student_repo.update(student, student_in)

      def remove_student(self, student_id: int) -> None:
          student = self.get_student_by_id(student_id)
          self.student_repo.delete(student)
  ```

  Commit the service file:

  ```bash theme={null}
  git add app/services/student_service.py
  git commit -m "feat: implement StudentService business logic"
  ```
</Accordion>

**Run & Verify**

Verify that services cleanly intercept logical failures and raise appropriate exception types.

***

# Step 12: Implement the REST API using APIRouter

**Objective**

Expose operations to users through API Routers using `Annotated` dependency injection.

**Instructions**

* Create `app/routers/student.py`.
* Map endpoints for creating, reading, updating, and deleting students.
* Inject database sessions cleanly using `Annotated` types.

**Task**

Implement student API endpoints and commit.

<Accordion title="Solution">
  **File:** `app/routers/student.py`

  ```python theme={null}
  from typing import Annotated
  from fastapi import APIRouter, Depends
  from sqlalchemy.orm import Session
  from app.core.database import get_db
  from app.schemas.student import StudentCreate, StudentUpdate, StudentResponse
  from app.services.student_service import StudentService

  router = APIRouter(prefix="/students", tags=["students"])

  DbSession = Annotated[Session, Depends(get_db)]

  @router.post("", response_model=StudentResponse, status_code=201)
  def register_student(db: DbSession, student_in: StudentCreate):
      student_service = StudentService(db)
      return student_service.register_student(student_in)

  @router.get("", response_model=list[StudentResponse])
  def read_students(
      db: DbSession,
      name: str | None = None,
      email: str | None = None
  ):
      student_service = StudentService(db)
      return student_service.get_students(name=name, email=email)

  @router.get("/{student_id}", response_model=StudentResponse)
  def read_student(db: DbSession, student_id: int):
      student_service = StudentService(db)
      return student_service.get_student_by_id(student_id)

  @router.put("/{student_id}", response_model=StudentResponse)
  def update_student(db: DbSession, student_id: int, student_in: StudentUpdate):
      student_service = StudentService(db)
      return student_service.update_student(student_id, student_in)

  @router.delete("/{student_id}", status_code=204)
  def delete_student(db: DbSession, student_id: int):
      student_service = StudentService(db)
      student_service.remove_student(student_id)
      return None
  ```

  Commit the router:

  ```bash theme={null}
  git add app/routers/student.py
  git commit -m "feat: implement student router and REST API endpoints"
  ```
</Accordion>

**Run & Verify**

Verify that router path endpoints align correctly with client specifications.

***

# Step 13: Integrate and Test the Student Management REST API

**Objective**

Integrate your routers and global exception handlers in the main entrypoint and run testing verifications.

**Instructions**

* Update `app/main.py`.
* Include routes, registers exception handlers, and start your uvicorn development server.

**Task**

Assemble main.py with endpoints router and verify application features.

<Accordion title="Solution">
  **File:** `app/main.py`

  ```python theme={null}
  from fastapi import FastAPI
  from app.core.database import Base, engine
  from app.core.exceptions import AppException
  from app.core.exception_handlers import app_exception_handler
  from app.routers import student

  # Create database tables
  Base.metadata.create_all(bind=engine)

  app = FastAPI(
      title="Student Management REST API",
      description="A modular API for student management using ORM",
      version="1.0.0"
  )

  # Register exception handlers
  app.add_exception_handler(AppException, app_exception_handler)

  # Include routers
  app.add_api_route("/health", lambda: {"status": "healthy"}, tags=["health"])
  app.include_router(student.router)
  ```

  Commit the final main configuration:

  ```bash theme={null}
  git add app/main.py
  git commit -m "feat: register student router and global exception handlers in app entrypoint"
  ```
</Accordion>

**Run & Verify**

Run the application:

```bash theme={null}
uv run uvicorn app.main:app --reload
```

Navigate to `http://127.0.0.1:8000/docs` to test registration, queries, and deletions.