FastAPI + SQLAlchemy Tutorial: Build a CRUD API Step-by-Step for Beginners | Web Engineering Notes
FastAPI + SQLAlchemy Tutorial: Build a CRUD API Step-by-Step for Beginners
Mar 7, 2026
FastAPISQLAlchemy
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.
Next.js + FastAPI Tutorial: Connect Frontend to Backend Step-by-Step
In this tutorial, you will learn how to connect a Next.js frontend to a FastAPI backend. We will build a simple full-stack application where the frontend fetches data from an API and displays it in the UI. You will also learn how to handle CORS, fetch API data, and understand how frontend and backend communicate in modern web applications.
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.
python
from sqlalchemy import Column, Integer, Stringfrom .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.
python
class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True, index=True) name = Column(String, index=True) email = Column(String, unique=True, index=True)
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:
After creating the SQLAlchemy model, our models.py will look like:
python
from sqlalchemy import Column, Integer, Stringfrom .database import Baseclass User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True, index=True) name = Column(String, index=True) email = Column(String, unique=True, 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.
python
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.
python
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.
python
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:
python
from pydantic import BaseModelclass UserCreate(BaseModel): name: str email: strclass 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.
python
from sqlalchemy.orm import Sessionfrom . 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.
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.
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.