🎓 What You Will Learn
- Set up PostgreSQL: Create a database, a table, and sample data
- Install the tools: FastAPI, SQLModel, psycopg, and Uvicorn
- Connect your app: Build the database engine and session
- Build endpoints: Create and fetch employees with CRUD routes
- Test everything: Use Swagger UI to check your API works
PostgreSQL Prerequisites
Before we connect FastAPI to PostgreSQL, install PostgreSQL first. Then create a database and a table. This way, your API has real data to read and write.
Download PostgreSQL here: https://www.postgresql.org/download/
Create Database
Create Employee Table
We will create an employee table with practical fields: name, email, department, salary, and hired_at. This is how real companies store employee records. It gives us good data for testing CRUD endpoints (Create, Read, Update, Delete) in FastAPI.
CREATE TABLE employee (
employee_id BIGSERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(120) UNIQUE NOT NULL,
department VARCHAR(80) NOT NULL,
salary NUMERIC(12,2) NOT NULL DEFAULT 0,
hired_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);Create Sample Data
The table now exists. Let’s insert sample rows so you can quickly test if your database connection works.
INSERT INTO employee (first_name, last_name, email, department, salary)
VALUES
('Jose', 'Iliga', '[email protected]', 'Engineering', 85000),
('Anna', 'Reyes', '[email protected]', 'HR', 45000);Install Python Libraries
pip install fastapi sqlmodel psycopg uvicornHere is a simple explanation of each library:
| Library Name | Definition |
|---|---|
| fastapi | Python framework for building APIs fast (with automatic Swagger docs). |
| sqlmodel | Library for defining database models and running SQL queries using Python classes (SQLAlchemy + Pydantic). |
| psycopg | PostgreSQL driver that lets Python connect and talk to Postgres. |
| uvicorn | Server that runs your FastAPI app locally (best for development). |
Create a Database Configuration
Create a file named main.py and add your database configuration:
from sqlmodel import create_engine, Session
# Database configuration -- move these to a .env file later
DATABASE_USERNAME = "joseiiigayares"
PASSWORD = ""
DATABASE_HOST = "localhost"
DATABASE_PORT = "5432"
DATABASE_NAME = "fastapi_db"
DATABASE_URL = f"postgresql+psycopg://{DATABASE_USERNAME}:{PASSWORD}@{DATABASE_HOST}:{DATABASE_PORT}/{DATABASE_NAME}"
# Create the database engine
engine = create_engine(
DATABASE_URL,
echo=True, # Set to False in production
pool_pre_ping=True,
pool_size=20,
max_overflow=40,
pool_timeout=30,
)
def get_session():
with Session(engine) as session:
try:
yield session
except Exception:
session.rollback()
raise⚠️ Never commit real passwords to Git. Move the username and password to a .env file. Load them with a library like python-dotenv.
💡 About the URL: The postgresql+psycopg:// prefix tells SQLAlchemy to use the modern psycopg (version 3) driver. If you installed the old psycopg2 package, use postgresql+psycopg2:// instead.
Configuration definitions:
| Name | Description |
|---|---|
| DATABASE_USERNAME | Account used to authenticate to PostgreSQL |
| PASSWORD | Password for the database account (leave blank if not set) |
| DATABASE_HOST | Server address where PostgreSQL is running |
| DATABASE_PORT | Network port PostgreSQL is listening on (default 5432) |
| DATABASE_NAME | Target database to connect to |
| DATABASE_URL | Final connection string used by SQLModel/SQLAlchemy |
This configuration does three important things:
- Builds DATABASE_URL — tells our app where Postgres is and which database to use
- Creates the database engine — manages connections, pooling, and reconnect checks
- Provides get_session() — lets FastAPI reuse a DB session safely per request
Check if Working: GET and CREATE Endpoint
Create FastAPI objects
from fastapi import FastAPI, Depends, HTTPException, status
from sqlmodel import SQLModel, Field, Session, create_engine, select
from typing import Optional, List
app = FastAPI(title="Employee CRUD - FastAPI + SQLModel")Create basic models
class EmployeeBase(SQLModel):
first_name: str
last_name: str
email: str
department: str
salary: float = 0
class Employee(EmployeeBase, table=True):
employee_id: Optional[int] = Field(default=None, primary_key=True)
class EmployeeCreate(EmployeeBase):
passFetch all employee endpoint
@app.get("/employees", response_model=List[Employee])
def fetch_all_employees(session: Session = Depends(get_session)):
employees = session.exec(select(Employee)).all()
return employeesCreate employee endpoint
@app.post("/employees", response_model=Employee, status_code=status.HTTP_201_CREATED)
def create_employee(
payload: EmployeeCreate,
session: Session = Depends(get_session),
):
employee = Employee.model_validate(payload)
session.add(employee)
session.commit()
session.refresh(employee)
return employeeRun FastAPI
uvicorn main:app --reloadTest in Swagger Docs
Open Swagger UI at http://127.0.0.1:8000/docs to test your API quickly without Postman.
- Test Create Employee and verify the response (expect
201 Created). - Confirm the new record is saved by checking your employee table in PostgreSQL.
- Test Fetch All Employees to confirm data is being read correctly.
✅ It works! If you see 201 Created and your employees list in Swagger UI, FastAPI is now talking to PostgreSQL.
We connected FastAPI to PostgreSQL using SQLModel and psycopg. We created an employee table and built working endpoints. Then we tested everything in Swagger UI (/docs). Our API can now create records (POST) and fetch data (GET) from a real Postgres database.
From here, you can add Update (PUT/PATCH) and Delete (DELETE) endpoints. Move your credentials into a .env file. Then use migrations (like Alembic) for safer database updates. This setup is a solid base for production-ready FastAPI apps with PostgreSQL.
Resources
- Github - https://github.com/thirdygayares/fastapi_postgres_connection
- SQLModel Docs - https://sqlmodel.tiangolo.com/
- FastAPI SQL Databases Guide - https://fastapi.tiangolo.com/tutorial/sql-databases/