FastAPI PostgreSQL CRUD API

A simple CRUD (Create, Read, Update, Delete) REST API built using FastAPI, PostgreSQL, and SQLAlchemy following a clean layered architecture.

Tech Stack

• Python 3.x
• FastAPI
• PostgreSQL
• SQLAlchemy ORM
• Pydantic
• Uvicorn

Project Structure

fastapi-postgres-crud/
│
├── app/
│   ├── api/
│   ├── core/
│   ├── db/
│   ├── models/
│   ├── repository/
│   ├── schemas/
│   ├── services/
│   └── main.py
│
├── .env
├── requirements.txt
└── README.md

Architecture

This project follows a layered architecture to keep the code clean and maintainable.

• API Layer – Handles HTTP requests and responses.
• Service Layer – Contains business logic.
• Repository Layer – Handles database operations.
• Model Layer – Defines database tables using SQLAlchemy.
• Schema Layer – Defines request and response models using Pydantic.
• Database Layer – Manages PostgreSQL connection and database sessions.

Features

• Create Employee
• Get All Employees
• Get Employee by ID
• Update Employee
• Delete Employee
• PostgreSQL Integration
• Clean Project Structure
• SQLAlchemy ORM
• Pydantic Validation
• Interactive Swagger API Documentation

Setup

1. Clone the repository

git clone <repository-url>
cd fastapi-postgres-crud

2. Create Virtual Environment

python -m venv .venv

Activate the virtual environment.

Windows

.venv\Scripts\activate

Linux / macOS

source .venv/bin/activate

3. Install Dependencies

pip install -r requirements.txt

4. Configure Environment Variables

Create a .env file in the project root.

DATABASE_URL=postgresql://username:password@localhost:5432/companydb

If your password contains @, replace it with %40.

Example

DATABASE_URL=postgresql://postgres:Shifal%400786@localhost:5432/companydb

5. Run the Application

uvicorn app.main:app --reload

The server will start at:

http://127.0.0.1:8000

API Documentation

Swagger UI

http://127.0.0.1:8000/docs

ReDoc

http://127.0.0.1:8000/redoc

Available Endpoints

Method	Endpoint	Description
GET	/employees	Get all employees
GET	/employees/{id}	Get employee by ID
POST	/employees	Create employee
PUT	/employees/{id}	Update employee
DELETE	/employees/{id}	Delete employee

Sample Request

{
  "name": "Syed Shifal",
  "email": "[email protected]",
  "department": "Engineering"
}

Learning Objectives

This project was created to learn:

• FastAPI fundamentals
• REST API development
• PostgreSQL integration
• SQLAlchemy ORM
• CRUD operations
• Layered architecture (API → Service → Repository → Database)
• Pydantic validation
• Dependency Injection

Future Improvements

• JWT Authentication
• Password Hashing
• Alembic Database Migrations
• Logging
• Pagination
• Filtering & Sorting
• Docker Support
• Unit Testing with Pytest
• Role-Based Access Control (RBAC)

Author

Syed Shifal