Building a Production-Ready User Management API with FastAPI and PostgreSQL

User Management APIs are often used as introductory backend projects.

However, they provide an excellent opportunity to learn several important backend engineering concepts including project structure, request validation, response validation, pagination, error handling, and database integration.

This article demonstrates how to build a User Management API using FastAPI and PostgreSQL while focusing on the architectural decisions that make an API maintainable.

Project Structure

One of the most common mistakes in beginner projects is placing everything inside a single file.

A better approach is to separate responsibilities.

app/
├── routers/
├── schemas/
├── crud/
├── database/
└── main.py

Each layer has a specific purpose.

routers

Responsible for handling HTTP requests and responses.

schemas

Contains Pydantic models for request and response validation.

crud

Contains database operations and SQL queries.

database

Responsible for database connections and configuration.

This structure becomes increasingly valuable as an application grows.

Database Connection Using Environment Variables

Database credentials should never be hardcoded.

Instead, use environment variables.

import os
from dotenv import load_dotenv
import psycopg

load_dotenv()

DB_HOST = os.getenv("DB_HOST")
DB_PORT = os.getenv("DB_PORT")
DB_NAME = os.getenv("DB_NAME")
DB_USER = os.getenv("DB_USER")
DB_PASSWORD = os.getenv("DB_PASSWORD")

Creating a reusable connection function:

def get_connection():
    return psycopg.connect(
        host=DB_HOST,
        port=DB_PORT,
        dbname=DB_NAME,
        user=DB_USER,
        password=DB_PASSWORD
    )

Benefits:

• Improved security
• Easier deployment
• Environment-specific configuration

Request Validation with Pydantic

FastAPI uses Pydantic models to validate incoming requests.

from pydantic import BaseModel

class UserCreate(BaseModel):
    name: str
    email: str

When a client sends data:

{
    "name": "John",
    "email": "john@example.com"
}

FastAPI automatically validates the payload before it reaches the endpoint.

Response Models

Many developers use request models but overlook response models.

Response models ensure that APIs return only the intended data.

class UserResponse(BaseModel):
    id: int
    name: str
    email: str

Usage:

@router.get(
    "/{user_id}",
    response_model=UserResponse
)
def read_user(user_id: int):
    ...

Response models provide:

• Output validation
• Consistent API contracts
• Automatic OpenAPI documentation

Creating a User

A PostgreSQL INSERT query can return the newly created record immediately.

cursor.execute(
    """
    INSERT INTO users
    (name, email)
    VALUES
    (%s, %s)
    RETURNING id, name, email
    """,
    (name, email)
)

user = cursor.fetchone()

The RETURNING clause is a useful PostgreSQL feature that avoids an additional query.

Error Handling

APIs should provide meaningful HTTP status codes.

Example:

from fastapi import HTTPException

if not user:
    raise HTTPException(
        status_code=404,
        detail="User not found"
    )

Response:

{
    "detail": "User not found"
}

This makes the API predictable for consumers.

Pagination

Returning every row from a database table is rarely a good idea.

A common approach is to implement pagination using LIMIT and OFFSET.

def get_users(skip=0, limit=10):

    cursor.execute(
        """
        SELECT *
        FROM users
        ORDER BY id
        LIMIT %s
        OFFSET %s
        """,
        (limit, skip)
    )

    return cursor.fetchall()

API Usage:

GET /users?skip=0&limit=10

This returns data in manageable chunks.

Why ORDER BY Matters

A common mistake is writing:

SELECT *
FROM users
LIMIT 10
OFFSET 0;

Many developers assume records will always be returned in insertion order.

SQL does not guarantee this.

The correct approach is:

SELECT *
FROM users
ORDER BY id
LIMIT 10
OFFSET 0;

Combining pagination with explicit ordering ensures consistent results.

Search Functionality

FastAPI query parameters make filtering straightforward.

Endpoint:

@router.get("/search")
def search_users(name: str):
    ...

SQL:

cursor.execute(
    """
    SELECT *
    FROM users
    WHERE name ILIKE %s
    """,
    (f"%{name}%",)
)

Example:

GET /users/search?name=john

Using ILIKE enables case-insensitive searches.

Key Takeaways

A User Management API is more than a CRUD exercise.

It introduces several concepts that appear repeatedly in backend systems:

• Layered project architecture
• Environment variable management
• Request validation
• Response validation
• Database integration
• Pagination
• Error handling
• Query parameters
• RESTful API design

Mastering these fundamentals creates a strong foundation before moving to topics such as ORM frameworks, authentication, testing, and deployment.

Source Code:https://github.com/IamVishnuSivaprasadan/FastAPI_user_Managemnet_API_V2