Sequelize ORM Node.js - N+1 Query That Brought Down API

// database/connection.js
// This file creates ONE shared Sequelize instance for the entire app.
// Import this wherever you need database access — never create a second instance.

const { Sequelize } = require('sequelize');

// Pull credentials from environment variables — NEVER hardcode these
const DB_NAME = process.env.DB_NAME || 'storefront_db';
const DB_USER = process.env.DB_USER || 'postgres';
const DB_PASS = process.env.DB_PASS || 'supersecret';
const DB_HOST = process.env.DB_HOST || 'localhost';

const sequelize = new Sequelize(DB_NAME, DB_USER, DB_PASS, {
  host: DB_HOST,
  dialect: 'postgres',       // swap to 'mysql' or 'sqlite' without touching anything else

  pool: {
    max: 10,                 // max 10 simultaneous connections — tune for your server
    min: 2,                  // always keep 2 warm connections ready
    acquire: 30000,          // wait up to 30s to get a connection before throwing
    idle: 10000              // release a connection that's been idle for 10s
  },

  logging: process.env.NODE_ENV === 'development'
    ? (sql) => console.log('[SQL]', sql)  // log queries in dev — silence in prod
    : false
});

// Test the connection on startup so you know immediately if credentials are wrong
async function testConnection() {
  try {
    await sequelize.authenticate();
    console.log('Database connection established successfully.');
  } catch (error) {
    console.error('Unable to connect to the database:', error.message);
    process.exit(1); // kill the app — there is no point running without a DB
  }
}

testConnection();

module.exports = sequelize;

// models/Product.js
// The Product model maps to the 'products' table in our database.
// We use the class-extension pattern so we can add instance methods later.

const { Model, DataTypes } = require('sequelize');
const sequelize = require('../database/connection');

class Product extends Model {
  // Instance method — available on any product object returned from a query
  getFormattedPrice() {
    // 'this' refers to the specific product row fetched from the DB
    return `$${(this.priceInCents / 100).toFixed(2)}`;
  }

  isInStock() {
    return this.stockQuantity > 0;
  }
}

Product.init(
  {
    // Sequelize adds 'id' as an auto-incrementing primary key by default,
    // but we define it explicitly here for clarity and UUID support if needed
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true
    },

    name: {
      type: DataTypes.STRING(200),  // VARCHAR(200) — not TEXT, names are short
      allowNull: false,             // NOT NULL constraint at the DB level
      validate: {
        notEmpty: { msg: 'Product name cannot be blank' },
        len: { args: [2, 200], msg: 'Name must be between 2 and 200 characters' }
      }
    },

    description: {
      type: DataTypes.TEXT,         // TEXT — descriptions can be long
      allowNull: true               // optional field is fine
    },

    priceInCents: {
      type: DataTypes.INTEGER,      // Store money as integers (cents) — NEVER floats
      allowNull: false,
      validate: {
        min: { args: [0], msg: 'Price cannot be negative' },
        isInt: { msg: 'Price must be a whole number of cents' }
      }
    },

    stockQuantity: {
      type: DataTypes.INTEGER,
      defaultValue: 0,             // new products start with 0 stock unless specified
      validate: {
        min: { args: [0], msg: 'Stock cannot go below zero' }
      }
    },

    isActive: {
      type: DataTypes.BOOLEAN,
      defaultValue: true           // new products are active by default
    }
  },
  {
    sequelize,                     // pass the connection instance
    modelName: 'Product',          // used internally by Sequelize
    tableName: 'products',         // explicit table name — don't let Sequelize guess
    timestamps: true,              // adds createdAt and updatedAt columns automatically
    underscored: true              // maps camelCase JS fields to snake_case DB columns
  }
);

// --- Quick usage demo ---
async function demoProductCreation() {
  // sync({ force: false }) creates the table if it doesn't exist — safe for dev
  await sequelize.sync({ alter: false });

  const laptop = await Product.create({
    name: 'Pro Laptop 15',
    description: 'A laptop built for developers.',
    priceInCents: 149999,          // $1,499.99 — stored as 149999 cents
    stockQuantity: 25
  });

  console.log('Created product:', laptop.name);
  console.log('Formatted price:', laptop.getFormattedPrice());
  console.log('In stock?', laptop.isInStock());
  console.log('Created at:', laptop.createdAt);
}

demoProductCreation();

module.exports = Product;

// models/index.js
// This is the single source of truth for all model associations.
// Import THIS file everywhere — not the individual model files.

const sequelize = require('../database/connection');
const { DataTypes, Model } = require('sequelize');

// --- Define User model ---
class User extends Model {}
User.init(
  {
    id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
    fullName: { type: DataTypes.STRING(150), allowNull: false },
    email: {
      type: DataTypes.STRING(255),
      allowNull: false,
      unique: true,              // DB-level unique constraint
      validate: { isEmail: { msg: 'Must be a valid email address' } }
    },
    passwordHash: { type: DataTypes.STRING(255), allowNull: false }
  },
  { sequelize, modelName: 'User', tableName: 'users', timestamps: true, underscored: true }
);

// --- Define Order model ---
class Order extends Model {}
Order.init(
  {
    id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
    // userId is the foreign key — Sequelize can add this automatically via associations,
    // but declaring it explicitly gives you control over the column name and constraints
    userId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: { model: 'users', key: 'id' }  // enforces FK at DB level
    },
    status: {
      type: DataTypes.ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled'),
      defaultValue: 'pending'
    },
    totalInCents: { type: DataTypes.INTEGER, allowNull: false }
  },
  { sequelize, modelName: 'Order', tableName: 'orders', timestamps: true, underscored: true }
);

// --- Define OrderItem (the join table for Orders <-> Products) ---
class OrderItem extends Model {}
OrderItem.init(
  {
    id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
    quantity: { type: DataTypes.INTEGER, allowNull: false, validate: { min: 1 } },
    unitPriceInCents: { type: DataTypes.INTEGER, allowNull: false }  // snapshot of price at purchase time
  },
  { sequelize, modelName: 'OrderItem', tableName: 'order_items', timestamps: false, underscored: true }
);

// Import Product from its own file (no circular dep risk since Product doesn't import models/index)
const Product = require('./Product');

// =====================================================================
// ASSOCIATIONS — defined HERE, not inside individual model files
// Both sides of every relationship must be declared.
// =====================================================================

// A User can place many Orders
User.hasMany(Order, {
  foreignKey: 'userId',
  as: 'orders'           // alias used in 'include' queries
});
// Each Order belongs to exactly one User
Order.belongsTo(User, {
  foreignKey: 'userId',
  as: 'customer'         // alias: order.customer gives you the User object
});

// An Order has many OrderItems (line items)
Order.hasMany(OrderItem, { foreignKey: 'orderId', as: 'lineItems' });
OrderItem.belongsTo(Order, { foreignKey: 'orderId', as: 'order' });

// A Product can appear in many OrderItems
Product.hasMany(OrderItem, { foreignKey: 'productId', as: 'orderItems' });
OrderItem.belongsTo(Product, { foreignKey: 'productId', as: 'product' });

// =====================================================================
// EAGER LOADING DEMO
// Fetch a user + all their orders + each order's line items + product names
// This produces ONE round trip to the DB — not N+1 queries
// =====================================================================
async function getUserOrderHistory(userId) {
  const userWithOrders = await User.findByPk(userId, {
    include: [
      {
        model: Order,
        as: 'orders',
        include: [
          {
            model: OrderItem,
            as: 'lineItems',
            include: [
              { model: Product, as: 'product', attributes: ['name', 'priceInCents'] }
            ]
          }
        ]
      }
    ],
    // Only return safe fields — never return passwordHash to the client
    attributes: { exclude: ['passwordHash'] }
  });

  if (!userWithOrders) {
    throw new Error(`User ${userId} not found`);
  }

  return userWithOrders;
}

// Sync all tables in dependency order (users before orders, etc.)
async function syncAllModels() {
  await sequelize.sync({ alter: true }); // alter: true updates columns without dropping data
  console.log('All tables synced.');
}

module.exports = { sequelize, User, Order, OrderItem, Product, getUserOrderHistory, syncAllModels };

// migrations/20240315102344-add-discount-code-to-orders.js
//
// Scenario: The marketing team wants to track discount codes on orders.
// We need to add a nullable 'discount_code' column to the orders table.
//
// Run this with: npx sequelize-cli db:migrate
// Undo this with: npx sequelize-cli db:migrate:undo

'use strict';

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface, Sequelize) {
    // queryInterface is Sequelize's low-level tool for schema changes
    // It does NOT go through your model — it speaks directly to the DB

    await queryInterface.addColumn(
      'orders',              // the exact table name in the database
      'discount_code',       // snake_case to match our 'underscored: true' config
      {
        type: Sequelize.STRING(50),
        allowNull: true,     // nullable — most orders won't have a discount
        defaultValue: null,
        after: 'status'      // MySQL-only: places the column after 'status' column
      }
    );

    // Add an index on discount_code so marketing can query by code efficiently
    await queryInterface.addIndex('orders', ['discount_code'], {
      name: 'orders_discount_code_idx',
      where: {
        discount_code: { [Sequelize.Op.ne]: null }  // partial index — only non-null rows
      }
    });

    console.log('Migration up: discount_code column added to orders.');
  },

  async down(queryInterface, Sequelize) {
    // The down() MUST be the exact reverse of up()
    // Always remove the index before removing the column

    await queryInterface.removeIndex('orders', 'orders_discount_code_idx');
    await queryInterface.removeColumn('orders', 'discount_code');

    console.log('Migration down: discount_code column removed from orders.');
  }
};

// =======================================================
// HOW TO GENERATE A NEW MIGRATION FILE:
// npx sequelize-cli migration:generate --name add-discount-code-to-orders
//
// HOW TO CHECK MIGRATION STATUS:
// npx sequelize-cli db:migrate:status
//
// Output:
// up 20240310091200-create-users.js
// up 20240311143000-create-products.js
// up 20240312090000-create-orders.js
// up 20240315102344-add-discount-code-to-orders.js
// =======================================================

// repositories/orderReport.js
// Example: monthly sales report grouped by category using raw query.
// This query uses a window function (PostgreSQL) — not possible with Sequelize's query builder.

const sequelize = require('../database/connection');

async function getMonthlySalesReport(year, month) {
  const [results, metadata] = await sequelize.query(
    `
    SELECT
      p.id AS product_id,
      p.name,
      SUM(oi.quantity * oi.unit_price_in_cents) AS total_sales_in_cents,
      COUNT(DISTINCT o.id) AS num_orders,
      RANK() OVER (ORDER BY SUM(oi.quantity * oi.unit_price_in_cents) DESC) AS rank
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.id
    JOIN products p ON oi.product_id = p.id
    WHERE EXTRACT(YEAR FROM o.created_at) = :year
      AND EXTRACT(MONTH FROM o.created_at) = :month
      AND o.status NOT IN ('cancelled')
    GROUP BY p.id, p.name
    ORDER BY total_sales_in_cents DESC
    LIMIT 20;
    `,
    {
      replacements: { year, month },   // parameterized — safe from injection
      type: sequelize.QueryTypes.SELECT
    }
  );

  return results;
}

module.exports = { getMonthlySalesReport };

// services/orderService.js
// Example: placing an order with stock deduction and order creation in a transaction.

const { sequelize, Order, OrderItem, Product } = require('../models');

async function placeOrder(userId, items) {
  // Managed transaction — Sequelize handles commit/rollback automatically
  const newOrder = await sequelize.transaction(async (t) => {
    
    // 1. Calculate total and verify stock
    let totalInCents = 0;
    const orderItemsData = [];

    for (const item of items) {
      const product = await Product.findByPk(item.productId, { transaction: t });
      if (!product) throw new Error(`Product ${item.productId} not found`);
      if (product.stockQuantity < item.quantity) {
        throw new Error(`Insufficient stock for product ${product.name}`);
      }

      // Deduct stock within the transaction
      await product.decrement('stockQuantity', {
        by: item.quantity,
        transaction: t
      });

      const lineTotal = product.priceInCents * item.quantity;
      totalInCents += lineTotal;

      orderItemsData.push({
        productId: product.id,
        quantity: item.quantity,
        unitPriceInCents: product.priceInCents
      });
    }

    // 2. Create the order
    const order = await Order.create(
      { userId, totalInCents, status: 'pending' },
      { transaction: t }
    );

    // 3. Create order items
    await OrderItem.bulkCreate(
      orderItemsData.map(item => ({ ...item, orderId: order.id })),
      { transaction: t }
    );

    return order;
  });

  // If we get here, the transaction committed successfully
  return newOrder;
}

module.exports = { placeOrder };