Database Testing

The Importance of Database Testing

Database testing is vital because most application failures occur due to data-related issues rather than code bugs. Think of your database as the foundation of your application - if it's unstable, everything built on top of it becomes vulnerable.

Key Benefits of Database Testing:

• Data Integrity: Ensures your data follows business rules and constraints
• Performance: Identifies slow queries and optimization opportunities
• Schema Validation: Verifies your database structure supports all requirements
• Security: Tests for injection vulnerabilities and access controls
• Migration Safety: Confirms schema migrations work correctly

Types of Database Tests

Unit Tests for Database Operations

Test individual functions or methods that interact with the database.

• Test CRUD operations for a single entity
• Verify validation logic
• Test error handling for database operations

Integration Tests for Data Access Layers

Test how your data access layer works with the actual database.

• Test repositories or data access objects
• Verify transactions and rollbacks
• Test relationships between entities

Schema Migration Tests

Verify that database schema changes can be applied and rolled back safely.

• Test upgrading from one version to another
• Test downgrading (rollbacks)
• Verify data preservation during migrations

Data Integrity Tests

Ensure data meets business rules and database constraints.

• Test unique constraints
• Test referential integrity
• Test custom business rules

Query Performance Tests

Measure and optimize query execution time.

• Test query execution plans
• Identify missing indexes
• Test with realistic data volumes

Test Database Setup Options

Choosing the right test database setup is crucial for effective testing. Each approach has trade-offs between realism, speed, and complexity.

In-Memory Database Setup with MongoDB Memory Server

// test/setup.js
const { MongoMemoryServer } = require('mongodb-memory-server');
const mongoose = require('mongoose');

let mongoServer;

// Setup before all tests
beforeAll(async () => {
  mongoServer = await MongoMemoryServer.create();
  const mongoUri = mongoServer.getUri();
  
  await mongoose.connect(mongoUri, {
    useNewUrlParser: true,
    useUnifiedTopology: true
  });
});

// Clean up after all tests
afterAll(async () => {
  await mongoose.disconnect();
  await mongoServer.stop();
});

// Clear database between tests
beforeEach(async () => {
  const collections = mongoose.connection.collections;
  
  for (const key in collections) {
    const collection = collections[key];
    await collection.deleteMany({});
  }
});

Docker Container Database Setup

// docker-compose.test.yml
version: '3.8'

services:
  postgres-test:
    image: postgres:13
    environment:
      POSTGRES_USER: testuser
      POSTGRES_PASSWORD: testpass
      POSTGRES_DB: testdb
    ports:
      - "5433:5432"
    volumes:
      - ./init-test-db.sql:/docker-entrypoint-initdb.d/init.sql
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U testuser -d testdb"]
      interval: 5s
      timeout: 5s
      retries: 5

// test/setup.js with Docker container
const { Client } = require('pg');
const { execSync } = require('child_process');

let client;

// Setup before all tests
beforeAll(async () => {
  // Start Docker container
  execSync('docker-compose -f docker-compose.test.yml up -d');
  
  // Wait for the database to be ready
  let retries = 5;
  while (retries > 0) {
    try {
      client = new Client({
        host: 'localhost',
        port: 5433,
        user: 'testuser',
        password: 'testpass',
        database: 'testdb'
      });
      
      await client.connect();
      console.log('Connected to test database');
      break;
    } catch (err) {
      console.log(`Connection attempt failed, retrying... (${retries} attempts left)`);
      retries--;
      await new Promise(resolve => setTimeout(resolve, 2000));
    }
  }
  
  if (retries === 0) {
    throw new Error('Could not connect to test database');
  }
});

// Clean up after all tests
afterAll(async () => {
  if (client) {
    await client.end();
  }
  
  // Stop Docker container
  execSync('docker-compose -f docker-compose.test.yml down');
});

// Reset database before each test
beforeEach(async () => {
  await client.query('TRUNCATE users, posts, comments RESTART IDENTITY CASCADE');
});

Testing ORMs and Data Access Layers

Object-Relational Mapping (ORM) libraries abstract database interactions, but it's still crucial to test them thoroughly.

Testing Mongoose (MongoDB ORM)

// models/user.js
const mongoose = require('mongoose');
const bcrypt = require('bcrypt');

const userSchema = new mongoose.Schema({
  username: {
    type: String,
    required: true,
    unique: true,
    trim: true,
    minlength: 3,
    maxlength: 20
  },
  email: {
    type: String,
    required: true,
    unique: true,
    trim: true,
    lowercase: true,
    match: [/^\S+@\S+\.\S+$/, 'Please enter a valid email address']
  },
  password: {
    type: String,
    required: true,
    minlength: 8
  },
  role: {
    type: String,
    enum: ['user', 'admin'],
    default: 'user'
  },
  createdAt: {
    type: Date,
    default: Date.now
  }
});

// Pre-save hook to hash password
userSchema.pre('save', async function(next) {
  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);
  }
});

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

// Static method to find user by email
userSchema.statics.findByEmail = function(email) {
  return this.findOne({ email });
};

const User = mongoose.model('User', userSchema);

module.exports = User;

// test/models/user.test.js
const mongoose = require('mongoose');
const User = require('../../models/user');
require('../setup'); // Database setup

describe('User Model', () => {
  describe('Schema Validation', () => {
    test('validates required fields', async () => {
      const userWithoutRequired = new User({});
      
      let error;
      try {
        await userWithoutRequired.validate();
      } catch (e) {
        error = e;
      }
      
      expect(error).toBeDefined();
      expect(error.errors.username).toBeDefined();
      expect(error.errors.email).toBeDefined();
      expect(error.errors.password).toBeDefined();
    });
    
    test('validates username length', async () => {
      const userWithShortUsername = new User({
        username: 'ab', // Too short
        email: 'test@example.com',
        password: 'password123'
      });
      
      let error;
      try {
        await userWithShortUsername.validate();
      } catch (e) {
        error = e;
      }
      
      expect(error).toBeDefined();
      expect(error.errors.username).toBeDefined();
      expect(error.errors.username.message).toContain('shorter than the minimum allowed length');
    });
    
    test('validates email format', async () => {
      const userWithInvalidEmail = new User({
        username: 'testuser',
        email: 'invalid-email', // Invalid format
        password: 'password123'
      });
      
      let error;
      try {
        await userWithInvalidEmail.validate();
      } catch (e) {
        error = e;
      }
      
      expect(error).toBeDefined();
      expect(error.errors.email).toBeDefined();
      expect(error.errors.email.message).toContain('valid email');
    });
  });
  
  describe('Middleware and Methods', () => {
    test('hashes password before saving', async () => {
      const userData = {
        username: 'testuser',
        email: 'test@example.com',
        password: 'password123'
      };
      
      const user = new User(userData);
      await user.save();
      
      // Password should be hashed
      expect(user.password).not.toBe(userData.password);
      expect(user.password).toHaveLength(60); // bcrypt hash length
    });
    
    test('comparePassword correctly validates passwords', async () => {
      const user = new User({
        username: 'testuser',
        email: 'test@example.com',
        password: 'password123'
      });
      
      await user.save();
      
      // Correct password
      const isMatch = await user.comparePassword('password123');
      expect(isMatch).toBe(true);
      
      // Incorrect password
      const isNotMatch = await user.comparePassword('wrongpassword');
      expect(isNotMatch).toBe(false);
    });
    
    test('findByEmail static method finds user', async () => {
      // Create test user
      await User.create({
        username: 'emailtest',
        email: 'find@example.com',
        password: 'password123'
      });
      
      const user = await User.findByEmail('find@example.com');
      
      expect(user).toBeDefined();
      expect(user.username).toBe('emailtest');
    });
  });
  
  describe('CRUD Operations', () => {
    test('creates and saves a user successfully', async () => {
      const userData = {
        username: 'newuser',
        email: 'new@example.com',
        password: 'newpassword',
        role: 'admin'
      };
      
      const user = await User.create(userData);
      
      expect(user._id).toBeDefined();
      expect(user.username).toBe(userData.username);
      expect(user.email).toBe(userData.email);
      expect(user.role).toBe(userData.role);
      expect(user.createdAt).toBeDefined();
      
      // Verify user was saved to database
      const foundUser = await User.findById(user._id);
      expect(foundUser).toBeDefined();
      expect(foundUser.username).toBe(userData.username);
    });
    
    test('enforces unique constraints', async () => {
      // Create initial user
      await User.create({
        username: 'uniqueuser',
        email: 'unique@example.com',
        password: 'password123'
      });
      
      // Try to create duplicate
      const duplicateUser = {
        username: 'another',
        email: 'unique@example.com', // Same email
        password: 'password123'
      };
      
      await expect(User.create(duplicateUser)).rejects.toThrow();
    });
    
    test('updates user properties', async () => {
      // Create user
      const user = await User.create({
        username: 'updatetest',
        email: 'update@example.com',
        password: 'password123'
      });
      
      // Update user
      user.username = 'updatedname';
      await user.save();
      
      // Verify update
      const updatedUser = await User.findById(user._id);
      expect(updatedUser.username).toBe('updatedname');
    });
    
    test('deletes a user', async () => {
      // Create user
      const user = await User.create({
        username: 'deletetest',
        email: 'delete@example.com',
        password: 'password123'
      });
      
      // Delete user
      await User.deleteOne({ _id: user._id });
      
      // Verify deletion
      const deletedUser = await User.findById(user._id);
      expect(deletedUser).toBeNull();
    });
  });
});

Testing Sequelize (SQL ORM)

// models/user.js with Sequelize
const { DataTypes, Model } = require('sequelize');
const bcrypt = require('bcrypt');
const sequelize = require('../config/database');

class User extends Model {
  static async findByEmail(email) {
    return await User.findOne({ where: { email } });
  }
  
  async comparePassword(candidatePassword) {
    return bcrypt.compare(candidatePassword, this.password);
  }
}

User.init({
  username: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,
    validate: {
      len: [3, 20]
    }
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,
    validate: {
      isEmail: true
    }
  },
  password: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: {
      len: [8, 100]
    }
  },
  role: {
    type: DataTypes.ENUM('user', 'admin'),
    defaultValue: 'user'
  }
}, {
  sequelize,
  modelName: 'User',
  hooks: {
    beforeCreate: async (user) => {
      if (user.password) {
        const salt = await bcrypt.genSalt(10);
        user.password = await bcrypt.hash(user.password, salt);
      }
    },
    beforeUpdate: async (user) => {
      if (user.changed('password')) {
        const salt = await bcrypt.genSalt(10);
        user.password = await bcrypt.hash(user.password, salt);
      }
    }
  }
});

module.exports = User;

// test/models/user.test.js with Sequelize
const { sequelize, User } = require('../../models');

describe('User Model', () => {
  beforeAll(async () => {
    // Sync database - force:true will drop tables before recreating them
    await sequelize.sync({ force: true });
  });
  
  afterAll(async () => {
    await sequelize.close();
  });
  
  beforeEach(async () => {
    // Clear users table
    await User.destroy({ where: {}, truncate: true });
  });
  
  describe('Validation', () => {
    test('requires username, email, and password', async () => {
      await expect(User.create({})).rejects.toThrow();
    });
    
    test('enforces username length', async () => {
      await expect(User.create({
        username: 'ab', // Too short
        email: 'test@example.com',
        password: 'password123'
      })).rejects.toThrow();
    });
    
    test('validates email format', async () => {
      await expect(User.create({
        username: 'testuser',
        email: 'invalid-email', // Invalid format
        password: 'password123'
      })).rejects.toThrow();
    });
  });
  
  describe('Hooks and Methods', () => {
    test('hashes password for new users', async () => {
      const userData = {
        username: 'testuser',
        email: 'test@example.com',
        password: 'password123'
      };
      
      const user = await User.create(userData);
      
      // Password should be hashed
      expect(user.password).not.toBe(userData.password);
      expect(user.password.startsWith('$2b$')).toBe(true); // bcrypt hash prefix
    });
    
    test('hashes password when updated', async () => {
      // Create user
      const user = await User.create({
        username: 'updatepass',
        email: 'updatepass@example.com',
        password: 'password123'
      });
      
      const originalHash = user.password;
      
      // Update password
      user.password = 'newpassword123';
      await user.save();
      
      // Password should be hashed differently
      expect(user.password).not.toBe(originalHash);
      expect(user.password.startsWith('$2b$')).toBe(true);
    });
    
    test('comparePassword correctly validates passwords', async () => {
      const user = await User.create({
        username: 'comparetest',
        email: 'compare@example.com',
        password: 'password123'
      });
      
      // Correct password
      const isMatch = await user.comparePassword('password123');
      expect(isMatch).toBe(true);
      
      // Incorrect password
      const isNotMatch = await user.comparePassword('wrongpassword');
      expect(isNotMatch).toBe(false);
    });
    
    test('findByEmail static method finds user', async () => {
      // Create test user
      await User.create({
        username: 'emailtest',
        email: 'find@example.com',
        password: 'password123'
      });
      
      const user = await User.findByEmail('find@example.com');
      
      expect(user).toBeDefined();
      expect(user.username).toBe('emailtest');
    });
  });
  
  describe('CRUD Operations', () => {
    test('creates a user successfully', async () => {
      const userData = {
        username: 'newuser',
        email: 'new@example.com',
        password: 'newpassword',
        role: 'admin'
      };
      
      const user = await User.create(userData);
      
      expect(user.id).toBeDefined();
      expect(user.username).toBe(userData.username);
      expect(user.email).toBe(userData.email);
      expect(user.role).toBe(userData.role);
      expect(user.createdAt).toBeDefined();
      
      // Verify user was saved to database
      const foundUser = await User.findByPk(user.id);
      expect(foundUser).toBeDefined();
      expect(foundUser.username).toBe(userData.username);
    });
    
    test('enforces unique constraints', async () => {
      // Create initial user
      await User.create({
        username: 'uniqueuser',
        email: 'unique@example.com',
        password: 'password123'
      });
      
      // Try to create duplicate
      const duplicateUser = {
        username: 'another',
        email: 'unique@example.com', // Same email
        password: 'password123'
      };
      
      await expect(User.create(duplicateUser)).rejects.toThrow();
    });
    
    test('updates user properties', async () => {
      // Create user
      const user = await User.create({
        username: 'updatetest',
        email: 'update@example.com',
        password: 'password123'
      });
      
      // Update user
      await user.update({ username: 'updatedname' });
      
      // Verify update
      const updatedUser = await User.findByPk(user.id);
      expect(updatedUser.username).toBe('updatedname');
    });
    
    test('deletes a user', async () => {
      // Create user
      const user = await User.create({
        username: 'deletetest',
        email: 'delete@example.com',
        password: 'password123'
      });
      
      // Delete user
      await user.destroy();
      
      // Verify deletion
      const deletedUser = await User.findByPk(user.id);
      expect(deletedUser).toBeNull();
    });
  });
});

Testing Repository Pattern

The Repository Pattern provides an abstraction layer between your business logic and data access layer, making your application more maintainable and testable.

User Repository Implementation

// repositories/userRepository.js
class UserRepository {
  constructor(userModel) {
    this.userModel = userModel;
  }
  
  async findAll() {
    return await this.userModel.find().select('-password');
  }
  
  async findById(id) {
    return await this.userModel.findById(id).select('-password');
  }
  
  async findByEmail(email) {
    return await this.userModel.findOne({ email });
  }
  
  async create(userData) {
    const user = new this.userModel(userData);
    await user.save();
    
    // Return user without password
    const userObject = user.toObject();
    delete userObject.password;
    return userObject;
  }
  
  async update(id, userData) {
    // Don't allow role updates through this method for security
    if (userData.role) {
      delete userData.role;
    }
    
    const user = await this.userModel.findByIdAndUpdate(
      id,
      { $set: userData },
      { new: true, runValidators: true }
    ).select('-password');
    
    return user;
  }
  
  async delete(id) {
    return await this.userModel.findByIdAndDelete(id);
  }
  
  async changeRole(id, role) {
    if (!['user', 'admin'].includes(role)) {
      throw new Error('Invalid role');
    }
    
    return await this.userModel.findByIdAndUpdate(
      id,
      { role },
      { new: true, runValidators: true }
    ).select('-password');
  }
  
  async checkPassword(id, password) {
    const user = await this.userModel.findById(id);
    if (!user) return false;
    
    return await user.comparePassword(password);
  }
}

module.exports = UserRepository;

// test/repositories/userRepository.test.js
const mongoose = require('mongoose');
const User = require('../../models/user');
const UserRepository = require('../../repositories/userRepository');
require('../setup'); // Database setup

describe('UserRepository', () => {
  let userRepository;
  
  beforeEach(async () => {
    userRepository = new UserRepository(User);
  });
  
  describe('findAll', () => {
    test('returns all users without passwords', async () => {
      // Create test users
      await User.create([
        {
          username: 'user1',
          email: 'user1@example.com',
          password: 'password123'
        },
        {
          username: 'user2',
          email: 'user2@example.com',
          password: 'password123'
        }
      ]);
      
      const users = await userRepository.findAll();
      
      expect(users).toHaveLength(2);
      expect(users[0].username).toBeDefined();
      expect(users[0].password).toBeUndefined();
    });
    
    test('returns empty array when no users exist', async () => {
      const users = await userRepository.findAll();
      expect(users).toEqual([]);
    });
  });
  
  describe('findById', () => {
    test('returns user by id without password', async () => {
      const createdUser = await User.create({
        username: 'findbyid',
        email: 'findbyid@example.com',
        password: 'password123'
      });
      
      const user = await userRepository.findById(createdUser._id);
      
      expect(user).toBeDefined();
      expect(user.username).toBe('findbyid');
      expect(user.password).toBeUndefined();
    });
    
    test('returns null for non-existent id', async () => {
      const nonExistentId = new mongoose.Types.ObjectId();
      const user = await userRepository.findById(nonExistentId);
      
      expect(user).toBeNull();
    });
  });
  
  describe('findByEmail', () => {
    test('returns user by email with password', async () => {
      // Since this method is often used for authentication, it includes the password
      const userData = {
        username: 'findbyemail',
        email: 'findbyemail@example.com',
        password: 'password123'
      };
      
      await User.create(userData);
      
      const user = await userRepository.findByEmail(userData.email);
      
      expect(user).toBeDefined();
      expect(user.username).toBe(userData.username);
      expect(user.password).toBeDefined(); // Password should be included
    });
    
    test('returns null for non-existent email', async () => {
      const user = await userRepository.findByEmail('nonexistent@example.com');
      expect(user).toBeNull();
    });
  });
  
  describe('create', () => {
    test('creates a user and returns it without password', async () => {
      const userData = {
        username: 'newuser',
        email: 'newuser@example.com',
        password: 'password123'
      };
      
      const createdUser = await userRepository.create(userData);
      
      expect(createdUser).toBeDefined();
      expect(createdUser.username).toBe(userData.username);
      expect(createdUser.email).toBe(userData.email);
      expect(createdUser.password).toBeUndefined();
      
      // Verify user was saved with hashed password
      const savedUser = await User.findOne({ email: userData.email });
      expect(savedUser).toBeDefined();
      expect(savedUser.password).toBeDefined();
      expect(savedUser.password).not.toBe(userData.password);
    });
    
    test('throws error for invalid data', async () => {
      const invalidData = {
        username: 'in',  // Too short
        email: 'invalid-email',
        password: 'short'
      };
      
      await expect(userRepository.create(invalidData)).rejects.toThrow();
    });
  });
  
  describe('update', () => {
    let userId;
    
    beforeEach(async () => {
      const user = await User.create({
        username: 'updatetest',
        email: 'update@example.com',
        password: 'password123',
        role: 'user'
      });
      
      userId = user._id;
    });
    
    test('updates user properties', async () => {
      const updateData = {
        username: 'updatedname',
        email: 'updated@example.com'
      };
      
      const updatedUser = await userRepository.update(userId, updateData);
      
      expect(updatedUser).toBeDefined();
      expect(updatedUser.username).toBe(updateData.username);
      expect(updatedUser.email).toBe(updateData.email);
      
      // Verify in database
      const dbUser = await User.findById(userId);
      expect(dbUser.username).toBe(updateData.username);
    });
    
    test('ignores role updates for security', async () => {
      const updateData = {
        username: 'hackerattempt',
        role: 'admin' // This should be ignored
      };
      
      const updatedUser = await userRepository.update(userId, updateData);
      
      expect(updatedUser.username).toBe(updateData.username);
      expect(updatedUser.role).toBe('user'); // Should remain unchanged
      
      // Verify in database
      const dbUser = await User.findById(userId);
      expect(dbUser.role).toBe('user');
    });
    
    test('returns null for non-existent user', async () => {
      const nonExistentId = new mongoose.Types.ObjectId();
      const updatedUser = await userRepository.update(nonExistentId, { username: 'test' });
      
      expect(updatedUser).toBeNull();
    });
  });
  
  describe('delete', () => {
    test('deletes a user', async () => {
      const user = await User.create({
        username: 'deletetest',
        email: 'delete@example.com',
        password: 'password123'
      });
      
      const deletedUser = await userRepository.delete(user._id);
      
      expect(deletedUser).toBeDefined();
      expect(deletedUser._id).toEqual(user._id);
      
      // Verify deletion in database
      const dbUser = await User.findById(user._id);
      expect(dbUser).toBeNull();
    });
    
    test('returns null for non-existent user', async () => {
      const nonExistentId = new mongoose.Types.ObjectId();
      const deletedUser = await userRepository.delete(nonExistentId);
      
      expect(deletedUser).toBeNull();
    });
  });
  
  describe('changeRole', () => {
    let userId;
    
    beforeEach(async () => {
      const user = await User.create({
        username: 'roletest',
        email: 'role@example.com',
        password: 'password123',
        role: 'user'
      });
      
      userId = user._id;
    });
    
    test('changes user role', async () => {
      const updatedUser = await userRepository.changeRole(userId, 'admin');
      
      expect(updatedUser).toBeDefined();
      expect(updatedUser.role).toBe('admin');
      
      // Verify in database
      const dbUser = await User.findById(userId);
      expect(dbUser.role).toBe('admin');
    });
    
    test('throws error for invalid role', async () => {
      await expect(userRepository.changeRole(userId, 'superuser')).rejects.toThrow('Invalid role');
      
      // Role should remain unchanged
      const dbUser = await User.findById(userId);
      expect(dbUser.role).toBe('user');
    });
  });
  
  describe('checkPassword', () => {
    let userId;
    
    beforeEach(async () => {
      const user = await User.create({
        username: 'passwordtest',
        email: 'password@example.com',
        password: 'password123'
      });
      
      userId = user._id;
    });
    
    test('returns true for correct password', async () => {
      const result = await userRepository.checkPassword(userId, 'password123');
      expect(result).toBe(true);
    });
    
    test('returns false for incorrect password', async () => {
      const result = await userRepository.checkPassword(userId, 'wrongpassword');
      expect(result).toBe(false);
    });
    
    test('returns false for non-existent user', async () => {
      const nonExistentId = new mongoose.Types.ObjectId();
      const result = await userRepository.checkPassword(nonExistentId, 'password123');
      
      expect(result).toBe(false);
    });
  });
});

Testing Transactions and Concurrency

Database transactions ensure that multiple operations are performed atomically - either all succeed or all fail together.

Transaction Support in Repositories

// repositories/orderRepository.js
class OrderRepository {
  constructor(db) {
    this.db = db;
  }
  
  async createOrder(userId, items, shippingAddress, paymentDetails, options = {}) {
    const session = options.session || null;
    
    try {
      // Calculate total
      let total = 0;
      for (const item of items) {
        const product = await this.db.Product.findById(item.productId, null, { session });
        if (!product) {
          throw new Error(`Product not found: ${item.productId}`);
        }
        
        if (product.inventory < item.quantity) {
          throw new Error(`Insufficient inventory for product: ${product.name}`);
        }
        
        total += product.price * item.quantity;
      }
      
      // Create order
      const order = await this.db.Order.create([{
        user: userId,
        items: items.map(item => ({
          product: item.productId,
          quantity: item.quantity,
          price: 0 // Will be filled in next step
        })),
        total,
        shippingAddress,
        paymentDetails,
        status: 'pending'
      }], { session });
      
      // Update product prices in order and reduce inventory
      for (const orderItem of order[0].items) {
        const product = await this.db.Product.findById(orderItem.product, null, { session });
        orderItem.price = product.price;
        
        // Reduce inventory
        product.inventory -= orderItem.quantity;
        await product.save({ session });
      }
      
      await order[0].save({ session });
      
      return order[0];
    } catch (error) {
      throw error;
    }
  }
  
  async processPayment(orderId, transactionDetails, options = {}) {
    const session = options.session || null;
    
    const order = await this.db.Order.findById(orderId, null, { session });
    if (!order) {
      throw new Error('Order not found');
    }
    
    if (order.status !== 'pending') {
      throw new Error(`Order already processed: ${order.status}`);
    }
    
    // Update order status
    order.status = 'paid';
    order.paymentDetails.transactionId = transactionDetails.transactionId;
    order.paymentDetails.processingDate = new Date();
    
    await order.save({ session });
    
    return order;
  }
}

module.exports = OrderRepository;

// test/repositories/orderRepository.test.js
const mongoose = require('mongoose');
const { Order, Product, User } = require('../../models');
const OrderRepository = require('../../repositories/orderRepository');
require('../setup'); // Database setup

describe('OrderRepository - Transactions', () => {
  let orderRepository, user, products;
  
  beforeEach(async () => {
    // Create sample DB facade
    const db = { Order, Product, User };
    orderRepository = new OrderRepository(db);
    
    // Create test user
    user = await User.create({
      username: 'ordertest',
      email: 'order@example.com',
      password: 'password123'
    });
    
    // Create test products
    products = await Product.create([
      {
        name: 'Product 1',
        description: 'Test product 1',
        price: 10.99,
        inventory: 5
      },
      {
        name: 'Product 2',
        description: 'Test product 2',
        price: 24.99,
        inventory: 10
      },
      {
        name: 'Product 3',
        description: 'Test product 3',
        price: 5.99,
        inventory: 0 // Out of stock
      }
    ]);
  });
  
  describe('createOrder with transaction', () => {
    test('successfully creates order and updates inventory', async () => {
      const session = await mongoose.startSession();
      
      try {
        await session.withTransaction(async () => {
          const orderItems = [
            { productId: products[0]._id, quantity: 2 },
            { productId: products[1]._id, quantity: 1 }
          ];
          
          const shippingAddress = {
            street: '123 Test St',
            city: 'Test City',
            state: 'TS',
            zipCode: '12345',
            country: 'Test Country'
          };
          
          const paymentDetails = {
            method: 'credit_card',
            cardLast4: '4242',
            expiryMonth: 12,
            expiryYear: 2030
          };
          
          const order = await orderRepository.createOrder(
            user._id, 
            orderItems, 
            shippingAddress, 
            paymentDetails,
            { session }
          );
          
          // Verify order was created correctly
          expect(order).toBeDefined();
          expect(order.user).toEqual(user._id);
          expect(order.items).toHaveLength(2);
          expect(order.total).toBeCloseTo(10.99 * 2 + 24.99);
          
          // Verify inventory was reduced
          const updatedProduct1 = await Product.findById(products[0]._id).session(session);
          expect(updatedProduct1.inventory).toBe(3); // 5 - 2
          
          const updatedProduct2 = await Product.findById(products[1]._id).session(session);
          expect(updatedProduct2.inventory).toBe(9); // 10 - 1
        });
      } finally {
        session.endSession();
      }
      
      // Verify changes persisted after transaction
      const product1 = await Product.findById(products[0]._id);
      expect(product1.inventory).toBe(3);
      
      const orders = await Order.find({ user: user._id });
      expect(orders).toHaveLength(1);
    });
    
    test('rolls back changes if transaction fails', async () => {
      const session = await mongoose.startSession();
      
      try {
        await session.withTransaction(async () => {
          const orderItems = [
            { productId: products[0]._id, quantity: 2 },
            { productId: products[2]._id, quantity: 1 } // Out of stock product
          ];
          
          const shippingAddress = {
            street: '123 Test St',
            city: 'Test City'
          };
          
          const paymentDetails = {
            method: 'credit_card',
            cardLast4: '4242'
          };
          
          // This should throw an error
          await expect(orderRepository.createOrder(
            user._id, 
            orderItems, 
            shippingAddress, 
            paymentDetails,
            { session }
          )).rejects.toThrow('Insufficient inventory');
          
          // Transaction should be aborted automatically
        });
      } catch (error) {
        // Expected error
      } finally {
        session.endSession();
      }
      
      // Verify no changes were made
      const product1 = await Product.findById(products[0]._id);
      expect(product1.inventory).toBe(5); // Unchanged
      
      const orders = await Order.find({ user: user._id });
      expect(orders).toHaveLength(0); // No order created
    });
  });
  
  describe('Payment processing with transaction', () => {
    let order;
    
    beforeEach(async () => {
      // Create a pending order
      const orderItems = [
        { productId: products[0]._id, quantity: 1 }
      ];
      
      const shippingAddress = {
        street: '123 Test St',
        city: 'Test City'
      };
      
      const paymentDetails = {
        method: 'credit_card',
        cardLast4: '4242'
      };
      
      order = await orderRepository.createOrder(
        user._id, 
        orderItems, 
        shippingAddress, 
        paymentDetails
      );
    });
    
    test('successfully processes payment', async () => {
      const session = await mongoose.startSession();
      
      try {
        await session.withTransaction(async () => {
          const transactionDetails = {
            transactionId: 'test-transaction-123',
            amount: order.total
          };
          
          const updatedOrder = await orderRepository.processPayment(
            order._id,
            transactionDetails,
            { session }
          );
          
          // Verify order was updated
          expect(updatedOrder.status).toBe('paid');
          expect(updatedOrder.paymentDetails.transactionId).toBe(transactionDetails.transactionId);
        });
      } finally {
        session.endSession();
      }
      
      // Verify changes persisted
      const dbOrder = await Order.findById(order._id);
      expect(dbOrder.status).toBe('paid');
    });
    
    test('prevents double payment', async () => {
      // Process payment first time
      await orderRepository.processPayment(
        order._id,
        { transactionId: 'first-transaction' }
      );
      
      // Try to process again
      const session = await mongoose.startSession();
      
      try {
        await session.withTransaction(async () => {
          await expect(orderRepository.processPayment(
            order._id,
            { transactionId: 'second-transaction' },
            { session }
          )).rejects.toThrow('Order already processed');
        });
      } catch (error) {
        // Expected error
      } finally {
        session.endSession();
      }
      
      // Verify original transaction is still there
      const dbOrder = await Order.findById(order._id);
      expect(dbOrder.paymentDetails.transactionId).toBe('first-transaction');
    });
  });
});

Testing Concurrency Issues

// test/repositories/concurrency.test.js
const mongoose = require('mongoose');
const { Product } = require('../../models');
const ProductRepository = require('../../repositories/productRepository');
require('../setup'); // Database setup

describe('Concurrency Tests', () => {
  let productRepository, product;
  
  beforeEach(async () => {
    productRepository = new ProductRepository(Product);
    
    // Create test product with limited inventory
    product = await Product.create({
      name: 'Limited Product',
      description: 'Only 5 available',
      price: 99.99,
      inventory: 5
    });
  });
  
  test('handles concurrent inventory updates correctly', async () => {
    // Create 10 concurrent update operations
    const updateOperations = [];
    for (let i = 0; i < 10; i++) {
      updateOperations.push(
        Product.findOneAndUpdate(
          { 
            _id: product._id,
            inventory: { $gt: 0 } // Only update if inventory > 0
          },
          { $inc: { inventory: -1 } }, // Decrease inventory by 1
          { new: true }
        )
      );
    }
    
    // Execute all operations concurrently
    const results = await Promise.all(
      updateOperations.map(op => op.catch(e => e))
    );
    
    // Count successful updates
    const successfulUpdates = results.filter(
      result => !(result instanceof Error) && result
    );
    
    // Should have exactly 5 successful updates (initial inventory = 5)
    expect(successfulUpdates.length).toBe(5);
    
    // Verify final inventory is 0
    const updatedProduct = await Product.findById(product._id);
    expect(updatedProduct.inventory).toBe(0);
  });
  
  test('handles concurrent product creation with unique constraints', async () => {
    // Try to create 5 products with the same SKU concurrently
    const createOperations = [];
    for (let i = 0; i < 5; i++) {
      createOperations.push(
        Product.create({
          name: `Test Product ${i}`,
          description: `Description ${i}`,
          price: 10 + i,
          sku: 'DUPLICATE-SKU' // Same SKU for all products
        })
      );
    }
    
    // Execute all operations concurrently
    const results = await Promise.allSettled(createOperations);
    
    // Count successful creations (should be 1)
    const fulfilled = results.filter(r => r.status === 'fulfilled');
    expect(fulfilled.length).toBe(1);
    
    // Count failures (should be 4)
    const rejected = results.filter(r => r.status === 'rejected');
    expect(rejected.length).toBe(4);
    
    // All rejections should be due to duplicate key error
    for (const result of rejected) {
      expect(result.reason.code).toBe(11000); // MongoDB duplicate key error code
    }
  });
  
  test('implements optimistic concurrency control', async () => {
    // Set up optimistic concurrency with version field
    const blogPostSchema = new mongoose.Schema({
      title: String,
      content: String,
      author: String,
      version: { type: Number, default: 0 }
    });
    
    // Pre-save hook to implement optimistic locking
    blogPostSchema.pre('findOneAndUpdate', function() {
      const update = this.getUpdate();
      if (update.$set) {
        update.$set.version = update.$set.version + 1;
      }
    });
    
    const BlogPost = mongoose.model('BlogPost', blogPostSchema);
    
    // Create initial blog post
    const post = await BlogPost.create({
      title: 'Original Title',
      content: 'Original content',
      author: 'Test Author'
    });
    
    // Simulate two users editing the same post concurrently
    
    // User 1 gets the post
    const user1Post = await BlogPost.findById(post._id);
    
    // User 2 gets the post
    const user2Post = await BlogPost.findById(post._id);
    
    // User 1 updates and saves
    user1Post.title = 'User 1 Updated Title';
    await user1Post.save();
    
    // User 2 tries to update and save
    user2Post.title = 'User 2 Updated Title';
    
    // Should fail because version has changed
    await expect(async () => {
      await BlogPost.findOneAndUpdate(
        { _id: post._id, version: user2Post.version },
        { $set: { title: user2Post.title, version: user2Post.version } },
        { new: true }
      );
    }).rejects.toThrow();
    
    // Verify post has User 1's update
    const updatedPost = await BlogPost.findById(post._id);
    expect(updatedPost.title).toBe('User 1 Updated Title');
    expect(updatedPost.version).toBe(1);
  });
});

Testing Database Migrations

Database migrations change your database schema over time. Testing migrations ensures they apply correctly and preserve data.

Migration Script Example

// migrations/20230501_add_user_profile.js
module.exports = {
  async up(db) {
    // Create a new collection for user profiles
    await db.createCollection('profiles');
    
    // Get all existing users
    const users = await db.collection('users').find({}).toArray();
    
    // Create profile for each user
    const profiles = users.map(user => ({
      userId: user._id,
      bio: '',
      avatar: null,
      socialLinks: {
        twitter: '',
        github: '',
        linkedin: ''
      },
      createdAt: new Date()
    }));
    
    if (profiles.length > 0) {
      await db.collection('profiles').insertMany(profiles);
    }
    
    // Add index for fast lookups
    await db.collection('profiles').createIndex({ userId: 1 }, { unique: true });
  },
  
  async down(db) {
    // Drop the profiles collection
    await db.collection('profiles').drop();
  }
};

// test/migrations/20230501_add_user_profile.test.js
const { MongoClient } = require('mongodb');
const { MongoMemoryServer } = require('mongodb-memory-server');
const migration = require('../../migrations/20230501_add_user_profile');

describe('Migration: Add User Profile', () => {
  let mongoServer, client, db;
  
  beforeAll(async () => {
    // Set up a MongoDB memory server
    mongoServer = await MongoMemoryServer.create();
    const mongoUri = mongoServer.getUri();
    
    client = new MongoClient(mongoUri);
    await client.connect();
    db = client.db('test-db');
  });
  
  afterAll(async () => {
    if (client) {
      await client.close();
    }
    if (mongoServer) {
      await mongoServer.stop();
    }
  });
  
  beforeEach(async () => {
    // Clear database
    const collections = await db.listCollections().toArray();
    for (const collection of collections) {
      await db.collection(collection.name).deleteMany({});
    }
    
    // Create users collection and add sample users
    await db.createCollection('users');
    await db.collection('users').insertMany([
      {
        username: 'user1',
        email: 'user1@example.com',
        password: 'password123'
      },
      {
        username: 'user2',
        email: 'user2@example.com',
        password: 'password123'
      }
    ]);
  });
  
  test('up migration creates profiles collection and adds profiles for existing users', async () => {
    // Get users before migration
    const users = await db.collection('users').find({}).toArray();
    expect(users.length).toBe(2);
    
    // Run the migration
    await migration.up(db);
    
    // Check that profiles collection exists
    const collections = await db.listCollections().toArray();
    const profileCollection = collections.find(c => c.name === 'profiles');
    expect(profileCollection).toBeDefined();
    
    // Check that profiles were created for each user
    const profiles = await db.collection('profiles').find({}).toArray();
    expect(profiles.length).toBe(2);
    
    // Check profile properties
    for (const profile of profiles) {
      expect(profile.userId).toBeDefined();
      expect(profile.bio).toBe('');
      expect(profile.avatar).toBeNull();
      expect(profile.socialLinks).toEqual({
        twitter: '',
        github: '',
        linkedin: ''
      });
    }
    
    // Check that the index was created
    const indexes = await db.collection('profiles').indexes();
    const userIdIndex = indexes.find(i => i.key && i.key.userId === 1);
    expect(userIdIndex).toBeDefined();
    expect(userIdIndex.unique).toBe(true);
  });
  
  test('down migration removes profiles collection', async () => {
    // Run the up migration first
    await migration.up(db);
    
    // Verify profiles collection exists
    let collections = await db.listCollections().toArray();
    expect(collections.some(c => c.name === 'profiles')).toBe(true);
    
    // Run the down migration
    await migration.down(db);
    
    // Verify profiles collection no longer exists
    collections = await db.listCollections().toArray();
    expect(collections.some(c => c.name === 'profiles')).toBe(false);
  });
  
  test('migration works with empty users collection', async () => {
    // Clear users collection
    await db.collection('users').deleteMany({});
    
    // Run the migration
    await migration.up(db);
    
    // Check that profiles collection exists but is empty
    const profiles = await db.collection('profiles').find({}).toArray();
    expect(profiles.length).toBe(0);
  });
});

Testing Database Schema Evolution

// test/migrations/schema-evolution.test.js
const { MongoClient } = require('mongodb');
const { MongoMemoryServer } = require('mongodb-memory-server');
const migrations = require('../../migrations');

describe('Schema Evolution', () => {
  let mongoServer, client, db;
  
  beforeAll(async () => {
    // Set up a MongoDB memory server
    mongoServer = await MongoMemoryServer.create();
    const mongoUri = mongoServer.getUri();
    
    client = new MongoClient(mongoUri);
    await client.connect();
    db = client.db('test-db');
  });
  
  afterAll(async () => {
    if (client) {
      await client.close();
    }
    if (mongoServer) {
      await mongoServer.stop();
    }
  });
  
  beforeEach(async () => {
    // Clear database
    const collections = await db.listCollections().toArray();
    for (const collection of collections) {
      await db.collection(collection.name).drop().catch(() => {});
    }
  });
  
  test('applies all migrations in order', async () => {
    // Initial state: empty database
    
    // Apply all migrations
    for (const migration of migrations) {
      await migration.up(db);
    }
    
    // Verify final schema
    const collections = await db.listCollections().toArray();
    const collectionNames = collections.map(c => c.name);
    
    // Check expected collections exist
    expect(collectionNames).toContain('users');
    expect(collectionNames).toContain('products');
    expect(collectionNames).toContain('orders');
    expect(collectionNames).toContain('profiles');
    
    // Check expected indexes exist
    const userIndexes = await db.collection('users').indexes();
    expect(userIndexes.some(i => i.key && i.key.email === 1 && i.unique)).toBe(true);
    
    const profileIndexes = await db.collection('profiles').indexes();
    expect(profileIndexes.some(i => i.key && i.key.userId === 1 && i.unique)).toBe(true);
  });
  
  test('migrations can be reversed in reverse order', async () => {
    // Apply all migrations first
    for (const migration of migrations) {
      await migration.up(db);
    }
    
    // Verify database has expected collections
    let collections = await db.listCollections().toArray();
    let collectionNames = collections.map(c => c.name);
    expect(collectionNames.length).toBeGreaterThan(0);
    
    // Reverse all migrations in reverse order
    for (let i = migrations.length - 1; i >= 0; i--) {
      await migrations[i].down(db);
    }
    
    // Verify database is back to initial state
    collections = await db.listCollections().toArray();
    expect(collections.length).toBe(0);
  });
  
  test('migrations preserve existing data', async () => {
    // Create initial users

    // Create initial users collection with data before migrations
    await db.createCollection('users');
    await db.collection('users').insertMany([
      {
        username: 'testuser',
        email: 'test@example.com',
        password: 'hashedpassword123',
        createdAt: new Date()
      }
    ]);
    
    // Apply first migration that adds role field with default 'user'
    await migrations[0].up(db);
    
    // Verify user data was preserved and role field was added
    let users = await db.collection('users').find({}).toArray();
    expect(users.length).toBe(1);
    expect(users[0].username).toBe('testuser');
    expect(users[0].role).toBe('user'); // Default role added
    
    // Apply second migration that creates profiles collection
    await migrations[1].up(db);
    
    // Verify user data still exists
    users = await db.collection('users').find({}).toArray();
    expect(users.length).toBe(1);
    expect(users[0].username).toBe('testuser');
    
    // Verify profile was created for existing user
    const profiles = await db.collection('profiles').find({}).toArray();
    expect(profiles.length).toBe(1);
    expect(profiles[0].userId).toEqual(users[0]._id);
  });
});

Testing Query Performance

Performance testing ensures your database queries are efficient and scale well with larger datasets.

Setting Up Performance Tests

// test/performance/query-perf.test.js
const mongoose = require('mongoose');
const { MongoMemoryServer } = require('mongodb-memory-server');
const { User, Product, Order } = require('../../models');
const UserRepository = require('../../repositories/userRepository');
const ProductRepository = require('../../repositories/productRepository');
const OrderRepository = require('../../repositories/orderRepository');
const { generateTestData } = require('../utils/testDataGenerator');

describe('Query Performance', () => {
  let mongoServer, userRepository, productRepository, orderRepository;
  
  beforeAll(async () => {
    // Set up MongoDB memory server
    mongoServer = await MongoMemoryServer.create();
    const mongoUri = mongoServer.getUri();
    
    await mongoose.connect(mongoUri, {
      useNewUrlParser: true,
      useUnifiedTopology: true
    });
    
    // Initialize repositories
    userRepository = new UserRepository(User);
    productRepository = new ProductRepository(Product);
    orderRepository = new OrderRepository({ Order, Product, User });
    
    // Generate test data
    await generateTestData({
      userCount: 1000,
      productCount: 5000,
      orderCount: 10000
    });
    
    // Ensure indexes are created
    await User.createIndexes();
    await Product.createIndexes();
    await Order.createIndexes();
  }, 60000); // Longer timeout for data generation
  
  afterAll(async () => {
    await mongoose.disconnect();
    await mongoServer.stop();
  });
  
  describe('User Queries', () => {
    test('findByEmail performance', async () => {
      const startTime = Date.now();
      
      // Perform query
      const user = await userRepository.findByEmail('user500@example.com');
      
      const endTime = Date.now();
      const duration = endTime - startTime;
      
      expect(user).toBeDefined();
      expect(user.username).toBe('user500');
      expect(duration).toBeLessThan(50); // Should be fast with index
    });
    
    test('findAll with pagination performance', async () => {
      const startTime = Date.now();
      
      // Perform paginated query
      const users = await userRepository.findAll({ page: 5, limit: 20 });
      
      const endTime = Date.now();
      const duration = endTime - startTime;
      
      expect(users.data.length).toBe(20);
      expect(duration).toBeLessThan(100);
    });
    
    test('search by username performance', async () => {
      const startTime = Date.now();
      
      // Perform search query
      const users = await userRepository.search('user50');
      
      const endTime = Date.now();
      const duration = endTime - startTime;
      
      expect(users.length).toBeGreaterThan(0);
      expect(duration).toBeLessThan(100);
    });
  });
  
  describe('Product Queries', () => {
    test('findByCategory with sorting performance', async () => {
      const startTime = Date.now();
      
      // Perform query
      const products = await productRepository.findByCategory('electronics', {
        sort: { price: 1 },
        page: 1,
        limit: 20
      });
      
      const endTime = Date.now();
      const duration = endTime - startTime;
      
      expect(products.data.length).toBeLessThanOrEqual(20);
      expect(duration).toBeLessThan(100);
    });
    
    test('findFeatured with pagination performance', async () => {
      const startTime = Date.now();
      
      // Perform query
      const products = await productRepository.findFeatured({
        page: 2,
        limit: 10
      });
      
      const endTime = Date.now();
      const duration = endTime - startTime;
      
      expect(duration).toBeLessThan(100);
    });
    
    test('search with filters performance', async () => {
      const startTime = Date.now();
      
      // Perform complex search query
      const products = await productRepository.search({
        query: 'product',
        minPrice: 20,
        maxPrice: 100,
        categories: ['electronics', 'accessories'],
        page: 1,
        limit: 20
      });
      
      const endTime = Date.now();
      const duration = endTime - startTime;
      
      expect(duration).toBeLessThan(200); // More complex query allowed more time
    });
  });
  
  describe('Order Queries', () => {
    test('findByUser with status filtering performance', async () => {
      // Find a user with multiple orders first
      const orderCounts = await Order.aggregate([
        { $group: { _id: '$user', count: { $sum: 1 } } },
        { $sort: { count: -1 } },
        { $limit: 1 }
      ]);
      
      const userId = orderCounts[0]._id;
      
      const startTime = Date.now();
      
      // Perform query
      const orders = await orderRepository.findByUser(userId, {
        status: 'completed',
        page: 1,
        limit: 10
      });
      
      const endTime = Date.now();
      const duration = endTime - startTime;
      
      expect(duration).toBeLessThan(100);
    });
    
    test('getOrderWithDetails performance', async () => {
      // Find a random order
      const randomOrder = await Order.findOne();
      
      const startTime = Date.now();
      
      // Perform query that joins with products and user
      const orderDetails = await orderRepository.getOrderWithDetails(randomOrder._id);
      
      const endTime = Date.now();
      const duration = endTime - startTime;
      
      expect(orderDetails).toBeDefined();
      expect(orderDetails.items).toBeDefined();
      expect(orderDetails.user).toBeDefined();
      expect(duration).toBeLessThan(200); // Complex join operation allowed more time
    });
    
    test('findOrdersByDateRange performance', async () => {
      const startDate = new Date('2023-01-01');
      const endDate = new Date('2023-06-30');
      
      const startTime = Date.now();
      
      // Perform query with date range
      const orders = await orderRepository.findByDateRange(startDate, endDate, {
        page: 1,
        limit: 20
      });
      
      const endTime = Date.now();
      const duration = endTime - startTime;
      
      expect(duration).toBeLessThan(150);
    });
  });
  
  describe('Complex Aggregation Queries', () => {
    test('sales by category performance', async () => {
      const startTime = Date.now();
      
      // Perform complex aggregation
      const results = await Order.aggregate([
        { $match: { status: 'completed' } },
        { $unwind: '$items' },
        { $lookup: {
            from: 'products',
            localField: 'items.product',
            foreignField: '_id',
            as: 'productDetails'
          }
        },
        { $unwind: '$productDetails' },
        { $group: {
            _id: '$productDetails.category',
            totalSales: { $sum: { $multiply: ['$items.price', '$items.quantity'] } },
            count: { $sum: 1 }
          }
        },
        { $sort: { totalSales: -1 } }
      ]);
      
      const endTime = Date.now();
      const duration = endTime - startTime;
      
      expect(results.length).toBeGreaterThan(0);
      expect(duration).toBeLessThan(500); // Complex aggregation allowed more time
    });
  });
});

Explain Plans and Index Analysis

// test/performance/index-analysis.test.js
const mongoose = require('mongoose');
const { MongoMemoryServer } = require('mongodb-memory-server');
const { User, Product, Order } = require('../../models');
const { generateTestData } = require('../utils/testDataGenerator');

describe('Index Analysis', () => {
  let mongoServer, db;
  
  beforeAll(async () => {
    // Set up MongoDB memory server
    mongoServer = await MongoMemoryServer.create();
    const mongoUri = mongoServer.getUri();
    
    await mongoose.connect(mongoUri, {
      useNewUrlParser: true,
      useUnifiedTopology: true
    });
    
    // Get native MongoDB connection
    db = mongoose.connection.db;
    
    // Generate test data
    await generateTestData({
      userCount: 500,
      productCount: 1000,
      orderCount: 2000
    });
    
    // Ensure indexes are created
    await User.createIndexes();
    await Product.createIndexes();
    await Order.createIndexes();
  }, 30000);
  
  afterAll(async () => {
    await mongoose.disconnect();
    await mongoServer.stop();
  });
  
  test('User email query uses index', async () => {
    // Get explain plan for findOne by email
    const explainPlan = await db.collection('users').find(
      { email: 'user100@example.com' }
    ).explain('executionStats');
    
    // Check that an index was used
    expect(explainPlan.queryPlanner.winningPlan.inputStage.indexName).toBeDefined();
    
    // Check for COLLSCAN (collection scan) which indicates no index was used
    expect(JSON.stringify(explainPlan)).not.toContain('COLLSCAN');
    
    // Check execution statistics
    expect(explainPlan.executionStats.executionTimeMillis).toBeLessThan(10);
    expect(explainPlan.executionStats.totalDocsExamined).toBe(1); // Should examine only one document
  });
  
  test('Product search query uses appropriate indexes', async () => {
    // Complex query with multiple conditions
    const query = {
      price: { $gte: 10, $lte: 100 },
      category: 'electronics',
      inventory: { $gt: 0 }
    };
    
    const explainPlan = await db.collection('products').find(query)
      .sort({ price: 1 })
      .limit(20)
      .explain('executionStats');
    
    // Check that an index was used
    expect(explainPlan.queryPlanner.winningPlan.inputStage.indexName).toBeDefined();
    
    // Check execution statistics
    expect(explainPlan.executionStats.totalDocsExamined).toBeLessThan(
      explainPlan.executionStats.totalKeysExamined * 2
    ); // Efficient index usage
  });
  
  test('Order lookup query performance', async () => {
    // Get a random user
    const user = await User.findOne();
    
    // Query that would be used to find user orders
    const query = { user: user._id };
    
    const explainPlan = await db.collection('orders').find(query)
      .sort({ createdAt: -1 })
      .limit(10)
      .explain('executionStats');
    
    // Check execution statistics
    expect(explainPlan.executionStats.executionTimeMillis).toBeLessThan(20);
  });
  
  test('Compound index for product filtering and sorting', async () => {
    // Create a compound index for category + price
    await db.collection('products').createIndex(
      { category: 1, price: 1 },
      { name: 'category_price_index' }
    );
    
    // Query that should use the compound index
    const query = {
      category: 'electronics'
    };
    
    const explainPlan = await db.collection('products').find(query)
      .sort({ price: 1 })
      .limit(20)
      .explain('executionStats');
    
    // Check that our specific index was used
    expect(explainPlan.queryPlanner.winningPlan.inputStage.indexName).toBe('category_price_index');
    
    // Check for efficient execution
    expect(explainPlan.executionStats.totalKeysExamined).toBeLessThan(100);
  });
  
  test('Text search index', async () => {
    // Create a text index
    await db.collection('products').createIndex(
      { name: 'text', description: 'text' },
      { name: 'text_search_index' }
    );
    
    // Perform a text search
    const query = { $text: { $search: 'premium' } };
    
    const explainPlan = await db.collection('products').find(query)
      .explain('executionStats');
    
    // Check that text index was used
    expect(explainPlan.queryPlanner.winningPlan.inputStage.indexName).toBe('text_search_index');
  });
});

Testing Database Security

Database security testing ensures that your database is protected against unauthorized access and data breaches.

Role-Based Access Control (RBAC) Testing

// services/userService.js with RBAC
class UserService {
  constructor(userRepository) {
    this.userRepository = userRepository;
  }
  
  async getAllUsers(currentUser) {
    // Only admins can get all users
    if (currentUser.role !== 'admin') {
      throw new Error('Unauthorized: Admin role required');
    }
    
    return this.userRepository.findAll();
  }
  
  async getUserById(id, currentUser) {
    // Admins can view any user, regular users can only view themselves
    if (currentUser.role !== 'admin' && currentUser.id.toString() !== id.toString()) {
      throw new Error('Unauthorized: You can only view your own profile');
    }
    
    return this.userRepository.findById(id);
  }
  
  async updateUser(id, userData, currentUser) {
    // Admins can update any user, regular users can only update themselves
    if (currentUser.role !== 'admin' && currentUser.id.toString() !== id.toString()) {
      throw new Error('Unauthorized: You can only update your own profile');
    }
    
    // Only admins can update roles
    if (userData.role && currentUser.role !== 'admin') {
      delete userData.role;
    }
    
    return this.userRepository.update(id, userData);
  }
  
  async deleteUser(id, currentUser) {
    // Only admins can delete users
    if (currentUser.role !== 'admin') {
      throw new Error('Unauthorized: Admin role required');
    }
    
    return this.userRepository.delete(id);
  }
  
  async changeRole(id, role, currentUser) {
    // Only admins can change roles
    if (currentUser.role !== 'admin') {
      throw new Error('Unauthorized: Admin role required');
    }
    
    return this.userRepository.changeRole(id, role);
  }
}

module.exports = UserService;

// test/services/userService-rbac.test.js
const mongoose = require('mongoose');
const User = require('../../models/user');
const UserRepository = require('../../repositories/userRepository');
const UserService = require('../../services/userService');
require('../setup'); // Database setup

describe('User Service - RBAC', () => {
  let userRepository, userService;
  let adminUser, regularUser, anotherUser;
  
  beforeEach(async () => {
    userRepository = new UserRepository(User);
    userService = new UserService(userRepository);
    
    // Create test users
    adminUser = await User.create({
      username: 'admin',
      email: 'admin@example.com',
      password: 'password123',
      role: 'admin'
    });
    
    regularUser = await User.create({
      username: 'regular',
      email: 'regular@example.com',
      password: 'password123',
      role: 'user'
    });
    
    anotherUser = await User.create({
      username: 'another',
      email: 'another@example.com',
      password: 'password123',
      role: 'user'
    });
  });
  
  describe('getAllUsers', () => {
    test('admin can get all users', async () => {
      const users = await userService.getAllUsers(adminUser);
      
      expect(users).toBeDefined();
      expect(users.length).toBeGreaterThanOrEqual(3);
    });
    
    test('regular user cannot get all users', async () => {
      await expect(userService.getAllUsers(regularUser))
        .rejects.toThrow('Unauthorized');
    });
  });
  
  describe('getUserById', () => {
    test('admin can get any user', async () => {
      const user = await userService.getUserById(regularUser._id, adminUser);
      
      expect(user).toBeDefined();
      expect(user.id.toString()).toBe(regularUser._id.toString());
    });
    
    test('regular user can get their own profile', async () => {
      const user = await userService.getUserById(regularUser._id, regularUser);
      
      expect(user).toBeDefined();
      expect(user.id.toString()).toBe(regularUser._id.toString());
    });
    
    test('regular user cannot get another user profile', async () => {
      await expect(userService.getUserById(anotherUser._id, regularUser))
        .rejects.toThrow('Unauthorized');
    });
  });
  
  describe('updateUser', () => {
    test('admin can update any user', async () => {
      const updatedUser = await userService.updateUser(
        regularUser._id,
        { username: 'updated-by-admin' },
        adminUser
      );
      
      expect(updatedUser).toBeDefined();
      expect(updatedUser.username).toBe('updated-by-admin');
    });
    
    test('admin can update roles', async () => {
      const updatedUser = await userService.updateUser(
        regularUser._id,
        { role: 'admin' },
        adminUser
      );
      
      expect(updatedUser).toBeDefined();
      expect(updatedUser.role).toBe('admin');
    });
    
    test('regular user can update own profile', async () => {
      const updatedUser = await userService.updateUser(
        regularUser._id,
        { username: 'self-updated' },
        regularUser
      );
      
      expect(updatedUser).toBeDefined();
      expect(updatedUser.username).toBe('self-updated');
    });
    
    test('regular user cannot update another user', async () => {
      await expect(userService.updateUser(
        anotherUser._id,
        { username: 'hack-attempt' },
        regularUser
      )).rejects.toThrow('Unauthorized');
      
      // Verify no change was made
      const unchangedUser = await User.findById(anotherUser._id);
      expect(unchangedUser.username).toBe('another');
    });
    
    test('regular user cannot update own role', async () => {
      const updatedUser = await userService.updateUser(
        regularUser._id,
        { 
          username: 'valid-update',
          role: 'admin' // This should be ignored
        },
        regularUser
      );
      
      expect(updatedUser).toBeDefined();
      expect(updatedUser.username).toBe('valid-update');
      expect(updatedUser.role).toBe('user'); // Role should remain unchanged
    });
  });
  
  describe('deleteUser', () => {
    test('admin can delete users', async () => {
      await userService.deleteUser(regularUser._id, adminUser);
      
      // Verify user was deleted
      const deletedUser = await User.findById(regularUser._id);
      expect(deletedUser).toBeNull();
    });
    
    test('regular user cannot delete any user', async () => {
      // Try to delete own account
      await expect(userService.deleteUser(regularUser._id, regularUser))
        .rejects.toThrow('Unauthorized');
      
      // Try to delete another user
      await expect(userService.deleteUser(anotherUser._id, regularUser))
        .rejects.toThrow('Unauthorized');
      
      // Verify no users were deleted
      const users = await User.find({});
      expect(users.length).toBeGreaterThanOrEqual(3);
    });
  });
  
  describe('changeRole', () => {
    test('admin can change roles', async () => {
      const updatedUser = await userService.changeRole(
        regularUser._id,
        'admin',
        adminUser
      );
      
      expect(updatedUser).toBeDefined();
      expect(updatedUser.role).toBe('admin');
    });
    
    test('regular user cannot change roles', async () => {
      await expect(userService.changeRole(
        anotherUser._id,
        'admin',
        regularUser
      )).rejects.toThrow('Unauthorized');
      
      // Verify role was not changed
      const unchangedUser = await User.findById(anotherUser._id);
      expect(unchangedUser.role).toBe('user');
    });
  });
});

Testing SQL Injection Prevention

// repositories/userRepository.js with SQL (PostgreSQL) example
const { Client } = require('pg');

class UserRepository {
  constructor() {
    this.client = new Client({
      connectionString: process.env.DATABASE_URL
    });
    this.client.connect();
  }
  
  async findByUsername(username) {
    // VULNERABLE to SQL injection
    // const query = `SELECT * FROM users WHERE username = '${username}'`;
    
    // SAFE approach using parameterized queries
    const query = 'SELECT * FROM users WHERE username = $1';
    const values = [username];
    
    const result = await this.client.query(query, values);
    return result.rows[0];
  }
  
  async searchUsers(searchTerm) {
    // SAFE approach for LIKE queries
    const query = 'SELECT * FROM users WHERE username LIKE $1';
    const values = [`%${searchTerm}%`];
    
    const result = await this.client.query(query, values);
    return result.rows;
  }
  
  async createUser(userData) {
    const { username, email, password, role = 'user' } = userData;
    
    // SAFE approach using parameterized queries
    const query = `
      INSERT INTO users (username, email, password, role)
      VALUES ($1, $2, $3, $4)
      RETURNING *
    `;
    const values = [username, email, password, role];
    
    const result = await this.client.query(query, values);
    return result.rows[0];
  }
}

module.exports = UserRepository;

// test/security/sql-injection.test.js
const { Client } = require('pg');
const UserRepository = require('../../repositories/userRepository');

// Use a test database
process.env.DATABASE_URL = 'postgresql://testuser:testpass@localhost:5432/testdb';

describe('SQL Injection Prevention', () => {
  let client, userRepository;
  
  beforeAll(async () => {
    // Connect directly to test database for setup
    client = new Client({
      connectionString: process.env.DATABASE_URL
    });
    await client.connect();
    
    // Create test schema
    await client.query(`
      CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(50) UNIQUE NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        password VARCHAR(100) NOT NULL,
        role VARCHAR(20) NOT NULL DEFAULT 'user'
      )
    `);
    
    userRepository = new UserRepository();
  });
  
  afterAll(async () => {
    // Clean up
    await client.query('DROP TABLE IF EXISTS users');
    await client.end();
    await userRepository.client.end();
  });
  
  beforeEach(async () => {
    // Clear users table
    await client.query('DELETE FROM users');
    
    // Add test users
    await client.query(`
      INSERT INTO users (username, email, password, role)
      VALUES
        ('admin', 'admin@example.com', 'hashedpassword1', 'admin'),
        ('user1', 'user1@example.com', 'hashedpassword2', 'user'),
        ('user2', 'user2@example.com', 'hashedpassword3', 'user')
    `);
  });
  
  test('findByUsername is protected against SQL injection', async () => {
    // Malicious input that would attempt to retrieve all users
    const maliciousInput = "admin' OR '1'='1";
    
    // If vulnerable, this would return all users
    // If protected, it should return null (no match)
    const result = await userRepository.findByUsername(maliciousInput);
    
    // Should not find a user with this exact name
    expect(result).toBeUndefined();
    
    // Verify only with exact match
    const legitimateUser = await userRepository.findByUsername('admin');
    expect(legitimateUser).toBeDefined();
    expect(legitimateUser.username).toBe('admin');
  });
  
  test('searchUsers is protected against SQL injection', async () => {
    // Malicious input that would attempt to retrieve all users
    const maliciousInput = "' OR '1'='1";
    
    const results = await userRepository.searchUsers(maliciousInput);
    
    // Should only return users that actually match the pattern
    expect(results.length).toBe(0);
    
    // Verify legitimate search works
    const legitimateResults = await userRepository.searchUsers('user');
    expect(legitimateResults.length).toBe(2);
  });
  
  test('createUser is protected against SQL injection', async () => {
    // Malicious input that would attempt to insert an admin
    const maliciousUsername = "hacker'; INSERT INTO users (username, email, password, role) VALUES ('hacker2', 'hacker2@evil.com', 'password', 'admin'); --";
    
    await userRepository.createUser({
      username: maliciousUsername,
      email: 'hacker@evil.com',
      password: 'password123'
    });
    
    // If vulnerable, we would have two new users
    // If protected, only one new user with the exact malicious username
    const allUsers = await client.query('SELECT * FROM users');
    
    // Should have 4 users total (3 initial + 1 new with exact malicious username)
    expect(allUsers.rows.length).toBe(4);
    
    // Should not find a user with the name 'hacker2'
    const hackerUser = await client.query("SELECT * FROM users WHERE username = 'hacker2'");
    expect(hackerUser.rows.length).toBe(0);
  });
});

Practice Activities

Further Reading

• Unit of Work Pattern by Martin Fowler
• Repository Pattern by Martin Fowler
• MongoDB Compound Indexes
• Use The Index, Luke! SQL Indexing and Tuning
• PostgreSQL Performance Tips
• ACID Properties of Transactions
• Flyway Database Migrations