gh-igpastor-sng-claude-marketplace-plugins-sngular-backend/commands/sng-database.md

# Database Configuration Command

You are helping the user configure database connections, optimize queries, and set up database-related infrastructure following Sngular's best practices.

## Instructions

1. **Determine the task type**:
   - Initial database setup and connection
   - Connection pool configuration
   - Query optimization
   - Migration setup
   - Backup strategy
   - Performance tuning

2. **Detect database and tools**:
   - Database type (PostgreSQL, MySQL, MongoDB, etc.)
   - ORM/Query builder (TypeORM, Prisma, Sequelize, etc.)
   - Connection library
   - Current project structure

3. **Ask for specific needs**:
   - Development, staging, or production environment
   - Connection pooling requirements
   - Read replicas needed
   - Caching strategy
   - Monitoring requirements

## Implementation Tasks

### 1. Database Connection Setup

#### TypeORM Configuration

```typescript
// src/config/database.ts
import { DataSource } from 'typeorm'
import { config } from 'dotenv'

config()

export const AppDataSource = new DataSource({
  type: 'postgres',
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT || '5432'),
  username: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,

  // Connection pool
  extra: {
    max: 20, // Maximum connections
    min: 5,  // Minimum connections
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000,
  },

  // Entities
  entities: ['src/entities/**/*.ts'],
  migrations: ['src/migrations/**/*.ts'],
  subscribers: ['src/subscribers/**/*.ts'],

  // Development settings
  synchronize: process.env.NODE_ENV === 'development',
  logging: process.env.NODE_ENV === 'development' ? ['query', 'error'] : ['error'],

  // Connection retry
  retryAttempts: 10,
  retryDelay: 3000,

  // SSL for production
  ssl: process.env.NODE_ENV === 'production' ? { rejectUnauthorized: false } : false,
})

// Initialize connection
export const initializeDatabase = async () => {
  try {
    await AppDataSource.initialize()
    console.log('✅ Database connection established')
  } catch (error) {
    console.error('❌ Database connection failed:', error)
    process.exit(1)
  }
}
```

#### Prisma Configuration

```typescript
// src/config/database.ts
import { PrismaClient } from '@prisma/client'

const prismaClientSingleton = () => {
  return new PrismaClient({
    log: process.env.NODE_ENV === 'development' ? ['query', 'error', 'warn'] : ['error'],
    datasources: {
      db: {
        url: process.env.DATABASE_URL,
      },
    },
  })
}

declare global {
  var prisma: undefined | ReturnType<typeof prismaClientSingleton>
}

export const prisma = globalThis.prisma ?? prismaClientSingleton()

if (process.env.NODE_ENV !== 'production') globalThis.prisma = prisma

// Graceful shutdown
export const disconnectDatabase = async () => {
  await prisma.$disconnect()
}

// Health check
export const checkDatabaseConnection = async () => {
  try {
    await prisma.$queryRaw`SELECT 1`
    return true
  } catch (error) {
    console.error('Database health check failed:', error)
    return false
  }
}
```

#### Mongoose (MongoDB) Configuration

```typescript
// src/config/database.ts
import mongoose from 'mongoose'

const MONGODB_URI = process.env.MONGODB_URI || 'mongodb://localhost:27017/myapp'

export const connectDatabase = async () => {
  try {
    await mongoose.connect(MONGODB_URI, {
      maxPoolSize: 10,
      minPoolSize: 5,
      socketTimeoutMS: 45000,
      serverSelectionTimeoutMS: 5000,
      family: 4, // Use IPv4
    })

    console.log('✅ MongoDB connected')

    // Connection events
    mongoose.connection.on('error', (err) => {
      console.error('MongoDB connection error:', err)
    })

    mongoose.connection.on('disconnected', () => {
      console.log('MongoDB disconnected')
    })

    // Graceful shutdown
    process.on('SIGINT', async () => {
      await mongoose.connection.close()
      process.exit(0)
    })
  } catch (error) {
    console.error('Failed to connect to MongoDB:', error)
    process.exit(1)
  }
}
```

### 2. Environment Variables

```bash
# .env
# Database
DB_HOST=localhost
DB_PORT=5432
DB_USER=myapp_user
DB_PASSWORD=secure_password_here
DB_NAME=myapp_db

# Alternative: Full connection string
DATABASE_URL=postgresql://myapp_user:secure_password_here@localhost:5432/myapp_db

# MongoDB
MONGODB_URI=mongodb://localhost:27017/myapp

# Connection pool
DB_POOL_MIN=5
DB_POOL_MAX=20

# Production
NODE_ENV=production
DB_SSL=true
```

```bash
# .env.example (commit this to git)
DB_HOST=localhost
DB_PORT=5432
DB_USER=your_db_user
DB_PASSWORD=your_db_password
DB_NAME=your_db_name
DATABASE_URL=postgresql://user:password@host:port/database
```

### 3. Connection Pool Configuration

```typescript
// Optimized pool settings by environment
export const getPoolConfig = () => {
  const env = process.env.NODE_ENV

  if (env === 'production') {
    return {
      max: 20,
      min: 10,
      idleTimeoutMillis: 30000,
      connectionTimeoutMillis: 2000,
    }
  } else if (env === 'staging') {
    return {
      max: 10,
      min: 5,
      idleTimeoutMillis: 30000,
      connectionTimeoutMillis: 2000,
    }
  } else {
    // development
    return {
      max: 5,
      min: 2,
      idleTimeoutMillis: 10000,
      connectionTimeoutMillis: 2000,
    }
  }
}
```

### 4. Query Optimization

```typescript
// Bad: N+1 query problem
const users = await User.find()
for (const user of users) {
  const posts = await Post.find({ authorId: user.id }) // N queries
}

// Good: Eager loading
const users = await User.find({
  relations: ['posts'],
})

// Good: Join query
const users = await dataSource
  .createQueryBuilder(User, 'user')
  .leftJoinAndSelect('user.posts', 'post')
  .getMany()

// Prisma with includes
const users = await prisma.user.findMany({
  include: {
    posts: true,
  },
})
```

```typescript
// Use indexes effectively
const users = await User.find({
  where: { email: 'test@example.com' }, // email column should be indexed
})

// Use select to fetch only needed fields
const users = await User.find({
  select: ['id', 'email', 'name'], // Don't fetch all columns
})

// Pagination with cursors (better than offset)
const users = await User.find({
  where: { id: MoreThan(lastSeenId) },
  take: 20,
  order: { id: 'ASC' },
})
```

### 5. Transactions

```typescript
// TypeORM transaction
await AppDataSource.transaction(async (manager) => {
  const user = await manager.save(User, { email: 'test@example.com' })
  await manager.save(Profile, { userId: user.id, bio: 'Hello' })
  // Both saved or both rolled back
})

// Prisma transaction
await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: { email: 'test@example.com' } })
  await tx.profile.create({ data: { userId: user.id, bio: 'Hello' } })
})

// Prisma sequential operations
await prisma.$transaction([
  prisma.user.create({ data: { email: 'test@example.com' } }),
  prisma.post.create({ data: { title: 'First post' } }),
])
```

### 6. Database Migrations

```typescript
// Create migration script in package.json
{
  "scripts": {
    "migration:generate": "typeorm migration:generate -d src/config/database.ts src/migrations/Migration",
    "migration:run": "typeorm migration:run -d src/config/database.ts",
    "migration:revert": "typeorm migration:revert -d src/config/database.ts",
    "schema:sync": "typeorm schema:sync -d src/config/database.ts",
    "schema:drop": "typeorm schema:drop -d src/config/database.ts"
  }
}

// Prisma migrations
{
  "scripts": {
    "prisma:migrate:dev": "prisma migrate dev",
    "prisma:migrate:deploy": "prisma migrate deploy",
    "prisma:migrate:reset": "prisma migrate reset",
    "prisma:generate": "prisma generate",
    "prisma:studio": "prisma studio"
  }
}
```

### 7. Health Check Endpoint

```typescript
// src/routes/health.ts
import { Request, Response } from 'express'
import { AppDataSource } from '../config/database'

export const healthCheck = async (req: Request, res: Response) => {
  try {
    // Check database connection
    await AppDataSource.query('SELECT 1')

    res.status(200).json({
      status: 'healthy',
      database: 'connected',
      timestamp: new Date().toISOString(),
    })
  } catch (error) {
    res.status(503).json({
      status: 'unhealthy',
      database: 'disconnected',
      error: error.message,
      timestamp: new Date().toISOString(),
    })
  }
}
```

### 8. Database Seeding

```typescript
// src/seeds/seed.ts
import { AppDataSource } from '../config/database'
import { User } from '../entities/User'
import { Role } from '../entities/Role'

export const seedDatabase = async () => {
  await AppDataSource.initialize()

  // Create roles
  const adminRole = await Role.create({ name: 'admin' }).save()
  const userRole = await Role.create({ name: 'user' }).save()

  // Create users
  await User.create({
    email: 'admin@example.com',
    name: 'Admin User',
    role: adminRole,
  }).save()

  await User.create({
    email: 'user@example.com',
    name: 'Regular User',
    role: userRole,
  }).save()

  console.log('✅ Database seeded')

  await AppDataSource.destroy()
}

// Run: ts-node src/seeds/seed.ts
if (require.main === module) {
  seedDatabase().catch(console.error)
}
```

### 9. Query Logging and Monitoring

```typescript
// Custom query logger
import { Logger, QueryRunner } from 'typeorm'

export class DatabaseLogger implements Logger {
  logQuery(query: string, parameters?: any[], queryRunner?: QueryRunner) {
    console.log('Query:', query)
    console.log('Parameters:', parameters)
  }

  logQueryError(error: string, query: string, parameters?: any[]) {
    console.error('Query Error:', error)
    console.error('Query:', query)
  }

  logQuerySlow(time: number, query: string, parameters?: any[]) {
    console.warn(`Slow query (${time}ms):`, query)
  }

  logSchemaBuild(message: string) {
    console.log('Schema Build:', message)
  }

  logMigration(message: string) {
    console.log('Migration:', message)
  }

  log(level: 'log' | 'info' | 'warn', message: any) {
    console.log(`[${level.toUpperCase()}]`, message)
  }
}
```

### 10. Database Backup Script

```bash
#!/bin/bash
# scripts/backup-db.sh

# Load environment variables
source .env

# Create backup directory
mkdir -p backups

# Generate timestamp
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")

# PostgreSQL backup
pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME > "backups/backup_${TIMESTAMP}.sql"

# Compress backup
gzip "backups/backup_${TIMESTAMP}.sql"

# Delete backups older than 30 days
find backups/ -name "*.gz" -mtime +30 -delete

echo "✅ Backup completed: backup_${TIMESTAMP}.sql.gz"
```

## Best Practices

1. **Always use connection pooling** - Reuse connections instead of creating new ones
2. **Use environment variables** - Never hardcode credentials
3. **Implement health checks** - Monitor database connectivity
4. **Use migrations** - Never modify database schema manually
5. **Index appropriately** - Index foreign keys and frequently queried columns
6. **Optimize queries** - Use explain plans to identify slow queries
7. **Use transactions** - For operations that must succeed or fail together
8. **Implement read replicas** - For high-read applications
9. **Set up monitoring** - Track query performance and connection pool metrics
10. **Regular backups** - Automate database backups

## Security Checklist

- [ ] Use SSL/TLS for database connections in production
- [ ] Store credentials in environment variables or secrets manager
- [ ] Use least privilege principle for database users
- [ ] Enable audit logging for sensitive operations
- [ ] Implement connection timeout and retry logic
- [ ] Validate and sanitize all inputs
- [ ] Use parameterized queries (prevent SQL injection)
- [ ] Regular security patches and updates
- [ ] Implement IP whitelisting for database access
- [ ] Enable database firewall rules

Ask the user: "What database configuration task would you like help with?"