FastAPI + SQLite: Quickly Build a Lightweight Database API Service

In modern application development, quickly building database-driven API services is a common requirement. As a high-performance Python framework, FastAPI combined with SQLite, a lightweight embedded database, can help us rapidly implement simple yet practical backend services. This article will guide you through building a database API service from scratch using a complete “student information management” example.

I. Environment Setup: Install Required Tools¶

First, install FastAPI, the Python interface for SQLite (SQLAlchemy for ORM operations), and the server (Uvicorn). Open your terminal and run the following command:

pip install fastapi uvicorn sqlalchemy

FastAPI: A high-performance Python web framework that automatically generates API documentation and supports asynchronous operations.
Uvicorn: An ASGI server for running FastAPI applications.
SQLAlchemy: An ORM (Object-Relational Mapping) library that allows direct database operations via Python code without writing raw SQL.
SQLite: An embedded database that does not require a separate server, making it ideal for development and small-scale projects.

II. Core Concepts: Data Models and Database Connection¶

Using “student information” as an example, we first define data models and database connections. Create a main.py file and follow these steps:

1. Import Dependencies¶

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

2. Define Data Models¶

Use SQLAlchemy to define the database table structure (ORM model). We’ll create a Student model corresponding to the students table in the database:

# 1. Connect to the SQLite database (file-based, no additional server required)
SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"  # Database file path: test.db in the current directory

# 2. Create a SQLAlchemy engine (core connection object)
engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}  # SQLite-specific parameter
)

# 3. Create a local session class (for database operations)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 4. Define the base class (all ORM models inherit from this)
Base = declarative_base()

# 5. Define the Student model (corresponds to the database table)
class Student(Base):
    __tablename__ = "students"  # Table name

    id = Column(Integer, primary_key=True, index=True)  # Primary key, auto-increment
    name = Column(String, index=True, nullable=False)   # Student name (non-null)
    age = Column(Integer, nullable=True)               # Student age (optional)

3. Create Database Tables¶

On the first run, create the table structure using SQLAlchemy’s create_all() method:

# Create all tables (execute after defining models)
Base.metadata.create_all(bind=engine)

4. Define Pydantic Models (Data Validation and Serialization)¶

FastAPI requires explicit data formats for requests/responses. Use Pydantic to define these models:

# Model for request data (no id, auto-generated by the database)
class StudentCreate(BaseModel):
    name: str
    age: int | None = None  # Age is optional

# Model for response data (includes id)
class StudentResponse(BaseModel):
    id: int
    name: str
    age: int | None = None

    class Config:
        orm_mode = True  # Allows reading data from ORM objects (SQLAlchemy models)

III. Database Operations (CRUD)¶

Next, write functions to perform database operations (CRUD: Create, Read, Update, Delete) using SQLAlchemy sessions:

# Dependency: Get a database session (new session per request, closed after use)
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# 1. Create a student (POST /students)
def create_student(db: Session, student: StudentCreate):
    db_student = Student(name=student.name, age=student.age)
    db.add(db_student)
    db.commit()
    db.refresh(db_student)  # Refresh to get the auto-generated id
    return db_student

# 2. Get a single student (GET /students/{student_id})
def get_student(db: Session, student_id: int):
    return db.query(Student).filter(Student.id == student_id).first()

# 3. Get all students (GET /students)
def get_all_students(db: Session, skip: int = 0, limit: int = 100):
    return db.query(Student).offset(skip).limit(limit).all()

# 4. Update a student (PUT /students/{student_id})
def update_student(db: Session, student_id: int, student_update: StudentCreate):
    db_student = get_student(db, student_id)
    if not db_student:
        return None
    db_student.name = student_update.name
    db_student.age = student_update.age
    db.commit()
    db.refresh(db_student)
    return db_student

# 5. Delete a student (DELETE /students/{student_id})
def delete_student(db: Session, student_id: int):
    db_student = get_student(db, student_id)
    if not db_student:
        return False
    db.delete(db_student)
    db.commit()
    return True

IV. Build FastAPI Routes (API Endpoints)¶

Now, bind the CRUD functions to FastAPI routes to implement specific API interfaces:

# Create a FastAPI instance
app = FastAPI(title="Student Information Management API")

# 1. Create a student (POST request)
@app.post("/students", response_model=StudentResponse, status_code=201)
def create_student_endpoint(student: StudentCreate, db: Session = Depends(get_db)):
    db_student = create_student(db=db, student=student)
    return db_student

# 2. Get a single student (GET request)
@app.get("/students/{student_id}", response_model=StudentResponse)
def get_student_endpoint(student_id: int, db: Session = Depends(get_db)):
    db_student = get_student(db, student_id)
    if not db_student:
        raise HTTPException(status_code=404, detail="Student not found")
    return db_student

# 3. Get all students (GET request)
@app.get("/students", response_model=list[StudentResponse])
def get_all_students_endpoint(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    students = get_all_students(db, skip=skip, limit=limit)
    return students

# 4. Update a student (PUT request)
@app.put("/students/{student_id}", response_model=StudentResponse)
def update_student_endpoint(student_id: int, student: StudentCreate, db: Session = Depends(get_db)):
    db_student = update_student(db, student_id, student)
    if not db_student:
        raise HTTPException(status_code=404, detail="Student not found")
    return db_student

# 5. Delete a student (DELETE request)
@app.delete("/students/{student_id}", status_code=204)
def delete_student_endpoint(student_id: int, db: Session = Depends(get_db)):
    success = delete_student(db, student_id)
    if not success:
        raise HTTPException(status_code=404, detail="Student not found")
    return None

V. Run and Test the API Service¶

1. Start the Application¶

Run the service in the terminal with auto-reload for code changes:

uvicorn main:app --reload

main: Filename (main.py).
app: FastAPI instance name.
After startup, access http://127.0.0.1:8000/docs or http://127.0.0.1:8000/redoc to view interactive API documentation.

2. Test the API¶

Use FastAPI’s interactive documentation (Swagger UI) or command-line tools:

Create a Student:
Click POST /students in /docs → “Try it out” → Enter JSON data (e.g., {"name": "Alice", "age": 20}) → “Execute”.
Get a Single Student:
Click GET /students/{student_id} → Enter the student ID (e.g., 1) → “Execute”.
Other Operations:
Test GET /students (get all), PUT /students/{id} (update), and DELETE /students/{id} (delete) similarly.

VI. Summary¶

You’ve built a lightweight database API service with FastAPI + SQLite. Key advantages:

Lightweight & Easy: SQLite requires no server, and FastAPI auto-generates documentation, lowering development barriers.
High Performance: FastAPI supports asynchronous operations (suitable for high concurrency, though SQLite is single-threaded for small-to-medium scale).
Scalability: Extend with SQLAlchemy models and routes for complex business logic (e.g., multi-table relationships, permissions).

Full Code Example (copy to main.py):

[Full code as provided in the original article]

This example demonstrates how to quickly build a production-ready API service with minimal dependencies, making it ideal for prototyping or small-scale applications.