Database Package

Comprehensive database layer for the VSCode Extensions Build & Publish System with PostgreSQL and Redis support.

Overview

This package provides a robust database solution with:

• PostgreSQL Integration: Full Prisma ORM support with connection pooling
• Redis Caching: High-performance caching and pub/sub capabilities
• Migration System: Automated database schema migrations
• Health Monitoring: Real-time health checks for all database services
• Backup & Recovery: Database backup and restore functionality
• Type Safety: Full TypeScript support with Zod validation

Features

Database Management

• Connection Pooling: Optimized PostgreSQL connection management
• Query Builder: Type-safe query construction
• Transaction Support: ACID transaction management
• Health Monitoring: Real-time database health checks

Redis Integration

• Caching: Intelligent caching with TTL support
• Pub/Sub: Message publishing and subscription
• Distributed Locking: Coordinated resource locking
• Queue Operations: Priority-based job queuing

Migration System

• Auto-Generated: Prisma-based schema generation
• Version Control: Track migration history and rollbacks
• Rollback Support: Safe migration rollback capabilities
• CLI Tools: Command-line migration management

Installation

This package is part of the monorepo and should be installed via workspace:

npm install @packages/database

Configuration

Environment Variables

Create a .env file in the package root:

# Database Configuration
DATABASE_URL="postgresql://localhost:5432/vscode_extensions"
DB_HOST="localhost"
DB_PORT=5432
DB_NAME="vscode_extensions"
DB_USER="postgres"
DB_PASSWORD="password"
DB_SSL="false"

# Redis Configuration
REDIS_HOST="localhost"
REDIS_PORT=6379
REDIS_DB="0"
REDIS_PASSWORD=""

# Connection Pooling
DB_MAX_CONNECTIONS=20
DB_MIN_CONNECTIONS=5
DB_IDLE_TIMEOUT_MS=30000
DB_CONNECTION_TIMEOUT_MS=2000

Prisma Configuration

The package uses Prisma for ORM. The database schema is defined in prisma/schema.prisma and includes:

• Extensions: Extension metadata and configuration
• Builds: Build records and artifacts
• Security Scans: Vulnerability and security scan results
• Test Results: Test execution results and coverage
• Users: User management and permissions
• Audit Logs: System audit trails

Usage

Basic Database Operations

import { DatabaseManager } from '@packages/database';

// Initialize database manager
const dbManager = DatabaseManager.getInstance({
  url: process.env.DATABASE_URL,
  maxConnections: 20,
});

// Connect to database
await dbManager.connect();

// Get database client
const client = dbManager.getClient();

// Execute queries
const extensions = await client.extension.findMany();
const builds = await client.build.findMany({
  include: { extension: true },
});

// Disconnect
await dbManager.disconnect();

Redis Operations

import { RedisManager } from '@packages/database';

// Initialize Redis manager
const redisManager = RedisManager.getInstance({
  host: process.env.REDIS_HOST,
  port: parseInt(process.env.REDIS_PORT),
});

// Connect to Redis
await redisManager.connect();

// Cache operations
await redisManager.cache('extension:123', extensionData, 3600);
const cachedData = await redisManager.getCached('extension:123');

// Pub/Sub
await redisManager.publish('builds', JSON.stringify(buildEvent));
await redisManager.subscribe('builds', (channel, message) => {
  console.log(`Received: ${message}`);
});

// Disconnect
await redisManager.disconnect();

Health Monitoring

import { HealthChecker } from '@packages/database';

// Initialize health checker
const healthChecker = new HealthChecker(dbManager, redisManager);

// Perform health check
const healthStatus = await healthChecker.performHealthCheck();
console.log('Overall status:', healthStatus.overall);

// Start periodic monitoring
healthChecker.startPeriodicChecks(30000); // 30 seconds

// Generate comprehensive health report
const report = await healthChecker.generateHealthReport();

Migration Management

import { MigrationManager } from '@packages/database';

// Initialize migration manager
const migrationManager = new MigrationManager(client, './migrations');

// Run migrations
await migrationManager.migrate('up');

// Create new migration
const migrationId = await migrationManager.createMigration('add_new_field');

// Check migration status
const status = await migrationManager.getStatus();

// Rollback migrations
await migrationManager.rollback(2);

CLI Tools

The package includes command-line tools for database management:

Migration CLI

# Run migrations
npm run migrate

# Create new migration
npm run migrate --create add_user_table

# Rollback migrations
npm run migrate --down --steps 2

# Show migration status
npm run migrate --status

# Reset database
npm run migrate --reset --force

Seed CLI

# Seed all data
npm run seed

# Seed only extensions
npm run seed --extensions-only

# Reset database before seeding
npm run seed --reset

API Reference

DatabaseManager

Main class for PostgreSQL database management.

Methods

• connect(): Promise<void> - Connect to database
• disconnect(): Promise<void> - Disconnect from database
• getClient(): PrismaClient - Get Prisma client instance
• healthCheck(): Promise<DatabaseStatus> - Perform health check
• executeQuery<T>(query: string, params?: any[]): Promise<T[]> - Execute raw SQL query
• executeTransaction<T>(callback: (tx: PrismaClient) => Promise<T>): Promise<T> - Execute transaction
• vacuum(table?: string): Promise<void> - Run VACUUM
• analyze(table?: string): Promise<void> - Run ANALYZE

RedisManager

Main class for Redis operations.

Methods

• connect(): Promise<void> - Connect to Redis
• disconnect(): Promise<void> - Disconnect from Redis
• getClient(): Redis - Get main Redis client
• get(key: string): Promise<string | null> - Get value
• set(key: string, value: string, ttl?: number): Promise<void> - Set value with optional TTL
• cache(key: string, data: any, ttl?: number): Promise<void> - Cache serialized data
• getCached<T>(key: string): Promise<T | null> - Get and parse cached data
• publish(channel: string, message: string): Promise<number> - Publish message
• subscribe(channel: string, callback: Function): Promise<void> - Subscribe to channel
• acquireLock(key: string, ttl?: number): Promise<string | null> - Acquire distributed lock
• releaseLock(key: string, lockId: string): Promise<boolean> - Release distributed lock

HealthChecker

Main class for health monitoring.

Methods

• performHealthCheck(): Promise<HealthCheckResult> - Perform comprehensive health check
• startPeriodicChecks(interval?: number): void - Start periodic monitoring
• stopPeriodicChecks(): void - Stop periodic monitoring
• getLastResult(): HealthCheckResult | null - Get last health check result
• checkDatabaseOnly(): Promise<DatabaseStatus> - Check only database
• checkRedisOnly(): Promise<RedisStatus> - Check only Redis
• generateHealthReport(): Promise<any> - Generate comprehensive health report

Development

Database Schema Changes

1. Modify prisma/schema.prisma
2. Run migration generation:

   npx prisma migrate dev

3. Update TypeScript types if needed
4. Test changes

Adding New Queries

// Use Prisma client with auto-completion
const extension = await client.extension.findUnique({
  where: { id: extensionId },
  include: {
    builds: {
      orderBy: { startedAt: 'desc' },
      take: 10,
    },
  },
});

Testing

# Run tests
npm test

# Run tests in watch mode
npm run test:watch

# Run tests with coverage
npm run test:coverage

Performance

Connection Pooling

• Max Connections: Default 20, configurable via DB_MAX_CONNECTIONS
• Min Connections: Default 5, configurable via DB_MIN_CONNECTIONS
• Idle Timeout: Default 30 seconds, configurable via DB_IDLE_TIMEOUT_MS

Caching Strategy

• Default TTL: 1 hour for most cache entries
• Redis Memory Usage: Monitor via health checks
• Cache Invalidation: Time-based and manual invalidation

Query Optimization

• Connection Reuse: Reuse database connections for multiple queries
• Transaction Batching: Group related operations in transactions
• Index Usage: Monitor index usage via health checks
• Slow Query Detection: Automated slow query identification

Security

Connection Security

• SSL/TLS: Enable database encryption via DB_SSL=true
• Password Protection: Use environment variables for sensitive data
• Connection Validation: Validate connection parameters on startup

Data Validation

• Zod Schemas: Comprehensive data validation for all inputs
• SQL Injection Prevention: Use parameterized queries
• Access Control: Row-level security via database policies
• Audit Logging: Complete audit trail of database operations

Monitoring

Health Metrics

• Database Connection: Active connections and pool status
• Query Performance: Response times and slow query detection
• Redis Performance: Memory usage and key distribution
• System Health: Overall system status and trends

Logging

• Structured Logging: Consistent logging format across all operations
• Error Tracking: Detailed error logging with context
• Performance Metrics: Query timing and resource usage
• Audit Trails: Complete audit logs for compliance

Backup & Recovery

Automated Backups

// Schedule regular backups
const scheduleBackup = async () => {
  await databaseManager.backup('/backups/daily/');
};

// Perform manual backup
await databaseManager.backup('/backups/manual/');

Recovery Procedures

// Restore from backup
await databaseManager.restore('/backups/daily/latest.sql');

Troubleshooting

Common Issues

1. Connection Errors: Check database connectivity and credentials
2. Migration Failures: Validate migration SQL and database permissions
3. Redis Connection: Verify Redis server status and network connectivity
4. Performance Issues: Monitor query times and connection pool status

Debug Mode

Enable debug logging:

const dbManager = DatabaseManager.getInstance({
  ...config,
  logLevel: 'debug',
});

Health Check Diagnostics

# Check database health
npm run migrate --status

# Generate health report
node dist/health-checker.js

License

MIT License - see LICENSE file for details.