paper-dynasty-database/QUICK_START.md

Quick Start - PostgreSQL Migration Testing

✅ PostgreSQL is Running!

Your PostgreSQL database is now running in Docker:

✅ PostgreSQL 17:  localhost:5432  (container: pd_postgres)
✅ Adminer UI:     localhost:8081  (database web interface)

---

🚀 Step-by-Step Testing Guide

Step 1: Set Environment Variables

cd /mnt/NV2/Development/paper-dynasty/database

export DATABASE_TYPE=postgresql
export POSTGRES_HOST=localhost
export POSTGRES_DB=pd_master
export POSTGRES_USER=pd_admin
export POSTGRES_PASSWORD=pd_dev_password
export POSTGRES_PORT=5432

Step 2: Create Database Schema

Run this Python command to create all 30 tables:

python -c "
from app.db_engine import db, Current, Rarity, Event, Cardset, MlbPlayer, Player, Team, PackType, Pack, Card, Roster, Result, BattingStat, PitchingStat, Award, Paperdex, Reward, GameRewards, Notification, GauntletReward, GauntletRun, BattingCard, BattingCardRatings, PitchingCard, PitchingCardRatings, CardPosition, StratGame, StratPlay, Decision

print('Creating database tables...')
db.create_tables([
    Current, Rarity, Event, Cardset, MlbPlayer, Player, Team, PackType, Pack,
    Card, Roster, Result, BattingStat, PitchingStat, Award, Paperdex, Reward,
    GameRewards, Notification, GauntletReward, GauntletRun, BattingCard,
    BattingCardRatings, PitchingCard, PitchingCardRatings, CardPosition,
    StratGame, StratPlay, Decision
])
print('✅ All tables created successfully!')
db.close()
"

Expected output:

Creating database tables...
✅ All tables created successfully!

Step 3: Verify Tables in Adminer

1. Open: http://localhost:8081
2. Login:
   • System: PostgreSQL
   • Server: pd_postgres
   • Username: pd_admin
   • Password: pd_dev_password
   • Database: pd_master
3. You should see 30 tables listed!

Step 4: Run the API Locally

# Make sure you're in the database directory
cd /mnt/NV2/Development/paper-dynasty/database

# Run with hot reload (best for development)
uvicorn app.main:app --reload --host 0.0.0.0 --port 8000

Expected output:

INFO:     Uvicorn running on http://0.0.0.0:8000
INFO:     Application startup complete.

Step 5: Test the API

Open another terminal and test:

# Test health endpoint
curl http://localhost:8000/

# Test an API endpoint (replace with actual endpoint)
curl http://localhost:8000/api/v2/current

Or open in browser:

• API Docs: http://localhost:8000/docs
• API Root: http://localhost:8000

---

🧪 Testing GROUP BY Queries

These are the critical queries we fixed for PostgreSQL:

# Test batting stats with different group_by modes
curl "http://localhost:8000/api/v2/stratplays/batting?group_by=player&limit=5"
curl "http://localhost:8000/api/v2/stratplays/batting?group_by=team&limit=5"
curl "http://localhost:8000/api/v2/stratplays/batting?group_by=playerteam&limit=5"
curl "http://localhost:8000/api/v2/stratplays/batting?group_by=playergame&limit=5"

# Test pitching stats
curl "http://localhost:8000/api/v2/stratplays/pitching?group_by=player&limit=5"
curl "http://localhost:8000/api/v2/stratplays/pitching?group_by=playerteam&limit=5"

---

🔄 Switching Between SQLite and PostgreSQL

To use SQLite (existing database):

export DATABASE_TYPE=sqlite
uvicorn app.main:app --reload --host 0.0.0.0 --port 8000

To use PostgreSQL:

export DATABASE_TYPE=postgresql
export POSTGRES_HOST=localhost
export POSTGRES_DB=pd_master
export POSTGRES_USER=pd_admin
export POSTGRES_PASSWORD=pd_dev_password
uvicorn app.main:app --reload --host 0.0.0.0 --port 8000

---

🛠️ Common Commands

Docker/Database Management:

# View PostgreSQL logs
docker compose logs -f postgres

# Stop database (keeps data)
docker compose down

# Restart database
docker compose restart postgres

# Connect to PostgreSQL shell
docker exec -it pd_postgres psql -U pd_admin -d pd_master

# Check container status
docker compose ps

PostgreSQL Commands (inside psql):

-- List all tables
\dt

-- Describe a table
\d player

-- Count records in a table
SELECT COUNT(*) FROM player;

-- List databases
\l

-- Quit
\q

---

📊 What's Been Fixed for PostgreSQL

✅ Code Changes Completed:

1. Database Configuration (app/db_engine.py)

   • Environment-based PostgreSQL/SQLite switching
   • Connection pooling for PostgreSQL
   • All 30 models have explicit table_name

2. GROUP BY Fixes (app/routers_v2/stratplays.py)

   • Fixed get_batting_totals()
   • Fixed get_pitching_totals()
   • Conditionally builds SELECT fields based on group_by parameter
   • Satisfies PostgreSQL's strict GROUP BY requirements

3. Dependencies (requirements.txt)

   • Added psycopg2-binary for PostgreSQL support

4. Docker Setup (docker-compose.yml)

   • PostgreSQL 17 Alpine container
   • Adminer database UI
   • Local directory mounts (not Docker volumes)

---

⚠️ Known Issues

API Service Commented Out

The API service in docker-compose.yml is intentionally commented out because:

• db_engine.py calls db.create_tables() at module import time
• Multiple gunicorn workers cause race conditions
• Results in "duplicate key" violations

Solution: Run the API locally with uvicorn instead (Step 4 above).

Logs Directory

If you get "FileNotFoundError" for logs:

mkdir -p logs/database

---

📝 Next Steps

1. ✅ Test all API endpoints with PostgreSQL
2. ✅ Verify data integrity
3. ✅ Test GROUP BY queries with different parameters
4. ✅ Compare results with SQLite baseline
5. ⏭️ Data Migration - See POSTGRES_MIGRATION_PLAN.md Step 2

---

🆘 Troubleshooting

PostgreSQL won't start

# Check logs
docker compose logs postgres

# Restart fresh
docker compose down
sudo rm -rf postgres_data/*
docker compose up -d postgres adminer

Can't connect to database

# Verify PostgreSQL is ready
docker exec pd_postgres pg_isready -U pd_admin

# Check environment variables
echo $DATABASE_TYPE
echo $POSTGRES_HOST

Table already exists errors

# Clear and recreate
docker exec -it pd_postgres psql -U pd_admin -d pd_master -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public;"
# Then run Step 2 again

---

Last Updated: 2025-11-07 Branch: postgres-migration Status: Ready for testing!