Node.js Database Integration

SQL Databases

1. Connecting to MySQL

Using mysql2 package (Native driver):

bash

npm install mysql2

javascript

// mysql-connection.js
const mysql = require('mysql2');

// Create connection pool (recommended for production)
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydatabase',
  waitForConnections: true,
  connectionLimit: 10, // Maximum number of connections
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
});

// Promise wrapper for pool
const promisePool = pool.promise();

// Test connection
async function testConnection() {
  try {
    const [rows] = await promisePool.query('SELECT 1 + 1 AS result');
    console.log('MySQL connected successfully:', rows[0].result);
    return true;
  } catch (error) {
    console.error('MySQL connection failed:', error);
    return false;
  }
}

// Create connection (single connection - not recommended for web apps)
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydatabase'
});

connection.connect((err) => {
  if (err) {
    console.error('Connection error:', err);
    return;
  }
  console.log('Connected to MySQL');
});

module.exports = { pool, promisePool, connection, testConnection };

Basic CRUD Operations with mysql2:

javascript

// mysql-crud.js
const { promisePool } = require('./mysql-connection');

class UserRepository {
  // Create
  async createUser(userData) {
    const sql = 'INSERT INTO users (name, email, age) VALUES (?, ?, ?)';
    const [result] = await promisePool.execute(sql, [
      userData.name,
      userData.email,
      userData.age
    ]);
    return { id: result.insertId, ...userData };
  }

  // Read
  async getUserById(id) {
    const sql = 'SELECT * FROM users WHERE id = ?';
    const [rows] = await promisePool.execute(sql, [id]);
    return rows[0] || null;
  }

  async getAllUsers(page = 1, limit = 10) {
    const offset = (page - 1) * limit;
    const sql = 'SELECT * FROM users LIMIT ? OFFSET ?';
    const countSql = 'SELECT COUNT(*) as total FROM users';
    
    const [rows] = await promisePool.execute(sql, [limit, offset]);
    const [[countResult]] = await promisePool.execute(countSql);
    
    return {
      data: rows,
      pagination: {
        page,
        limit,
        total: countResult.total,
        pages: Math.ceil(countResult.total / limit)
      }
    };
  }

  // Update
  async updateUser(id, userData) {
    const fields = [];
    const values = [];
    
    Object.entries(userData).forEach(([key, value]) => {
      if (value !== undefined) {
        fields.push(`${key} = ?`);
        values.push(value);
      }
    });
    
    if (fields.length === 0) {
      throw new Error('No fields to update');
    }
    
    values.push(id);
    const sql = `UPDATE users SET ${fields.join(', ')} WHERE id = ?`;
    
    const [result] = await promisePool.execute(sql, values);
    return result.affectedRows > 0;
  }

  // Delete
  async deleteUser(id) {
    const sql = 'DELETE FROM users WHERE id = ?';
    const [result] = await promisePool.execute(sql, [id]);
    return result.affectedRows > 0;
  }

  // Transaction example
  async transferMoney(fromId, toId, amount) {
    const connection = await promisePool.getConnection();
    
    try {
      await connection.beginTransaction();
      
      // Deduct from sender
      const deductSql = 'UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?';
      const [deductResult] = await connection.execute(deductSql, [amount, fromId, amount]);
      
      if (deductResult.affectedRows === 0) {
        throw new Error('Insufficient balance or account not found');
      }
      
      // Add to receiver
      const addSql = 'UPDATE accounts SET balance = balance + ? WHERE id = ?';
      await connection.execute(addSql, [amount, toId]);
      
      // Create transaction record
      const transSql = 'INSERT INTO transactions (from_account, to_account, amount) VALUES (?, ?, ?)';
      await connection.execute(transSql, [fromId, toId, amount]);
      
      await connection.commit();
      return { success: true, message: 'Transfer completed' };
      
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }
}

module.exports = new UserRepository();

2. Connecting to PostgreSQL

Using pg package:

bash

npm install pg

javascript

// postgres-connection.js
const { Pool } = require('pg');

// Create connection pool
const pool = new Pool({
  host: 'localhost',
  port: 5432,
  user: 'postgres',
  password: 'password',
  database: 'mydatabase',
  max: 20, // Maximum number of clients
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Connection event handlers
pool.on('connect', () => {
  console.log('New client connected to PostgreSQL');
});

pool.on('error', (err) => {
  console.error('Unexpected error on idle client', err);
});

// Test connection
async function testConnection() {
  try {
    const client = await pool.connect();
    const result = await client.query('SELECT NOW()');
    console.log('PostgreSQL connected successfully:', result.rows[0].now);
    client.release();
    return true;
  } catch (error) {
    console.error('PostgreSQL connection failed:', error);
    return false;
  }
}

// Helper function for queries
async function query(sql, params = []) {
  const client = await pool.connect();
  try {
    const result = await client.query(sql, params);
    return result;
  } finally {
    client.release();
  }
}

module.exports = { pool, query, testConnection };

PostgreSQL CRUD Operations:

javascript

// postgres-crud.js
const { query } = require('./postgres-connection');

class ProductRepository {
  // Create table (migration example)
  async createTable() {
    const sql = `
      CREATE TABLE IF NOT EXISTS products (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        price DECIMAL(10, 2) NOT NULL,
        description TEXT,
        category VARCHAR(100),
        in_stock BOOLEAN DEFAULT true,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );
      
      CREATE INDEX IF NOT EXISTS idx_products_category ON products(category);
      CREATE INDEX IF NOT EXISTS idx_products_price ON products(price);
    `;
    
    await query(sql);
    console.log('Products table created or already exists');
  }

  // Create
  async createProduct(productData) {
    const sql = `
      INSERT INTO products (name, price, description, category, in_stock)
      VALUES ($1, $2, $3, $4, $5)
      RETURNING *;
    `;
    
    const values = [
      productData.name,
      productData.price,
      productData.description,
      productData.category,
      productData.in_stock !== undefined ? productData.in_stock : true
    ];
    
    const result = await query(sql, values);
    return result.rows[0];
  }

  // Read with advanced querying
  async getProducts(filters = {}, page = 1, limit = 10) {
    let whereClause = '';
    const values = [];
    let valueIndex = 1;
    
    // Build WHERE clause dynamically
    if (filters.category) {
      whereClause += ` WHERE category = $${valueIndex}`;
      values.push(filters.category);
      valueIndex++;
    }
    
    if (filters.minPrice) {
      whereClause += whereClause ? ' AND' : ' WHERE';
      whereClause += ` price >= $${valueIndex}`;
      values.push(filters.minPrice);
      valueIndex++;
    }
    
    if (filters.maxPrice) {
      whereClause += whereClause ? ' AND' : ' WHERE';
      whereClause += ` price <= $${valueIndex}`;
      values.push(filters.maxPrice);
      valueIndex++;
    }
    
    if (filters.inStock !== undefined) {
      whereClause += whereClause ? ' AND' : ' WHERE';
      whereClause += ` in_stock = $${valueIndex}`;
      values.push(filters.inStock);
      valueIndex++;
    }
    
    // Pagination values
    const offset = (page - 1) * limit;
    values.push(limit, offset);
    
    const sql = `
      SELECT *, COUNT(*) OVER() AS total_count
      FROM products
      ${whereClause}
      ORDER BY created_at DESC
      LIMIT $${valueIndex} OFFSET $${valueIndex + 1};
    `;
    
    const result = await query(sql, values);
    
    return {
      data: result.rows.map(row => {
        const { total_count, ...product } = row;
        return product;
      }),
      pagination: {
        page,
        limit,
        total: result.rows.length > 0 ? parseInt(result.rows[0].total_count) : 0,
        pages: Math.ceil((result.rows.length > 0 ? parseInt(result.rows[0].total_count) : 0) / limit)
      }
    };
  }

  // Update with RETURNING clause
  async updateProduct(id, productData) {
    const fields = [];
    const values = [];
    let valueIndex = 1;
    
    Object.entries(productData).forEach(([key, value]) => {
      if (value !== undefined) {
        fields.push(`${key} = $${valueIndex}`);
        values.push(value);
        valueIndex++;
      }
    });
    
    if (fields.length === 0) {
      throw new Error('No fields to update');
    }
    
    // Add updated_at timestamp
    fields.push('updated_at = CURRENT_TIMESTAMP');
    
    values.push(id);
    
    const sql = `
      UPDATE products 
      SET ${fields.join(', ')}
      WHERE id = $${valueIndex}
      RETURNING *;
    `;
    
    const result = await query(sql, values);
    return result.rows[0] || null;
  }

  // Delete with cascade check
  async deleteProduct(id) {
    // Check if product has orders
    const checkSql = 'SELECT COUNT(*) FROM order_items WHERE product_id = $1';
    const checkResult = await query(checkSql, [id]);
    
    if (parseInt(checkResult.rows[0].count) > 0) {
      throw new Error('Cannot delete product with existing orders');
    }
    
    const sql = 'DELETE FROM products WHERE id = $1 RETURNING id';
    const result = await query(sql, [id]);
    return result.rows.length > 0;
  }

  // Full-text search example
  async searchProducts(searchTerm, page = 1, limit = 10) {
    const offset = (page - 1) * limit;
    
    const sql = `
      SELECT *, 
             ts_rank_cd(to_tsvector('english', name || ' ' || description), plainto_tsquery($1)) AS rank,
             COUNT(*) OVER() AS total_count
      FROM products
      WHERE to_tsvector('english', name || ' ' || description) @@ plainto_tsquery($1)
      ORDER BY rank DESC, created_at DESC
      LIMIT $2 OFFSET $3;
    `;
    
    const result = await query(sql, [searchTerm, limit, offset]);
    
    return {
      data: result.rows.map(row => {
        const { total_count, rank, ...product } = row;
        return { ...product, relevance: rank };
      }),
      pagination: {
        page,
        limit,
        total: result.rows.length > 0 ? parseInt(result.rows[0].total_count) : 0,
        pages: Math.ceil((result.rows.length > 0 ? parseInt(result.rows[0].total_count) : 0) / limit)
      }
    };
  }
}

module.exports = new ProductRepository();

Using ORMs (Object-Relational Mappers)

1. Sequelize (MySQL, PostgreSQL, SQLite, MSSQL)

Installation:

bash

npm install sequelize
# For specific database
npm install pg pg-hstore # PostgreSQL
npm install mysql2 # MySQL
npm install sqlite3 # SQLite

Basic Setup:

javascript

// config/database.js
const { Sequelize } = require('sequelize');
const env = process.env.NODE_ENV || 'development';

const configs = {
  development: {
    username: 'root',
    password: 'password',
    database: 'mydb_dev',
    host: 'localhost',
    dialect: 'mysql',
    logging: console.log,
    pool: {
      max: 5,
      min: 0,
      acquire: 30000,
      idle: 10000
    }
  },
  test: {
    username: 'root',
    password: 'password',
    database: 'mydb_test',
    host: 'localhost',
    dialect: 'mysql',
    logging: false
  },
  production: {
    username: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    host: process.env.DB_HOST,
    dialect: 'postgres',
    logging: false,
    dialectOptions: {
      ssl: {
        require: true,
        rejectUnauthorized: false
      }
    },
    pool: {
      max: 10,
      min: 2,
      acquire: 30000,
      idle: 10000
    }
  }
};

const config = configs[env];
const sequelize = new Sequelize(config.database, config.username, config.password, config);

// Test connection
async function testConnection() {
  try {
    await sequelize.authenticate();
    console.log('Database connection has been established successfully.');
    return true;
  } catch (error) {
    console.error('Unable to connect to the database:', error);
    return false;
  }
}

module.exports = { sequelize, Sequelize, testConnection };

Defining Models:

javascript

// models/User.js
const { DataTypes } = require('sequelize');
const { sequelize } = require('../config/database');

const User = sequelize.define('User', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  username: {
    type: DataTypes.STRING(50),
    allowNull: false,
    unique: true,
    validate: {
      len: [3, 50],
      isAlphanumeric: true
    }
  },
  email: {
    type: DataTypes.STRING(100),
    allowNull: false,
    unique: true,
    validate: {
      isEmail: true
    }
  },
  password: {
    type: DataTypes.STRING(255),
    allowNull: false
  },
  role: {
    type: DataTypes.ENUM('user', 'admin', 'moderator'),
    defaultValue: 'user'
  },
  isActive: {
    type: DataTypes.BOOLEAN,
    defaultValue: true
  },
  lastLogin: {
    type: DataTypes.DATE
  }
}, {
  tableName: 'users',
  timestamps: true, // Adds createdAt and updatedAt
  underscored: true, // Converts camelCase to snake_case
  hooks: {
    beforeCreate: (user) => {
      // Hash password before saving
      user.password = hashPassword(user.password);
    },
    beforeUpdate: (user) => {
      // Re-hash password if changed
      if (user.changed('password')) {
        user.password = hashPassword(user.password);
      }
    }
  }
});

// models/Post.js
const Post = sequelize.define('Post', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  title: {
    type: DataTypes.STRING(200),
    allowNull: false
  },
  content: {
    type: DataTypes.TEXT,
    allowNull: false
  },
  published: {
    type: DataTypes.BOOLEAN,
    defaultValue: false
  },
  views: {
    type: DataTypes.INTEGER,
    defaultValue: 0
  }
}, {
  tableName: 'posts',
  timestamps: true
});

// Define relationships
User.hasMany(Post, {
  foreignKey: 'authorId',
  as: 'posts',
  onDelete: 'CASCADE'
});

Post.belongsTo(User, {
  foreignKey: 'authorId',
  as: 'author'
});

module.exports = { User, Post };

// Helper function
function hashPassword(password) {
  // Implement hashing logic
  return password; // Replace with actual hashing
}

Sequelize CRUD Operations:

javascript

// services/userService.js
const { User, Post } = require('../models');
const { Op } = require('sequelize');

class UserService {
  // Create user
  async createUser(userData) {
    try {
      const user = await User.create(userData);
      return user;
    } catch (error) {
      if (error.name === 'SequelizeUniqueConstraintError') {
        throw new Error('Username or email already exists');
      }
      throw error;
    }
  }

  // Get user by ID with posts
  async getUserById(id, includePosts = false) {
    const options = {
      where: { id }
    };
    
    if (includePosts) {
      options.include = [{
        model: Post,
        as: 'posts',
        where: { published: true },
        required: false // LEFT JOIN
      }];
    }
    
    const user = await User.findOne(options);
    
    if (!user) {
      throw new Error('User not found');
    }
    
    return user;
  }

  // Get all users with pagination and filtering
  async getAllUsers(filters = {}, page = 1, limit = 10) {
    const where = {};
    
    // Apply filters
    if (filters.role) {
      where.role = filters.role;
    }
    
    if (filters.isActive !== undefined) {
      where.isActive = filters.isActive;
    }
    
    if (filters.search) {
      where[Op.or] = [
        { username: { [Op.like]: `%${filters.search}%` } },
        { email: { [Op.like]: `%${filters.search}%` } }
      ];
    }
    
    if (filters.createdAfter) {
      where.createdAt = { [Op.gte]: new Date(filters.createdAfter) };
    }
    
    const options = {
      where,
      limit,
      offset: (page - 1) * limit,
      order: [['createdAt', 'DESC']],
      attributes: { exclude: ['password'] } // Don't return password
    };
    
    const { count, rows } = await User.findAndCountAll(options);
    
    return {
      data: rows,
      pagination: {
        page,
        limit,
        total: count,
        pages: Math.ceil(count / limit)
      }
    };
  }

  // Update user
  async updateUser(id, updateData) {
    const user = await User.findByPk(id);
    
    if (!user) {
      throw new Error('User not found');
    }
    
    // Prevent updating sensitive fields
    const allowedFields = ['username', 'email', 'role', 'isActive'];
    const filteredUpdate = {};
    
    Object.keys(updateData).forEach(key => {
      if (allowedFields.includes(key)) {
        filteredUpdate[key] = updateData[key];
      }
    });
    
    await user.update(filteredUpdate);
    return user.reload();
  }

  // Delete user (soft delete)
  async deleteUser(id) {
    const user = await User.findByPk(id);
    
    if (!user) {
      throw new Error('User not found');
    }
    
    // Soft delete (set isActive to false)
    await user.update({ isActive: false });
    
    // OR hard delete
    // await user.destroy();
    
    return true;
  }

  // Complex query with transactions
  async createUserWithPosts(userData, postsData) {
    const transaction = await sequelize.transaction();
    
    try {
      // Create user
      const user = await User.create(userData, { transaction });
      
      // Create posts with userId
      const postsWithUserId = postsData.map(post => ({
        ...post,
        authorId: user.id
      }));
      
      await Post.bulkCreate(postsWithUserId, { transaction });
      
      // Commit transaction
      await transaction.commit();
      
      // Fetch user with posts
      const userWithPosts = await User.findByPk(user.id, {
        include: [{ model: Post, as: 'posts' }],
        transaction
      });
      
      return userWithPosts;
      
    } catch (error) {
      // Rollback transaction on error
      await transaction.rollback();
      throw error;
    }
  }

  // Raw SQL query when needed
  async getActiveUserStats() {
    const sql = `
      SELECT 
        COUNT(*) as total_users,
        AVG(TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE())) as avg_age,
        role,
        COUNT(CASE WHEN is_active = true THEN 1 END) as active_users
      FROM users
      GROUP BY role
      ORDER BY total_users DESC
    `;
    
    const [results] = await sequelize.query(sql);
    return results;
  }
}

module.exports = new UserService();

2. Prisma (Next-gen ORM)

Installation:

bash

npm install prisma @prisma/client
npx prisma init

Prisma Schema (schema.prisma):

prisma

// schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql" // or "mysql", "sqlite", "sqlserver"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  password  String
  role      Role     @default(USER)
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  @@map("users")
}

model Profile {
  id        Int      @id @default(autoincrement())
  bio       String?
  avatar    String?
  userId    Int      @unique
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  createdAt DateTime @default(now())
  
  @@map("profiles")
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  tags      Tag[]    @relation("PostTags")
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  @@map("posts")
  @@index([authorId])
}

model Tag {
  id        Int      @id @default(autoincrement())
  name      String   @unique
  posts     Post[]   @relation("PostTags")
  createdAt DateTime @default(now())
  
  @@map("tags")
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

Prisma Client Setup:

javascript

// lib/prisma.js
const { PrismaClient } = require('@prisma/client');

const prisma = new PrismaClient({
  log: [
    { emit: 'event', level: 'query' },
    { emit: 'event', level: 'error' },
    { emit: 'event', level: 'info' },
    { emit: 'event', level: 'warn' }
  ],
  errorFormat: 'pretty'
});

// Logging (optional)
prisma.$on('query', (e) => {
  console.log('Query: ' + e.query);
  console.log('Params: ' + e.params);
  console.log('Duration: ' + e.duration + 'ms');
});

// Connection handling
async function connectDB() {
  try {
    await prisma.$connect();
    console.log('Database connected via Prisma');
  } catch (error) {
    console.error('Database connection failed:', error);
    process.exit(1);
  }
}

// Disconnect on app shutdown
process.on('beforeExit', async () => {
  await prisma.$disconnect();
});

module.exports = { prisma, connectDB };

Prisma CRUD Operations:

javascript

// services/prismaUserService.js
const { prisma } = require('../lib/prisma');

class PrismaUserService {
  // Create user with transaction
  async createUser(userData) {
    return await prisma.$transaction(async (tx) => {
      // Check if user exists
      const existingUser = await tx.user.findUnique({
        where: { email: userData.email }
      });
      
      if (existingUser) {
        throw new Error('User already exists');
      }
      
      // Create user
      const user = await tx.user.create({
        data: {
          email: userData.email,
          name: userData.name,
          password: hashPassword(userData.password),
          role: userData.role || 'USER'
        }
      });
      
      // Create profile
      if (userData.bio || userData.avatar) {
        await tx.profile.create({
          data: {
            bio: userData.bio,
            avatar: userData.avatar,
            userId: user.id
          }
        });
      }
      
      // Return user with profile
      return await tx.user.findUnique({
        where: { id: user.id },
        include: { profile: true }
      });
    });
  }

  // Get user by ID with related data
  async getUserById(id) {
    const user = await prisma.user.findUnique({
      where: { id: parseInt(id) },
      include: {
        profile: true,
        posts: {
          where: { published: true },
          include: { tags: true },
          orderBy: { createdAt: 'desc' },
          take: 10
        },
        _count: {
          select: {
            posts: true
          }
        }
      }
    });
    
    if (!user) {
      throw new Error('User not found');
    }
    
    return user;
  }

  // Get all users with pagination, filtering, and sorting
  async getAllUsers(options = {}) {
    const {
      page = 1,
      limit = 10,
      search = '',
      role,
      sortBy = 'createdAt',
      sortOrder = 'desc'
    } = options;
    
    const skip = (page - 1) * limit;
    
    // Build where clause
    const where = {};
    
    if (role) {
      where.role = role;
    }
    
    if (search) {
      where.OR = [
        { name: { contains: search, mode: 'insensitive' } },
        { email: { contains: search, mode: 'insensitive' } }
      ];
    }
    
    // Execute query with pagination
    const [users, total] = await Promise.all([
      prisma.user.findMany({
        where,
        skip,
        take: limit,
        orderBy: { [sortBy]: sortOrder },
        select: {
          id: true,
          email: true,
          name: true,
          role: true,
          createdAt: true,
          updatedAt: true,
          profile: { select: { bio: true, avatar: true } },
          _count: { select: { posts: true } }
        }
      }),
      prisma.user.count({ where })
    ]);
    
    return {
      data: users,
      pagination: {
        page,
        limit,
        total,
        pages: Math.ceil(total / limit),
        hasNext: page * limit < total,
        hasPrev: page > 1
      }
    };
  }

  // Update user
  async updateUser(id, updateData) {
    // First, check if user exists
    const existingUser = await prisma.user.findUnique({
      where: { id: parseInt(id) }
    });
    
    if (!existingUser) {
      throw new Error('User not found');
    }
    
    // Prepare data for update
    const data = {};
    
    if (updateData.name !== undefined) {
      data.name = updateData.name;
    }
    
    if (updateData.email !== undefined) {
      data.email = updateData.email;
    }
    
    if (updateData.role !== undefined) {
      data.role = updateData.role;
    }
    
    if (updateData.password !== undefined) {
      data.password = hashPassword(updateData.password);
    }
    
    // Update profile if provided
    if (updateData.bio !== undefined || updateData.avatar !== undefined) {
      data.profile = {
        upsert: {
          create: {
            bio: updateData.bio || '',
            avatar: updateData.avatar || ''
          },
          update: {
            bio: updateData.bio,
            avatar: updateData.avatar
          }
        }
      };
    }
    
    const user = await prisma.user.update({
      where: { id: parseInt(id) },
      data,
      include: { profile: true }
    });
    
    return user;
  }

  // Delete user with cascade
  async deleteUser(id) {
    return await prisma.$transaction(async (tx) => {
      // Check if user exists
      const user = await tx.user.findUnique({
        where: { id: parseInt(id) },
        include: { posts: true }
      });
      
      if (!user) {
        throw new Error('User not found');
      }
      
      // Delete user (cascade will delete related posts and profile)
      await tx.user.delete({
        where: { id: parseInt(id) }
      });
      
      return { message: 'User deleted successfully' };
    });
  }

  // Advanced queries
  async getUsersWithPostCounts() {
    return await prisma.user.findMany({
      select: {
        id: true,
        name: true,
        email: true,
        _count: {
          select: {
            posts: {
              where: { published: true }
            }
          }
        }
      },
      orderBy: {
        posts: {
          _count: 'desc'
        }
      }
    });
  }

  // Raw SQL when needed
  async getAggregatedStats() {
    const result = await prisma.$queryRaw`
      SELECT 
        COUNT(*) as total_users,
        COUNT(CASE WHEN role = 'ADMIN' THEN 1 END) as admin_count,
        AVG(EXTRACT(YEAR FROM AGE(created_at))) as avg_account_age_years,
        DATE_TRUNC('month', created_at) as signup_month,
        COUNT(*) as monthly_signups
      FROM users
      GROUP BY DATE_TRUNC('month', created_at)
      ORDER BY signup_month DESC
    `;
    
    return result;
  }
}

module.exports = new PrismaUserService();

// Helper function
function hashPassword(password) {
  // Implement actual hashing
  return password;
}

Running Migrations:

bash

# Create migration
npx prisma migrate dev --name init

# Deploy to production
npx prisma migrate deploy

# Generate Prisma Client
npx prisma generate

# Open Prisma Studio (GUI)
npx prisma studio

NoSQL - MongoDB

1. MongoDB Fundamentals

MongoDB Core Concepts:

• Database: Container for collections

• Collection: Equivalent to table in SQL

• Document: Equivalent to row in SQL (BSON format)

• Field: Equivalent to column in SQL

• Embedded Documents: Nested documents within a document

• References: Linking documents between collections

MongoDB vs SQL Comparison:

javascript

// SQL Table Structure
users table:
| id | name  | email           | age |
|----|-------|-----------------|-----|
| 1  | John  | john@email.com  | 25  |
| 2  | Jane  | jane@email.com  | 30  |

// MongoDB Document Structure
users collection:
[
  {
    "_id": ObjectId("507f1f77bcf86cd799439011"),
    "name": "John",
    "email": "john@email.com",
    "age": 25,
    "address": {
      "street": "123 Main St",
      "city": "New York"
    },
    "tags": ["customer", "vip"]
  },
  {
    "_id": ObjectId("507f1f77bcf86cd799439012"),
    "name": "Jane",
    "email": "jane@email.com",
    "age": 30,
    "orders": [
      { "product": "Laptop", "price": 999 },
      { "product": "Mouse", "price": 25 }
    ]
  }
]

2. Mongoose (ODM for MongoDB)

Installation:

bash

npm install mongoose

Connection Setup:

javascript

// config/mongodb.js
const mongoose = require('mongoose');

const connectDB = async () => {
  try {
    const conn = await mongoose.connect(process.env.MONGODB_URI || 'mongodb://localhost:27017/mydatabase', {
      useNewUrlParser: true,
      useUnifiedTopology: true,
      serverSelectionTimeoutMS: 5000,
      socketTimeoutMS: 45000,
      family: 4, // Use IPv4, skip trying IPv6
      maxPoolSize: 10, // Maintain up to 10 socket connections
      minPoolSize: 5, // Maintain at least 5 socket connections
      maxIdleTimeMS: 10000 // Close sockets after 10 seconds of inactivity
    });
    
    console.log(`MongoDB Connected: ${conn.connection.host}`);
    
    // Connection events
    mongoose.connection.on('connected', () => {
      console.log('Mongoose connected to DB');
    });
    
    mongoose.connection.on('error', (err) => {
      console.error(`Mongoose connection error: ${err}`);
    });
    
    mongoose.connection.on('disconnected', () => {
      console.log('Mongoose disconnected');
    });
    
    // Graceful shutdown
    process.on('SIGINT', async () => {
      await mongoose.connection.close();
      console.log('Mongoose connection closed through app termination');
      process.exit(0);
    });
    
    return conn;
  } catch (error) {
    console.error(`Error: ${error.message}`);
    process.exit(1);
  }
};

// Helper to check connection state
function getConnectionState() {
  return {
    state: mongoose.connection.readyState,
    stateName: ['disconnected', 'connected', 'connecting', 'disconnecting'][mongoose.connection.readyState],
    host: mongoose.connection.host,
    port: mongoose.connection.port,
    name: mongoose.connection.name
  };
}

module.exports = { connectDB, mongoose, getConnectionState };

3. Mongoose Schemas & Models

Basic Schema Definition:

javascript

// models/User.js
const mongoose = require('mongoose');
const { Schema } = mongoose;
const validator = require('validator');
const bcrypt = require('bcryptjs');
const jwt = require('jsonwebtoken');

// Sub-schema for address
const addressSchema = new Schema({
  street: {
    type: String,
    trim: true,
    maxlength: [200, 'Street cannot be more than 200 characters']
  },
  city: {
    type: String,
    trim: true,
    required: [true, 'City is required']
  },
  state: {
    type: String,
    trim: true,
    uppercase: true,
    minlength: [2, 'State must be 2 characters'],
    maxlength: [2, 'State must be 2 characters']
  },
  zipCode: {
    type: String,
    match: [/^\d{5}(-\d{4})?$/, 'Please provide a valid zip code']
  },
  country: {
    type: String,
    default: 'USA',
    trim: true
  }
}, { _id: false }); // Don't create _id for sub-documents

// Main User Schema
const userSchema = new Schema({
  // Basic Information
  firstName: {
    type: String,
    required: [true, 'First name is required'],
    trim: true,
    minlength: [2, 'First name must be at least 2 characters'],
    maxlength: [50, 'First name cannot exceed 50 characters']
  },
  
  lastName: {
    type: String,
    required: [true, 'Last name is required'],
    trim: true,
    minlength: [2, 'Last name must be at least 2 characters'],
    maxlength: [50, 'Last name cannot exceed 50 characters']
  },
  
  email: {
    type: String,
    required: [true, 'Email is required'],
    unique: true,
    lowercase: true,
    trim: true,
    validate: {
      validator: validator.isEmail,
      message: 'Please provide a valid email'
    },
    index: true // Create index for faster queries
  },
  
  password: {
    type: String,
    required: [true, 'Password is required'],
    minlength: [8, 'Password must be at least 8 characters'],
    select: false // Don't return password in queries by default
  },
  
  // Profile Information
  age: {
    type: Number,
    min: [13, 'Age must be at least 13'],
    max: [120, 'Age must be less than 120']
  },
  
  gender: {
    type: String,
    enum: {
      values: ['male', 'female', 'other'],
      message: 'Gender must be male, female, or other'
    }
  },
  
  phone: {
    type: String,
    validate: {
      validator: function(v) {
        return /^\+?[\d\s-]{10,}$/.test(v);
      },
      message: 'Please provide a valid phone number'
    }
  },
  
  // Address (embedded document)
  address: addressSchema,
  
  // Array of strings
  interests: [{
    type: String,
    trim: true
  }],
  
  // Object for preferences
  preferences: {
    newsletter: {
      type: Boolean,
      default: true
    },
    notifications: {
      email: { type: Boolean, default: true },
      sms: { type: Boolean, default: false }
    },
    theme: {
      type: String,
      enum: ['light', 'dark', 'auto'],
      default: 'light'
    }
  },
  
  // Status and roles
  isActive: {
    type: Boolean,
    default: true
  },
  
  role: {
    type: String,
    enum: ['user', 'admin', 'moderator'],
    default: 'user'
  },
  
  // Authentication
  emailVerified: {
    type: Boolean,
    default: false
  },
  
  verificationToken: String,
  verificationTokenExpires: Date,
  
  passwordResetToken: String,
  passwordResetExpires: Date,
  
  // Timestamps
  lastLogin: Date,
  loginCount: {
    type: Number,
    default: 0
  }
  
}, {
  timestamps: true, // Adds createdAt and updatedAt
  toJSON: { virtuals: true }, // Include virtuals when converting to JSON
  toObject: { virtuals: true }
});

// Virtual field (not stored in DB)
userSchema.virtual('fullName').get(function() {
  return `${this.firstName} ${this.lastName}`;
});

userSchema.virtual('ageGroup').get(function() {
  if (!this.age) return 'unknown';
  if (this.age < 18) return 'minor';
  if (this.age < 30) return 'young adult';
  if (this.age < 50) return 'adult';
  return 'senior';
});

// Indexes (compound index example)
userSchema.index({ firstName: 1, lastName: 1 });
userSchema.index({ 'address.city': 1, 'address.state': 1 });
userSchema.index({ createdAt: -1 }); // For sorting by recent

// Middleware (hooks)
userSchema.pre('save', async function(next) {
  // Only hash password if it's modified
  if (!this.isModified('password')) return next();
  
  try {
    const salt = await bcrypt.genSalt(10);
    this.password = await bcrypt.hash(this.password, salt);
    next();
  } catch (error) {
    next(error);
  }
});

userSchema.pre('save', function(next) {
  // Update full name if first or last name changed
  if (this.isModified('firstName') || this.isModified('lastName')) {
    this.fullName = `${this.firstName} ${this.lastName}`;
  }
  next();
});

userSchema.post('save', function(doc, next) {
  console.log(`User ${doc.email} was saved`);
  next();
});

// Instance methods
userSchema.methods.comparePassword = async function(candidatePassword) {
  return await bcrypt.compare(candidatePassword, this.password);
};

userSchema.methods.generateAuthToken = function() {
  return jwt.sign(
    { userId: this._id, email: this.email, role: this.role },
    process.env.JWT_SECRET,
    { expiresIn: '7d' }
  );
};

userSchema.methods.toJSON = function() {
  const user = this.toObject();
  delete user.password;
  delete user.__v;
  delete user.verificationToken;
  delete user.passwordResetToken;
  return user;
};

// Static methods
userSchema.statics.findByEmail = function(email) {
  return this.findOne({ email }).select('+password'); // Include password
};

userSchema.statics.getActiveUsers = function() {
  return this.find({ isActive: true }).sort({ createdAt: -1 });
};

// Query helpers
userSchema.query.byName = function(name) {
  return this.where({ 
    $or: [
      { firstName: new RegExp(name, 'i') },
      { lastName: new RegExp(name, 'i') }
    ]
  });
};

// Create and export model
const User = mongoose.model('User', userSchema);
module.exports = User;

Product Schema with Relationships:

javascript

// models/Product.js
const mongoose = require('mongoose');
const { Schema } = mongoose;

const reviewSchema = new Schema({
  user: {
    type: Schema.Types.ObjectId,
    ref: 'User',
    required: true
  },
  rating: {
    type: Number,
    required: true,
    min: 1,
    max: 5
  },
  comment: {
    type: String,
    trim: true,
    maxlength: [500, 'Comment cannot exceed 500 characters']
  },
  helpful: {
    type: Number,
    default: 0
  }
}, { timestamps: true });

const productSchema = new Schema({
  name: {
    type: String,
    required: [true, 'Product name is required'],
    trim: true,
    maxlength: [200, 'Product name cannot exceed 200 characters'],
    index: true
  },
  
  slug: {
    type: String,
    required: true,
    unique: true,
    lowercase: true,
    index: true
  },
  
  description: {
    type: String,
    required: [true, 'Description is required'],
    trim: true
  },
  
  price: {
    type: Number,
    required: [true, 'Price is required'],
    min: [0, 'Price must be positive'],
    set: v => parseFloat(v.toFixed(2)) // Store with 2 decimal places
  },
  
  comparePrice: {
    type: Number,
    min: 0
  },
  
  cost: {
    type: Number,
    min: 0
  },
  
  sku: {
    type: String,
    unique: true,
    uppercase: true,
    sparse: true // Allow null values for uniqueness
  },
  
  barcode: String,
  
  quantity: {
    type: Number,
    required: true,
    min: 0,
    default: 0
  },
  
  category: {
    type: Schema.Types.ObjectId,
    ref: 'Category',
    required: true,
    index: true
  },
  
  brand: {
    type: String,
    trim: true,
    index: true
  },
  
  tags: [{
    type: String,
    trim: true,
    lowercase: true
  }],
  
  images: [{
    url: String,
    alt: String,
    isPrimary: { type: Boolean, default: false }
  }],
  
  attributes: [{
    name: String,
    value: String
  }],
  
  specifications: {
    type: Map,
    of: String
  },
  
  reviews: [reviewSchema],
  
  averageRating: {
    type: Number,
    min: 0,
    max: 5,
    default: 0
  },
  
  reviewCount: {
    type: Number,
    default: 0
  },
  
  isActive: {
    type: Boolean,
    default: true
  },
  
  isFeatured: {
    type: Boolean,
    default: false
  },
  
  isDigital: {
    type: Boolean,
    default: false
  },
  
  weight: Number, // in grams
  dimensions: {
    length: Number,
    width: Number,
    height: Number
  },
  
  metaTitle: String,
  metaDescription: String,
  metaKeywords: [String],
  
  createdBy: {
    type: Schema.Types.ObjectId,
    ref: 'User'
  },
  
  updatedBy: {
    type: Schema.Types.ObjectId,
    ref: 'User'
  }
  
}, {
  timestamps: true,
  toJSON: { virtuals: true },
  toObject: { virtuals: true }
});

// Virtuals
productSchema.virtual('inStock').get(function() {
  return this.quantity > 0;
});

productSchema.virtual('discountPercentage').get(function() {
  if (!this.comparePrice || this.comparePrice <= this.price) return 0;
  return Math.round(((this.comparePrice - this.price) / this.comparePrice) * 100);
});

// Indexes
productSchema.index({ name: 'text', description: 'text', tags: 'text' });
productSchema.index({ price: 1 });
productSchema.index({ category: 1, brand: 1 });
productSchema.index({ averageRating: -1 });

// Middleware to update average rating
productSchema.pre('save', function(next) {
  if (this.reviews && this.reviews.length > 0) {
    const total = this.reviews.reduce((sum, review) => sum + review.rating, 0);
    this.averageRating = parseFloat((total / this.reviews.length).toFixed(1));
    this.reviewCount = this.reviews.length;
  }
  next();
});

// Static methods
productSchema.statics.findByCategory = function(categoryId) {
  return this.find({ category: categoryId, isActive: true });
};

productSchema.statics.search = function(query) {
  return this.find({
    $text: { $search: query },
    isActive: true
  });
};

// Instance methods
productSchema.methods.addReview = function(reviewData) {
  this.reviews.push(reviewData);
  return this.save();
};

productSchema.methods.updateStock = function(quantity) {
  this.quantity += quantity;
  if (this.quantity < 0) {
    throw new Error('Insufficient stock');
  }
  return this.save();
};

const Product = mongoose.model('Product', productSchema);
module.exports = Product;

4. CRUD with MongoDB/Mongoose

Complete CRUD Operations:

javascript

// services/productService.js
const Product = require('../models/Product');
const Category = require('../models/Category');

class ProductService {
  // CREATE
  async createProduct(productData, userId) {
    try {
      // Generate slug from name
      const slug = productData.name
        .toLowerCase()
        .replace(/[^\w\s]/g, '')
        .replace(/\s+/g, '-');
      
      // Check if slug exists
      const existingProduct = await Product.findOne({ slug });
      if (existingProduct) {
        throw new Error('Product with similar name already exists');
      }
      
      // Check category exists
      const category = await Category.findById(productData.category);
      if (!category) {
        throw new Error('Category not found');
      }
      
      // Create product
      const product = await Product.create({
        ...productData,
        slug,
        createdBy: userId,
        updatedBy: userId
      });
      
      // Update category product count
      await Category.findByIdAndUpdate(category._id, {
        $inc: { productCount: 1 }
      });
      
      return product;
      
    } catch (error) {
      throw error;
    }
  }

  // READ - Get single product with related data
  async getProductById(id, populate = true) {
    const query = Product.findById(id);
    
    if (populate) {
      query.populate('category', 'name slug')
           .populate('createdBy', 'firstName lastName email')
           .populate('reviews.user', 'firstName lastName');
    }
    
    const product = await query;
    
    if (!product) {
      throw new Error('Product not found');
    }
    
    return product;
  }

  // READ - Get product by slug
  async getProductBySlug(slug) {
    const product = await Product.findOne({ slug })
      .populate('category', 'name slug description')
      .populate('reviews.user', 'firstName lastName')
      .populate('createdBy', 'firstName lastName');
    
    if (!product) {
      throw new Error('Product not found');
    }
    
    return product;
  }

  // READ - Get all products with advanced filtering
  async getAllProducts(filters = {}, options = {}) {
    const {
      page = 1,
      limit = 12,
      sort = 'createdAt',
      order = 'desc',
      search = '',
      select = ''
    } = options;
    
    // Build query
    let query = {};
    
    // Text search
    if (search) {
      query.$text = { $search: search };
    }
    
    // Filter by category
    if (filters.category) {
      query.category = filters.category;
    }
    
    // Filter by brand
    if (filters.brand) {
      query.brand = filters.brand;
    }
    
    // Filter by tags
    if (filters.tags && filters.tags.length > 0) {
      query.tags = { $in: filters.tags };
    }
    
    // Price range
    if (filters.minPrice || filters.maxPrice) {
      query.price = {};
      if (filters.minPrice) query.price.$gte = parseFloat(filters.minPrice);
      if (filters.maxPrice) query.price.$lte = parseFloat(filters.maxPrice);
    }
    
    // In stock filter
    if (filters.inStock === 'true') {
      query.quantity = { $gt: 0 };
    } else if (filters.inStock === 'false') {
      query.quantity = { $lte: 0 };
    }
    
    // Featured products
    if (filters.featured === 'true') {
      query.isFeatured = true;
    }
    
    // Active products only
    query.isActive = filters.isActive !== 'false';
    
    // Build sort
    const sortOptions = {};
    if (sort === 'price') {
      sortOptions.price = order === 'desc' ? -1 : 1;
    } else if (sort === 'rating') {
      sortOptions.averageRating = order === 'desc' ? -1 : 1;
    } else if (sort === 'name') {
      sortOptions.name = order === 'desc' ? -1 : 1;
    } else {
      sortOptions[sort] = order === 'desc' ? -1 : 1;
    }
    
    // Execute query with pagination
    const [products, total] = await Promise.all([
      Product.find(query)
        .select(select)
        .populate('category', 'name slug')
        .sort(sortOptions)
        .skip((page - 1) * limit)
        .limit(limit)
        .lean(), // Returns plain JavaScript objects
      Product.countDocuments(query)
    ]);
    
    // Calculate pagination info
    const pages = Math.ceil(total / limit);
    const hasNext = page < pages;
    const hasPrev = page > 1;
    
    return {
      products,
      pagination: {
        page,
        limit,
        total,
        pages,
        hasNext,
        hasPrev,
        nextPage: hasNext ? page + 1 : null,
        prevPage: hasPrev ? page - 1 : null
      }
    };
  }

  // READ - Advanced aggregations
  async getProductStats() {
    const stats = await Product.aggregate([
      {
        $match: { isActive: true }
      },
      {
        $group: {
          _id: '$category',
          totalProducts: { $sum: 1 },
          averagePrice: { $avg: '$price' },
          totalStock: { $sum: '$quantity' },
          totalValue: { $sum: { $multiply: ['$price', '$quantity'] } },
          minPrice: { $min: '$price' },
          maxPrice: { $max: '$price' }
        }
      },
      {
        $lookup: {
          from: 'categories',
          localField: '_id',
          foreignField: '_id',
          as: 'categoryInfo'
        }
      },
      {
        $unwind: '$categoryInfo'
      },
      {
        $project: {
          category: '$categoryInfo.name',
          totalProducts: 1,
          averagePrice: { $round: ['$averagePrice', 2] },
          totalStock: 1,
          totalValue: { $round: ['$totalValue', 2] },
          priceRange: {
            min: '$minPrice',
            max: '$maxPrice'
          }
        }
      },
      {
        $sort: { totalProducts: -1 }
      }
    ]);
    
    return stats;
  }

  // UPDATE - Update product
  async updateProduct(id, updateData, userId) {
    const product = await Product.findById(id);
    
    if (!product) {
      throw new Error('Product not found');
    }
    
    // If name is being updated, generate new slug
    if (updateData.name && updateData.name !== product.name) {
      const slug = updateData.name
        .toLowerCase()
        .replace(/[^\w\s]/g, '')
        .replace(/\s+/g, '-');
      
      // Check if new slug exists for different product
      const existingSlug = await Product.findOne({
        slug,
        _id: { $ne: id }
      });
      
      if (existingSlug) {
        throw new Error('Product with similar name already exists');
      }
      
      updateData.slug = slug;
    }
    
    // Update product
    Object.keys(updateData).forEach(key => {
      if (updateData[key] !== undefined) {
        product[key] = updateData[key];
      }
    });
    
    product.updatedBy = userId;
    product.updatedAt = new Date();
    
    await product.save();
    return product;
  }

  // UPDATE - Partial update (patch)
  async partialUpdateProduct(id, updateData, userId) {
    const allowedUpdates = [
      'name', 'description', 'price', 'quantity', 'images',
      'isActive', 'isFeatured', 'tags', 'attributes'
    ];
    
    const updates = Object.keys(updateData);
    const isValidOperation = updates.every(update => 
      allowedUpdates.includes(update)
    );
    
    if (!isValidOperation) {
      throw new Error('Invalid updates');
    }
    
    const product = await Product.findByIdAndUpdate(
      id,
      { 
        ...updateData,
        updatedBy: userId,
        updatedAt: new Date()
      },
      { new: true, runValidators: true }
    );
    
    if (!product) {
      throw new Error('Product not found');
    }
    
    return product;
  }

  // UPDATE - Add review
  async addReview(productId, reviewData, userId) {
    const product = await Product.findById(productId);
    
    if (!product) {
      throw new Error('Product not found');
    }
    
    // Check if user already reviewed
    const alreadyReviewed = product.reviews.find(
      review => review.user.toString() === userId.toString()
    );
    
    if (alreadyReviewed) {
      throw new Error('Product already reviewed by this user');
    }
    
    const review = {
      user: userId,
      rating: reviewData.rating,
      comment: reviewData.comment
    };
    
    product.reviews.push(review);
    await product.save();
    
    return product;
  }

  // DELETE - Soft delete (mark as inactive)
  async softDeleteProduct(id, userId) {
    const product = await Product.findByIdAndUpdate(
      id,
      {
        isActive: false,
        updatedBy: userId,
        deletedAt: new Date()
      },
      { new: true }
    );
    
    if (!product) {
      throw new Error('Product not found');
    }
    
    // Decrement category product count
    await Category.findByIdAndUpdate(product.category, {
      $inc: { productCount: -1 }
    });
    
    return product;
  }

  // DELETE - Hard delete
  async hardDeleteProduct(id) {
    const product = await Product.findById(id);
    
    if (!product) {
      throw new Error('Product not found');
    }
    
    // Check if product has orders
    const hasOrders = await Order.exists({ 'items.product': id });
    if (hasOrders) {
      throw new Error('Cannot delete product with existing orders');
    }
    
    // Decrement category product count
    await Category.findByIdAndUpdate(product.category, {
      $inc: { productCount: -1 }
    });
    
    await product.deleteOne();
    return { message: 'Product deleted successfully' };
  }

  // BULK Operations
  async bulkUpdateStock(updates) {
    const session = await Product.startSession();
    session.startTransaction();
    
    try {
      const operations = updates.map(update => ({
        updateOne: {
          filter: { _id: update.productId },
          update: { $inc: { quantity: update.quantity } }
        }
      }));
      
      await Product.bulkWrite(operations, { session });
      
      await session.commitTransaction();
      session.endSession();
      
      return { success: true, message: 'Stock updated successfully' };
      
    } catch (error) {
      await session.abortTransaction();
      session.endSession();
      throw error;
    }
  }

  // SEARCH with autocomplete
  async searchProductsAutocomplete(query, limit = 10) {
    return await Product.aggregate([
      {
        $match: {
          isActive: true,
          $or: [
            { name: { $regex: query, $options: 'i' } },
            { description: { $regex: query, $options: 'i' } },
            { tags: { $regex: query, $options: 'i' } }
          ]
        }
      },
      {
        $project: {
          name: 1,
          slug: 1,
          price: 1,
          images: 1,
          category: 1,
          score: {
            $add: [
              { $cond: [{ $regexMatch: { input: '$name', regex: query, options: 'i' } }, 10, 0] },
              { $cond: [{ $regexMatch: { input: '$tags', regex: query, options: 'i' } }, 5, 0] },
              { $cond: [{ $regexMatch: { input: '$description', regex: query, options: 'i' } }, 1, 0] }
            ]
          }
        }
      },
      {
        $sort: { score: -1, name: 1 }
      },
      {
        $limit: limit
      },
      {
        $lookup: {
          from: 'categories',
          localField: 'category',
          foreignField: '_id',
          as: 'category'
        }
      },
      {
        $unwind: '$category'
      }
    ]);
  }
}

module.exports = new ProductService();

Express Routes using MongoDB:

javascript

// routes/productRoutes.js
const express = require('express');
const router = express.Router();
const productService = require('../services/productService');
const auth = require('../middleware/auth');
const validate = require('../middleware/validate');
const { productValidation } = require('../validations');

// GET /api/products - Get all products
router.get('/', validate(productValidation.getProducts), async (req, res) => {
  try {
    const result = await productService.getAllProducts(req.query, {
      page: parseInt(req.query.page) || 1,
      limit: parseInt(req.query.limit) || 12,
      sort: req.query.sort,
      order: req.query.order,
      search: req.query.search
    });
    
    res.json({
      success: true,
      data: result.products,
      pagination: result.pagination
    });
    
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
});

// GET /api/products/:id - Get single product
router.get('/:id', async (req, res) => {
  try {
    const product = await productService.getProductById(req.params.id);
    res.json({
      success: true,
      data: product
    });
  } catch (error) {
    res.status(404).json({
      success: false,
      error: error.message
    });
  }
});

// GET /api/products/slug/:slug - Get product by slug
router.get('/slug/:slug', async (req, res) => {
  try {
    const product = await productService.getProductBySlug(req.params.slug);
    res.json({
      success: true,
      data: product
    });
  } catch (error) {
    res.status(404).json({
      success: false,
      error: error.message
    });
  }
});

// POST /api/products - Create product (Admin only)
router.post('/', auth('admin'), validate(productValidation.createProduct), async (req, res) => {
  try {
    const product = await productService.createProduct(req.body, req.user.id);
    res.status(201).json({
      success: true,
      data: product
    });
  } catch (error) {
    res.status(400).json({
      success: false,
      error: error.message
    });
  }
});

// PUT /api/products/:id - Update product (Admin only)
router.put('/:id', auth('admin'), validate(productValidation.updateProduct), async (req, res) => {
  try {
    const product = await productService.updateProduct(
      req.params.id,
      req.body,
      req.user.id
    );
    res.json({
      success: true,
      data: product
    });
  } catch (error) {
    res.status(400).json({
      success: false,
      error: error.message
    });
  }
});

// PATCH /api/products/:id - Partial update
router.patch('/:id', auth('admin'), async (req, res) => {
  try {
    const product = await productService.partialUpdateProduct(
      req.params.id,
      req.body,
      req.user.id
    );
    res.json({
      success: true,
      data: product
    });
  } catch (error) {
    res.status(400).json({
      success: false,
      error: error.message
    });
  }
});

// POST /api/products/:id/reviews - Add review
router.post('/:id/reviews', auth(), validate(productValidation.addReview), async (req, res) => {
  try {
    const product = await productService.addReview(
      req.params.id,
      req.body,
      req.user.id
    );
    res.status(201).json({
      success: true,
      data: product
    });
  } catch (error) {
    res.status(400).json({
      success: false,
      error: error.message
    });
  }
});

// DELETE /api/products/:id - Delete product (Admin only)
router.delete('/:id', auth('admin'), async (req, res) => {
  try {
    await productService.softDeleteProduct(req.params.id, req.user.id);
    res.json({
      success: true,
      message: 'Product deleted successfully'
    });
  } catch (error) {
    res.status(400).json({
      success: false,
      error: error.message
    });
  }
});

// GET /api/products/stats - Get product statistics (Admin only)
router.get('/stats/summary', auth('admin'), async (req, res) => {
  try {
    const stats = await productService.getProductStats();
    res.json({
      success: true,
      data: stats
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
});

// GET /api/products/search/autocomplete - Autocomplete search
router.get('/search/autocomplete', async (req, res) => {
  try {
    const query = req.query.q || '';
    const limit = parseInt(req.query.limit) || 10;
    
    const results = await productService.searchProductsAutocomplete(query, limit);
    
    res.json({
      success: true,
      data: results
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
});

module.exports = router;

This extensive guide covers database integration in Node.js for both SQL (MySQL, PostgreSQL with Sequelize/Prisma) and NoSQL (MongoDB with Mongoose) databases, including connection setup, schema design, CRUD operations, and advanced querying techniques.