FastAPI CRUD Tutorial with SQLAlchemy and PostgreSQL

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

1. Prerequisites
2. Project Overview
3. Project Setup
4. Installing Dependencies
5. Setting Up PostgreSQL Database
6. Creating the Database Connection
7. Creating SQLAlchemy Models
8. Creating Pydantic Schemas
9. Implementing CRUD Operations
10. Creating FastAPI Routes
11. Running and Testing the API
12. Common Errors and Fixes
13. 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 Database

Step-by-step flow:

1. A client sends an HTTP request to the FastAPI API.
2. FastAPI routes handle the request.
3. CRUD functions perform database operation.
4. SQLAlchemy communicates with the PostgreSQL database.
5. The database returns the result.
6. 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 user
• GET /users — Get all users
• GET /user/{id} — Get a single user by ID
• PUT /user/{id} — Update a user
• DELETE /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.txt

Each 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-app

This 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 venv

Activate the virtual environment.

Mac/Linux:

source venv/bin/activate

Windows:

venv\Scripts\Activate

Once 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 app

Inside 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.py

Your project structure should now look like this:

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

Now 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-dotenv

These 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 postgres

You 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_db

You should see the following response that means the database has been created:

CREATE DATABASE

Step 3 – Verify the Database

To see the list of databases, run:

\l

You should see fastapi_crud_db in the list.

Step 4 – Connect to the Database

Now connect to the newly created database.

\c fastapi_crud_db

If 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:

\q

Now 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_base

These 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_name

Let’s define a connection string using the above pattern.

DATABASE_URL = "postgresql://postgres:password@localhost:5432/fastapi_crud_db"

• postgres – database username
• password – PostgreSQL database password
• localhost – database server
• 5432 – PostgreSQL default port
• fastapi_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 Base

These 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 the database.py file 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, 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.
• primary_key=True also means values cannot repeat and cannot be empty, because it’s the main identifier of the row.
• nullable=False means that the column value cannot be NULL.
• unique=True means 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:

1. UserCreate – for creating a new user
2. UserUpdate – for updating user data
3. 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, EmailStr

• BaseModel – 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: EmailStr

This schema ensure that any request to create a user must contain:

• A name field of type string.
• An email field 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 = True

• orm_mode=True tells 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, schemas

• Session – 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_user

This functions will performs the following steps:

• Creates a new user object 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 skip and limit

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 None if 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_user

This 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, engine

The 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 Local and engine – 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(db

c) 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 --reload

Let’s understand each part of the command:

• uvicorn – an ASGI server used to run FastAPI applications.
• app – our folder name
• main – Our python file main.py inside 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:8000

Step 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/docs

This 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 failed

This 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 username is correct.
• The password is correct.
• The database name exists.
• PostgreSQL is running.

If necessary, verify your credentials by logging into PostgreSQL manually.

psql -U postgres

2. 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-app

Then start the server:

uvicorn app.main:app --reload

3. 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 constraint

This 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 refused

This means PostgreSQL is not running.

Start the PostgreSQL service and try again.

Example:

Linux:

sudo service postgresql start

Mac:

brew services start postgresql

Windows:

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.