Database Test Automation

Master database testing techniques for data integrity and performance validation

Overview

Database testing ensures the integrity, performance, and reliability of database operations. This comprehensive guide covers SQL testing, data validation, transaction testing, and performance optimization techniques across different database systems.

      Key Areas Covered: SQL query validation, data integrity testing, performance benchmarking, transaction testing, and database schema validation.
    

⚠️ Important: Always use separate test databases and never run destructive tests against production data. Implement proper data backup and rollback strategies.

SQL Query Testing

Testing SQL queries involves validating query correctness, performance, and result accuracy:

Basic Query Validation

// Using Node.js with MySQL/PostgreSQL
const mysql = require('mysql2/promise');
const { expect } = require('chai');

describe('Database Query Tests', () => {
  
  let connection;

  beforeEach(async () => {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'testuser',
      password: 'testpass',
      database: 'test_db'
    });
  });

  afterEach(async () => {
    await connection.end();
  });


  test('should retrieve users with valid email format', async () => {
    const query = `
      SELECT id, name, email 
      FROM users 
      WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}'
      LIMIT 10
    `;

    const [rows] = await connection.execute(query);
    
    expect(rows).to.have.length.greaterThan(0);
    
    rows.forEach(user => {
      expect(user.email).to.match(/@.*\./);
      expect(user.name)
        .to.be.a('string')
        .and.have.length.greaterThan(0);
    });
  });


  test('should validate user count by status', async () => {
    const activeUsersQuery = 
      'SELECT COUNT(*) as count FROM users WHERE status = "active"';
    const totalUsersQuery = 
      'SELECT COUNT(*) as count FROM users';

    const [activeResult] = await connection.execute(activeUsersQuery);
    const [totalResult] = await connection.execute(totalUsersQuery);

    const activeCount = activeResult[0].count;
    const totalCount = totalResult[0].count;

    expect(activeCount).to.be.at.least(0);
    expect(totalCount).to.be.at.least(activeCount);
    expect(activeCount / totalCount).to.be.at.most(1);
  });


  test('should validate referential integrity', async () => {
    const query = `
      SELECT u.id, u.name, COUNT(o.id) as order_count
      FROM users u
      LEFT JOIN orders o ON u.id = o.user_id
      WHERE u.status = 'active'
      GROUP BY u.id, u.name
      HAVING order_count > 0
    `;

    const [rows] = await connection.execute(query);
    
    for (const user of rows) {
      expect(user.order_count).to.be.greaterThan(0);
      
      // Verify orders actually exist
      const [orderCheck] = await connection.execute(
        'SELECT COUNT(*) as count FROM orders WHERE user_id = ?',
        [user.id]
      );
      
      expect(orderCheck[0].count).to.equal(user.order_count);
    }
  });


  test('should validate complex data relationships', async () => {
    const query = `
      SELECT 
        p.id,
        p.name,
        p.price,
        COUNT(DISTINCT oi.order_id) as times_ordered,
        SUM(oi.quantity) as total_quantity_sold,
        AVG(oi.unit_price) as avg_selling_price
      FROM products p
      LEFT JOIN order_items oi ON p.id = oi.product_id
      WHERE p.status = 'active'
      GROUP BY p.id, p.name, p.price
      HAVING times_ordered > 0
      ORDER BY total_quantity_sold DESC
      LIMIT 20
    `;

    const [rows] = await connection.execute(query);
    
    rows.forEach(product => {
      // Validate business logic
      expect(product.price).to.be.above(0);
      expect(product.times_ordered).to.be.above(0);
      expect(product.total_quantity_sold).to.be.above(0);
      expect(product.avg_selling_price).to.be.above(0);
      
      // Average selling price should be reasonable compared to base price
      const priceRatio = product.avg_selling_price / product.price;
      expect(priceRatio).to.be.within(0.5, 2.0);
    });
  });

});
      

// Using JDBC with TestNG
import java.sql.*;
import org.testng.annotations.*;
import static org.testng.Assert.*;

public class DatabaseQueryTest {
    
    private Connection connection;
    
    @BeforeMethod
    public void setUp() throws SQLException {
        String url = "jdbc:mysql://localhost:3306/test_db";
        String username = "testuser";
        String password = "testpass";
        
        connection = DriverManager.getConnection(url, username, password);
    }
    
    @AfterMethod
    public void tearDown() throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
    
    
    @Test
    public void testValidEmailUsers() throws SQLException {
        String query = """
            SELECT id, name, email 
            FROM users 
            WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\\\.[A-Za-z]{2,}'
            LIMIT 10
            """;
        
        PreparedStatement stmt = connection.prepareStatement(query);
        ResultSet rs = stmt.executeQuery();
        
        int count = 0;
        while (rs.next()) {
            count++;
            String email = rs.getString("email");
            String name = rs.getString("name");
            
            assertTrue(email.contains("@"));
            assertTrue(email.contains("."));
            assertFalse(name.isEmpty());
        }
        
        assertTrue(count > 0, "Should find users with valid emails");
    }
    
    
    @Test
    public void testUserStatusCounts() throws SQLException {
        // Test active users count
        String activeQuery = "SELECT COUNT(*) FROM users WHERE status = 'active'";
        String totalQuery = "SELECT COUNT(*) FROM users";
        
        PreparedStatement activeStmt = connection.prepareStatement(activeQuery);
        PreparedStatement totalStmt = connection.prepareStatement(totalQuery);
        
        ResultSet activeRs = activeStmt.executeQuery();
        ResultSet totalRs = totalStmt.executeQuery();
        
        activeRs.next();
        totalRs.next();
        
        int activeCount = activeRs.getInt(1);
        int totalCount = totalRs.getInt(1);
        
        assertTrue(activeCount >= 0);
        assertTrue(totalCount >= activeCount);
        assertTrue((double) activeCount / totalCount <= 1.0);
    }
    
    
    @Test
    public void testReferentialIntegrity() throws SQLException {
        String query = """
            SELECT u.id, u.name, COUNT(o.id) as order_count
            FROM users u
            LEFT JOIN orders o ON u.id = o.user_id
            WHERE u.status = 'active'
            GROUP BY u.id, u.name
            HAVING order_count > 0
            """;
        
        PreparedStatement stmt = connection.prepareStatement(query);
        ResultSet rs = stmt.executeQuery();
        
        while (rs.next()) {
            int userId = rs.getInt("id");
            int orderCount = rs.getInt("order_count");
            
            assertTrue(orderCount > 0);
            
            // Verify orders exist
            PreparedStatement orderStmt = connection.prepareStatement(
                "SELECT COUNT(*) FROM orders WHERE user_id = ?"
            );
            orderStmt.setInt(1, userId);
            ResultSet orderRs = orderStmt.executeQuery();
            orderRs.next();
            
            assertEquals(orderCount, orderRs.getInt(1));
        }
    }
    
    
    @Test
    public void testComplexDataRelationships() throws SQLException {
        String query = """
            SELECT 
                p.id,
                p.name,
                p.price,
                COUNT(DISTINCT oi.order_id) as times_ordered,
                SUM(oi.quantity) as total_quantity_sold,
                AVG(oi.unit_price) as avg_selling_price
            FROM products p
            LEFT JOIN order_items oi ON p.id = oi.product_id
            WHERE p.status = 'active'
            GROUP BY p.id, p.name, p.price
            HAVING times_ordered > 0
            ORDER BY total_quantity_sold DESC
            LIMIT 20
            """;
        
        PreparedStatement stmt = connection.prepareStatement(query);
        ResultSet rs = stmt.executeQuery();
        
        while (rs.next()) {
            double price = rs.getDouble("price");
            int timesOrdered = rs.getInt("times_ordered");
            int totalQuantity = rs.getInt("total_quantity_sold");
            double avgPrice = rs.getDouble("avg_selling_price");
            
            // Validate business logic
            assertTrue(price > 0);
            assertTrue(timesOrdered > 0);
            assertTrue(totalQuantity > 0);
            assertTrue(avgPrice > 0);
            
            // Price consistency check
            double priceRatio = avgPrice / price;
            assertTrue(priceRatio >= 0.5 && priceRatio <= 2.0,
                "Price ratio out of expected range: " + priceRatio);
        }
    }
}
      

# Using PyMySQL/psycopg2 with pytest
import pymysql
import pytest

class TestDatabaseQueries:
    
    @pytest.fixture(autouse=True)
    def setup_connection(self):
        self.connection = pymysql.connect(
            host='localhost',
            user='testuser',
            password='testpass',
            database='test_db',
            charset='utf8mb4'
        )
        yield
        self.connection.close()
    
    
    def test_valid_email_users(self):
        query = """
            SELECT id, name, email 
            FROM users 
            WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}'
            LIMIT 10
        """
        
        with self.connection.cursor() as cursor:
            cursor.execute(query)
            results = cursor.fetchall()
            
            assert len(results) > 0, "Should find users with valid emails"
            
            for user in results:
                user_id, name, email = user
                assert '@' in email
                assert '.' in email
                assert len(name) > 0
    
    
    def test_user_status_counts(self):
        with self.connection.cursor() as cursor:
            # Get active users count
            cursor.execute("SELECT COUNT(*) FROM users WHERE status = 'active'")
            active_count = cursor.fetchone()[0]
            
            # Get total users count
            cursor.execute("SELECT COUNT(*) FROM users")
            total_count = cursor.fetchone()[0]
            
            assert active_count >= 0
            assert total_count >= active_count
            assert active_count / total_count <= 1.0
    
    
    def test_referential_integrity(self):
        query = """
            SELECT u.id, u.name, COUNT(o.id) as order_count
            FROM users u
            LEFT JOIN orders o ON u.id = o.user_id
            WHERE u.status = 'active'
            GROUP BY u.id, u.name
            HAVING order_count > 0
        """
        
        with self.connection.cursor() as cursor:
            cursor.execute(query)
            results = cursor.fetchall()
            
            for user in results:
                user_id, name, order_count = user
                assert order_count > 0
                
                # Verify orders actually exist
                cursor.execute(
                    "SELECT COUNT(*) FROM orders WHERE user_id = %s",
                    (user_id,)
                )
                actual_count = cursor.fetchone()[0]
                assert actual_count == order_count
    
    
    def test_data_consistency(self):
        """Test data consistency across related tables"""
        with self.connection.cursor() as cursor:
            # Check if all order totals match sum of order items
            query = """
                SELECT o.id, o.total_amount, 
                       COALESCE(SUM(oi.quantity * oi.unit_price), 0) as calculated_total
                FROM orders o
                LEFT JOIN order_items oi ON o.id = oi.order_id
                GROUP BY o.id, o.total_amount
                HAVING ABS(o.total_amount - calculated_total) > 0.01
            """
            
            cursor.execute(query)
            inconsistent_orders = cursor.fetchall()
            
            assert len(inconsistent_orders) == 0, \
                f"Found {len(inconsistent_orders)} orders with inconsistent totals"
    
    
    def test_complex_data_relationships(self):
        query = """
            SELECT 
                p.id,
                p.name,
                p.price,
                COUNT(DISTINCT oi.order_id) as times_ordered,
                SUM(oi.quantity) as total_quantity_sold,
                AVG(oi.unit_price) as avg_selling_price
            FROM products p
            LEFT JOIN order_items oi ON p.id = oi.product_id
            WHERE p.status = 'active'
            GROUP BY p.id, p.name, p.price
            HAVING times_ordered > 0
            ORDER BY total_quantity_sold DESC
            LIMIT 20
        """
        
        with self.connection.cursor() as cursor:
            cursor.execute(query)
            results = cursor.fetchall()
            
            for product in results:
                (product_id, name, price, times_ordered, 
                 total_quantity, avg_price) = product
                
                # Validate business logic
                assert price > 0
                assert times_ordered > 0
                assert total_quantity > 0
                assert avg_price > 0
                
                # Price consistency check
                price_ratio = avg_price / price
                assert 0.5 <= price_ratio <= 2.0, \
                    f"Price ratio out of range: {price_ratio}"
      

Data Integrity Testing

Ensure data consistency, constraints, and business rules are properly enforced:

Constraint Validation

// Data Integrity Testing
describe('Data Integrity Tests', () => {
  
  test('should enforce unique email constraint', async () => {
    const duplicateEmail = 'duplicate@example.com';
    
    // Insert first user
    await connection.execute(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      ['User One', duplicateEmail]
    );
    
    // Attempt to insert duplicate email should fail
    try {
      await connection.execute(
        'INSERT INTO users (name, email) VALUES (?, ?)',
        ['User Two', duplicateEmail]
      );
      
      throw new Error('Expected duplicate email to be rejected');
    } catch (error) {
      expect(error.code).to.equal('ER_DUP_ENTRY');
    }
  });


  test('should validate foreign key constraints', async () => {
    const nonExistentUserId = 99999;
    
    try {
      await connection.execute(
        'INSERT INTO orders (user_id, total_amount) VALUES (?, ?)',
        [nonExistentUserId, 100.00]
      );
      
      throw new Error('Expected foreign key constraint violation');
    } catch (error) {
      expect(error.code).to.equal('ER_NO_REFERENCED_ROW_2');
    }
  });


  test('should validate check constraints', async () => {
    // Test negative price constraint
    try {
      await connection.execute(
        'INSERT INTO products (name, price) VALUES (?, ?)',
        ['Test Product', -10.00]
      );
      
      throw new Error('Expected check constraint violation for negative price');
    } catch (error) {
      expect(error.code).to.equal('ER_CHECK_CONSTRAINT_VIOLATED');
    }
  });


  test('should maintain data consistency during transactions', async () => {
    await connection.beginTransaction();
    
    try {
      // Create user
      const [userResult] = await connection.execute(
        'INSERT INTO users (name, email) VALUES (?, ?)',
        ['Transaction User', 'transaction@example.com']
      );
      
      const userId = userResult.insertId;
      
      // Create order for user
      await connection.execute(
        'INSERT INTO orders (user_id, total_amount) VALUES (?, ?)',
        [userId, 150.00]
      );
      
      // Verify both records exist before commit
      const [userCheck] = await connection.execute(
        'SELECT COUNT(*) as count FROM users WHERE id = ?',
        [userId]
      );
      
      const [orderCheck] = await connection.execute(
        'SELECT COUNT(*) as count FROM orders WHERE user_id = ?',
        [userId]
      );
      
      expect(userCheck[0].count).to.equal(1);
      expect(orderCheck[0].count).to.equal(1);
      
      await connection.commit();
    } catch (error) {
      await connection.rollback();
      throw error;
    }
  });


  test('should validate business rule constraints', async () => {
    // Test that order total matches sum of order items
    const query = `
      INSERT INTO orders (user_id, total_amount) VALUES (1, 100.00);
      SET @order_id = LAST_INSERT_ID();
      
      INSERT INTO order_items (order_id, product_id, quantity, unit_price) 
      VALUES (@order_id, 1, 2, 25.00), (@order_id, 2, 1, 50.00);
    `;
    
    // Execute the transaction
    await connection.beginTransaction();
    
    try {
      // Insert order and items
      const [orderResult] = await connection.execute(
        'INSERT INTO orders (user_id, total_amount) VALUES (?, ?)',
        [1, 100.00]
      );
      
      const orderId = orderResult.insertId;
      
      await connection.execute(
        'INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (?, ?, ?, ?)',
        [orderId, 1, 2, 25.00]
      );
      
      await connection.execute(
        'INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (?, ?, ?, ?)',
        [orderId, 2, 1, 50.00]
      );
      
      // Verify total calculation
      const [verification] = await connection.execute(`
        SELECT 
          o.total_amount,
          SUM(oi.quantity * oi.unit_price) as calculated_total
        FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        WHERE o.id = ?
        GROUP BY o.id, o.total_amount
      `, [orderId]);
      
      const { total_amount, calculated_total } = verification[0];
      expect(total_amount).to.equal(calculated_total);
      
      await connection.commit();
    } catch (error) {
      await connection.rollback();
      throw error;
    }
  });

});
      

@Test(expectedExceptions = SQLException.class)
public void testUniqueEmailConstraint() throws SQLException {
    String duplicateEmail = "duplicate@example.com";
    
    // Insert first user
    PreparedStatement stmt1 = connection.prepareStatement(
        "INSERT INTO users (name, email) VALUES (?, ?)"
    );
    stmt1.setString(1, "User One");
    stmt1.setString(2, duplicateEmail);
    stmt1.executeUpdate();
    
    // This should throw SQLException due to unique constraint
    PreparedStatement stmt2 = connection.prepareStatement(
        "INSERT INTO users (name, email) VALUES (?, ?)"
    );
    stmt2.setString(1, "User Two");
    stmt2.setString(2, duplicateEmail);
    stmt2.executeUpdate();
}


@Test(expectedExceptions = SQLException.class)
public void testForeignKeyConstraint() throws SQLException {
    int nonExistentUserId = 99999;
    
    PreparedStatement stmt = connection.prepareStatement(
        "INSERT INTO orders (user_id, total_amount) VALUES (?, ?)"
    );
    stmt.setInt(1, nonExistentUserId);
    stmt.setDouble(2, 100.00);
    stmt.executeUpdate(); // Should throw foreign key constraint violation
}


@Test
public void testTransactionConsistency() throws SQLException {
    connection.setAutoCommit(false);
    
    try {
        // Insert user
        PreparedStatement userStmt = connection.prepareStatement(
            "INSERT INTO users (name, email) VALUES (?, ?)",
            Statement.RETURN_GENERATED_KEYS
        );
        userStmt.setString(1, "Transaction User");
        userStmt.setString(2, "transaction@example.com");
        userStmt.executeUpdate();
        
        ResultSet keys = userStmt.getGeneratedKeys();
        keys.next();
        int userId = keys.getInt(1);
        
        // Insert order
        PreparedStatement orderStmt = connection.prepareStatement(
            "INSERT INTO orders (user_id, total_amount) VALUES (?, ?)"
        );
        orderStmt.setInt(1, userId);
        orderStmt.setDouble(2, 150.00);
        orderStmt.executeUpdate();
        
        // Verify consistency before commit
        PreparedStatement checkStmt = connection.prepareStatement(
            "SELECT COUNT(*) FROM orders WHERE user_id = ?"
        );
        checkStmt.setInt(1, userId);
        ResultSet rs = checkStmt.executeQuery();
        rs.next();
        
        assertEquals(1, rs.getInt(1));
        
        connection.commit();
    } catch (SQLException e) {
        connection.rollback();
        throw e;
    } finally {
        connection.setAutoCommit(true);
    }
}


@Test
public void testBusinessRuleConstraints() throws SQLException {
    connection.setAutoCommit(false);
    
    try {
        // Insert order
        PreparedStatement orderStmt = connection.prepareStatement(
            "INSERT INTO orders (user_id, total_amount) VALUES (?, ?)",
            Statement.RETURN_GENERATED_KEYS
        );
        orderStmt.setInt(1, 1);
        orderStmt.setDouble(2, 100.00);
        orderStmt.executeUpdate();
        
        ResultSet keys = orderStmt.getGeneratedKeys();
        keys.next();
        int orderId = keys.getInt(1);
        
        // Insert order items
        PreparedStatement itemStmt = connection.prepareStatement(
            "INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (?, ?, ?, ?)"
        );
        
        // Item 1
        itemStmt.setInt(1, orderId);
        itemStmt.setInt(2, 1);
        itemStmt.setInt(3, 2);
        itemStmt.setDouble(4, 25.00);
        itemStmt.executeUpdate();
        
        // Item 2
        itemStmt.setInt(2, 2);
        itemStmt.setInt(3, 1);
        itemStmt.setDouble(4, 50.00);
        itemStmt.executeUpdate();
        
        // Verify total calculation
        PreparedStatement verifyStmt = connection.prepareStatement("""
            SELECT 
                o.total_amount,
                SUM(oi.quantity * oi.unit_price) as calculated_total
            FROM orders o
            JOIN order_items oi ON o.id = oi.order_id
            WHERE o.id = ?
            GROUP BY o.id, o.total_amount
            """);
        verifyStmt.setInt(1, orderId);
        
        ResultSet rs = verifyStmt.executeQuery();
        rs.next();
        
        double totalAmount = rs.getDouble("total_amount");
        double calculatedTotal = rs.getDouble("calculated_total");
        
        assertEquals(totalAmount, calculatedTotal, 0.001);
        
        connection.commit();
    } catch (SQLException e) {
        connection.rollback();
        throw e;
    } finally {
        connection.setAutoCommit(true);
    }
}
      

def test_unique_email_constraint(self):
    duplicate_email = "duplicate@example.com"
    
    with self.connection.cursor() as cursor:
        # Insert first user
        cursor.execute(
            "INSERT INTO users (name, email) VALUES (%s, %s)",
            ("User One", duplicate_email)
        )
        
        # Attempt to insert duplicate should raise exception
        with pytest.raises(pymysql.IntegrityError):
            cursor.execute(
                "INSERT INTO users (name, email) VALUES (%s, %s)",
                ("User Two", duplicate_email)
            )


def test_foreign_key_constraint(self):
    non_existent_user_id = 99999
    
    with self.connection.cursor() as cursor:
        with pytest.raises(pymysql.IntegrityError):
            cursor.execute(
                "INSERT INTO orders (user_id, total_amount) VALUES (%s, %s)",
                (non_existent_user_id, 100.00)
            )


def test_transaction_consistency(self):
    try:
        with self.connection.cursor() as cursor:
            # Start transaction
            self.connection.begin()
            
            # Insert user
            cursor.execute(
                "INSERT INTO users (name, email) VALUES (%s, %s)",
                ("Transaction User", "transaction@example.com")
            )
            user_id = cursor.lastrowid
            
            # Insert order
            cursor.execute(
                "INSERT INTO orders (user_id, total_amount) VALUES (%s, %s)",
                (user_id, 150.00)
            )
            
            # Verify consistency before commit
            cursor.execute(
                "SELECT COUNT(*) FROM orders WHERE user_id = %s",
                (user_id,)
            )
            order_count = cursor.fetchone()[0]
            
            assert order_count == 1
            
            # Commit transaction
            self.connection.commit()
            
    except Exception as e:
        self.connection.rollback()
        raise e


def test_data_type_constraints(self):
    """Test that data type constraints are enforced"""
    with self.connection.cursor() as cursor:
        # Test string length constraint
        with pytest.raises((pymysql.DataError, pymysql.IntegrityError)):
            cursor.execute(
                "INSERT INTO users (name, email) VALUES (%s, %s)",
                ("x" * 300, "test@example.com")  # Assuming name has max length < 300
            )
        
        # Test numeric range constraint
        with pytest.raises((pymysql.DataError, pymysql.IntegrityError)):
            cursor.execute(
                "INSERT INTO products (name, price) VALUES (%s, %s)",
                ("Test Product", -10.00)  # Assuming price cannot be negative
            )


def test_business_rule_constraints(self):
    """Test business logic constraints"""
    try:
        with self.connection.cursor() as cursor:
            # Start transaction
            self.connection.begin()
            
            # Insert order
            cursor.execute(
                "INSERT INTO orders (user_id, total_amount) VALUES (%s, %s)",
                (1, 100.00)
            )
            order_id = cursor.lastrowid
            
            # Insert order items
            cursor.execute(
                "INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (%s, %s, %s, %s)",
                (order_id, 1, 2, 25.00)
            )
            
            cursor.execute(
                "INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (%s, %s, %s, %s)",
                (order_id, 2, 1, 50.00)
            )
            
            # Verify total calculation
            cursor.execute("""
                SELECT 
                    o.total_amount,
                    SUM(oi.quantity * oi.unit_price) as calculated_total
                FROM orders o
                JOIN order_items oi ON o.id = oi.order_id
                WHERE o.id = %s
                GROUP BY o.id, o.total_amount
            """, (order_id,))
            
            result = cursor.fetchone()
            total_amount, calculated_total = result
            
            assert abs(total_amount - calculated_total) < 0.001
            
            # Commit transaction
            self.connection.commit()
            
    except Exception as e:
        self.connection.rollback()
        raise e
      

Performance Testing

Database performance testing ensures queries execute within acceptable time limits and system resources are used efficiently:

// Database Performance Testing
describe('Database Performance Tests', () => {
  
  test('should execute user lookup query within 100ms', async () => {
    const startTime = Date.now();
    
    const query = `
      SELECT u.id, u.name, u.email, COUNT(o.id) as order_count
      FROM users u
      LEFT JOIN orders o ON u.id = o.user_id
      WHERE u.status = 'active'
      GROUP BY u.id, u.name, u.email
      ORDER BY order_count DESC
      LIMIT 50
    `;
    
    const [rows] = await connection.execute(query);
    
    const executionTime = Date.now() - startTime;
    
    expect(executionTime).to.be.below(100, `Query took ${executionTime}ms`);
    expect(rows).to.have.length.above(0);
  });


  test('should handle concurrent database connections', async () => {
    const concurrentQueries = 10;
    const maxExecutionTime = 200;
    
    const queries = Array(concurrentQueries).fill().map(async (_, index) => {
      const startTime = Date.now();
      
      const [rows] = await connection.execute(
        'SELECT * FROM products WHERE category_id = ? LIMIT 20',
        [index % 5 + 1]
      );
      
      const executionTime = Date.now() - startTime;
      return { executionTime, rowCount: rows.length };
    });
    
    const results = await Promise.all(queries);
    
    results.forEach((result, index) => {
      expect(result.executionTime).to.be.below(
        maxExecutionTime,
        `Query ${index} took ${result.executionTime}ms`
      );
    });
    
    const avgExecutionTime = results
      .reduce((sum, r) => sum + r.executionTime, 0) / results.length;
    
    console.log(`Average execution time: ${avgExecutionTime.toFixed(2)}ms`);
  });


  test('should efficiently handle large result sets with pagination', async () => {
    const pageSize = 1000;
    const maxPages = 5;
    
    for (let page = 0; page < maxPages; page++) {
      const startTime = Date.now();
      const offset = page * pageSize;
      
      const [rows] = await connection.execute(
        'SELECT id, name, email FROM users ORDER BY id LIMIT ? OFFSET ?',
        [pageSize, offset]
      );
      
      const executionTime = Date.now() - startTime;
      
      expect(executionTime).to.be.below(150, 
        `Page ${page} took ${executionTime}ms`
      );
      
      if (rows.length === 0) break; // No more data
    }
  });


  test('should execute complex aggregation query efficiently', async () => {
    const startTime = Date.now();
    
    const query = `
      SELECT 
        DATE(o.created_at) as order_date,
        COUNT(*) as order_count,
        SUM(o.total_amount) as daily_revenue,
        AVG(o.total_amount) as avg_order_value,
        COUNT(DISTINCT o.user_id) as unique_customers
      FROM orders o
      WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
      GROUP BY DATE(o.created_at)
      ORDER BY order_date DESC
    `;
    
    const [rows] = await connection.execute(query);
    const executionTime = Date.now() - startTime;
    
    expect(executionTime).to.be.below(300, `Complex query took ${executionTime}ms`);
    expect(rows).to.have.length.above(0);
    
    // Validate data consistency
    rows.forEach(row => {
      expect(row.order_count).to.be.above(0);
      expect(row.daily_revenue).to.be.above(0);
      expect(row.avg_order_value).to.be.above(0);
      expect(row.unique_customers).to.be.above(0);
    });
  });


  test('should maintain performance under load', async () => {
    const loadTestDuration = 10000; // 10 seconds
    const queriesPerSecond = 20;
    const maxResponseTime = 100;
    
    const startTime = Date.now();
    const results = [];
    
    while (Date.now() - startTime < loadTestDuration) {
      const queryPromises = [];
      
      for (let i = 0; i < queriesPerSecond; i++) {
        const queryStart = Date.now();
        
        const queryPromise = connection.execute(
          'SELECT COUNT(*) FROM users WHERE status = ?',
          ['active']
        ).then(() => {
          const queryTime = Date.now() - queryStart;
          results.push(queryTime);
          return queryTime;
        });
        
        queryPromises.push(queryPromise);
      }
      
      await Promise.all(queryPromises);
      await new Promise(resolve => setTimeout(resolve, 1000));
    }
    
    const avgResponseTime = results
      .reduce((sum, time) => sum + time, 0) / results.length;
    
    const maxTime = Math.max(...results);
    
    console.log(`Load test results: Avg: ${avgResponseTime.toFixed(2)}ms, Max: ${maxTime}ms`);
    
    expect(avgResponseTime).to.be.below(maxResponseTime);
    expect(maxTime).to.be.below(maxResponseTime * 3); // Allow 3x for outliers
  });

});
      

@Test
public void testQueryExecutionTime() throws SQLException {
    long startTime = System.currentTimeMillis();
    
    String query = """
        SELECT u.id, u.name, u.email, COUNT(o.id) as order_count
        FROM users u
        LEFT JOIN orders o ON u.id = o.user_id
        WHERE u.status = 'active'
        GROUP BY u.id, u.name, u.email
        ORDER BY order_count DESC
        LIMIT 50
        """;
    
    PreparedStatement stmt = connection.prepareStatement(query);
    ResultSet rs = stmt.executeQuery();
    
    long executionTime = System.currentTimeMillis() - startTime;
    
    assertTrue(executionTime < 100, 
        "Query took " + executionTime + "ms, expected < 100ms");
    
    int rowCount = 0;
    while (rs.next()) {
        rowCount++;
    }
    
    assertTrue(rowCount > 0, "Should return at least one row");
}


@Test
public void testConcurrentQueries() throws InterruptedException {
    ExecutorService executor = Executors.newFixedThreadPool(10);
    List<Future<Long>> futures = new ArrayList<>();
    
    for (int i = 0; i < 10; i++) {
        final int queryId = i;
        
        Future<Long> future = executor.submit(() -> {
            try (Connection conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/test_db", 
                    "testuser", "testpass")) {
                
                long startTime = System.currentTimeMillis();
                
                PreparedStatement stmt = conn.prepareStatement(
                    "SELECT * FROM products WHERE category_id = ? LIMIT 20"
                );
                stmt.setInt(1, queryId % 5 + 1);
                ResultSet rs = stmt.executeQuery();
                
                int count = 0;
                while (rs.next()) {
                    count++;
                }
                
                long executionTime = System.currentTimeMillis() - startTime;
                return executionTime;
                
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        });
        
        futures.add(future);
    }
    
    executor.shutdown();
    executor.awaitTermination(10, TimeUnit.SECONDS);
    
    for (int i = 0; i < futures.size(); i++) {
        try {
            Long executionTime = futures.get(i).get();
            assertTrue(executionTime < 200, 
                "Concurrent query " + i + " took " + executionTime + "ms");
        } catch (ExecutionException e) {
            fail("Query execution failed: " + e.getMessage());
        }
    }
}


@Test
public void testLargeResultSetPerformance() throws SQLException {
    int pageSize = 1000;
    int maxPages = 5;
    
    for (int page = 0; page < maxPages; page++) {
        long startTime = System.currentTimeMillis();
        int offset = page * pageSize;
        
        PreparedStatement stmt = connection.prepareStatement(
            "SELECT id, name, email FROM users ORDER BY id LIMIT ? OFFSET ?"
        );
        stmt.setInt(1, pageSize);
        stmt.setInt(2, offset);
        
        ResultSet rs = stmt.executeQuery();
        
        long executionTime = System.currentTimeMillis() - startTime;
        
        assertTrue(executionTime < 150, 
            "Page " + page + " took " + executionTime + "ms");
        
        int rowCount = 0;
        while (rs.next()) {
            rowCount++;
        }
        
        if (rowCount == 0) break; // No more data
    }
}


@Test
public void testComplexAggregationPerformance() throws SQLException {
    long startTime = System.currentTimeMillis();
    
    String query = """
        SELECT 
            DATE(o.created_at) as order_date,
            COUNT(*) as order_count,
            SUM(o.total_amount) as daily_revenue,
            AVG(o.total_amount) as avg_order_value,
            COUNT(DISTINCT o.user_id) as unique_customers
        FROM orders o
        WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        GROUP BY DATE(o.created_at)
        ORDER BY order_date DESC
        """;
    
    PreparedStatement stmt = connection.prepareStatement(query);
    ResultSet rs = stmt.executeQuery();
    
    long executionTime = System.currentTimeMillis() - startTime;
    
    assertTrue(executionTime < 300, 
        "Complex query took " + executionTime + "ms");
    
    int rowCount = 0;
    while (rs.next()) {
        rowCount++;
        
        // Validate data consistency
        int orderCount = rs.getInt("order_count");
        double dailyRevenue = rs.getDouble("daily_revenue");
        double avgOrderValue = rs.getDouble("avg_order_value");
        int uniqueCustomers = rs.getInt("unique_customers");
        
        assertTrue(orderCount > 0);
        assertTrue(dailyRevenue > 0);
        assertTrue(avgOrderValue > 0);
        assertTrue(uniqueCustomers > 0);
    }
    
    assertTrue(rowCount > 0, "Should return at least one row");
}
      

import time
import threading
from concurrent.futures import ThreadPoolExecutor, as_completed

def test_query_execution_time(self):
    start_time = time.time()
    
    query = """
        SELECT u.id, u.name, u.email, COUNT(o.id) as order_count
        FROM users u
        LEFT JOIN orders o ON u.id = o.user_id
        WHERE u.status = 'active'
        GROUP BY u.id, u.name, u.email
        ORDER BY order_count DESC
        LIMIT 50
    """
    
    with self.connection.cursor() as cursor:
        cursor.execute(query)
        results = cursor.fetchall()
    
    execution_time = (time.time() - start_time) * 1000  # Convert to ms
    
    assert execution_time < 100, \
        f"Query took {execution_time:.2f}ms, expected < 100ms"
    assert len(results) > 0, "Should return at least one row"


def test_concurrent_queries(self):
    def execute_query(query_id):
        connection = pymysql.connect(
            host='localhost',
            user='testuser',
            password='testpass',
            database='test_db'
        )
        
        try:
            start_time = time.time()
            
            with connection.cursor() as cursor:
                cursor.execute(
                    "SELECT * FROM products WHERE category_id = %s LIMIT 20",
                    (query_id % 5 + 1,)
                )
                results = cursor.fetchall()
            
            execution_time = (time.time() - start_time) * 1000
            return execution_time, len(results)
            
        finally:
            connection.close()
    
    # Execute 10 concurrent queries
    with ThreadPoolExecutor(max_workers=10) as executor:
        futures = [executor.submit(execute_query, i) for i in range(10)]
        
        for i, future in enumerate(as_completed(futures)):
            execution_time, row_count = future.result()
            assert execution_time < 200, \
                f"Concurrent query {i} took {execution_time:.2f}ms"
            assert row_count > 0


def test_large_result_set_performance(self):
    page_size = 1000
    max_pages = 5
    
    for page in range(max_pages):
        start_time = time.time()
        offset = page * page_size
        
        with self.connection.cursor() 