Python & PostgreSQL CRUD Web Application

A production-ready, full-stack CRUD (Create, Read, Update, Delete) web application built with Python 3.13, FastAPI, and PostgreSQL, managed by Astral's ultra-fast uv package manager. Designed as an exemplary portfolio project showcasing clean architecture, parameterized database security, interactive UI, and containerized deployment.

---

Highlights & Features

• Interactive User Form:
  • Details: Name, Email, Mobile Number, Hobbies (multi-tag interactive selector), Gender, Date of Birth, Location (City/Country), Bio, and Active Status.
  • Client-side & server-side validation using Pydantic v2.
  • Automatic mobile number format sanitization and email normalization.
  • Duplicate detection for both email and mobile numbers.
• Modern Responsive Dashboard UI:
  • Built with Tailwind CSS and Plus Jakarta Sans typography.
  • Dark Mode and Light Mode with persistent toggle.
  • Key Metrics: Total Users, Active Profile Rate, Top Hobby with user count, Recent Signups.
  • Animated toast notifications for all CRUD operations.
• Data Table with Advanced Controls:
  • Real-time debounced search across Name, Email, Mobile, and City.
  • Filtering by Hobby, Gender, and Active status.
  • Multi-column sorting (Name, Date Created, etc.).
  • Configurable pagination.
  • User profile modal with complete metadata.
• Developer Features:
  • One-click Seed Sample Data generator (populates 10 realistic profiles for demonstrations).
  • One-click Export to CSV.
  • Interactive OpenAPI / Swagger documentation at /docs.
  • Health check endpoint at /health reporting live database connectivity.
• Production Architecture:
  • Layered pattern: Domain Models → Database & Connection Pool → Repository → Service → REST API → UI.
  • Connection pooling with psycopg-pool and automated schema migrations.
  • 100% parameterized queries guarding against SQL injection.
  • Docker Compose setup running both PostgreSQL and the FastAPI application.

---

Tech Stack

Layer	Technology
Backend Framework	FastAPI
Runtime & Language	Python 3.13+
Database	PostgreSQL 16+ (with GIN indexes for array searching)
Driver & Pooling	psycopg 3 with psycopg-pool
Data Validation	Pydantic v2
Frontend Styling	Tailwind CSS (via CDN)
Templating	Jinja2
Package Manager	uv (Astral)
Testing	Pytest & HTTPX
Containerization	Docker & Docker Compose

---

Quickstart with Docker Compose (Recommended)

Spin up the entire application (PostgreSQL + FastAPI Web App) with a single command:

# Clone the repository
git clone git@github.com:faiyazkhan024/python-postgresql-crud.git
cd python-postgresql-crud

# Copy environment variables
cp .env.example .env

# Build and start services
docker compose up --build

Once started:

• Web UI: http://localhost:8000
• Interactive Swagger Docs: http://localhost:8000/docs
• Health Check: http://localhost:8000/health

---

Local Development with uv

1. Prerequisites

• Python >= 3.13
• uv
• PostgreSQL running locally or via Docker

2. Start PostgreSQL with Docker

docker compose up postgres -d

3. Install Dependencies

uv sync --all-extras --dev

4. Run the Application

uv run python-postgresql-crud

---

Project Structure

python-postgresql-crud/
├── .github/
│   └── workflows/
│       └── ci.yml               # GitHub Actions CI workflow
├── src/
│   └── python_postgresql_crud/
│       ├── __init__.py          # Package entrypoint & CLI runner
│       ├── config.py            # Environment settings (.env loader)
│       ├── database.py          # PostgreSQL connection pool & migrations
│       ├── models.py            # Pydantic validation models
│       ├── repository.py        # Parameterized SQL CRUD queries
│       ├── service.py           # Domain service & sample data seeder
│       ├── main.py              # FastAPI app & lifespan manager
│       ├── api/
│       │   ├── __init__.py
│       │   └── routes.py        # REST API routes (/api/v1/users)
│       ├── templates/
│       │   ├── base.html        # Base HTML template with Tailwind CSS
│       │   └── index.html       # Dashboard, metrics, table, modals
│       └── static/
│           ├── css/
│           │   └── styles.css   # Custom animations & transitions
│           └── js/
│               └── app.js       # Frontend controller & fetch API
├── tests/
│   ├── __init__.py
│   ├── test_basic.py            # Smoke tests
│   ├── test_models.py           # Schema & field validation tests
│   ├── test_service.py          # Domain logic & mock repository tests
│   └── test_api.py              # FastAPI TestClient endpoint tests
├── .env.example                 # Example environment variables
├── .gitignore                   # Git ignore rules
├── AGENT.md                     # Operational manual for AI coding agents
├── CONTRIBUTING.md              # Community contribution guide
├── Dockerfile                   # Multi-stage production container build
├── docker-compose.yml           # PostgreSQL + web app services
├── LICENSE                      # MIT License
└── pyproject.toml               # Project configuration & dependencies

---

REST API Reference

Method	Endpoint	Description
POST	/api/v1/users	Create a new user
GET	/api/v1/users	List users with search, filtering, and pagination
GET	/api/v1/users/{id}	Get user details by ID
PUT	/api/v1/users/{id}	Update an existing user
DELETE	/api/v1/users/{id}	Delete a user by ID
GET	/api/v1/users/stats	Get directory metrics and top hobbies
GET	/api/v1/users/export/csv	Download all users as CSV
POST	/api/v1/users/seed	Seed realistic demo users
GET	/health	System and database health status

Example cURL: Create a User

curl -X POST http://localhost:8000/api/v1/users \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Faiyaz Khan",
    "email": "faiyaz@example.com",
    "mobile": "+919876543210",
    "hobbies": ["Coding", "Photography", "Chess"],
    "gender": "male",
    "date_of_birth": "1998-05-15",
    "city": "Bengaluru",
    "country": "India",
    "bio": "Software engineer building scalable backend applications.",
    "is_active": true
  }'

---

Testing & Code Quality

Run tests using Pytest:

uv run pytest -v

Format code with Ruff:

uv run ruff format .

Lint with auto-fix:

uv run ruff check . --fix

---

License

This project is licensed under the MIT License.

Copyright © 2026 Faiyaz Khan.