Building a Robust Product Catalog Backend with FastAPI and PostgreSQL

Introduction

For the Tienda-de-Ropa project, a core requirement was to establish a reliable backend capable of managing product data. This post details the implementation of a FastAPI-based backend, integrated with PostgreSQL, to provide comprehensive Create, Read, Update, and Delete (CRUD) operations for a simplified product catalog.

Prerequisites

To follow along or understand this architecture, a basic grasp of the following is beneficial:

Python programming language
FastAPI web framework
PostgreSQL database fundamentals
Pydantic for data validation
SQLAlchemy for database interactions

Step 1: Defining the Product Data Models

We start by defining how a product is structured, both for API requests and database storage. Pydantic is used for request/response validation in FastAPI, while SQLAlchemy defines the database schema.

Pydantic Model for API Interaction

from pydantic import BaseModel
from typing import Optional

class ProductBase(BaseModel):
    name: str
    description: Optional[str] = None
    price: float
    category: str
    stock: int

class ProductCreate(ProductBase):
    pass

class ProductUpdate(ProductBase):
    name: Optional[str] = None
    price: Optional[float] = None
    category: Optional[str] = None
    stock: Optional[int] = None

class Product(ProductBase):
    id: int

    class Config:
        from_attributes = True # Formerly orm_mode = True

SQLAlchemy Model for Database Schema

from sqlalchemy import Column, Integer, String, Float
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class DBProduct(Base):
    __tablename__ = "products"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    description = Column(String)
    price = Column(Float)
    category = Column(String)
    stock = Column(Integer)

This separation allows us to have flexible API models (e.g., for partial updates) while maintaining a consistent database schema.

Step 2: Setting Up the Database

A products table is required in PostgreSQL. Here's an example SQL script to create it and populate it with some initial data:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL,
    category VARCHAR(100) NOT NULL,
    stock INTEGER NOT NULL
);

INSERT INTO products (name, description, price, category, stock) VALUES
('T-Shirt Basic', 'Comfortable cotton t-shirt', 19.99, 'Tops', 150),
('Denim Jeans Slim Fit', 'Stylish slim fit jeans', 49.99, 'Bottoms', 80),
('Hoodie Urban', 'Warm and cozy hoodie', 35.50, 'Outerwear', 120);

Step 3: Implementing CRUD Endpoints with FastAPI

With our models defined and database prepared, we can now create the FastAPI endpoints to handle product interactions. We'll use SQLAlchemy for database sessions.

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session
from typing import List

# Assume create_db_session and models are imported

app = FastAPI()

# Dependency to get a database session
def get_db():
    db = create_db_session()
    try:
        yield db
    finally:
        db.close()

@app.post("/products/", response_model=Product)
def create_product(product: ProductCreate, db: Session = Depends(get_db)):
    db_product = DBProduct(**product.model_dump())
    db.add(db_product)
    db.commit()
    db.refresh(db_product)
    return db_product

@app.get("/products/", response_model=List[Product])
def read_products(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    products = db.query(DBProduct).offset(skip).limit(limit).all()
    return products

@app.get("/products/{product_id}", response_model=Product)
def read_product(product_id: int, db: Session = Depends(get_db)):
    product = db.query(DBProduct).filter(DBProduct.id == product_id).first()
    if product is None:
        raise HTTPException(status_code=404, detail="Product not found")
    return product

@app.put("/products/{product_id}", response_model=Product)
def update_product(product_id: int, product: ProductUpdate, db: Session = Depends(get_db)):
    db_product = db.query(DBProduct).filter(DBProduct.id == product_id).first()
    if db_product is None:
        raise HTTPException(status_code=404, detail="Product not found")
    for key, value in product.model_dump(exclude_unset=True).items():
        setattr(db_product, key, value)
    db.commit()
    db.refresh(db_product)
    return db_product

@app.delete("/products/{product_id}")
def delete_product(product_id: int, db: Session = Depends(get_db)):
    db_product = db.query(DBProduct).filter(DBProduct.id == product_id).first()
    if db_product is None:
        raise HTTPException(status_code=404, detail="Product not found")
    db.delete(db_product)
    db.commit()
    return {"message": "Product deleted successfully"}

Each endpoint leverages FastAPI's dependency injection for database sessions and Pydantic for request and response validation, ensuring data integrity and consistency.

Results

The implementation of this backend provides Tienda-de-Ropa with a fully functional API for managing products. This robust foundation allows the frontend application to interact seamlessly with the product catalog, enabling dynamic display, creation, and modification of product listings. The simplified category model (as text) provides flexibility while keeping the initial scope manageable.

Next Steps

Future enhancements could include:

Authentication and Authorization: Secure endpoints to ensure only authorized users can perform certain actions.
Image Uploads: Integrate cloud storage for product images.
Advanced Search and Filtering: Implement more complex querying capabilities for the product catalog.
Error Handling: Refine error responses and add custom exceptions for better user experience and debugging.
Dockerization: Package the FastAPI application and PostgreSQL database into Docker containers for easier deployment and scaling.

Generated with Gitvlg.com