triviathang/MIGRATIONS.md

Database Migrations Guide

This document covers the proper workflow for database migrations and how to fix common issues.

Migration Workflow Checklist

When making model changes, always follow this order:

1. Make model changes

Edit backend/models.py with your changes.

2. Create the migration

# Local development
uv run flask --app backend.app:create_app db migrate -m "Description of changes"

# Docker development
docker compose exec backend uv run flask --app backend.app:create_app db migrate -m "Description of changes"

3. Review the generated migration

Check migrations/versions/ for the new file. Verify it does what you expect.

4. Apply locally and test

uv run flask --app backend.app:create_app db upgrade

5. Commit BOTH the model AND migration together

git add backend/models.py migrations/versions/
git commit -m "Add field_name to Model"

CRITICAL: Never commit model changes without the corresponding migration file!

6. Deploy and run migrations on production

# Rebuild to include new migration file
docker compose -f docker-compose.production.yml up --build -d

# Apply the migration
docker compose -f docker-compose.production.yml exec backend uv run flask --app backend.app:create_app db upgrade

Common Issues and Fixes

Error: "Can't locate revision identified by 'xxxxx'"

Cause: The database references a migration that doesn't exist in the codebase (migration was applied but never committed).

Fix:

# 1. Check what revision the DB thinks it's at
docker compose -f docker-compose.production.yml exec backend python -c "
import sqlite3
conn = sqlite3.connect('backend/instance/trivia.db')
print(conn.execute('SELECT * FROM alembic_version').fetchall())
"

# 2. Find the latest valid migration in your codebase
ls migrations/versions/

# 3. Force the DB to point to a valid migration
docker compose -f docker-compose.production.yml exec backend python -c "
import sqlite3
conn = sqlite3.connect('backend/instance/trivia.db')
conn.execute(\"UPDATE alembic_version SET version_num = 'YOUR_VALID_REVISION'\")
conn.commit()
print('Done')
"

# 4. Manually add any missing columns
docker compose -f docker-compose.production.yml exec backend python -c "
import sqlite3
conn = sqlite3.connect('backend/instance/trivia.db')
conn.execute('ALTER TABLE tablename ADD COLUMN columnname TYPE')
conn.commit()
print('Column added')
"

Error: "table has no column named X"

Cause: Model has columns that don't exist in the database.

Fix: Add the columns manually:

docker compose -f docker-compose.production.yml exec backend python -c "
import sqlite3
conn = sqlite3.connect('backend/instance/trivia.db')
cursor = conn.execute('PRAGMA table_info(tablename)')
print('Current columns:', [col[1] for col in cursor.fetchall()])
"

# Then add missing columns:
docker compose -f docker-compose.production.yml exec backend python -c "
import sqlite3
conn = sqlite3.connect('backend/instance/trivia.db')
conn.execute('ALTER TABLE games ADD COLUMN completed_at DATETIME')
conn.execute('ALTER TABLE games ADD COLUMN winners JSON')
conn.commit()
print('Done')
"

Checking current migration state

# What migration does the DB think it's at?
docker compose -f docker-compose.production.yml exec backend uv run flask --app backend.app:create_app db current

# What's the latest migration in the codebase?
docker compose -f docker-compose.production.yml exec backend uv run flask --app backend.app:create_app db heads

# Show migration history
docker compose -f docker-compose.production.yml exec backend uv run flask --app backend.app:create_app db history

Nuclear option: Reset migrations (destroys all data!)

Only use if you can recreate all data:

# Delete database
docker compose -f docker-compose.production.yml exec backend rm -f backend/instance/trivia.db

# Recreate from scratch
docker compose -f docker-compose.production.yml exec backend uv run flask --app backend.app:create_app db upgrade

SQLite Column Type Reference

When adding columns manually, use these SQLite types:

• INTEGER - for integers, booleans
• TEXT or VARCHAR(N) - for strings
• DATETIME - for dates/times
• JSON - for JSON data
• FLOAT or REAL - for decimals

Pre-deployment Checklist

Before deploying model changes:

• Migration file exists in migrations/versions/
• Migration file is committed to git
• Migration tested locally with db upgrade and db downgrade
• Model changes and migration are in the same commit