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

A developer has connected a FastAPI application to an SQLite database using SQLAlchemy, replacing the previous in-memory Python list storage that caused data loss on server restart. The implementation uses SQLAlchemy's Object Relational Mapper (ORM) to interact with database tables through Python classes, with the database configuration handled in a dedicated `database.py` file. This setup ensures student data persists across application restarts, addressing a critical limitation of the earlier CRUD API.

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 python 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. python 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: python @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: This is called Dependency Injection. Create a file named models.py python 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 python 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. python 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. python @app.get "/students" def get students db: Session = Depends get db : return db.query models.Student .all Get a student by ID. python @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.