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

Source: Dev.to

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:

• 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
    }
]

Enter fullscreen mode


Exit fullscreen mode

This worked for learning CRUD operations.

However, consider what happens when the server restarts:

FastAPI Server Stops
        ↓
Python Memory Cleared
        ↓
All Student Data Lost

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

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;

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

becomes:

class Student(Base):
    ...

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

If you haven’t installed FastAPI yet:

pip install fastapi uvicorn

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

This becomes repetitive.

Instead, FastAPI can automatically create and close sessions for us.

Later we’ll use:

db: Session = Depends(get_db)

Enter fullscreen mode


Exit fullscreen mode

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)

Enter fullscreen mode


Exit fullscreen mode

Understanding the Model

__tablename__ = "students"

Enter fullscreen mode


Exit fullscreen mode

This creates a table named:

students

Enter fullscreen mode


Exit fullscreen mode

id = Column(Integer, primary_key=True)

Enter fullscreen mode


Exit fullscreen mode

Creates the primary key.

Every student must have a unique ID.

name = Column(String)

Enter fullscreen mode


Exit fullscreen mode

Creates a text column.

The same applies to department.

cgpa = Column(Float)

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

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)

Enter fullscreen mode


Exit fullscreen mode

Creating Tables Automatically

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

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

What Happens Here?

db.add(new_student)

Enter fullscreen mode


Exit fullscreen mode

Adds the object to the session.

db.commit()

Enter fullscreen mode


Exit fullscreen mode

Permanently saves data to the database.

db.refresh(new_student)

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

Running the Application

Start the server:

uvicorn main:app --reload

Enter fullscreen mode


Exit fullscreen mode

Open:

http://127.0.0.1:8000/docs

Enter fullscreen mode


Exit fullscreen mode

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"

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

Install the MySQL driver:

pip install pymysql

Enter fullscreen mode


Exit fullscreen mode

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

Enter fullscreen mode


Exit fullscreen mode

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.