FastAPI + SQLAlchemy Tutorial: Build a CRUD API Step-by-Step for Beginners

Introduction

FastAPI is a modern Python framework used to build high performance backend APIs. However, API alone is not useful without a database to store and manage data.

SQLAlchemy is a powerful ORM that allows FastAPI applications to interact with databases and perform CRUD operations such as creating, reading, updating and deleting records.

Many beginners struggle when trying to integrate SQLAlchemy with FastAPI.

In this beginner-friendly tutorial, you will learn how to build a simple CRUD API using FastAPI and SQLAlchemy step by step.

Table of Contents

1. Prerequisites
2. Project Setup
3. Project Structure
4. Creating the Database Connection
5. Creating SQLAlchemy Models
6. Creating Pydantic Schemas
7. Implementing CRUD Operations
8. Creating FastAPI Routes
9. Running and Testing the API
10. Common Errors and Fixes
11. Conclusion
12. Next Steps

Prerequisites

Before starting this tutorial, make sure you have the following installed:

• Python 3.9 or later
• Basic knowledge of Python
• Basic understanding of REST APIs
• A code editor such as VS Code

Project Setup

In this section, we will build a new FastAPI project and install the required dependencies.

1. Create a Project Folder

First, create a new folder for the project and navigate into it.

mkdir fastapi-sqlalchemy-tutorial
cd fastapi-sqlalchemy-tutorial

2. Create a Virtual Environment

It is recommended to use a virtual environment to manage project dependencies.

python -m venv venv

Activate the virtual environment.

Mac/Linux

source venv/bin/activate

Windows

venv\Scripts\activate

3. Install Required Packages

Now install the required dependencies for this project.

pip install fastapi uvicorn sqlalchemy

These packages are used for the following purposes.

• FastAPI — used to build the API
• Uvicorn — an ASGI server used to run the FastAPI application.
• SQLAlchemy — an ORM used to interact with the database.

Project Structure

Before we start writing code, let’s organize our project structure.

Create the following files inside your project folder.

fastapi-sqlalchemy-tutorial
│
├── app
│   ├── main.py
│   ├── database.py
│   ├── models.py
│   ├── schemas.py
│   └── crud.py

Each file has a specific purpose:

• main.py – This is the main entry point of the FastAPI Application where API routes are defined.
• database.py – This file configures the database connection.
• models.py – Contains SQLAlchemy models that represents database tables.
• schemas.py – Contains Pydantic schemas for request and response validation.
• crud.py – Contain functions that perform database operations such as create, read, update, and delete.

Now that our project structure is ready, let’s create the database connection using SQLAlchemy.

Creating the Database Connection

Before we can store or retrieve data, we need to connect our FastAPI application to a database.

In this tutorial, we will use SQLAlchemy to manage the database connection and interact with the database.

Step 1 — Import Required Modules

First, open the database.py file and import the required modules.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import declarative_base

These modules are used for the following purposes:

• create_engine – creates the connection between the application and the database
• sessionmaker – creates database sessions used to perform operations
• declarative_base – used to create database models

Step 2 — Define the Database URL

Now define the database connection string.

For beginners, SQLite is best because it requires no installation.

DATABASE_URL = "sqlite:///./test.db"

This url tells SQLAlchemy to use a SQLite database named test.db in the project directory.

Step 3 — Create the Database Engine

Now create the engine.

engine = create_engine(
    DATABASE_URL, connect_args={"check_same_thread": False}
)

This engine is responsible for establishing the connection between the application and the database.

”check_same_thread”:False allows SQLite to be used with FastAPI since FastAPI runs multiple requests at a time.

Step 4 — Create SessionLocal

Now create the session factory.

SessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine
)

SessionLocal is used to create database sessions. These sessions allow us to perform operations such as inserting, updating and deleting records.

Step 5 — Create Base Class

Now create the base class for models.

Base = declarative_base()

The Base class will be used to create SQLAlchemy models. Each model will represent a table in the database.

Final database.py

After performing all the steps, our database.py will finally look like:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import declarative_base
 
DATABASE_URL = "sqlite:///./test.db"
 
engine = create_engine(
    DATABASE_URL, connect_args={"check_same_thread": False}
)
 
SessionLocal = sessionmaker

Now that the database connection is configured, let’s create the SQLAlchemy models that will represent the database tables.

Creating SQLAlchemy Models

SQLAlchemy models define the structure of the database tables. Each model represents a database table, and each attribute in the model represents a column in the table.

In this tutorial we will create a simple User model.

Step 1 — Import Required Modules

Open the models.py file and import the required modules.

from sqlalchemy import Column, Integer, String
from .database import Base

These imports are used for the following purposes.

• Column is used to define a column in the database table.
• Integer and String define the data types of the columns.
• Base is the base class that all SQLAlchemy models must inherit from.

Step 2 — Create the User Model

Now create the table.

class User(Base):
    __tablename__ = "users"
 
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    email = Column(String, unique=True, index

Let's understand what each part does:

• User is the model class that represents the table.
• __tablename__ defines the name of the database table.
• id is the primary key used to uniquely identify each user.
• name stores the user name.
• email stores the user's email address.

This model will create the following table in the database:

users
-------------------------
id      | integer
name    | string
email   | string

Final models.py

After creating the SQLAlchemy model, our models.py will look like:

from sqlalchemy import Column, Integer, String
from .database import Base
 
class User(Base):
    __tablename__ = "users"
 
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True

Now that we have defined the database models, the next step is to create pydantic schemas that will be used to validate API requests and responses.

Creating Pydantic Schemas

Pydantic schemas are used to validate the data sent to and returned from the API. They ensure the data follows the correct structure and data types.

In this tutorial, we will create schemas for the User model.

Step 1 — Import Pydantic

Open the schemas.py file and import the required module.

from pydantic import BaseModel

BaseModel is used to create pydantic schemas that define the structure of API requests and responses.

Step 2 — Create User Schema for Requests

This schema will be used when creating a new user.

class UserCreate(BaseModel):
    name: str
    email: str

This schema defines the data required to create a new user.

• name stores the user name and the data type is a string.
• email stores the user’s email address and the data type is a string.

Step 3 — Create Schema for API Responses

Now create the response schema.

class UserResponse(BaseModel):
    id: int
    name: str
    email: str
 
    class Config:
        orm_mode = True

This schema defines the structure of the data returned by the API.

orm_mode=True allows Pydantic to read the data directly from SQLAlchemy models.

We created two schemas. Let’s understand the difference.

• UserCreate – used when sending data to the API.
• UserResponse – used when returning data from the API.

Final schema.py

After defining the Pydantic schemas our schemas.py finally looks like:

from pydantic import BaseModel
 
class UserCreate(BaseModel):
    name: str
    email: str
 
 
class UserResponse(BaseModel):
    id: int
    name: str
    email: str
 
    class Config:
        orm_mode = True

Now that we have defined our database models and Pydantic schemas, the next step is to implement the CRUD operations that will interact with the database.

Implementing CRUD Operations

CRUD stands for Create, Read, Update, Delete. These operations allow us to interact with the database and manage data.

In this section, we will implement CRUD functions for the User model using SQLAlchemy.

Step 1 — Import Required Modules

Open the crud.py file and import the required modules.

from sqlalchemy.orm import Session
from . import models, schemas

• Session is used to interact with the database.
• models contains the SQLAlchemy database model.
• schemas contains the Pydantic schemas used for validation.

Step 2 — Create a User

This function will insert a new user into the database.

def create_user(db: Session, user: schemas.UserCreate):
    db_user = models.User(name=user.name, email=user.email)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user

• db.add() adds the new user to the session.
• db.commit() saves the changes to the database.
• db.refresh() retrieves the newly created record.

Step 3 — Get all Users

Now create a function to retrieve all users.

def get_users(db: Session):
    return db.query(models.User).all()

Step 4 — Get User by ID

def get_user(db: Session, user_id: int):
    return db.query(models.User).filter(models.User.id == user_id).first()

This function retrieves the single user using the user’s ID.

Step 5 — Delete a User

def delete_user(db: Session, user_id: int):
    user = db.query(models.User).filter(models.User.id == user_id).first()
 
    if user:
        db.delete(user)
        db.commit()
 
    return user

This function finds a user by ID and deletes the record from the database.

Final crud.py

After following all the steps, our crud.py file will look like:

from sqlalchemy.orm import Session
from . import models, schemas
 
 
def create_user(db: Session, user: schemas.UserCreate):
    db_user = models.User(name=user.name, email=user.email)
    db.add(db_user)
    db.commit()
    db.refresh

Now that we have implemented the CRUD functions, the next step is to create API routes in FastAPI that will call these functions.

Creating FastAPI Routes

Now that we have created database models, schemas, and CRUD functions, we can create API routes that will allow clients to interact with our application.

The routes will call the CRUD functions to perform operations on the database.

Step 1 — Import Required Modules

Open the main.py file and import the required modules.

from fastapi import FastAPI, Depends
from sqlalchemy.orm import Session
 
from . import models, schemas, crud
from .database import SessionLocal, engine

• FastAPI is used to create the API application.
• Depends is used for dependency injection.
• Session is used to interact with the database.
• crud, models, and schemas contain the logic we created earlier.

Step 2 — Create the FastAPI App

Now initialize the application.

app = FastAPI()

This creates a new FastAPI application instance.

Step 3 — Create Database Tables

Now create the tables from the models.

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

This line tells SQLAlchemy to create the database tables based on the models we defined earlier.

Step 4 — Create Database Dependency

We need a function that provides a database session for each request.

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

This function creates a database session for each request and closes it after the request is completed.

Step 5 — Create API Routes

Create User:

@app.post("/users", response_model=schemas.UserResponse)
def create_user(user: schemas.UserCreate, db: Session = Depends(get_db)):
    return crud.create_user(db, user)

This endpoint creates a new user in the database.

Get All Users:

@app.get("/users", response_model=list[schemas.UserResponse])
def read_users(db: Session = Depends(get_db)):
    return crud.get_users(db)

This endpoint retrieves all the users from the database.

Get User By ID:

@app.get("/users/{user_id}", response_model=schemas.UserResponse)
def read_user(user_id: int, db: Session = Depends(get_db)):
    return crud.get_user(db, user_id)

This endpoint retrieves a single user using the user ID.

Delete User:

@app.delete("/users/{user_id}")
def delete_user(user_id: int, db: Session = Depends(get_db)):
    return crud.delete_user(db, user_id)

This endpoint deletes the user from the database.

Final main.py

Now that we have defined API routes, our final main.py will look like:

from fastapi import FastAPI, Depends
from sqlalchemy.orm import Session
 
from . import models, schemas, crud
from .database import SessionLocal, engine
 
app = FastAPI()
 
models.Base.metadata.create_all(bind=engine)
 
 
def get_db():
    db =

Now that our API routes are ready, let’s run the FastAPI application and test the endpoints using the built-in interactive API documentation.

Running and Testing the API

Now that our API routes are ready, we can run the FastAPI application and test the endpoints.

Step 1 — Run the Application

Run the following command in the project directory.

uvicorn app.main:app --reload

• app.main refers to the main.py file inside the app folder.
• app is the FastAPI application instance.
• --reload automatically reloads the server when code changes.

Step 2 — Open the API Documentation

Once the server start, open the browser and go to:

http://127.0.0.1:8000/docs

FastAPI automatically generates interactive API documentation using Swagger UI. This allows you to test your APIs directly from the browser.

Step 3 — Test the Endpoints

You can now test the endpoints.

Create a User

Use the Post /users endpoints and send the following request body:

{
  "name": "John Doe",
  "email": "john@example.com"
}

Get All Users

Use the Get /users endpoint to retrieve all users stored in the database.

Get User by ID

Use the GET /user/{user_id} endpoint to retrieve a specific user.

Delete a User

Use the DELETE /user/{user_id} endpoint to remove a user from the database.

When you call an API endpoint:

1. FastAPI receives the request.
2. The requested data is validated using Pydantic schemas.
3. The route calls the corresponding CRUD function.
4. SQLAlchemy performs the database operation.
5. The response is returned to the client using the Pydantic response model.

Common Errors and Fixes

Here are some common errors you might encounter while building a FastAPI + SQLAlchemy API, and how to fix them.

SQLite Thread Error

Error: SQLite objects created in a thread can only be used in that same thread

Fix: Add connect_args={"check_same_thread": False} when creating the engine in database.py.

User Already Exists Error

Error: UNIQUE constraint failed: users.email

Fix: Ensure that each user email is unique before adding a new record, or handle the exception using try/except.

Congratulations! You have successfully built a CRUD API using FastAPI and SQLAlchemy.

If you want to learn how to connect FastAPI API to a Next.js frontend, I recommend you to read our following tutorial:

Next.js + FastAPI Tutorial: Connect Frontend to Backend Step-by-Step

Let's summarize what we learned in this tutorial.

Conclusion

In this tutorial, we learned how to build a simple CRUD API using FastAPI and SQLAlchemy.

We started by setting up the project and organizing the folder structure. Then we configured the database connection using SQLAlchemy and created models to represent database tables. After that, we defined Pydantic schemas to validate API requests and responses.

Next, we implemented CRUD functions to interact with the database and created API routes to expose these operations through FastAPI. Finally, we ran the application and tested the endpoints using the interactive API documentation.

By the end of this tutorial, you now understand how FastAPI works with SQLAlchemy to build database-driven APIs.

Next Steps

To improve this project further, you can try the following:

• Add update functionality for users.
• Connect the application to PostgreSQL instead of SQLite.
• Implementing authentication with JWT. If you want to learn how to secure your API, read this guide on JWT Authentication in FastAPI.
• Deploy the API to a cloud server.