Playwright Database Helper

A comprehensive toolkit for working with databases in combination with Playwright testing framework. This package provides utilities for database operations, test data seeding, database state management, and database schema export during automated testing.

Features

• 🎭 Playwright Integration: Seamless integration with Playwright test fixtures
• 🗄️ Multi-Database Support: MySQL (fully implemented), PostgreSQL, SQLite, and MongoDB
• 📋 Schema Export: Export database schema to text or JSON format (NEW!)
• 🧪 Test Utilities: Data seeding, table backup/restore, and condition waiting
• 🔧 Type Safe: Full TypeScript support with comprehensive type definitions
• 🚀 Easy Setup: Simple configuration and minimal boilerplate
• 📦 Environment Driven: Automatic configuration from environment variables
• 🎯 Production Ready: Based on proven solution from real QA automation projects
• 💡 IntelliSense Support: Full autocomplete and type hints for database methods
• ⚡ Console & Test Usage: Use from command line or within Playwright tests

Installation

npm install playwright-db-helper

Database Drivers

Install the appropriate database driver for your database:

# MySQL (recommended - fully implemented)
npm install mysql2

# PostgreSQL (basic support)
npm install pg @types/pg

# SQLite (basic support)
npm install sqlite3

# MongoDB (basic support)
npm install mongodb

Quick Start

Environment Configuration

Create a .env file:

# Database Configuration
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_password
DB_NAME=your_database
DB_PORT=3306

# Alternative naming (also supported)
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=your_password
MYSQL_DATABASE=your_database
MYSQL_PORT=3306

Basic Usage with Enhanced Fixtures

import { testWithDB, DatabaseUtils } from 'playwright-db-helper';
import { expect } from '@playwright/test';

testWithDB('should create and verify user', async ({ dbPage }) => {
  // Clean up before test
  await dbPage.db.cleanTestData(['users']);
  
  // Insert test data
  await dbPage.db.insert('users', [{
    name: 'John Doe',
    email: '[email protected]',
    age: 30
  }]);

  // Navigate to your application
  await dbPage.goto('https://yourapp.com/users');
  
  // Verify the data appears in UI
  await expect(dbPage.locator('text=John Doe')).toBeVisible();
  
  // Verify in database
  const users = await dbPage.db.select('users', { email: '[email protected]' });
  expect(users[0].name).toBe('John Doe');
});

Manual Setup Approach

import { setupDatabaseExtension, DatabaseUtils } from 'playwright-db-helper';
import { test, expect } from '@playwright/test';

test('manual database setup', async ({ page }) => {
  // Set up database extension
  const dbPage = setupDatabaseExtension(page);

  // Clean and seed data
  await dbPage.db.deleteAll('products');
  await dbPage.db.insert('products', [
    { name: 'Laptop', price: 999.99, category: 'Electronics' },
    { name: 'Mouse', price: 29.99, category: 'Electronics' }
  ]);

  // Test your application
  await dbPage.goto('https://yourapp.com/products');
  await expect(dbPage.locator('text=Laptop')).toBeVisible();
  
  // Verify database state
  const productCount = await DatabaseUtils.countRecords(dbPage.db, 'products');
  expect(productCount).toBe(2);
});

Database Fixtures

import { createDatabaseFixture } from 'playwright-db-helper';

const dbConfig = {
  type: 'mysql' as const,
  host: 'localhost',
  database: 'test_db',
  username: 'root',
  password: 'password'
};

const test = createDatabaseFixture(dbConfig);

test('using database fixture', async ({ db, page }) => {
  await db.cleanTestData(['orders']);
  
  await db.insert('orders', [{
    customer_id: 1,
    total: 99.99,
    status: 'pending'
  }]);
  
  // Your test logic here...
});

💡 IntelliSense Support

This package provides full TypeScript IntelliSense support for all database methods:

✅ Automatic Type Detection

import { setupDatabaseExtension } from 'playwright-db-helper';

test('test with intellisense', async ({ page: initialPage }) => {
  const page = setupDatabaseExtension(initialPage);
  
  // IntelliSense shows all available methods when you type page.db.
  await page.db.cleanTestData(['users']); // ✅ Full autocomplete
  await page.db.insert('users', [{ name: 'John' }]); // ✅ Parameter hints
  const users = await page.db.select('users'); // ✅ Return type inference
});

🔧 Manual Type Declaration (if needed)

import { setupDatabaseExtension, PageWithDatabase } from 'playwright-db-helper';

test('test with explicit types', async ({ page: initialPage }) => {
  const page: PageWithDatabase = setupDatabaseExtension(initialPage);
  
  // Guaranteed IntelliSense support
  await page.db.cleanTestData(['users']);
});

📚 Available Methods with IntelliSense

• cleanTestData(tables: string[]): Promise<void> - Clean multiple tables
• deleteAll(table: string): Promise<void> - Delete all records from table
• insert(table: string, data: Record<string, any>[]): Promise<void> - Insert records
• select(table: string, conditions?: Record<string, any>): Promise<any[]> - Select records
• update(table: string, data: Record<string, any>, where: Record<string, any>): Promise<void> - Update records
• delete(table: string, where: Record<string, any>): Promise<any> - Delete specific records
• query(sql: string, params?: any[]): Promise<any> - Execute custom SQL
• connect(): Promise<void> - Connect to database
• disconnect(): Promise<void> - Disconnect from database

See INTELLISENSE_GUIDE.md for troubleshooting and advanced setup.

API Reference

Enhanced Page Methods (dbPage.db)

interface DatabaseMethods {
  // Basic operations
  deleteAll(table: string): Promise<void>;
  insert(table: string, records: Record<string, any>[]): Promise<void>;
  select(table: string, conditions?: Record<string, any>): Promise<any[]>;
  update(table: string, data: Record<string, any>, conditions: Record<string, any>): Promise<void>;
  query(sql: string, params?: any[]): Promise<any>;
  
  // Test utilities
  cleanTestData(tables: string[]): Promise<void>;
}

Database Helper Class

const dbHelper = new PlaywrightDatabaseHelper({
  type: 'mysql',
  host: 'localhost',
  database: 'test_db',
  username: 'root',
  password: 'password'
});

await dbHelper.connect();
await dbHelper.insert('users', [{ name: 'John', email: '[email protected]' }]);
await dbHelper.disconnect();

Utility Functions

Generate Test Data

import { DatabaseUtils } from 'playwright-db-helper';

const testUsers = DatabaseUtils.generateTestData({
  name: (index: number) => `User ${index}`,
  email: 'user{{index}}@test.com',
  age: () => DatabaseUtils.generateRandomData().randomNumber(20, 60),
  department: (index: number) => ['Engineering', 'Sales', 'Marketing'][index % 3]
}, 10);

await dbPage.db.insert('users', testUsers);

Random Data Generators

const random = DatabaseUtils.generateRandomData();

const userData = {
  name: random.randomString(10),
  email: random.randomEmail(),
  phone: random.randomPhone(),
  age: random.randomNumber(18, 65),
  birthDate: random.randomDate(),
  isActive: random.randomBoolean()
};

Wait for Conditions

// Wait for async database operations
await DatabaseUtils.waitForCondition(async () => {
  const count = await DatabaseUtils.countRecords(dbPage.db, 'processed_orders');
  return count > 0;
}, 5000); // timeout in milliseconds

Backup and Restore

// Backup before test
const backup = await DatabaseUtils.backupTable(dbPage.db, 'users');

// Run test operations...
await dbPage.db.insert('users', [{ name: 'Test User' }]);

// Restore after test
await DatabaseUtils.restoreTable(dbPage.db, 'users', backup);

Advanced Usage

Environment-based Configuration

import { DatabaseUtils } from 'playwright-db-helper';

// Automatically uses environment variables
const config = DatabaseUtils.getEnvConfig();

const dbPage = setupDatabaseExtension(page, {
  type: 'mysql',
  ...config
});

Multiple Database Connections

const userDbPage = setupDatabaseExtension(page, userDbConfig);
const logDbPage = setupDatabaseExtension(page, logDbConfig);

await userDbPage.db.insert('users', userData);
await logDbPage.db.insert('audit_logs', logData);

Complex Test Scenarios

testWithDB('complex billing scenario', async ({ dbPage }) => {
  // Set up related data with foreign keys
  await dbPage.db.cleanTestData(['billing_entries', 'jobs', 'candidates', 'clients']);
  
  // Disable foreign key checks temporarily
  await dbPage.db.query('SET FOREIGN_KEY_CHECKS = 0');
  
  await dbPage.db.insert('clients', [{ id: 999, name: 'Test Client' }]);
  await dbPage.db.insert('jobs', [{ id: 999, client_id: 999, title: 'Developer' }]);
  await dbPage.db.insert('candidates', [{ id: 999, name: 'John Doe' }]);
  
  // Re-enable foreign key checks
  await dbPage.db.query('SET FOREIGN_KEY_CHECKS = 1');
  
  // Test your complex business logic...
  await dbPage.goto('/admin/billing');
  
  // Verify complex database state
  const billingEntries = await dbPage.db.query(`
    SELECT be.*, c.name as client_name, j.title as job_title 
    FROM billing_entries be
    JOIN jobs j ON be.job_id = j.id
    JOIN clients c ON j.client_id = c.id
    WHERE be.candidate_id = ?
  `, [999]);
  
  expect(billingEntries).toHaveLength(1);
});

🗄️ Database Schema Export (NEW!)

Export Schema from Tests

import { setupDatabaseExtension, PageWithDatabase } from 'playwright-db-helper';

test('export database schema', async ({ page: initialPage }) => {
  const page: PageWithDatabase = setupDatabaseExtension(initialPage);
  
  // Export schema as object for analysis
  const schemas = await page.db.exportSchema();
  console.log(`Found ${schemas.length} tables`);
  
  // Save schema to file (text format)
  await page.db.dumpSchema({
    outputFile: 'database-schema.txt',
    format: 'text'
  });
  
  // Save as JSON for programmatic use
  await page.db.dumpSchema({
    outputFile: 'database-schema.json',
    format: 'json'
  });
});

Export Schema from Console

# Basic usage
npm run export-schema -- -d my_database -u root -p password

# With custom output
npm run export-schema -- -d test_db -o schema.json -f json

# Using environment variables
DB_NAME=my_db DB_USER=admin npm run export-schema

# Show help
npm run export-schema -- --help

Schema Analysis Example

test('analyze database structure', async ({ page: initialPage }) => {
  const page = setupDatabaseExtension(initialPage);
  const schemas = await page.db.exportSchema();
  
  // Calculate statistics
  const stats = {
    totalTables: schemas.length,
    totalColumns: schemas.reduce((sum, table) => sum + table.columns.length, 0),
    largestTable: schemas.reduce((largest, table) => 
      table.columns.length > largest.columns.length ? table : largest
    )
  };
  
  console.log('Database Statistics:', stats);
});

Configuration Examples

MySQL (Fully Supported)

const config = {
  type: 'mysql' as const,
  host: 'localhost',
  port: 3306,
  database: 'myapp_test',
  username: 'root',
  password: 'password'
};

PostgreSQL (Basic Support)

const config = {
  type: 'postgresql' as const,
  host: 'localhost',
  port: 5432,
  database: 'myapp_test',
  username: 'postgres',
  password: 'password'
};

Using Environment Variables

// Automatically detected from .env file
const config = {
  type: 'mysql' as const,
  ...DatabaseUtils.getEnvConfig()
};

Best Practices

1. Use Enhanced Fixtures: Always use testWithDB or createDatabaseFixture for automatic connection management
2. Clean Up Properly: Use cleanTestData() at the beginning of each test
3. Environment Variables: Store database credentials in .env files
4. Test Isolation: Each test should clean up its own data
5. Foreign Keys: Use DatabaseUtils.cleanTablesInOrder() for tables with foreign key constraints

Real-World Integration

This package is based on a proven solution used in production QA automation projects. It has been tested with:

• Complex multi-table relationships
• Large datasets (1000+ records)
• Concurrent test execution
• CI/CD pipelines
• Docker containerized databases

Migration Guide

See INTEGRATION_GUIDE.md for detailed migration instructions from existing database testing setups.

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

ISC

Changelog

1.0.0

• Initial release with full MySQL support
• Playwright fixtures and page extensions
• Utility functions for test data management
• Environment-based configuration
• TypeScript support with comprehensive types
• Real-world tested implementation