Introduction
Modern backend applications often need to store and manage data in a database. A common way to achieve this in Python is by combining FastAPI with SQLAlchemy and PostgreSQL.
FastAPI is a modern Python framework used to build high performance APIs. SQLAlchemy is a powerful ORM that allows Python applications to interact with databases using Python objects instead of raw SQL queries. Postgres is a popular open-source relational database used in many production systems.
In this tutorial, you will learn how to build a complete CRUD API using FastAPI, SQLAlchemy and PostgresSQL.
CRUD stands for:
- Create – add new records
- Read – retrieve data
- Update – modify existing records
- Delete – remove records
By the end of this tutorial, you will have a fully working backend API that can create, read, update and delete users stored in a PostgreSQL database.
If you want to learn the same CRUD tutorial using SQLite instead of PostgreSQL, you can read my previous guide:
Build a CRUD API with FastAPI and SQLAlchemy using SQLite
That tutorial focuses on setting up a lightweight SQLite Database, which is great for beginners and small projects.
Table of Contents
- Prerequisites
- Project Overview
- Project Setup
- Installing Dependencies
- Setting Up PostgreSQL Database
- Creating the Database Connection
- Creating SQLAlchemy Models
- Creating Pydantic Schemas
- Implementing CRUD Operations
- Creating FastAPI Routes
- Running and Testing the API
- Common Errors and Fixes
- Conclusion
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
- PostgresSQL installed on your system
- A code editor such as VS Code
In this tutorial, we will build a backend API that stores and manages user data in a PostgreSQL database.
Project Overview
In this tutorial, we will build a simple backend API that performs CRUD operations using FastAPI, SQLAlchemy, and PostgreSQL.
The API will manage a list of users stored in a PostgreSQL database. Each user will have fields such as ID, name and email.
This API will allow clients to:
- Create a new user
- Retrieve all users
- Retrieve a single user by ID
- Update a user
- Delete a user
Application Architecture
The backend will use the following architecture:
Client Request
↓
FastAPI Routes
↓
CRUD Functions
↓
SQLAlchemy ORM
↓
PostgreSQL DatabaseStep-by-step flow:
- A client sends an HTTP request to the FastAPI API.
- FastAPI routes handle the request.
- CRUD functions perform database operation.
- SQLAlchemy communicates with the PostgreSQL database.
- The database returns the result.
- FastAPI sends the response back to the client.
API Endpoints We Will Build
During this tutorial, we will build the following endpoints:
POST /users— Creates a new userGET /users— Get all usersGET /user/{id}— Get a single user by IDPUT /user/{id}— Update a userDELETE /user/{id}— Delete a user
These endpoints represent the four main CRUD operations used in most backend applications.
Project Structure
Our project will follow a clean and organized structure.
fastapi-crud-app
│
├── app
│ ├── __init__.py
│ ├── main.py
│ ├── database.py
│ ├── models.py
│ ├── schemas.py
│ └── crud.py
│
└── requirements.txtEach file has a specific responsibility:
- main.py — FastAPI application and routes.
- database.py — Database configuration and connection.
- models.py — SQLAlchemy database models.
- schemas.py — Pundantic schemas for request and response validation.
- crud.py — Functions that interact with the database.
By organizing the project this way, the code becomes easier to maintain and scale as the application grows.
Project Setup
In this section, we will set up the project environment for our CRUD API.
We will:
- Create the project folder
- Create a virtual environment
- Initialize the project structure
Step 1 – Create the Project Folder
First, create a new folder for the project.
mkdir fastapi-crud-app
cd fastapi-crud-appThis folder will contain all the files related to our FastAPI CRUD application.
Step 2 – Create a Virtual Environment
A virtual environment keeps project dependencies isolated from other python projects.
Create a virtual environment using the following command:
python -m venv venvActivate the virtual environment.
Mac/Linux:
source venv/bin/activateWindows:
venv\Scripts\ActivateOnce activated, your terminal should display venv at the beginning of the prompt.
Step 3 – Create the Application Folder
Now create the folder to store application code.
mkdir app
cd appInside this folder we will place all the backend modules such as models, schemas, and database configuration.
Step 4 – Create the Project Files
Create the following files inside the app folder:
touch main.py database.py models.py schemas.py crud.pyYour project structure should now look like this:
fastapi-crud-app
│
├── app
│ ├── main.py
│ ├── database.py
│ ├── models.py
│ ├── schemas.py
│ └── crud.pyNow that our project structure is ready, the next step is to install the dependencies required to build a CRUD API.
Installing Dependencies
To build our FastAPI CRUD API with SQLAlchemy and PostgreSQL, we need to install a few Python packages.
pip install --upgrade pip fastapi "uvicorn[standard]" sqlalchemy psycopg2-binary python-dotenvThese dependencies are used for the following purposes:
fastapi– the main framework for building CRUD APIs.uvicorn– It is an ASGI server used to run the FastAPI application.sqlalchemy– It is the ORM that allows us to interact with PostgreSQL using Python classes.psycopg2-binary– to connect SQLAlchemy with PostgreSQL.python-dotenv– used to load Python environmental variables.
Verify Installation
You can verify the installation by running:
python -c "import fastapi, sqlalchemy, psycopg2; print('All packages installed!')"If you see All packages installed you are ready to move on.
Setting Up PostgreSQL Database
In this section, we will create a PostgreSQL database for our CRUD application. Our FastAPI application will store and retrieve user data from this database.
Step 1 – Open the PostgreSQL Terminal
Open the PostgreSQL terminal by running the following command:
psql -U postgresYou may be asked to enter the PostgreSQL password.
Once connected, you will see a prompt simialr to this:
postgres=#Step 2 – Create a New Database
Now create a new database for the application.
CREATE DATABASE fastapi_crud_dbYou should see the following response that means the database has been created:
CREATE DATABASEStep 3 – Verify the Database
To see the list of databases, run:
\lYou should see fastapi_crud_db in the list.
Step 4 – Connect to the Database
Now connect to the newly created database.
\c fastapi_crud_dbIf successful, postgres will respond something like:
You are now connected to the database "fastapi_crud_db".Step 5 – Exit PostgreSQL
You can exit the PostgreSQL terminal with:
\qNow the database is ready and we can connect it to our FastAPI application using SQLAlchemy.
Creating the Database Connection
Now that the PostgreSQL database is ready, we need to connect it to our FastAPI application using SQLAlchemy.
All the database configuration will be placed inside the database.py file.
Step 1 – Open the database.py File
Navigate to the app folder and open database.py.
This file will handle the connection between our application and the PostgreSQL database.
Step 2 – Import Required Modules
First, import the necessary modules.
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import declarative_baseThese modules are used for the following purposes:
create_engine– Creates the database connection.sessionmaker– Creates database sessions used for database queries.declarative_base– Base class for SQLAlchemy models that represents the table in the database.
Step 3 – Define the Database URL
To connect with our PostgreSQL database, we need to define a connection string. The database URL must follow the following pattern:
postgresql://username:password@host:port/database_nameLet’s define a connection string using the above pattern.
DATABASE_URL = "postgresql://postgres:password@localhost:5432/fastapi_crud_db"postgres– database usernamepassword– PostgreSQL database passwordlocalhost– database server5432– PostgreSQL default portfastapi_crud_db– database name
Note: Make sure you replace the password with your actual PostgreSQL password.
Step 4 – Create the SQLAlchemy Engine
Now create the engine that connects to PostgreSQL.
engine = create_engine(DATABASE_URL)The engine is responsible for managing the connection to the database.
Step 5 – Create Session Factory
Next, create a session factory that will generate database sessions.
SessionLocal = sessionmaker(
autocommit=False,
autoflush=False,
bind=engine
)Each request in the FatsAPI will use a database session created by this session factory.
Step 6 – Create a Base Class for Models
Finally, define a base class for our SQLAlchemy models.
Base = declarative_base()All database models will inherit from this base class.
Final database.py File
Your database.py should now look like this:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import declarative_base
DATABASE_URL = "postgresql://postgres:password@localhost:5432/fastapi_crud_db"
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(
autocommit=False,
autoflush=
This configuration allows FastAPI to communicate with the PostgreSQL database using SQLAlchemy.
Creating SQLAlchemy Models
In this section, we will create the database models for our application.
SQLAlchemy models define the structure of database tables using python classes. Each class represents a table, and each attribute represents a column in that table.
We will create a User model that will store user information in the PostgreSQL database.
Step 1 – Open the models.py File
Navigate to the app folder and open models.py file.
This file will contain all the database models for our application.
from sqlalchemy import Column, Integer, String
from .database import BaseThese modules serve the following purposes:
Column– Defines a column in the database table.Integer– Defines the integer data type of column.String– Defines the string data type of column.Base– Base class we defined earlier in thedatabase.pyfile used to connect the model to SQLAlchemy
Step 3 – Create the User Model
Now create the user model.
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, index=True)
name = Column(String, nullable=False)
email = Column(String, unique=True, indexLet's understand what each part does:
Useris the model class that represents the table.__tablename__defines the name of the database table.idis the primary key used to uniquely identify each user.namestores the user name.emailstores the user's email address.primary_key=Truealso means values cannot repeat and cannot be empty, because it’s the main identifier of the row.nullable=Falsemeans that the column value cannot be NULL.unique=Truemeans values cannot repeat, but it’s okay to leave them empty.
Final models.py
Your file should now look like this:
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, nullable=False
This models defines the structure of the users table that will be created in the PostgreSQL database.
Now that we have defined the SQLAlchemy models, the next step is to define Pydantic schemas for request and response validation.
Creating Pydantic Schemas
Pydantic schemas are used in FastAPI to:
- Validate incoming request data.
- Defines the structure of API responses.
- Serialize and deserialize data
We will create three schemas for the User model:
- UserCreate – for creating a new user
- UserUpdate – for updating user data
- UserResponse – for sending user data in API responses
Step 1 – Open the schemas.py File
Navigate to the app folder and open schemas.py file.
Step 2 – Import Required Module
Add the following imports:
from pydantic import BaseModel, EmailStrBaseModel– Base class for pydantic Schemas.EmailStr– Validates that the email is in a proper format.
Step 3 – Create UserCreate Schema
class UserCreate(BaseModel):
name: str
email: EmailStrThis schema ensure that any request to create a user must contain:
- A
namefield of type string. - An
emailfield of valid email format.
Step 4 – Create UserUpdate Schema
class UserUpdate(BaseModel):
name: str | None = None
email: EmailStr | None = None- All fields are optional None.
- Allows partial update of user record.
Step 5 – Create UserResponse Schema
class UserResponse(BaseModel):
id: int
name: str
email: EmailStr
class Config:
orm_mode = Trueorm_mode=Truetells pydantic to work with SQLAlchemy models. However If you want understand it fully, I recommend you to read our article Why orm_mode = True is Required in FastAPI (Simple Explanation)- This schema is used when sending user data back to the client.
Final schemas.py File
After performing all the steps your schemas.py should look like:
from pydantic import BaseModel, EmailStr
class UserCreate(BaseModel):
name: str
email: EmailStr
class UserUpdate(BaseModel):
name: str | None = None
email: EmailStr | None = None
class UserResponse(BaseModel):
Implementing CRUD Operations
We will write all CRUD operations in the crud.py file. Each function will interact with the database using SQLAlchemy sessions.
Step 1 – Open the crud.py File
Navigate to the app folder and open crud.py file.
Add the following imports:
from sqlalchemy.orm import Session
from . import models, schemasSession– used to interact with the database.models– Our SQLAlchemy models.schemas– Our pydantic schemas
Step 3 – Create a User
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_userThis functions will performs the following steps:
- Creates a new
userobject from the schema - Add it to the session (
db.add) - Commit the transaction to save it in the database
- Refresh to get the updated object with
id - Return the created user
Step 4 – Get All Users
def get_users(db: Session, skip: int = 0, limit: int = 100):
return db.query(models.User).offset(skip).limit(limit).all()This function gets all users in the database by performing the following steps:
- Query all users from the database
- Support pagination using
skipandlimit
Step 5 – Get a Single User
This function gets a single user by user id.
def get_user(db: Session, user_id: int):
return db.query(models.User).filter(models.User.id == user_id).first()This function will perform the following steps:
- Filters users by id
- Return the first match or
Noneif not found
Step 6 – Update a User
To update a user we use the following function:
def update_user(db: Session, user_id: int, user: schemas.UserUpdate):
db_user = db.query(models.User).filter(models.User.id == user_id).first()
if not db_user:
return None
if user.name is not None:
db_user.name = user.name
Let’s understand how this function works.
- Fetches user by id
- Updates fields if provided
- Commit the changes and returns the object
Step 7 – Delete a User
def delete_user(db: Session, user_id: int):
db_user = db.query(models.User).filter(models.User.id == user_id).first()
if not db_user:
return None
db.delete(db_user)
db.commit()
return db_userThis function:
- Finds the user
- Delete it from database
- Returns the deleted user object
Final crud.py File
After adding all the functions, our crud.py should look like this:
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(db_user)
Now that the CRUD functions are ready, the next step is to define routes that actually use these functions.
Creating FastAPI Routes
We will define API routes in the main.py file. Each route will use the CRUD functions we created earlier.
Step 1 – Open the main.py File
Navigate to the app folder and open main.py file.
Step 2 – Import the Required Modules
Add the following imports:
from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session
from . import models, schemas, crud
from .database import SessionLocal, engineThe above imports are used for the following purposes:
FastAPI– Framework for building API routes.Depends– Dependency injection for database session.HTTPException– Used to return HTTP errors to the client.Session Localandengine– Used for database sessions and connection.model,schemas,crud– Our app modules
Step 3 – Create the Database Tables
Before starting the app, create the tables in the database.
models.Base.metadata.create_all(bind=engine)This code will generate the user table automatically if it doesn't exist.
Step 4 – Create FastAPI App Instance
app=FastAPI()Step 5 – Create Database Dependency
We need a dependency that provides a database session to routes.
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()This ensures each route uses a separate session and closes it afterward.
Step 6 – Create CRUD Routes
a) Create a User
@app.post("/users", response_model=schemas.UserResponse)
def create_user(user: schemas.UserCreate, db: Session = Depends(get_db)):
return crud.create_user(db=db, user=user)b) Get All User
@app.get("/users", response_model=list[schemas.UserResponse])
def read_users(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
return crud.get_users(dbc) Get Single User
@app.get("/users/{user_id}", response_model=schemas.UserResponse)
def read_user(user_id: int, db: Session = Depends(get_db)):
db_user = crud.get_user(db=db, user_id=user_id)
if not
d) Update User
@app.put("/users/{user_id}", response_model=schemas.UserResponse)
def update_user(user_id: int, user: schemas.UserUpdate, db: Session = Depends(get_db)):
db_user = crud.update_user(db=db, user_id=user_id,
e) Delete User
@app.delete("/users/{user_id}", response_model=schemas.UserResponse)
def delete_user(user_id: int, db: Session = Depends(get_db)):
db_user = crud.delete_user(db=db, user_id=user_id)
if
Final main.py File
After creating the routes, your final main.py should look like this:
from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session
from . import models, schemas, crud
from .database import SessionLocal, engine
models.Base.metadata.create_all(bind=engine)
app = FastAPI()
def get_db():
db = SessionLocal
Now that our API routes are ready, the next step is to run the FastAPI app and test the API endpoints.
Running and Testing the API
We can now start the server and test the APIs by following the below step:
Step 1 – Run the FastAPI Server
Navigate to the root project directory fastapi-crud-app.
Then start the server using the following command in the terminal.
uvicorn app.main:app --reloadLet’s understand each part of the command:
uvicorn– an ASGI server used to run FastAPI applications.app– our folder namemain– Our python filemain.pyinside the app folder.--reload– Automatically reload the server when code changes.
If the server starts automatically, you will see something like:
Uvicorn running on http://127.0.0.1:8000Step 2 – Open the Interactive API Docs
FastAPI automatically generates interactive API documentation using SwaggerUI.
Open the following URL in your browser:
http://127.0.0.1:8000/docsThis URL will open the SwaggerUI, where you can test all the API endpoints directly from the browser.
Step 3 – Test the Create User Endpoint
Expand the POST /user endpoint in the docs. Click Try it Out and send a request like this:
{
"name": "John Doe",
"email": "john@example.com"
}If successful, the API will return the following response:
{
"id": 1,
"name": "John Doe",
"email": "john@example.com"
}This means the user has successfully been created and stored in the PostgreSQL database.
Step 4 – Test the Get All Users Endpoint
Use the GET /users endpoint.
It will return a list of all users:
[
{
"id": 1,
"name": "John Doe",
"email": "john@example.com"
}
]Step 5 – Test the Get Single User Endpoint
Use the GET /users/{user_id} endpoint.
Example:
GET /users/1
You will get the following response:
{
"id": 1,
"name": "John Doe",
"email": "john@example.com"
}Step 6 – Test the Update User Endpoint
Use the PUT /users/{user_id} endpoint.
Example request:
{
"name": "John Smith"
}Response:
{
"id": 1,
"name": "John Smith",
"email": "john@example.com"
}7. Test the Delete User Endpoint
Use the DELETE /users/{user_id} endpoint.
Example:
DELETE /users/1
Response:
{
"id": 1,
"name": "John Smith",
"email": "john@example.com"
}This confirms that the user has been removed from the database.
At this point, your FastAPI CRUD API is fully functional.
However, a full-stack web application also requires a frontend to show these APIs data in a user interface. In modern web applications, Next.js is the most popular framework to build the user interface.
The next step is to connect your FastAPI application with Next.js for a full-stack web application. For this, I recommend you to read our following tutorial:
Next.js + FastAPI Tutorial: Connect Frontend to Backend Step-by-Step
Common Error and Fixes
When building a CRUD API with FastAPI, SQLAlchemy, and PostgreSQL, beginners often run into a few common errors. Below are some frequent issues and how to fix them.
1. PostgreSQL Authentication Error
When connecting the PostgreSQL database you may see an error like:
psycopg2.OperationalError: FATAL: password authentication failedThis usually happens when the database credentials in DATABASE_URL are incorrect.
Example connection string:
DATABASE_URL = "postgresql://postgres:password@localhost:5432/fastapi_crud_db"Make sure that:
- The
usernameis correct. - The
passwordis correct. - The database name exists.
- PostgreSQL is running.
If necessary, verify your credentials by logging into PostgreSQL manually.
psql -U postgres2. Module Not Found Error
Sometime you may see an error like:
ModuleNotFoundError: No module named 'app'This usually happens when the server is started from the wrong directory.
Make sure you run the server from the project root folder.
fastapi-crud-appThen start the server:
uvicorn app.main:app --reload3. Tables Not Created in Database
If the user table is not created automatically, verify that the following line exists in the main.py.
models.Base.metadata.create_all(bind=engine)This command instructs SQLAlchemy to create all tables defined in the models.
4. Duplicate Email Error
Since the email column is defined as unique, trying the insert the same email twice will cause an error like:
IntegrityError: duplicate key value violates unique constraintThis happens because the database prevents duplicate emails.
To avoid this, you can check if the email already exists in the database, before creating the new one.
5. Database Connection Refused
Another common error like this:
connection refusedThis means PostgreSQL is not running.
Start the PostgreSQL service and try again.
Example:
Linux:
sudo service postgresql startMac:
brew services start postgresqlWindows:
Start PostgreSQL from Services or the PgAdmin dashboard.
These are some of the most common issues developers face when building APIs with FastAPI and SQLAlchemy. Understanding these errors will help you debug problems more quickly.
Conclusion
In this tutorial, we built a complete CRUD API using FastAPI, SQLAlchemy, and PostgreSQL.
Step by step, we covered:
- Setting up the project structure
- Installing the required dependencies
- Creating a PostgreSQL database
- Connecting the database using SQLAlchemy
- Defining database models
- Creating Pydantic schemas for request validation
- Implementing CRUD operations
- Building API routes in FastAPI
- Running and testing the API
- Troubleshooting common errors
By the end of this tutorial, you now have a fully functional backend API that can:
- Create users
- Retrieve users
- Update user data
- Delete users
These CRUD operations form the foundation of most real-world backend applications such as SaaS platforms, dashboards, and web services.
From here, you can continue improving this project by adding features like:
- Authentication and authorization
- Pagination for large datasets
- Environment-based configuration
- Docker deployment
- Connecting a frontend such as Next.js
Mastering these backend fundamentals will help you build scalable and production-ready APIs with FastAPI. Some of these fundamentals are also covered in my other blogs. You can check them in the Related Articles section.
If you found this tutorial helpful, consider sharing it with other developers who are learning FastAPI and backend development.