In the previous article, we explored how to build our first CRUD API using FastAPI. While our API worked correctly, there was one major problem.
We were storing data inside Python lists, which exist only in memory.
If you've ever wondered how applications like Instagram, LinkedIn, or ChatGPT remember information even after a server restart, the answer is simple: databases.
In this article, we'll solve the problem of in-memory storage by connecting our FastAPI application to SQLite using SQLAlchemy.
If you haven't read the previous post, check it out:
By the end of this article, you'll understand:
Previously, our application stored students inside a Python list.
students = [
{
"id": 1,
"name": "Ananya",
"department": "CSE",
"cgpa": 8.9
}
]
This worked for learning CRUD operations.
However, consider what happens when the server restarts:
FastAPI Server Stops
↓
Python Memory Cleared
↓
All Student Data Lost
This is unacceptable in real-world applications.
We need a place where data can survive application restarts.
This is where databases come in.
SQLite is a lightweight relational database.
Unlike MySQL or PostgreSQL, SQLite doesn't require a separate database server.
Instead, everything is stored inside a single file.
students.db
Advantages of SQLite:
For this article, we'll use SQLite.
Before SQLAlchemy, developers often wrote raw SQL queries.
Example:
SELECT * FROM students;
While SQL is powerful, writing queries everywhere quickly becomes difficult to maintain.
SQLAlchemy solves this problem using an ORM.
ORM stands for Object Relational Mapper.
It allows us to interact with database tables using Python classes.
Think of it like a translator.
| Database | Python |
|---|---|
| Table | Class |
| Row | Object |
| Column | Attribute |
For example:
Database table:
students
id name department cgpa
1 Ananya CSE 8.9
becomes:
class Student(Base):
...
Instead of writing SQL manually, we work with Python objects.
SQLAlchemy generates SQL behind the scenes.
Create the following structure:
project/
│
├── database.py
├── models.py
├── schemas.py
├── main.py
└── students.db
Each file has a specific responsibility.
Responsible for:
Responsible for:
Responsible for:
Responsible for:
pip install sqlalchemy
If you haven't installed FastAPI yet:
pip install fastapi uvicorn
Create a file named database.py
from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker
DATABASE_URL = "sqlite:///./students.db"
engine = create_engine(
DATABASE_URL,
connect_args={"check_same_thread": False} #allows the same connection to be used across threads
)
SessionLocal = sessionmaker(
autocommit=False,
autoflush=False,
bind=engine
)
Base = declarative_base()
Normally, SQLAlchemy uses transactional mode:
You make changes → they are staged in the session → you call commit() to persist them.
If autocommit is enabled, each statement is committed immediately (like SQLite’s default).
When autoflush=True (default), SQLAlchemy automatically flushes pending changes to the database before executing a query.
Flush means:
Synchronize in-memory changes with the database inside the current transaction.
Does not commit — changes are still rollback-able until you call commit().
engine = create_engine(...)
SQLAlchemy needs a way to communicate with the database.
The Engine object acts as the bridge between FastAPI and SQLite.
Whenever we:
SQLAlchemy uses the engine to talk to the database.
SessionLocal = sessionmaker(...)
A session represents a conversation with the database.
Imagine visiting a bank:
A database session works similarly.
Every database operation happens through a session.
Base = declarative_base()
Every database model we create will inherit from Base.
SQLAlchemy uses Base to keep track of all models and create tables automatically.
Add this function below the previous code.
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()
Without this function, every route would need to create and close sessions manually.
Example:
@app.get("/students")
def get_students():
db = SessionLocal()
db.close()
This becomes repetitive.
Instead, FastAPI can automatically create and close sessions for us.
Later we'll use:
db: Session = Depends(get_db)
FastAPI will:
This is called Dependency Injection.
Create a file named models.py
from sqlalchemy import Column, Integer, String, Float
from database import Base
class Student(Base):
__tablename__ = "students"
id = Column(Integer, primary_key=True, index=True)
name = Column(String)
department = Column(String)
cgpa = Column(Float)
__tablename__ = "students"
This creates a table named:
students
id = Column(Integer, primary_key=True)
Creates the primary key.
Every student must have a unique ID.
name = Column(String)
Creates a text column.
The same applies to department.
cgpa = Column(Float)
Creates a floating-point column.
Create a file named schemas.py
from pydantic import BaseModel
class StudentCreate(BaseModel):
name: str
department: str
cgpa: float
class StudentResponse(StudentCreate):
id: int
class Config:
from_attributes = True
Schemas define what data our API expects.
For now, think of schemas as blueprints.
We're using Pydantic behind the scenes.
We'll explore:
in a dedicated article later in this series.
from fastapi import FastAPI, Depends
from sqlalchemy.orm import Session
import models
import schemas
from database import engine, get_db
app = FastAPI()
models.Base.metadata.create_all(bind=engine)
models.Base.metadata.create_all(bind=engine)
When FastAPI starts:
Our Student table is now created inside SQLite.
@app.post("/student", response_model=schemas.StudentResponse)
def create_student(
student: schemas.StudentCreate,
db: Session = Depends(get_db)
):
new_student = models.Student(
name=student.name,
department=student.department,
cgpa=student.cgpa
)
db.add(new_student)
db.commit()
db.refresh(new_student)
return new_student
db.add(new_student)
Adds the object to the session.
db.commit()
Permanently saves data to the database.
db.refresh(new_student)
Reloads the object from the database.
This is useful because the database automatically generates the ID.
Get all students.
@app.get("/students")
def get_students(
db: Session = Depends(get_db)
):
return db.query(models.Student).all()
Get a student by ID.
@app.get("/student/{id}")
def get_student(
id: int,
db: Session = Depends(get_db)
):
return (
db.query(models.Student)
.filter(models.Student.id == id)
.first()
)
python
@app.put("/student/{id}")
def update_student(
id: int,
updated_student: schemas.StudentCreate,
db: Session = Depends(get_db)
):
student = (
db.query(models.Student)
.filter(models.Student.id == id)
.first()
)
if not student:
return {"message": "Student not found"}
student.name = updated_student.name
student.department = updated_student.department
student.cgpa = updated_student.cgpa
db.commit()
db.refresh(student)
return student
python
@app.delete("/student/{id}")
def delete_student(
id: int,
db: Session = Depends(get_db)
):
student = (
db.query(models.Student)
.filter(models.Student.id == id)
.first()
)
if not student:
return {"message": "Student not found"}
db.delete(student)
db.commit()
return {"message": "Student deleted successfully"}
Start the server:
uvicorn main:app --reload
Open:
http://127.0.0.1:8000/docs
Use Swagger UI to:
The good news is that SQLAlchemy makes switching databases extremely easy.
Current SQLite connection:
DATABASE_URL = "sqlite:///./students.db"
MySQL connection:
MYSQL_USER = "root"
DB_PASSWORD = "123456" # use your MySQL login password
MYSQL_HOST = 'localhost'
MYSQL_PORT = '3306'
MYSQL_DATABASE = 'fastapi_db'
DATABASE_URL = f"mysql+pymysql://{MYSQL_USER}:{DB_PASSWORD}@{MYSQL_HOST}:{MYSQL_PORT}/{MYSQL_DATABASE}"
Install the MySQL driver:
pip install pymysql
Everything else remains almost identical. Ensure you have MySQL in your desktop, open MySQL WorkBench and connect to database to see the database and tables in it.
This is one of the biggest advantages of using an ORM.
Client Request
│
▼
FastAPI Route
│
▼
Pydantic Schema
│
▼
Database Session
│
▼
SQLAlchemy Model
│
▼
SQLite / MySQL
When a user creates a student:
We've now moved beyond in-memory storage and built our first database-backed FastAPI application.
Most production AI applications use the same architecture, whether they're storing chat histories, user profiles, agent memory, evaluation results, or feedback data.
In the next article, we'll take a deeper look at Pydantic and understand how FastAPI validates incoming data automatically.