scriptshare-cursor/DOCKER_DATABASE_DEPLOYMENT.md

🐳 ScriptShare Docker Deployment with Database

📋 Overview

Your ScriptShare application now includes a complete Docker deployment setup with an integrated MySQL database. This provides a full-stack deployment that's ready for production use.

🏗️ Architecture

┌─────────────────────────────────────────────────────────┐
│                   Docker Network                        │
│                  (scriptshare-network)                 │
│                                                         │
│  ┌─────────────────┐  ┌─────────────────┐  ┌──────────┐│
│  │   Frontend      │  │   Backend API   │  │ MySQL DB ││
│  │   (Nginx)       │  │   (Node.js)     │  │   8.0    ││
│  │   Port 80       │  │   Port 3000     │  │Port 3306 ││
│  └─────────────────┘  └─────────────────┘  └──────────┘│
│           │                     │                │     │
│           └─────────────────────┼────────────────┘     │
│                                 │                      │
│                    ┌─────────────────────┐             │
│                    │ Persistent Volume   │             │
│                    │ (Database Data)     │             │
│                    └─────────────────────┘             │
└─────────────────────────────────────────────────────────┘

🗂️ New Files Created

1. Docker Compose Configuration

• docker-compose.production.yml - Complete multi-service setup
  • MySQL 8.0 database with health checks
  • API server with database connectivity
  • Frontend with proper networking
  • Persistent volumes for data

2. Database Setup

• scripts/init-db.sql - Database initialization script
  • Creates all required tables
  • Sets up proper indexes and relationships
  • Includes sample data and admin user
  • Optimized for performance

3. Enhanced API Container

• Dockerfile.api - Updated with database integration
  • MySQL client tools
  • Database connection waiting logic
  • Automatic migration execution
  • Enhanced health checks

4. Configuration & Environment

• env.production.example - Production environment template
  • Database credentials
  • API configuration
  • Frontend settings
  • Security settings

5. Deployment Scripts

• scripts/deploy-with-db.sh - Linux/macOS deployment script
• scripts/deploy-with-db.ps1 - Windows PowerShell deployment script

🚀 Quick Deployment

Prerequisites:

• Docker Engine 20.10+
• Docker Compose 2.0+
• 4GB+ RAM recommended
• 20GB+ disk space

Linux/macOS Deployment:

# Make script executable
chmod +x scripts/deploy-with-db.sh

# Run deployment
./scripts/deploy-with-db.sh

Windows Deployment:

# Run PowerShell deployment
.\scripts\deploy-with-db.ps1

Manual Deployment:

# 1. Copy environment file
cp env.production.example .env

# 2. Edit environment variables
nano .env  # Update passwords, URLs, etc.

# 3. Deploy stack
docker compose -f docker-compose.production.yml up -d

# 4. Check status
docker compose -f docker-compose.production.yml ps

⚙️ Configuration

Environment Variables:

# Database Configuration
DB_HOST=scriptshare-db
DB_PORT=3306
DB_NAME=scriptshare
DB_USER=scriptshare_user
DB_PASSWORD=YourSecurePassword!
DB_ROOT_PASSWORD=YourRootPassword!

# Application Configuration
APP_NAME=ScriptShare
APP_URL=https://your-domain.com
JWT_SECRET=your-super-secret-jwt-key

# Ports
API_PORT=3001      # External API port
FRONTEND_PORT=80   # External frontend port

Database Schema:

The initialization script creates:

• users - User accounts and profiles
• scripts - Script repository
• ratings - Script ratings and reviews
• script_analytics - Usage analytics
• script_collections - Script collections
• collection_scripts - Collection membership
• script_versions - Version control

Default Admin User:

• Email: admin@scriptshare.local
• Username: admin
• Password: admin123
• Permissions: Full admin access

🔧 Management Commands

Service Management:

# Start services
docker compose -f docker-compose.production.yml up -d

# Stop services
docker compose -f docker-compose.production.yml down

# Restart services
docker compose -f docker-compose.production.yml restart

# View logs
docker compose -f docker-compose.production.yml logs -f

# Service-specific logs
docker compose -f docker-compose.production.yml logs -f scriptshare-api

Database Management:

# Connect to database
docker compose -f docker-compose.production.yml exec scriptshare-db \
  mysql -u scriptshare_user -p scriptshare

# Run database backup
docker compose -f docker-compose.production.yml exec scriptshare-db \
  mysqldump -u root -p scriptshare > backup.sql

# Access database as root
docker compose -f docker-compose.production.yml exec scriptshare-db \
  mysql -u root -p

Application Management:

# Run database migrations
docker compose -f docker-compose.production.yml exec scriptshare-api \
  npm run db:migrate

# Check API health
curl http://localhost:3001/api/health

# View API logs
docker compose -f docker-compose.production.yml logs -f scriptshare-api

🏥 Health Monitoring

Built-in Health Checks:

1. Database Health Check:

   • Interval: 30s
   • Timeout: 10s
   • Start period: 60s
   • Tests MySQL connectivity

2. API Health Check:

   • Interval: 30s
   • Timeout: 15s
   • Start period: 60s
   • Tests HTTP endpoint + database

3. Frontend Health Check:

   • Interval: 30s
   • Timeout: 10s
   • Start period: 40s
   • Tests Nginx serving

Check Service Status:

# Docker health status
docker compose -f docker-compose.production.yml ps

# Detailed health check
docker inspect scriptshare-api --format='{{.State.Health.Status}}'

🔐 Security Features

Database Security:

• Isolated Docker network
• Non-root database user
• Encrypted password storage
• Connection limits and timeouts

API Security:

• JWT token authentication
• CORS configuration
• Request rate limiting
• Health check authentication

Network Security:

• Private Docker network
• Service-to-service communication
• External port exposure control

📊 Production Considerations

Performance Optimization:

• Database: InnoDB buffer pool, optimized indexes
• API: Connection pooling, query optimization
• Frontend: Static file caching, gzip compression

Data Persistence:

• Database data: Persistent Docker volume
• Logs: Container log aggregation
• Backups: Automated backup scripts

Scaling Options:

• Horizontal: Multiple API containers behind load balancer
• Vertical: Increase container resource limits
• Database: Read replicas, connection pooling

🔄 Backup & Recovery

Automated Backup Script:

#!/bin/bash
# Create timestamped backup
DATE=$(date +%Y%m%d_%H%M%S)
docker compose -f docker-compose.production.yml exec -T scriptshare-db \
  mysqldump -u root -p"$DB_ROOT_PASSWORD" --single-transaction \
  --routines --triggers scriptshare > "backups/scriptshare_$DATE.sql"

Recovery:

# Restore from backup
docker compose -f docker-compose.production.yml exec -T scriptshare-db \
  mysql -u root -p"$DB_ROOT_PASSWORD" scriptshare < backup.sql

🚨 Troubleshooting

Common Issues:

1. Database Connection Failed:

   # Check database container
   docker compose -f docker-compose.production.yml logs scriptshare-db
   
   # Test connectivity
   docker compose -f docker-compose.production.yml exec scriptshare-api \
     mysqladmin ping -h scriptshare-db -u scriptshare_user -p
   

2. API Not Starting:

   # Check API logs
   docker compose -f docker-compose.production.yml logs scriptshare-api
   
   # Check environment variables
   docker compose -f docker-compose.production.yml exec scriptshare-api env
   

3. Frontend Not Loading:

   # Check frontend logs
   docker compose -f docker-compose.production.yml logs scriptshare-frontend
   
   # Test API connectivity
   curl http://localhost:3001/api/health
   

🎯 Summary

Your ScriptShare application now includes:

• ✅ Complete Database Integration - MySQL 8.0 with full schema
• ✅ Production-Ready Deployment - Docker Compose with health checks
• ✅ Automated Setup - Database initialization and migrations
• ✅ Easy Management - Deployment scripts and management commands
• ✅ Security - Isolated networks and secure defaults
• ✅ Monitoring - Health checks and logging
• ✅ Persistence - Data volumes and backup strategies

Your application is now ready for production deployment with a complete database backend! 🎉