Ananya S

Posted on Jun 6

FastAPI for AI Engineers - Part 3: Connecting to a database

#ai #backend #fastapi #python

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:

Ananya S

Jun 1

FastAPI for AI Engineers - Part 2: Building Your First CRUD API

#ai #backend #fastapi #python

4 min read

By the end of this article, you'll understand:

Why in-memory storage is a problem
What SQLite is
What SQLAlchemy is
How ORM works
How to create database tables using Python classes
How to perform CRUD operations using a real database

The Problem with In-Memory Storage

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.

What is SQLite?

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:

No installation required
Lightweight
Easy to learn
Perfect for local development
Great for small projects

For this article, we'll use SQLite.

What is SQLAlchemy?

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.

What is 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.

Project Structure

Create the following structure:

project/
│
├── database.py
├── models.py
├── schemas.py
├── main.py
└── students.db

Each file has a specific responsibility.

database.py

Responsible for:

Database connection
Session creation
Base class creation

models.py

Responsible for:

Database tables

schemas.py

Responsible for:

Request validation
Response structure

main.py

Responsible for:

API routes
Business logic

Installing Dependencies

pip install sqlalchemy

If you haven't installed FastAPI yet:

pip install fastapi uvicorn

Step 1: Creating database.py

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().

Understanding create_engine()

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:

insert data
retrieve data
update data
delete data

SQLAlchemy uses the engine to talk to the database.

Understanding SessionLocal

SessionLocal = sessionmaker(...)

A session represents a conversation with the database.

Imagine visiting a bank:

Start conversation
Perform transactions
End conversation

A database session works similarly.

Every database operation happens through a session.

Understanding Base

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.

Creating Database Sessions

Add this function below the previous code.

def get_db():

    db = SessionLocal()

    try:
        yield db

    finally:
        db.close()

Why Do We Need get_db()?

Without this function, every route would need to create and close sessions manually.

Example:

@app.get("/students")
def get_students():

    db = SessionLocal()

    # Database operations

    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:

Create a session
Give it to the route
Close it automatically

This is called Dependency Injection.

Step 2: Creating models.py

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)

Understanding the Model

__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.

Step 3: Creating schemas.py

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

Why Do We Need Schemas?

Schemas define what data our API expects.

For now, think of schemas as blueprints.

We're using Pydantic behind the scenes.

We'll explore:

Validation
Optional fields
Custom validators
Response models

in a dedicated article later in this series.

Step 4: Creating main.py

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)

Creating Tables Automatically

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

When FastAPI starts:

SQLAlchemy checks all models.
Looks for missing tables.
Creates them automatically.

Our Student table is now created inside SQLite.

CREATE Operation

@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

What Happens Here?

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.

READ Operation

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()
    )

UPDATE Operation

@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

DELETE Operation

@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"}

Running the Application

Start the server:

uvicorn main:app --reload

Open:

http://127.0.0.1:8000/docs

Use Swagger UI to:

Create students
Retrieve students
Update students
Delete students

SQLite vs MySQL

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.

How Everything Works Together

Client Request
      │
      ▼
FastAPI Route
      │
      ▼
Pydantic Schema
      │
      ▼
Database Session
      │
      ▼
SQLAlchemy Model
      │
      ▼
SQLite / MySQL

When a user creates a student:

FastAPI receives the request
Pydantic validates the incoming data
A database session is created
SQLAlchemy converts the Python object into SQL
SQLite stores the data permanently

Conclusion

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.

DEV Community

FastAPI for AI Engineers - Part 3: Connecting to a database

FastAPI for AI Engineers - Part 2: Building Your First CRUD API

The Problem with In-Memory Storage

What is SQLite?

What is SQLAlchemy?

What is an ORM?

Project Structure

database.py

models.py

schemas.py

main.py

Installing Dependencies

Step 1: Creating database.py

Understanding create_engine()

Understanding SessionLocal

Understanding Base

Creating Database Sessions

Why Do We Need get_db()?

Step 2: Creating models.py

Understanding the Model

Step 3: Creating schemas.py

Why Do We Need Schemas?

Step 4: Creating main.py

Creating Tables Automatically

CREATE Operation

What Happens Here?

READ Operation

UPDATE Operation

DELETE Operation

Running the Application

SQLite vs MySQL

How Everything Works Together

Conclusion

Top comments (0)