Database Integration for Full-Stack Apps

Express + MySQL Integration Patterns

database
mysql
express
integration
Lecture
Auteur
Affiliations

Emmanuel BRUNO

Université de Toulon

LIS UMR CNRS 7020

Date de publication

2025-10-12

Résumé

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

Database Integration 🗄️

2-Hour Focus: Express + MySQL → Production Patterns

🎯 Today’s Mission

What We’re Building (2 Hours)

Hour 1: MySQL + Express Integration (connection pools, query patterns, error handling)
Hour 2: Advanced Patterns (transactions, migrations, performance optimization)

Using: Review Aggregator backend from Session 4
Goal: Replace in-memory arrays with persistent MySQL storage! 🎉

Prerequisites: You know SQL basics (SELECT, INSERT, UPDATE, DELETE, JOINs)
Focus: Integration patterns, not SQL syntax

🗺️ The Integration Journey

Hour 1: MYSQL INTEGRATION        Hour 2: PRODUCTION PATTERNS
┌─────────────────┐             ┌─────────────────┐
│ Connection Pool │             │ Transactions    │
│ Query Patterns  │      →      │ Migrations      │
│ Error Handling  │             │ Optimization    │
│ Replace Arrays  │             │ Security        │
└─────────────────┘             └─────────────────┘
   Working Database               Production Ready

Hour 1: MySQL + Express Integration

0:00 - 1:00

💡 Why Database Integration Matters

The Problem with In-Memory Storage (from Session 4):

// Session 4: In-memory arrays
let reviews = [
  { id: 1, title: "Great product!", rating: 5 }
];

// Problems:
❌ Data lost on server restart
❌ No concurrent access protection
❌ Memory limitations
❌ No data relationships
❌ No backup/recovery

Database Integration Benefits:

// With MySQL
const [rows] = await db.execute(
  'SELECT * FROM reviews WHERE product_id = ?',
  [productId]
);

// Benefits:
✅ Data persists across restarts
✅ ACID transactions
✅ Concurrent access safe
✅ Scalable to millions of records
✅ Automatic backups
✅ Rich query capabilities

🔧 Connection Pool Setup

Why Connection Pools?

// ❌ Bad: Create new connection each time
async function getReviews() {
  const connection = mysql.createConnection(config);
  const result = await connection.query('SELECT * FROM reviews');
  connection.end(); // Expensive!
  return result;
}

// ✅ Good: Use connection pool
const pool = mysql.createPool(config);
async function getReviews() {
  const [rows] = await pool.execute('SELECT * FROM reviews');
  return rows; // Pool manages connections
}

Pool Configuration:

// config/database.js
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME || 'review_aggregator',
  waitForConnections: true,
  connectionLimit: 10,      // Max simultaneous connections
  queueLimit: 0,           // No limit on queued requests
  acquireTimeout: 60000,   // 60s timeout to get connection
  timeout: 60000,          // 60s query timeout
  reconnect: true          // Auto-reconnect on disconnect
});

module.exports = pool;

📊 Database Schema Design

Review Aggregator Schema:

-- Products table (reference)
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10,2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Reviews table (our focus)
CREATE TABLE reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  source ENUM('Amazon', 'BestBuy', 'Walmart') NOT NULL,
  external_review_id VARCHAR(100) NOT NULL,
  author VARCHAR(255),
  rating INT CHECK (rating BETWEEN 1 AND 5),
  title VARCHAR(500),
  content TEXT,
  review_date DATETIME,
  fetched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  -- Prevent duplicates
  UNIQUE KEY unique_review (product_id, source, external_review_id),
  
  -- Performance indexes
  INDEX idx_product_id (product_id),
  INDEX idx_source (source),
  INDEX idx_rating (rating),
  
  -- Foreign key
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

Key Design Decisions: - UNIQUE constraint prevents duplicate reviews - Indexes on commonly filtered columns - ENUM for source (controlled values) - Foreign key maintains referential integrity

🔄 Query Patterns

Replace GET /api/reviews with SQL

Before (Session 4):

app.get('/api/reviews', (req, res) => {
  const { product_id, source } = req.query;
  let filtered = reviews.filter(r => r.product_id === product_id);
  if (source) filtered = filtered.filter(r => r.source === source);
  res.json(filtered);
});

After (with MySQL):

const db = require('../config/database');

app.get('/api/reviews', async (req, res) => {
  try {
    const { product_id, source, limit = 20, offset = 0 } = req.query;
    
    // Build dynamic query
    let sql = 'SELECT * FROM reviews WHERE product_id = ?';
    let params = [product_id];
    
    if (source) {
      sql += ' AND source = ?';
      params.push(source);
    }
    
    sql += ' ORDER BY review_date DESC LIMIT ? OFFSET ?';
    params.push(parseInt(limit), parseInt(offset));
    
    const [rows] = await db.execute(sql, params);
    
    res.json({
      success: true,
      data: rows,
      meta: { count: rows.length, limit, offset }
    });
  } catch (error) {
    console.error('Database error:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to fetch reviews'
    });
  }
});

Replace POST /api/reviews with SQL

Pattern: INSERT with Duplicate Handling

app.post('/api/reviews', async (req, res) => {
  try {
    const {
      product_id,
      source,
      external_review_id,
      author,
      rating,
      title,
      content,
      review_date
    } = req.body;
    
    const [result] = await db.execute(
      `INSERT INTO reviews 
       (product_id, source, external_review_id, author, rating, title, content, review_date)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [product_id, source, external_review_id, author, rating, title, content, review_date]
    );
    
    // Fetch the created review
    const [rows] = await db.execute(
      'SELECT * FROM reviews WHERE id = ?',
      [result.insertId]
    );
    
    res.status(201).json({
      success: true,
      data: rows[0]
    });
    
  } catch (error) {
    // Handle MySQL errors
    if (error.code === 'ER_DUP_ENTRY') {
      return res.status(409).json({
        success: false,
        error: 'Review already exists',
        code: 'DUPLICATE_REVIEW'
      });
    }
    
    if (error.code === 'ER_NO_REFERENCED_ROW_2') {
      return res.status(400).json({
        success: false,
        error: 'Product not found',
        code: 'INVALID_PRODUCT'
      });
    }
    
    console.error('Database error:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to create review'
    });
  }
});

Aggregate Statistics with SQL

Calculate stats directly in database:

app.get('/api/reviews/aggregate', async (req, res) => {
  try {
    const { product_id } = req.query;
    
    // Overall statistics
    const [overall] = await db.execute(
      `SELECT 
        AVG(rating) as average_rating,
        COUNT(*) as total_reviews,
        MIN(rating) as min_rating,
        MAX(rating) as max_rating
       FROM reviews 
       WHERE product_id = ?`,
      [product_id]
    );
    
    // Per-source breakdown
    const [sources] = await db.execute(
      `SELECT 
        source,
        AVG(rating) as average_rating,
        COUNT(*) as review_count
       FROM reviews 
       WHERE product_id = ?
       GROUP BY source
       ORDER BY review_count DESC`,
      [product_id]
    );
    
    // Rating distribution
    const [distribution] = await db.execute(
      `SELECT 
        rating,
        COUNT(*) as count
       FROM reviews 
       WHERE product_id = ?
       GROUP BY rating
       ORDER BY rating DESC`,
      [product_id]
    );
    
    // Transform distribution to object
    const ratingHistogram = { "5": 0, "4": 0, "3": 0, "2": 0, "1": 0 };
    distribution.forEach(row => {
      ratingHistogram[row.rating.toString()] = row.count;
    });
    
    res.json({
      success: true,
      data: {
        overall: {
          average_rating: overall[0].average_rating 
            ? Math.round(overall[0].average_rating * 10) / 10 
            : 0,
          total_reviews: overall[0].total_reviews,
          min_rating: overall[0].min_rating,
          max_rating: overall[0].max_rating
        },
        by_source: sources.map(row => ({
          source: row.source,
          average_rating: Math.round(row.average_rating * 10) / 10,
          review_count: row.review_count
        })),
        rating_histogram: ratingHistogram
      }
    });
    
  } catch (error) {
    console.error('Aggregate query error:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to calculate statistics'
    });
  }
});

🔒 SQL Injection Prevention

Always Use Parameterized Queries:

// ❌ DANGEROUS - SQL Injection vulnerability
app.get('/api/reviews/:id', async (req, res) => {
  const sql = `SELECT * FROM reviews WHERE id = ${req.params.id}`;
  const [rows] = await db.execute(sql); // NEVER DO THIS!
});

// Attacker sends: /api/reviews/1; DROP TABLE reviews; --
// Your database is gone! 💥

// ✅ SAFE - Parameterized query
app.get('/api/reviews/:id', async (req, res) => {
  const [rows] = await db.execute(
    'SELECT * FROM reviews WHERE id = ?',
    [req.params.id]
  );
  // MySQL automatically escapes the parameter
});

Dynamic WHERE Clauses (Safe Way):

function buildWhereClause(filters) {
  const conditions = [];
  const params = [];
  
  if (filters.product_id) {
    conditions.push('product_id = ?');
    params.push(filters.product_id);
  }
  
  if (filters.source) {
    conditions.push('source = ?');
    params.push(filters.source);
  }
  
  if (filters.min_rating) {
    conditions.push('rating >= ?');
    params.push(filters.min_rating);
  }
  
  const whereClause = conditions.length > 0 
    ? 'WHERE ' + conditions.join(' AND ')
    : '';
    
  return { whereClause, params };
}

// Usage
const filters = req.query;
const { whereClause, params } = buildWhereClause(filters);
const sql = `SELECT * FROM reviews ${whereClause} ORDER BY review_date DESC`;
const [rows] = await db.execute(sql, params);
✅ Hour 1 Checkpoint

You should now have: - ✅ Connection pool setup and tested - ✅ Replaced GET endpoints with SQL queries - ✅ Implemented POST with duplicate handling - ✅ Added aggregate statistics calculation - ✅ Proper SQL injection prevention

Hour 2 Preview: Transactions, migrations, performance optimization!

Hour 2: Production Patterns

1:00 - 2:00

🔄 Database Transactions

Why Transactions Matter:

// Scenario: Fetch reviews AND update product stats
// WITHOUT transaction - can fail partially:
async function fetchAndUpdateStats(productId) {
  // Step 1: Insert new reviews (succeeds)
  await insertReviews(newReviews);
  
  // Step 2: Update product stats (fails - server crash!)
  await updateProductStats(productId);
  
  // Result: New reviews exist but stats are stale!
  // Data inconsistency! 😱
}

WITH Transaction - All or Nothing:

async function fetchAndUpdateStats(productId) {
  const connection = await db.getConnection();
  
  try {
    await connection.beginTransaction();
    
    // Step 1: Insert reviews
    for (const review of newReviews) {
      await connection.execute(
        'INSERT INTO reviews (...) VALUES (...)',
        [review.data]
      );
    }
    
    // Step 2: Update product average
    await connection.execute(
      'UPDATE products SET avg_rating = (SELECT AVG(rating) FROM reviews WHERE product_id = ?) WHERE id = ?',
      [productId, productId]
    );
    
    await connection.commit();
    console.log('✅ All operations completed successfully');
    
  } catch (error) {
    await connection.rollback();
    console.error('❌ Transaction failed, rolling back:', error);
    throw error;
  } finally {
    connection.release();
  }
}

📋 Database Migrations

What are Migrations?

Scripts that version your database schema like Git versions your code.

Migration Structure:

migrations/
├── 001_create_products_table.sql
├── 002_create_reviews_table.sql
├── 003_add_review_indexes.sql
├── 004_add_sentiment_column.sql
└── migrate.js

Example Migration:

-- migrations/003_add_review_indexes.sql
-- Description: Add performance indexes for common queries
-- Date: 2025-10-02

-- Add indexes
CREATE INDEX idx_reviews_product_source ON reviews(product_id, source);
CREATE INDEX idx_reviews_rating_date ON reviews(rating, review_date);

-- Rollback (if needed):
-- DROP INDEX idx_reviews_product_source ON reviews;
-- DROP INDEX idx_reviews_rating_date ON reviews;

Migration Runner:

// scripts/migrate.js
const fs = require('fs');
const path = require('path');
const db = require('../config/database');

async function runMigrations() {
  try {
    // Create migrations table if not exists
    await db.execute(`
      CREATE TABLE IF NOT EXISTS migrations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        filename VARCHAR(255) NOT NULL UNIQUE,
        executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);
    
    // Get executed migrations
    const [executed] = await db.execute('SELECT filename FROM migrations');
    const executedSet = new Set(executed.map(row => row.filename));
    
    // Get all migration files
    const migrationsDir = path.join(__dirname, '../migrations');
    const files = fs.readdirSync(migrationsDir)
      .filter(file => file.endsWith('.sql'))
      .sort();
    
    // Run pending migrations
    for (const file of files) {
      if (executedSet.has(file)) {
        console.log(`⏭️  Skipping ${file} (already executed)`);
        continue;
      }
      
      console.log(`🔄 Executing ${file}...`);
      
      const sql = fs.readFileSync(path.join(migrationsDir, file), 'utf8');
      await db.execute(sql);
      
      // Record as executed
      await db.execute(
        'INSERT INTO migrations (filename) VALUES (?)',
        [file]
      );
      
      console.log(`✅ Completed ${file}`);
    }
    
    console.log('🎉 All migrations completed!');
  } catch (error) {
    console.error('❌ Migration failed:', error);
    process.exit(1);
  }
}

runMigrations();

Usage:

npm run migrate    # Run pending migrations

⚡ Performance Optimization

Query Optimization

Use EXPLAIN to Analyze Queries:

-- Check if your query uses indexes
EXPLAIN SELECT * FROM reviews 
WHERE product_id = 1 AND source = 'Amazon' 
ORDER BY review_date DESC 
LIMIT 10;

-- Look for:
-- ✅ type: 'ref' or 'range' (good)
-- ❌ type: 'ALL' (full table scan - bad!)
-- ✅ Extra: 'Using index' (great!)
-- ❌ Extra: 'Using filesort' (slow!)

Add Strategic Indexes:

-- For common query patterns
CREATE INDEX idx_product_source_date ON reviews(product_id, source, review_date);
CREATE INDEX idx_rating_filter ON reviews(product_id, rating);

Connection Pool Tuning

Monitor Pool Usage:

// Add pool monitoring
setInterval(() => {
  // Use an instrumentation helper exported from your database config
  // (see config/database.js for a lightweight wrapper example)
  const poolStats = getPoolStats();
  console.log('Pool status:', poolStats);
}, 30000); // Every 30 seconds

Optimize Pool Size:

const pool = mysql.createPool({
  // ...other config
  connectionLimit: process.env.NODE_ENV === 'production' ? 20 : 5,
  acquireTimeout: 30000,      // Shorter timeout
  timeout: 30000,             // Query timeout
  idleTimeout: 300000,        // 5 minutes idle timeout
  reconnect: true,
  multipleStatements: false   // Security: prevent multiple statements
});

📊 Monitoring & Logging

Query Performance Logging:

// middleware/queryLogger.js
function logSlowQueries(threshold = 1000) {
  const originalExecute = db.execute;
  
  db.execute = async function(sql, params) {
    const start = Date.now();
    
    try {
      const result = await originalExecute.call(this, sql, params);
      const duration = Date.now() - start;
      
      if (duration > threshold) {
        console.warn(`🐌 Slow query (${duration}ms):`, {
          sql: sql.substring(0, 100) + '...',
          params: params?.slice(0, 5),
          duration
        });
      }
      
      return result;
    } catch (error) {
      const duration = Date.now() - start;
      console.error(`❌ Query failed (${duration}ms):`, {
        sql: sql.substring(0, 100) + '...',
        params: params?.slice(0, 5),
        error: error.message
      });
      throw error;
    }
  };
}

// Enable in development
if (process.env.NODE_ENV === 'development') {
  logSlowQueries(500); // Log queries > 500ms
}

Health Monitoring:

// routes/health.js
app.get('/health/detailed', async (req, res) => {
  const health = {
    timestamp: new Date().toISOString(),
    service: 'review-aggregator-api',
    version: process.env.npm_package_version,
    environment: process.env.NODE_ENV,
    uptime: process.uptime(),
    memory: process.memoryUsage(),
    database: { status: 'unknown' }
  };
  
  try {
    const start = Date.now();
    const [rows] = await db.execute('SELECT COUNT(*) as review_count FROM reviews');
    const queryTime = Date.now() - start;
    
    // Use a safe helper to expose pool stats if available. Avoid accessing
    // internal pool properties (like db.pool.totalConnections) directly as
    // these internals are not portable across mysql2 versions.
    let poolStatus = { connectionLimit: null, activeConnections: null };
    try {
      if (typeof db.getPoolStats === 'function') {
        poolStatus = db.getPoolStats();
      } else if (db.pool && db.pool.config && db.pool.config.connectionLimit) {
        poolStatus.connectionLimit = db.pool.config.connectionLimit;
      }
    } catch (e) {
      // best-effort only
    }

    health.database = {
      status: 'healthy',
      response_time_ms: queryTime,
      total_reviews: rows[0].review_count,
      pool_status: poolStatus
    };
    
    const overallStatus = queryTime < 1000 ? 'healthy' : 'degraded';
    res.status(overallStatus === 'healthy' ? 200 : 503).json({
      status: overallStatus,
      ...health
    });
    
  } catch (error) {
    health.database = {
      status: 'unhealthy',
      error: error.message
    };
    
    res.status(503).json({
      status: 'unhealthy',
      ...health
    });
  }
});

🔐 Security Best Practices

Database User Permissions:

-- Create app-specific database user (not root!)
CREATE USER 'review_app'@'localhost' IDENTIFIED BY 'strong_password_here';

-- Grant only necessary permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON review_aggregator.* TO 'review_app'@'localhost';

-- No DDL permissions (CREATE, DROP, ALTER) for app user
-- Use separate admin user for migrations

Environment Variables:

# .env
DB_HOST=localhost
DB_USER=review_app
DB_PASSWORD=strong_password_here
DB_NAME=review_aggregator
DB_PORT=3306

# Production: Use secrets management
# AWS: Parameter Store, Secrets Manager
# Azure: Key Vault
# Google: Secret Manager

Input Validation Middleware:

const { body, query, validationResult } = require('express-validator');

const validateReviewInput = [
  body('product_id').isString().isLength({ min: 1, max: 50 }),
  body('source').isIn(['Amazon', 'BestBuy', 'Walmart']),
  body('rating').isInt({ min: 1, max: 5 }),
  body('title').optional().isString().isLength({ max: 500 }),
  body('content').optional().isString().isLength({ max: 5000 }),
  (req, res, next) => {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({
        success: false,
        errors: errors.array()
      });
    }
    next();
  }
];

app.post('/api/reviews', validateReviewInput, async (req, res) => {
  // Input is validated and sanitized
});

🎯 Complete Integration Example

Full Review Fetch Endpoint (Production-Ready):

app.post('/api/products/:id/fetch', 
  validateProductId,
  rateLimitByUser,
  async (req, res) => {
    const productId = req.params.id;
    const connection = await db.getConnection();
    
    try {
      await connection.beginTransaction();
      
      // 1. Verify product exists
      const [products] = await connection.execute(
        'SELECT id FROM products WHERE id = ?',
        [productId]
      );
      
      if (products.length === 0) {
        return res.status(404).json({
          success: false,
          error: 'Product not found'
        });
      }
      
      // 2. Call external scraper
      const scraperResponse = await fetch(`${SCRAPER_URL}/scrape/${productId}`, {
        method: 'POST',
        headers: { 'Authorization': `Bearer ${SCRAPER_API_KEY}` }
      });
      
      if (!scraperResponse.ok) {
        throw new Error('Scraper service unavailable');
      }
      
      const newReviews = await scraperResponse.json();
      
      // 3. Insert new reviews (skip duplicates)
      let insertCount = 0;
      for (const review of newReviews) {
        try {
          await connection.execute(
            `INSERT INTO reviews 
             (product_id, source, external_review_id, author, rating, title, content, review_date)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
            [productId, review.source, review.id, review.author, review.rating, review.title, review.body, review.date]
          );
          insertCount++;
        } catch (error) {
          if (error.code === 'ER_DUP_ENTRY') {
            // Skip duplicate, continue with others
            continue;
          }
          throw error; // Re-throw other errors
        }
      }
      
      // 4. Update product statistics
      await connection.execute(
        `UPDATE products SET 
         avg_rating = (SELECT AVG(rating) FROM reviews WHERE product_id = ?),
         review_count = (SELECT COUNT(*) FROM reviews WHERE product_id = ?),
         last_scraped = NOW()
         WHERE id = ?`,
        [productId, productId, productId]
      );
      
      await connection.commit();
      
      res.json({
        success: true,
        message: `Successfully fetched ${insertCount} new reviews`,
        data: { new_reviews: insertCount, duplicates_skipped: newReviews.length - insertCount }
      });
      
    } catch (error) {
      await connection.rollback();
      
      console.error('Fetch reviews failed:', error);
      
      if (error.message.includes('Scraper service')) {
        return res.status(503).json({
          success: false,
          error: 'External service temporarily unavailable',
          code: 'SCRAPER_UNAVAILABLE'
        });
      }
      
      res.status(500).json({
        success: false,
        error: 'Failed to fetch reviews',
        code: 'FETCH_FAILED'
      });
    } finally {
      connection.release();
    }
  }
);

🎉 Session Summary

What You’ve Learned Today

Hour 1: Integration Basics - ✅ Connection pooling setup - ✅ Parameterized queries (SQL injection prevention) - ✅ Error handling patterns - ✅ CRUD operations with MySQL - ✅ Aggregate statistics - ✅ Health monitoring

Hour 2: Production Patterns - ✅ Database transactions - ✅ Migration systems - ✅ Performance optimization - ✅ Query monitoring - ✅ Security best practices - ✅ Complete integration example

Key Takeaways

  1. Connection pools are essential for performance and reliability
  2. Always use parameterized queries to prevent SQL injection
  3. Transactions ensure data consistency for multi-step operations
  4. Migrations version your database like Git versions code
  5. Monitor query performance and add indexes strategically
  6. Security is layered - database users, input validation, environment variables

From In-Memory to Production Database

You’ve now completed the journey: - ✅ Session 4: Express API with in-memory arrays - ✅ Today: Production database integration - 🔜 Next: Connect React frontend to database-backed API

Your Review Aggregator now has persistent, scalable, secure data storage! 🚀

📚 Practice Session Preview

Immediately after this lecture: 1-hour hands-on practice replacing your in-memory arrays with MySQL database persistence using the patterns learned today.

What you’ll do: - Set up connection pools and database schema - Replace all CRUD operations with SQL queries - Implement transaction-based batch operations - Add comprehensive error handling and monitoring

This prepares you for: Next session’s full-stack integration where your React frontend will consume real, persistent data!

🎯 You’re Now a Database Integration Expert!

Key Achievements: - ⚡ Set up production-ready database connections - 🗄️ Implemented persistent data storage with MySQL - 🔒 Secured operations against SQL injection - 🔄 Added transaction support for data consistency - 📊 Created monitoring and health checks - 🏗️ Built maintainable, scalable code architecture

Next Steps: Practice session to implement these patterns, then connect to your React frontend! 🚀

Réutilisation