qrmaster/MIGRATION_FROM_SUPABASE.md

Migration Guide: From Supabase to Local PostgreSQL

This guide helps you migrate your QR Master application from Supabase to a local PostgreSQL database with Docker.

What Changed

✅ Removed

• Supabase connection pooling (DIRECT_URL environment variable)
• Supabase-specific configurations
• External database dependency

✨ Added

• Local PostgreSQL 16 database in Docker
• Redis cache for better performance
• Adminer database management UI
• Complete Docker setup with docker-compose
• Database initialization scripts
• Development and production Docker configurations

Migration Steps

1. Backup Your Supabase Database (IMPORTANT!)

Before making any changes, backup your existing data:

# If you have access to Supabase CLI
supabase db dump > backup_$(date +%Y%m%d).sql

# Or use pg_dump directly with your Supabase credentials
pg_dump "postgresql://postgres:[PASSWORD]@[PROJECT_REF].supabase.co:5432/postgres" > backup.sql

2. Update Your Codebase

Pull the latest changes or update these files:

Updated Files:

• ✏️ prisma/schema.prisma - Removed directUrl field
• ✏️ src/lib/env.ts - Removed DIRECT_URL variable
• ✏️ docker-compose.yml - Updated with PostgreSQL setup
• ✏️ Dockerfile - Enhanced with PostgreSQL support
• ✏️ package.json - Added Docker scripts and tsx

New Files:

• 📄 docker-compose.dev.yml - Development setup
• 📄 docker/init-db.sh - Database initialization
• 📄 docker/README.md - Docker documentation
• 📄 DOCKER_SETUP.md - Complete Docker guide
• 📄 env.example - Environment template
• 📄 .dockerignore - Docker build optimization

3. Set Up Environment Variables

1. Remove Supabase-specific variables:

# Remove these from .env:
# DIRECT_URL=...
# SUPABASE_URL=...
# SUPABASE_ANON_KEY=...

2. Update database connection:

# For Docker (default):
DATABASE_URL=postgresql://postgres:postgres@db:5432/qrmaster?schema=public

# For local development (without Docker):
DATABASE_URL=postgresql://postgres:postgres@localhost:5432/qrmaster?schema=public

3. Copy from template:

cp env.example .env

4. Generate secure secrets:

# Linux/Mac
openssl rand -base64 32  # Use for NEXTAUTH_SECRET
openssl rand -base64 32  # Use for IP_SALT

# Windows PowerShell
[Convert]::ToBase64String((1..32 | ForEach-Object { Get-Random -Maximum 256 }))

4. Start Local PostgreSQL

Option A: Development Mode (Recommended)

# Start database only (run app on host)
npm run docker:dev

# Wait for database to be ready
docker-compose -f docker-compose.dev.yml logs -f db

Option B: Full Docker

# Start all services
npm run docker:prod

# Wait for all services to be ready
docker-compose logs -f

5. Restore Your Data

Option 1: Using Prisma Migrations (Clean Start)

# Generate Prisma client
npm run db:generate

# Run migrations
npm run db:migrate

# Seed with demo data
npm run db:seed

Option 2: Restore from Backup (Preserve Data)

# Restore your Supabase backup
cat backup.sql | docker-compose exec -T db psql -U postgres qrmaster

# Or if running locally
psql -U postgres -d qrmaster < backup.sql

# Then run migrations to update schema
npm run db:deploy

6. Verify Migration

1. Check Database Connection:

# Connect to database
npm run docker:db

# Or manually
docker-compose exec db psql -U postgres -d qrmaster

# Run test query
SELECT COUNT(*) FROM "User";
SELECT COUNT(*) FROM "QRCode";

2. Access Adminer (Database UI):

   • URL: http://localhost:8080
   • System: PostgreSQL
   • Server: db
   • Username: postgres
   • Password: postgres
   • Database: qrmaster

3. Test Your Application:

# Start the app (if using dev mode)
npm run dev

# Access: http://localhost:3050

7. Update Your Deployment

For Docker Production:

# Build and deploy
docker-compose up -d --build

# Run migrations
docker-compose exec web npx prisma migrate deploy

# Check logs
docker-compose logs -f web

For Other Platforms (Vercel, Railway, etc.):

Update your environment variables in the platform's dashboard:

• Remove: DIRECT_URL
• Update: DATABASE_URL to your new PostgreSQL connection string

Troubleshooting

Issue: Connection Refused

# Check if database is running
docker-compose ps

# Check database logs
docker-compose logs db

# Restart database
docker-compose restart db

Issue: Migration Errors

# Reset migrations (⚠️ deletes data!)
npm run db:migrate reset

# Or manually reset
docker-compose down -v
docker-compose up -d db
npm run db:migrate

Issue: Prisma Client Not Generated

# Regenerate Prisma client
npm run db:generate

# Or
npx prisma generate

Issue: Data Not Migrated

# Check if backup was restored correctly
docker-compose exec db psql -U postgres -d qrmaster -c "
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
"

Differences: Supabase vs Local PostgreSQL

Feature	Supabase	Local PostgreSQL
Hosting	Cloud (managed)	Self-hosted (Docker)
Connection Pooling	Built-in (Supavisor)	Prisma built-in
Database UI	Supabase Studio	Adminer (included)
Backups	Automatic	Manual (or scripted)
Cost	Free tier + paid	Free (infrastructure cost only)
Latency	Internet dependent	Local network
Setup	Account required	Docker only
Scaling	Automatic	Manual

Benefits of Local PostgreSQL

✅ Full Control: Own your data and infrastructure
✅ No Vendor Lock-in: Standard PostgreSQL
✅ Lower Latency: Local network speed
✅ Cost: No monthly fees
✅ Privacy: Data stays on your infrastructure
✅ Development: Easy local testing
✅ Offline: Works without internet

Next Steps

1. ✅ Verify all data migrated correctly
2. ✅ Test all application features
3. ✅ Update your CI/CD pipelines
4. ✅ Set up automated backups:

# Create backup script
cat > backup.sh << 'EOF'
#!/bin/bash
BACKUP_DIR="./backups"
mkdir -p $BACKUP_DIR
docker-compose exec -T db pg_dump -U postgres qrmaster > "$BACKUP_DIR/backup_$(date +%Y%m%d_%H%M%S).sql"
EOF

chmod +x backup.sh

# Run daily backups (cron example)
# 0 2 * * * /path/to/backup.sh

5. ✅ Monitor your application
6. ✅ Update documentation

Rollback Plan

If you need to rollback to Supabase:

1. Keep your Supabase project active during testing
2. Keep your backup files safe
3. To rollback, simply change DATABASE_URL back to Supabase
4. Add back DIRECT_URL to prisma/schema.prisma:

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

Support

For issues:

1. Check DOCKER_SETUP.md for detailed Docker help
2. Check docker/README.md for Docker commands
3. Review logs: docker-compose logs -f
4. Open an issue on GitHub

---

🎉 Congratulations! You've successfully migrated from Supabase to local PostgreSQL!