Build a complete E-commerce Product Management API in TypeScript with MySQL. This task brings together everything from Module 2: database design, CRUD operations, relationships, and JOINs.

You’ll work with three related database entities:

  • Categories (product organisation).
  • Products (items for sale).
  • Reviews (customer feedback on products).

Database Setup

  1. Create a new database called ecommerce.
  2. Design and create three tables with proper relationships:
    • Use AUTO_INCREMENT primary keys.
    • Include foreign key constraints with appropriate CASCADE behaviour.
    • Consider what happens when categories or products are deleted.

Required Fields

Categories: id, name, description. Products: id, name, price, description, category_id, stock_quantity, created_at. Reviews: id, product_id, reviewer_name, rating (1-5), comment, created_at.

Project Setup

Set up your project from scratch:

  • Use TypeScript with proper configuration.
  • Use .env for database credentials and port.
  • Install mysql2, express, dotenv, and TypeScript dependencies.
  • Set up database connection pool following Module 2 patterns.
  • Use express.json() middleware for JSON parsing.

Category Routes

Start with categories as the foundation.

TypeScript Interface

interface Category {
  id: number;
  name: string;
  description: string;
}

Endpoints

  • GET /categories → Get all categories.
  • GET /categories/:id → Get one category.
  • POST /categories → Create a category.
  • PUT /categories/:id → Update all fields category.
  • PATCH /categories/:id → Update some or all fields in a category.
  • DELETE /categories/:id → Delete a category.

Validation

  • name and description required on POST/PUT.
  • name and/or description required on PATCH.
  • Return 400 for bad input, 404 for not found.
  • Use parameterised queries for SQL injection prevention.

Product Routes

Products belong to categories and form the core of your e-commerce system.

TypeScript Interface

interface Product {
  id: number;
  name: string;
  price: number;
  description: string;
  category_id: number;
  stock_quantity: number;
  created_at: string;
}

interface ProductWithCategory extends Product {
  category_name: string;
  category_description: string;
}

Endpoints

  • GET /products → All products with category information (use JOIN).
  • GET /products/:id → One product with category information.
  • GET /categories/:id/products → All products in a specific category.
  • POST /products → Create a product.
    • Must validate that category_id exists.
    • Price must be positive, stock_quantity must be non-negative.
  • PUT /products/:id → Update a product.
  • PATCH /products/:id → Update some or all fields in a product.
    • At least one field required on PATCH.
  • DELETE /products/:id → Delete a product.

Advanced Features

  • GET /products?minPrice=10&maxPrice=100 → Filter by price range.
  • GET /products?category=electronics → Filter by category name.
  • Add pagination with limit and offset query parameters.

Review Routes

Reviews connect to products and provide customer feedback.

TypeScript Interface

interface Review {
  id: number;
  product_id: number;
  reviewer_name: string;
  rating: number;
  comment: string;
  created_at: string;
}

interface ReviewWithProduct extends Review {
  product_name: string;
  product_price: number;
}

Endpoints

  • GET /reviews → All reviews with product information (use JOIN).
  • GET /reviews/:id → One review with product information.
  • GET /products/:id/reviews → All reviews for a specific product.
  • POST /reviews → Create a review.
    • Must validate that product_id exists.
    • Rating must be between 1-5.
    • reviewer_name and comment required.
  • DELETE /reviews/:id → Delete a review.

Data Relationships

  • When a category is deleted, decide what happens to its products.
  • When a product is deleted, its reviews should be deleted too.
  • Implement appropriate CASCADE DELETE behaviour.

Implementation Strategy

This is a complex task that builds on everything from Module 2. Follow this order to manage complexity:

  1. Start with Categories
    • No dependencies on other entities.
    • Implement all CRUD operations (GET, POST, PUT, PATCH, DELETE).
    • Test thoroughly before moving on.
  2. Then Products
    • Depends on Categories (foreign key relationship).
    • Implement basic CRUD operations first.
    • Add JOIN queries to include category information.
    • Add advanced features (filtering, pagination) last.
  3. Finally Reviews
    • Depends on Products (foreign key relationship).
    • Simpler than Products (no updates needed).
    • Focus on JOIN queries to include product information.

Tips

  • Test each entity completely before starting the next.
  • Use Postman or similar tool to verify all endpoints work.
  • Start with simple queries, then add JOINs and advanced features.
  • Don’t forget to test your CASCADE DELETE behaviour.

Error Handling & Validation

Apply these best practices:

  • Parameterised queries everywhere (prevent SQL injection).
  • Proper TypeScript interfaces for all entities.
  • Error handling with try/catch.
  • Appropriate HTTP status codes (200, 201, 400, 404, 500).
  • Input validation for all POST/PUT/PATCH requests.
  • Foreign key validation before creating related records.

Deliverables & Testing

Your Complete API Should Include:

  • MySQL database with proper relationships and constraints.
  • CRUD operations on all three entities (Categories, Products, Reviews).
  • JOIN queries that return related data in single requests.
  • TypeScript interfaces throughout for type safety.
  • Proper validation and error handling.
  • Sample data for testing (3-4 categories, 8-10 products, 15-20 reviews).

Testing Verification

Test your API to ensure:

  • ✅ All CRUD operations work correctly for each entity.
  • ✅ JOIN queries return complete data (products include category names, not just IDs).
  • ✅ Foreign key constraints prevent invalid data (can’t create products with non-existent category IDs).
  • ✅ CASCADE DELETE behaviour works as expected (deleting products removes their reviews).
  • ✅ Pagination works on product list endpoints.
  • ✅ Error cases return appropriate status codes (400, 404, 500).
  • ✅ Advanced features work (price filtering, category filtering).
Tags: