Practice 4 — Node.js Express Backend

Build REST API for Review Aggregator with Database Integration

Practice
Node.js
Express
Backend
MySQL
Auteur
Affiliations

Emmanuel BRUNO

Université de Toulon

LIS UMR CNRS 7020

Date de publication

2025-10-11

Résumé

Course lectures and practices for JavaScript full‑stack web development with AI‑assisted workflows.

Practice Overview

Duration: 60 minutes
Prerequisites: Node.js Express lecture completed, MySQL setup
Project: Multi-Source Product Review Aggregator backend
Objective: Build Express REST API with MySQL database for review aggregation

What You’ll Build (60 minutes)

Complete Express backend for the review aggregator project:

  • ✅ Express server with proper middleware and routing
  • ✅ REST API endpoints for product reviews (CRUD operations)
  • ✅ MySQL database integration with connection pooling
  • ✅ Review aggregation logic (statistics calculation)
  • ✅ External scraper integration (simulated)
  • ✅ Error handling and input validation

Technologies: Node.js, Express.js, MySQL, async/await
Foundation: Using fullstack-minimal-app/backend as starting point

Part 1: Project Setup (10 min)

Step 1.1: Navigate to Backend

# Make sure you're in the right place
cd fullstack-minimal-app/backend

# Install additional dependencies
npm install mysql2 dotenv joi cors morgan

# Verify existing structure
ls -la src/

Step 1.2: Database Setup

Use the standardized database schema:

# Option 1: Run the standardized schema file
mysql -u root -p < database-schema.sql

# Option 2: Connect to MySQL and copy/paste from database-schema.sql
mysql -u root -p
Use Standardized Schema

File reference: See database-schema.sql in the course root directory for the complete, standardized database schema.

This schema is used consistently across all sessions (4-6) to avoid conflicts and ensure compatibility.

Key features: - Proper indexing for performance - Foreign key constraints - Sample data for testing - Consistent field names and types

Quick verification:

-- Verify tables exist
USE review_aggregator;
SHOW TABLES;

-- Should show: products, reviews
-- Should have sample data: SELECT * FROM products;

Step 1.3: Environment Configuration

Create .env file in backend root:

# Database Configuration
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_mysql_password
DB_NAME=review_aggregator
DB_PORT=3306

# Server Configuration
PORT=4000
NODE_ENV=development

# External Scraper (mock)
SCRAPER_BASE_URL=http://localhost:5000
SCRAPER_API_KEY=mock_api_key_12345

Part 2: Database Connection (8 min)

Exercise 2.1: Create Database Configuration

Use AI to generate the database connection:

Prompt for AI:

Context: Node.js Express backend for review aggregator
Task: Create database connection configuration for MySQL

Requirements:
1. File: src/config/database.js
2. Use mysql2/promise for async/await support
3. Create connection pool for performance
4. Load config from environment variables
5. Export pool and connection test function
6. Handle connection errors gracefully
7. Include proper connection pooling settings

Configuration needed:
- host, user, password, database from process.env
- Pool settings: connectionLimit=10, queueLimit=0
- Connection timeout handling

Output: Complete database.js configuration file with:
- createPool function
- testConnection function  
- Proper error handling
- JSDoc comments

Save to src/config/database.js

Exercise 2.2: Test Database Connection

Add to your main server file to test the connection:

// In src/app.js or server.js
const { testConnection } = require('./config/database');

// Test database connection on startup
testConnection();

Verify: Run npm run dev and check console for successful database connection.

Part 3: API Routes Development (30 min)

Exercise 3.1: Create Review Routes Structure (8 min)

Prompt for AI:

Context: Express.js REST API for product review aggregator
Task: Create routes/reviews.js with complete review management endpoints

Required endpoints:
1. POST /api/products/:id/fetch - Fetch reviews from external scraper and store
2. GET /api/products/:id/reviews - Get all reviews for a product with optional filtering
3. GET /api/products/:id/aggregate - Get aggregated review statistics
4. DELETE /api/reviews/:reviewId - Delete a specific review (admin function)

Database schema:
- reviews table with: id, product_id, source, external_review_id, author, rating, title, content, review_date, fetched_at
- Use prepared statements for security
- Handle duplicate reviews (UNIQUE constraint)

Requirements:
- Import database pool from config
- Input validation using Joi
- Proper error handling with try/catch
- HTTP status codes (200, 201, 400, 404, 500)
- JSON responses with consistent format: {success: boolean, data: any, message?: string}
- Comments explaining each endpoint

Mock external scraper:
- Create mockScraper.js that generates 5-10 random reviews
- Simulate different sources (Amazon, BestBuy, Walmart)
- Random ratings and realistic content
- Include delays to simulate network calls

Output: Complete routes/reviews.js file

Save to src/routes/reviews.js

Exercise 3.2: Create Mock Scraper Service (5 min)

Prompt for AI:

Context: Mock external scraper service for review aggregator
Task: Create services/mockScraper.js to simulate external review sources

Requirements:
1. Function: fetchReviewsFromSources(productId)
2. Return 5-10 mock reviews with realistic data
3. Different sources: Amazon, BestBuy, Walmart (random mix)
4. Realistic review content for the given product
5. Random ratings (1-5 stars) with realistic distribution
6. Author names (fake but realistic)
7. Dates within last 6 months
8. Include network delay simulation (1-3 seconds)
9. 10% chance to throw error (simulate network issues)

Review object structure:
{
  source: string,
  external_review_id: string,
  author: string,
  rating: number,
  title: string,
  content: string,
  review_date: Date
}

Output: Complete mockScraper service file

Save to src/services/mockScraper.js

Exercise 3.3: Create Validation Schemas (5 min)

Create src/validators/reviewValidators.js:

const Joi = require('joi');

const fetchReviewsSchema = Joi.object({
  sources: Joi.array().items(
    Joi.string().valid('Amazon', 'BestBuy', 'Walmart')
  ).optional(),
  limit: Joi.number().integer().min(1).max(50).default(20)
});

const getReviewsSchema = Joi.object({
  source: Joi.string().valid('Amazon', 'BestBuy', 'Walmart').optional(),
  minRating: Joi.number().integer().min(1).max(5).optional(),
  maxRating: Joi.number().integer().min(1).max(5).optional(),
  limit: Joi.number().integer().min(1).max(100).default(20),
  offset: Joi.number().integer().min(0).default(0)
});

module.exports = {
  fetchReviewsSchema,
  getReviewsSchema
};

Exercise 3.4: Create Aggregation Service (8 min)

Prompt for AI:

Context: Service to calculate review statistics and aggregations
Task: Create services/reviewAggregator.js with statistics calculation functions

Functions needed:
1. calculateOverallStats(reviews) - overall average, total count
2. calculateSourceBreakdown(reviews) - average and count per source
3. calculateRatingHistogram(reviews) - count for each rating (1-5)
4. getProductAggregateStats(productId) - fetch from DB and calculate all stats

Return format for aggregate stats:
{
  overall_average: number,
  total_reviews: number,
  source_breakdown: [
    { source: string, average: number, count: number }
  ],
  rating_histogram: {
    "1": number, "2": number, "3": number, "4": number, "5": number
  }
}

Requirements:
- Use MySQL database queries for efficiency
- Handle edge cases (no reviews, division by zero)
- Round averages to 1 decimal place
- Import database pool
- Export all functions

Output: Complete reviewAggregator.js service file

Save to src/services/reviewAggregator.js

Exercise 3.5: Update Routes with Services (4 min)

Update your routes to use the services:

// Add to top of routes/reviews.js
const mockScraper = require('../services/mockScraper');
const reviewAggregator = require('../services/reviewAggregator');
const { fetchReviewsSchema, getReviewsSchema } = require('../validators/reviewValidators');

Update the route handlers to use these services instead of placeholder logic.

Part 4: Middleware and Error Handling (8 min)

Exercise 4.1: Create Error Handling Middleware

Create src/middleware/errorHandler.js:

const errorHandler = (err, req, res, next) => {
  console.error('Error:', err);

  // Joi validation errors
  if (err.isJoi) {
    return res.status(400).json({
      success: false,
      message: 'Validation error',
      errors: err.details.map(detail => detail.message)
    });
  }

  // MySQL duplicate entry error
  if (err.code === 'ER_DUP_ENTRY') {
    return res.status(409).json({
      success: false,
      message: 'Duplicate review - already exists in database'
    });
  }

  // MySQL foreign key constraint error
  if (err.code === 'ER_NO_REFERENCED_ROW_2') {
    return res.status(404).json({
      success: false,
      message: 'Product not found'
    });
  }

  // Default server error
  res.status(500).json({
    success: false,
    message: 'Internal server error',
    ...(process.env.NODE_ENV === 'development' && { stack: err.stack })
  });
};

module.exports = errorHandler;

Exercise 4.2: Create Request Logging Middleware

Create src/middleware/requestLogger.js:

const requestLogger = (req, res, next) => {
  const start = Date.now();
  
  res.on('finish', () => {
    const duration = Date.now() - start;
    const { method, originalUrl } = req;
    const { statusCode } = res;
    
    console.log(`${method} ${originalUrl} ${statusCode} - ${duration}ms`);
  });
  
  next();
};

module.exports = requestLogger;

Exercise 4.3: Update App Configuration

Update src/app.js to use middleware:

// ...existing code...
const errorHandler = require('./middleware/errorHandler');
const requestLogger = require('./middleware/requestLogger');

// Middleware
app.use(cors());
app.use(express.json({ limit: '10mb' }));
app.use(express.urlencoded({ extended: true }));
app.use(requestLogger);

// Routes
app.use('/api', require('./routes/reviews'));

// Error handling (must be last)
app.use(errorHandler);

// ...existing code...

Part 5: Testing & Integration (4 min)

Exercise 5.1: Test API Endpoints

Create test-api.http file for testing:

### Test fetch reviews for product 1
POST http://localhost:4000/api/products/1/fetch
Content-Type: application/json

{
  "sources": ["Amazon", "BestBuy"],
  "limit": 10
}

### Get all reviews for product 1
GET http://localhost:4000/api/products/1/reviews

### Get reviews filtered by source
GET http://localhost:4000/api/products/1/reviews?source=Amazon&limit=5

### Get aggregate statistics
GET http://localhost:4000/api/products/1/aggregate

### Delete a specific review (replace :id with actual review ID)
DELETE http://localhost:4000/api/reviews/1

Exercise 5.2: Test Complete Flow

  1. Start the server:

    npm run dev

  2. Test sequence:

    • POST to fetch reviews → Should return success with count
    • GET reviews → Should return the stored reviews
    • GET aggregate → Should return calculated statistics
    • Test error cases (invalid product ID, malformed requests)

  3. Check database:

    SELECT * FROM reviews WHERE product_id = 1;
SELECT COUNT(*) as total, source, AVG(rating) as avg_rating 
FROM reviews WHERE product_id = 1 GROUP BY source;

Exercise 5.3: Performance Testing

Test with curl for performance:

# Time the fetch endpoint
time curl -X POST http://localhost:4000/api/products/1/fetch \
  -H "Content-Type: application/json" \
  -d '{"limit": 20}'

# Test concurrent requests
for i in {1..5}; do
  curl -X GET http://localhost:4000/api/products/1/reviews &
done
wait

Deliverables

Required files: - src/config/database.js - MySQL connection configuration - src/routes/reviews.js - Complete review API routes - src/services/mockScraper.js - Mock external scraper service - src/services/reviewAggregator.js - Statistics calculation service - src/validators/reviewValidators.js - Input validation schemas
- src/middleware/errorHandler.js - Centralized error handling - src/middleware/requestLogger.js - Request logging - test-api.http - API testing file - .env - Environment configuration

Testing checklist: - [ ] Database connection works - [ ] POST /fetch stores reviews in database - [ ] GET /reviews returns stored reviews - [ ] GET /aggregate calculates statistics correctly - [ ] Filtering and pagination work - [ ] Error handling responds appropriately - [ ] Validation rejects invalid input - [ ] No duplicate reviews stored - [ ] Performance is reasonable (< 2s for fetch)

Extension Challenges (Optional)

Challenge 1: Caching

Implement Redis caching for aggregate statistics: - Cache GET /aggregate results for 5 minutes - Invalidate cache when new reviews are fetched - Add cache hit/miss logging

Challenge 2: Real External API

Replace mock scraper with real external API: - Create connector for actual review API - Handle rate limiting and retries
- Add configuration for multiple sources

Challenge 3: Authentication

Add API key authentication: - Generate API keys for users - Validate API key on protected endpoints - Rate limiting per API key

Challenge 4: Batch Operations

Add bulk review operations: - POST /reviews/batch - Insert multiple reviews - DELETE /products/:id/reviews - Delete all reviews for product - Background job for periodic review updates

Common Issues & Solutions

Database Connection Fails

# Check MySQL is running
brew services start mysql  # macOS
systemctl start mysql      # Linux

# Verify credentials in .env
mysql -u root -p review_aggregator

Validation Errors

// Check Joi schema matches request data
const { error, value } = schema.validate(req.body);
if (error) {
  console.log('Validation error:', error.details);
}

Duplicate Key Errors

-- Check existing reviews
SELECT product_id, source, external_review_id, COUNT(*) 
FROM reviews 
GROUP BY product_id, source, external_review_id 
HAVING COUNT(*) > 1;

Performance Issues

// Add database indexes
CREATE INDEX idx_reviews_product_id ON reviews(product_id);
CREATE INDEX idx_reviews_source ON reviews(source);
CREATE INDEX idx_reviews_rating ON reviews(rating);

Assessment Rubric

API Implementation (40%)

Criteria Excellent (10) Good (7) Needs Work (4)
Endpoints All endpoints working correctly Most endpoints working Some endpoints working
Error Handling Comprehensive error handling Basic error handling Minimal error handling
Validation Proper input validation Some validation Little/no validation
Database Integration Clean, efficient queries Working but could be better Query issues

Code Quality (30%)

Criteria Excellent (10) Good (7) Needs Work (4)
Organization Well-structured, modular Mostly organized Poor structure
Comments Clear documentation Some comments Minimal comments
Error Handling Graceful error recovery Basic error handling Poor error handling

Testing & Integration (20%)

Criteria Excellent (10) Good (7) Needs Work (4)
API Testing Comprehensive test coverage Basic testing Minimal testing
Database Testing Verifies data integrity Basic verification No verification

Performance (10%)

Criteria Excellent (10) Good (7) Needs Work (4)
Response Time Fast responses (< 1s) Reasonable (< 3s) Slow (> 3s)
Error Recovery Handles failures gracefully Basic recovery Poor recovery

Réutilisation