moyosapp_beta.0.0.3.3_beta1/docs/COOLIFY_DATABASE_MIGRATION.md

Coolify Database Migration Guide

This guide helps you backup your current database and restore it on Coolify.

📋 Prerequisites

• PostgreSQL client tools (pg_dump, psql) installed
• Access to current database
• Access to Coolify database

🔄 Step 1: Backup Current Database

Option A: Using Backup Script (Recommended)

# Make script executable
chmod +x scripts/backup-database.sh

# Run backup
./scripts/backup-database.sh

This creates a backup in backups/YYYYMMDD_HHMMSS/ with:

• schema.sql - Database schema
• data.sql - All data
• full_dump.sql - Complete dump (schema + data)
• migrations/ - Prisma migrations
• schema.prisma - Prisma schema file
• metadata.json - Backup metadata

Option B: Manual Backup

# Export full database
pg_dump -h localhost -U postgres -d your_database_name > backup.sql

# Or using DATABASE_URL
pg_dump $DATABASE_URL > backup.sql

🚀 Step 2: Set Up Database on Coolify

2.1 Create PostgreSQL Service in Coolify

1. In Coolify, create a new PostgreSQL service
2. Note the container name (e.g., postgres-CONTAINER_ID)
3. Note the database credentials

2.2 Configure Environment Variables

Set these in your Next.js app service in Coolify:

# Database URLs (use internal Docker network)
DATABASE_URL=postgresql://postgres:PASSWORD@postgres-CONTAINER_ID:5432/postgres
RUNTIME_DATABASE_URL=postgresql://postgres:PASSWORD@postgres-CONTAINER_ID:5432/postgres

# Optional: Shadow database for migrations
SHADOW_DATABASE_URL=postgresql://postgres:PASSWORD@postgres-CONTAINER_ID:5432/postgres

Important: Replace CONTAINER_ID with actual container ID from Coolify.

🔧 Step 3: Restore Database on Coolify

Option A: Using Restore Script

1. Copy backup to Coolify server:

   # From your local machine
   scp -r backups/latest user@coolify-server:/path/to/app/
   

2. SSH into Coolify server:

   ssh user@coolify-server
   cd /path/to/app
   

3. Run restore script:

   chmod +x scripts/restore-database.sh
   ./scripts/restore-database.sh backups/latest
   

Option B: Manual Restore via Coolify

1. Access PostgreSQL container:

   # In Coolify, open terminal for PostgreSQL service
   # Or SSH and run:
   docker exec -it postgres-CONTAINER_ID psql -U postgres
   

2. Create database (if needed):

   CREATE DATABASE your_database_name;
   \c your_database_name
   

3. Restore from backup:

   # Copy backup.sql into container
   docker cp backup.sql postgres-CONTAINER_ID:/tmp/
   
   # Restore
   docker exec -it postgres-CONTAINER_ID psql -U postgres -d your_database_name -f /tmp/backup.sql
   

Option C: Using Prisma Migrations (Recommended)

If you have Prisma migrations, use them instead:

1. Ensure migrations are in repo:

   # Migrations should be in prisma/migrations/
   ls prisma/migrations/
   

2. Deploy migrations in Coolify:

   # Add to build command or run manually
   npx prisma migrate deploy
   

3. Seed data (if needed):

   npm run db:seed
   

✅ Step 4: Verify Database

Check Connection

# Test connection
docker exec -it postgres-CONTAINER_ID psql -U postgres -d your_database_name -c "SELECT version();"

Verify Data

# Check tables
docker exec -it postgres-CONTAINER_ID psql -U postgres -d your_database_name -c "\dt"

# Check record counts
docker exec -it postgres-CONTAINER_ID psql -U postgres -d your_database_name -c "SELECT COUNT(*) FROM \"Guest\";"

Test from Application

1. Deploy your Next.js app in Coolify
2. Check application logs for database connection errors
3. Test key features that require database access

🔍 Troubleshooting

Issue: Connection Refused

Problem: App can't connect to database

Solution:

• Verify DATABASE_URL uses correct container name
• Check Docker network allows communication
• Ensure PostgreSQL container is running

Issue: Migration Errors

Problem: Prisma migrations fail

Solution:

# Reset and redeploy
npx prisma migrate reset
npx prisma migrate deploy

# Or use db push (development only)
npx prisma db push

Issue: Permission Denied

Problem: Database user lacks permissions

Solution:

-- Grant necessary permissions
GRANT ALL PRIVILEGES ON DATABASE your_database_name TO postgres;
GRANT ALL ON SCHEMA public TO postgres;

Issue: Schema Mismatch

Problem: Restored schema doesn't match Prisma schema

Solution:

# Sync Prisma schema with database
npx prisma db pull
npx prisma generate

📝 Database Configuration Reference

Current Database Config

Your database uses:

• Provider: PostgreSQL
• Schema: Defined in prisma/schema.prisma
• Migrations: prisma/migrations/

Key Models

• Guest - Guest information and RSVP
• Song - Song requests
• GuestbookEntry - Guestbook messages
• GalleryPhoto - Photo gallery
• Table - Seating arrangements
• Admin - Admin users
• Tribute - Tributes/dedications
• Reminder - Email/SMS reminders
• ABTest - A/B testing
• CouplesPlaylist - Couple's playlist

Seed Data

Seed script: prisma/seed.ts

• Seeds guests from data/guests.csv
• Creates admin user
• Creates sample tables

🔐 Security Notes

1. Never commit backups with real data
2. Use strong passwords for production database
3. Restrict database access to application only
4. Use connection pooling (RUNTIME_DATABASE_URL)
5. Enable SSL for production connections

📚 Related Documentation

• COOLIFY_MIGRATION.md - General Coolify migration guide
• PRODUCTION_ENV_VARIABLES.md - Environment variables reference
• Prisma Migrations

---

Last Updated: January 2025