Database MCP Integration

What is Database MCP?

Database MCP (Model Context Protocol) provides intelligent database interactions for testing and development. It enables AI to understand your database schema, generate test data, and create comprehensive database testing workflows.

Core Capabilities

• 🧠 Schema Intelligence - Understands your database structure
• 🧪 Smart Test Data - Generates realistic, consistent data
• 📊 Query Optimization - Suggests efficient queries and indexes
• 🔄 Migration Testing - Validates database changes safely

How Database MCP Works 🔧

The Workflow Magic

1. Schema Discovery - Analyzes tables, relationships, constraints
2. Data Generation - Creates realistic test datasets
3. Test Orchestration - Manages test database lifecycle
4. Performance Monitoring - Tracks query performance
5. Automatic Cleanup - Resets state between tests

When to Use Database MCP

Perfect For

• Database testing with realistic data
• Migration validation and rollback testing
• Performance testing with large datasets
• Integration testing with complex workflows
• Data pipeline testing and validation

Alternative Approaches

• Simple unit tests → Use in-memory databases or mocks
• Production debugging → Use direct database tools
• Schema design → Use dedicated database design tools

Setup and Configuration

Database Connection

// database-mcp.config.js
export default {
  connections: {
    test: {
      host: 'localhost',
      port: 5432,
      database: 'app_test',
      username: 'test_user',
      password: 'test_password',
      pool: { min: 2, max: 10 }
    },
    staging: {
      host: 'staging-db.company.com',
      port: 5432,
      database: 'app_staging',
      username: process.env.STAGING_DB_USER,
      password: process.env.STAGING_DB_PASSWORD
    }
  },
  
  // Schema introspection settings
  schema: {
    autoDiscover: true,
    includeTables: ['users', 'orders', 'products'],
    excludeTables: ['migrations', 'sessions'],
    trackRelationships: true
  },
  
  // Test data generation
  testData: {
    locale: 'en_US',
    seed: 12345, // For reproducible data
    defaultRowCount: 100
  }
};

Project Structure

tests/
├── database/
│   ├── config/
│   │   └── database-mcp.config.js
│   ├── fixtures/
│   │   ├── users.json
│   │   └── products.sql
│   ├── migrations/
│   │   └── test-schema.sql
│   └── integration/
│       ├── user-orders.test.js
│       └── performance.test.js
├── utils/
│   └── database-helpers.js
└── setup/
    └── test-db-setup.js

Core Features

1. Intelligent Schema Analysis

Database MCP automatically understands your database structure:

// Discovered schema example
const schemaAnalysis = {
  tables: {
    users: {
      columns: {
        id: { type: 'SERIAL', primaryKey: true },
        email: { type: 'VARCHAR(255)', unique: true, nullable: false },
        name: { type: 'VARCHAR(100)', nullable: false },
        created_at: { type: 'TIMESTAMP', default: 'NOW()' },
        updated_at: { type: 'TIMESTAMP', default: 'NOW()' }
      },
      relationships: {
        orders: { type: 'hasMany', foreignKey: 'user_id' }
      },
      indexes: ['email_idx', 'created_at_idx']
    },
    
    orders: {
      columns: {
        id: { type: 'SERIAL', primaryKey: true },
        user_id: { type: 'INTEGER', references: 'users.id' },
        total: { type: 'DECIMAL(10,2)', nullable: false },
        status: { type: 'VARCHAR(20)', default: 'pending' },
        created_at: { type: 'TIMESTAMP', default: 'NOW()' }
      },
      relationships: {
        user: { type: 'belongsTo', foreignKey: 'user_id' },
        orderItems: { type: 'hasMany', foreignKey: 'order_id' }
      }
    }
  }
};

2. Smart Test Data Generation

Generate realistic data that respects constraints and relationships:

// AI-Generated Test Data
describe('User Order Flow', () => {
  beforeEach(async () => {
    // Database MCP generates consistent test data
    await generateTestData({
      users: {
        count: 50,
        profiles: {
          premium: 10,    // 10 premium users
          regular: 30,    // 30 regular users  
          inactive: 10    // 10 inactive users
        }
      },
      
      products: {
        count: 100,
        categories: ['electronics', 'books', 'clothing'],
        priceRange: { min: 10, max: 1000 }
      },
      
      orders: {
        count: 200,
        statusDistribution: {
          completed: 0.7,
          pending: 0.2,
          cancelled: 0.1
        },
        dateRange: { 
          start: '2024-01-01', 
          end: '2024-12-31' 
        }
      }
    });
  });

  test('calculates user statistics correctly', async () => {
    const stats = await getUserStatistics();
    
    expect(stats.totalUsers).toBe(50);
    expect(stats.premiumUsers).toBe(10);
    expect(stats.averageOrderValue).toBeGreaterThan(0);
    expect(stats.topCategory).toBeOneOf(['electronics', 'books', 'clothing']);
  });
});

3. Advanced Data Relationships

Database MCP understands and maintains referential integrity:

// Complex relationship testing
test('maintains referential integrity in complex scenarios', async () => {
  // Generate interconnected test data
  const testData = await generateTestData({
    users: { count: 10 },
    orders: { 
      count: 30,
      ensureAllUsersHaveOrders: true,
      minOrdersPerUser: 1,
      maxOrdersPerUser: 5
    },
    orderItems: {
      count: 100,
      ensureAllOrdersHaveItems: true,
      minItemsPerOrder: 1,
      maxItemsPerOrder: 10
    }
  });

  // Verify data consistency
  const orphanedOrders = await db.query(`
    SELECT COUNT(*) as count 
    FROM orders 
    WHERE user_id NOT IN (SELECT id FROM users)
  `);
  
  expect(orphanedOrders[0].count).toBe(0);

  // Verify business rules
  const orderTotals = await db.query(`
    SELECT o.id, o.total, SUM(oi.price * oi.quantity) as calculated_total
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    GROUP BY o.id, o.total
  `);

  orderTotals.forEach(order => {
    expect(order.total).toBeCloseTo(order.calculated_total, 2);
  });
});

Practical Testing Scenarios

1. User Registration Flow

// tests/integration/user-registration.test.js
describe('User Registration with Database MCP', () => {
  let testDb;

  beforeAll(async () => {
    testDb = await setupTestDatabase();
  });

  beforeEach(async () => {
    await testDb.reset(); // Clean slate for each test
    
    // Pre-populate with some existing users to test uniqueness
    await generateTestData({
      users: {
        count: 10,
        emailDomain: 'existing.com'
      }
    });
  });

  test('creates new user successfully', async () => {
    const newUser = {
      email: 'newuser@test.com',
      name: 'New User',
      password: 'securePassword123'
    };

    const createdUser = await userService.register(newUser);

    // Verify in database
    const dbUser = await testDb.users.findById(createdUser.id);
    expect(dbUser.email).toBe(newUser.email);
    expect(dbUser.name).toBe(newUser.name);
    expect(dbUser.password).not.toBe(newUser.password); // Should be hashed
    expect(dbUser.created_at).toBeInstanceOf(Date);
  });

  test('prevents duplicate email registration', async () => {
    const existingUser = await testDb.users.findFirst();
    
    const duplicateUser = {
      email: existingUser.email, // Same email
      name: 'Different Name',
      password: 'differentPassword'
    };

    await expect(userService.register(duplicateUser))
      .rejects.toThrow('Email already exists');

    // Verify no duplicate was created
    const userCount = await testDb.users.count({ 
      where: { email: existingUser.email } 
    });
    expect(userCount).toBe(1);
  });

  test('handles concurrent registration attempts', async () => {
    const userData = {
      email: 'concurrent@test.com',
      name: 'Concurrent User',
      password: 'password123'
    };

    // Simulate concurrent registration
    const registrationAttempts = Array(5).fill().map(() => 
      userService.register(userData)
    );

    const results = await Promise.allSettled(registrationAttempts);
    
    // Only one should succeed
    const successful = results.filter(r => r.status === 'fulfilled');
    const failed = results.filter(r => r.status === 'rejected');
    
    expect(successful).toHaveLength(1);
    expect(failed).toHaveLength(4);

    // Verify only one user was created
    const userCount = await testDb.users.count({ 
      where: { email: userData.email } 
    });
    expect(userCount).toBe(1);
  });
});

2. E-commerce Order Processing

// tests/integration/order-processing.test.js
describe('Order Processing Pipeline', () => {
  beforeEach(async () => {
    // Generate realistic e-commerce test data
    await generateTestData({
      users: {
        count: 20,
        profiles: {
          premium: 5,
          regular: 15
        }
      },
      
      products: {
        count: 50,
        categories: ['electronics', 'books', 'clothing'],
        inventory: { min: 0, max: 100 } // Some out of stock
      },
      
      discountCodes: {
        count: 10,
        types: ['percentage', 'fixed'],
        active: 8, // 2 expired codes
        usageLimit: { min: 1, max: 100 }
      }
    });
  });

  test('processes complete order successfully', async () => {
    const user = await testDb.users.findFirst({ where: { type: 'premium' } });
    const products = await testDb.products.findMany({ 
      where: { inventory: { gt: 5 } },
      limit: 3 
    });

    const orderData = {
      userId: user.id,
      items: products.map(p => ({
        productId: p.id,
        quantity: 2,
        price: p.price
      })),
      discountCode: 'PREMIUM10'
    };

    const order = await orderService.createOrder(orderData);

    // Verify order creation
    expect(order.id).toBeDefined();
    expect(order.status).toBe('pending');
    expect(order.userId).toBe(user.id);

    // Verify order items
    const orderItems = await testDb.orderItems.findMany({
      where: { orderId: order.id }
    });
    expect(orderItems).toHaveLength(3);

    // Verify inventory reduction
    for (const item of orderData.items) {
      const product = await testDb.products.findById(item.productId);
      const originalProduct = products.find(p => p.id === item.productId);
      
      expect(product.inventory).toBe(originalProduct.inventory - item.quantity);
    }

    // Verify discount application
    const discount = await testDb.discountCodes.findFirst({
      where: { code: 'PREMIUM10' }
    });
    expect(discount.usedCount).toBe(1);
  });

  test('handles out of stock scenario', async () => {
    const outOfStockProduct = await testDb.products.findFirst({
      where: { inventory: 0 }
    });

    const orderData = {
      userId: 1,
      items: [{
        productId: outOfStockProduct.id,
        quantity: 1,
        price: outOfStockProduct.price
      }]
    };

    await expect(orderService.createOrder(orderData))
      .rejects.toThrow('Product out of stock');

    // Verify no order was created
    const orderCount = await testDb.orders.count();
    expect(orderCount).toBe(0);
  });

  test('applies bulk discount correctly', async () => {
    const products = await testDb.products.findMany({
      where: { inventory: { gt: 10 } },
      limit: 1
    });

    const orderData = {
      userId: 1,
      items: [{
        productId: products[0].id,
        quantity: 10, // Bulk quantity
        price: products[0].price
      }]
    };

    const order = await orderService.createOrder(orderData);
    const expectedTotal = products[0].price * 10 * 0.9; // 10% bulk discount

    expect(order.total).toBeCloseTo(expectedTotal, 2);
  });
});

3. Performance Testing

// tests/performance/database-performance.test.js
describe('Database Performance Testing', () => {
  beforeAll(async () => {
    // Generate large dataset for performance testing
    await generateTestData({
      users: { count: 10000 },
      products: { count: 5000 },
      orders: { count: 50000 },
      orderItems: { count: 200000 }
    });
  });

  test('user lookup performance', async () => {
    const startTime = Date.now();
    
    // Test various lookup patterns
    const userById = await testDb.users.findById(5000);
    const userByEmail = await testDb.users.findFirst({
      where: { email: 'user5000@test.com' }
    });
    
    const endTime = Date.now();
    const executionTime = endTime - startTime;

    expect(executionTime).toBeLessThan(100); // Should be under 100ms
    expect(userById).toBeTruthy();
    expect(userByEmail).toBeTruthy();
  });

  test('complex query performance', async () => {
    const startTime = Date.now();

    // Complex aggregation query
    const result = await testDb.query(`
      SELECT 
        u.name,
        COUNT(o.id) as order_count,
        SUM(o.total) as total_spent,
        AVG(o.total) as avg_order_value
      FROM users u
      LEFT JOIN orders o ON u.id = o.user_id
      WHERE o.created_at >= $1
      GROUP BY u.id, u.name
      HAVING COUNT(o.id) > 5
      ORDER BY total_spent DESC
      LIMIT 100
    `, ['2024-01-01']);

    const endTime = Date.now();
    const executionTime = endTime - startTime;

    expect(executionTime).toBeLessThan(500); // Complex query under 500ms
    expect(result.length).toBeGreaterThan(0);
    expect(result[0]).toHaveProperty('total_spent');
  });

  test('index effectiveness', async () => {
    // Test with and without indexes
    await testDb.query('DROP INDEX IF EXISTS orders_user_id_idx');
    
    const startTimeWithoutIndex = Date.now();
    await testDb.query('SELECT * FROM orders WHERE user_id = $1', [1000]);
    const timeWithoutIndex = Date.now() - startTimeWithoutIndex;

    // Recreate index
    await testDb.query('CREATE INDEX orders_user_id_idx ON orders(user_id)');
    
    const startTimeWithIndex = Date.now();
    await testDb.query('SELECT * FROM orders WHERE user_id = $1', [1000]);
    const timeWithIndex = Date.now() - startTimeWithIndex;

    // Index should improve performance significantly
    expect(timeWithIndex).toBeLessThan(timeWithoutIndex * 0.5);
  });
});

Advanced Features

1. Migration Testing

// tests/migrations/migration-testing.test.js
describe('Database Migration Testing', () => {
  test('validates migration rollback safety', async () => {
    // Capture current state
    const originalData = await captureTableData(['users', 'orders']);
    
    // Apply migration
    await runMigration('2024_01_15_add_user_preferences');
    
    // Verify new structure
    const columns = await getTableColumns('users');
    expect(columns).toContain('preferences');
    
    // Test rollback
    await rollbackMigration('2024_01_15_add_user_preferences');
    
    // Verify data integrity after rollback
    const restoredData = await captureTableData(['users', 'orders']);
    expect(restoredData).toEqual(originalData);
  });

  test('validates data transformation in migration', async () => {
    // Create test data with old format
    await generateTestData({
      users: {
        count: 100,
        customFields: {
          old_status_field: ['active', 'inactive', 'pending']
        }
      }
    });

    // Apply migration that transforms data
    await runMigration('2024_01_20_normalize_user_status');

    // Verify transformation
    const users = await testDb.users.findMany();
    users.forEach(user => {
      expect(user.status).toBeOneOf(['active', 'inactive', 'pending']);
      expect(user.old_status_field).toBeUndefined();
    });
  });
});

2. Data Validation Testing

// tests/validation/data-validation.test.js
describe('Data Validation Rules', () => {
  test('enforces business rules across tables', async () => {
    // Generate data that should trigger validation rules
    await generateTestData({
      orders: {
        count: 10,
        customRules: {
          // Some orders with invalid business rules
          invalidDiscounts: 2, // Discount > order total
          futureOrders: 1,     // Order date in future
          negativeTotal: 1     // Negative total
        }
      }
    });

    // Run validation
    const validationResults = await validateDatabaseRules();

    expect(validationResults.errors).toHaveLength(4);
    expect(validationResults.errors).toContainEqual(
      expect.objectContaining({
        rule: 'discount_cannot_exceed_total',
        table: 'orders',
        count: 2
      })
    );
  });

  test('checks referential integrity', async () => {
    // Create orphaned records intentionally
    await testDb.query(`
      INSERT INTO order_items (order_id, product_id, quantity, price)
      VALUES (99999, 1, 1, 10.00)
    `);

    const integrityCheck = await checkReferentialIntegrity();
    
    expect(integrityCheck.violations).toHaveLength(1);
    expect(integrityCheck.violations[0]).toMatchObject({
      table: 'order_items',
      column: 'order_id',
      references: 'orders.id',
      violatingRecords: 1
    });
  });
});

3. Test Data Fixtures

// tests/fixtures/user-scenarios.js
export const userScenarios = {
  newCustomer: {
    users: { count: 1, profile: 'new' },
    orders: { count: 0 }
  },
  
  loyalCustomer: {
    users: { count: 1, profile: 'premium' },
    orders: { 
      count: 20,
      statusDistribution: { completed: 0.9, returned: 0.1 },
      dateRange: { months: 12 }
    },
    reviews: { count: 15, averageRating: 4.5 }
  },
  
  problematicCustomer: {
    users: { count: 1, profile: 'regular' },
    orders: {
      count: 10,
      statusDistribution: { 
        completed: 0.3,
        cancelled: 0.4,
        disputed: 0.3
      }
    },
    supportTickets: { count: 8, priority: 'high' }
  }
};

// Usage in tests
test('handles loyal customer workflow', async () => {
  await loadFixture('loyalCustomer');
  
  const customer = await testDb.users.findFirst();
  const stats = await getCustomerStats(customer.id);
  
  expect(stats.orderCount).toBe(20);
  expect(stats.averageRating).toBeCloseTo(4.5, 1);
  expect(stats.loyaltyTier).toBe('premium');
});

Integration with Other Tools

Playwright MCP Integration

// Combine Database MCP with Playwright for full-stack testing
test('end-to-end order flow with database validation', async ({ page }) => {
  // Setup test data
  const testUser = await generateTestData({
    users: { count: 1, profile: 'premium' },
    products: { count: 5, category: 'electronics' }
  });

  // UI interaction via Playwright
  await page.goto('/login');
  await page.fill('[data-testid="email"]', testUser.users[0].email);
  await page.fill('[data-testid="password"]', 'testPassword');
  await page.click('[data-testid="login-button"]');

  await page.goto('/products');
  await page.click('[data-product-id="1"]');
  await page.click('button:text("Add to Cart")');
  await page.click('button:text("Checkout")');

  // Wait for order completion
  await expect(page.locator('.order-success')).toBeVisible();

  // Validate database state
  const orders = await testDb.orders.findMany({
    where: { userId: testUser.users[0].id }
  });
  
  expect(orders).toHaveLength(1);
  expect(orders[0].status).toBe('completed');
});

TestCraft Integration

// Use Database MCP to setup data for TestCraft-generated tests
// TestCraft generates test structure, Database MCP provides data

describe('TestCraft + Database MCP: Order Service', () => {
  beforeEach(async () => {
    // Database MCP sets up consistent test data
    await generateTestData({
      users: { count: 10 },
      products: { count: 20, inStock: 15 },
      orders: { count: 5, status: 'completed' }
    });
  });

  // TestCraft-generated test structure with Database MCP data
  test('calculates order total correctly', async () => {
    const user = await testDb.users.findFirst();
    const products = await testDb.products.findMany({ limit: 3 });
    
    const orderData = {
      userId: user.id,
      items: products.map(p => ({
        productId: p.id,
        quantity: 2,
        price: p.price
      }))
    };

    const order = await orderService.calculateTotal(orderData);
    const expectedTotal = products.reduce((sum, p) => sum + (p.price * 2), 0);
    
    expect(order.total).toBeCloseTo(expectedTotal, 2);
  });
});

Best Practices 📋

✅ Do's

• Use realistic test data that matches production patterns
• Reset database state between tests for isolation
• Monitor query performance during test execution
• Clean up resources in afterAll/afterEach hooks
• Use separate test databases - never test against production

❌ Don'ts

• Don't hardcode IDs - use generated or found data
• Don't ignore async operations - await all database calls
• Don't leave test data in shared databases
• Don't rely on test execution order - ensure test independence

Troubleshooting Common Issues

Connection Issues

// Handle connection failures gracefully
beforeAll(async () => {
  try {
    await testDb.connect();
    await testDb.migrate();
  } catch (error) {
    console.error('Database setup failed:', error);
    throw new Error(`Cannot connect to test database: ${error.message}`);
  }
});

Data Generation Problems

// Debug data generation
test.only('debug data generation', async () => {
  const result = await generateTestData({
    users: { count: 5 },
    debug: true // Enable detailed logging
  });
  
  console.log('Generated data:', result);
  
  const users = await testDb.users.findMany();
  console.log('Database users:', users);
});

Performance Issues

// Optimize test database for speed
beforeAll(async () => {
  // Disable unnecessary features for testing
  await testDb.query('SET synchronous_commit = OFF');
  await testDb.query('SET fsync = OFF');
  await testDb.query('SET checkpoint_segments = 32');
});

Next Steps

Ready to enhance your database testing workflow?

• 🎭 Try Playwright MCP - Browser + database testing
• 🎨 Explore TestCraft Extension - Automated test generation
• 📚 Learn MCP Fundamentals - Core MCP concepts

Pro Tip

Start with simple database operations before testing complex business logic. Let Database MCP learn your data patterns gradually! 🎯

Key Takeaways 📝

• 🗄️ Database MCP provides intelligent database testing workflows
• 🧪 Realistic test data generation with relationship awareness
• 📊 Performance testing capabilities with large datasets
• 🔄 Migration testing for safe database changes
• 🔧 Integration-ready with other testing tools

Transform your database testing with intelligent data management! 🚀