PostgreSQL Prerequisites
Before connecting FastAPI to PostgreSQL, make sure you already installed PostgreSQL and created a database + 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 like name, email, department, salary, and hired_at. This mirrors how real companies store employee records and gives us a solid dataset 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
Now that the table exists, let’s insert sample rows so you can instantly 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 -- add this on .env
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:
session.rollback()
raiseConfiguration 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.
We’ve successfully connected FastAPI to PostgreSQL using SQLModel and psycopg, created an employee table, and built working CRUD endpoints. By testing everything in Swagger UI (/docs), we confirmed that our API can create records (POST) and fetch data (GET) from a real Postgres database. From here, we can upgrade this project by adding Update (PUT/PATCH) and Delete (DELETE) endpoints, moving credentials into a .env file, and using migrations (like Alembic) for safer database updates. This setup is a solid foundation for building production-ready FastAPI applications with PostgreSQL.