148 lines
4.5 KiB
Markdown
148 lines
4.5 KiB
Markdown
# 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
|
|
```bash
|
|
# 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
|
|
```bash
|
|
uv run flask --app backend.app:create_app db upgrade
|
|
```
|
|
|
|
### 5. Commit BOTH the model AND migration together
|
|
```bash
|
|
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
|
|
```bash
|
|
# 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:**
|
|
```bash
|
|
# 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:
|
|
```bash
|
|
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
|
|
|
|
```bash
|
|
# 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:
|
|
```bash
|
|
# 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
|