# Database Migrations Guide
This document provides instructions for working with database migrations in the project.
## Prerequisites
- Python 3.x
- PostgreSQL
- Alembic
- psql command-line tool
## Quick Start
To run all pending migrations:
```bash
./run_migration.sh -d postgresql://user:password@host:port/dbname
```
Example:
```bash
./run_migration.sh -d postgresql://nomy:nomy_dev_password@localhost:5442/nomy_dev
```
## Migration Script Options
```bash
Usage: ./run_migration.sh [-d|--database-url <database_url>] [-h|--help]
Options:
-d, --database-url <url> Specify the database URL (required)
Format: postgresql://user:password@host:port/dbname
-h, --help Show this help message
```
## Working with Migrations
### Creating a New Migration
To create a new migration:
```bash
alembic revision -m "description_of_your_changes"
```
This will create a new file in `alembic/versions/` with an auto-generated version ID.
### Writing Migration Content
Migration files contain two main functions:
- `upgrade()`: Changes to apply to the database
- `downgrade()`: How to reverse those changes
Example migration:
```python
def upgrade():
op.create_table(
'example_table',
sa.Column('id', sa.Integer(), primary_key=True),
sa.Column('name', sa.String(), nullable=False),
)
def downgrade():
op.drop_table('example_table')
```
### Common Migration Operations
1. Creating a table:
```python
op.create_table(
'table_name',
sa.Column('id', sa.Integer(), primary_key=True),
sa.Column('field', sa.String()),
)
```
2. Adding a column:
```python
op.add_column('table_name', sa.Column('new_column', sa.String()))
```
3. Creating an index:
```python
op.create_index('index_name', 'table_name', ['column_name'])
```
4. Adding a foreign key:
```python
op.create_foreign_key(
'fk_name', 'source_table', 'target_table',
['source_column'], ['target_column']
)
```
### Migration Commands
1. View current migration status:
```bash
alembic current
```
2. View migration history:
```bash
alembic history
```
3. Upgrade to a specific version:
```bash
alembic upgrade <version_id>
```
4. Downgrade to a specific version:
```bash
alembic downgrade <version_id>
```
5. Upgrade to latest version:
```bash
alembic upgrade head
```
6. Downgrade one version:
```bash
alembic downgrade -1
```
## Best Practices
1. **Always Test Migrations**: Test both upgrade and downgrade operations in a development environment before applying to production.
2. **One Change per Migration**: Each migration should handle one logical change to make it easier to track and rollback if needed.
3. **Meaningful Names**: Use descriptive names for migration files that clearly indicate what changes they make.
4. **Data Migrations**: When modifying data, consider breaking it into separate steps:
- Add new column (nullable)
- Migrate data
- Make column non-nullable (if required)
5. **Backup First**: Always backup your database before running migrations in production.
## Troubleshooting
### Common Issues
1. **Connection Errors**:
- Verify database URL is correct
- Check if database is running
- Verify network connectivity and firewall settings
2. **Permission Issues**:
- Ensure user has necessary privileges
- Check schema ownership and permissions
3. **Migration Conflicts**:
- If multiple developers create migrations, ensure proper merging
- Use `alembic heads` to check for multiple heads
### Getting Help
If you encounter issues:
1. Check the alembic logs
2. Verify database connection
3. Review migration file for syntax errors
4. Check database permissions
## Version Control
- Always commit migration files to version control
- Include both upgrade and downgrade operations
- Document any manual steps required
## Safety Checks
The migration script (`run_migration.sh`) includes several safety checks:
1. Database connection verification
2. Alembic environment validation
3. Error handling and reporting
4. Clear success/failure messages
## Production Deployments
When deploying to production:
1. Always backup the database first
2. Test migrations in a staging environment
3. Plan for rollback scenarios
4. Schedule maintenance window if needed
5. Monitor logs during migration
## Additional Resources
- [Alembic Documentation](https://alembic.sqlalchemy.org/)
- [SQLAlchemy Documentation](https://www.sqlalchemy.org/)
- [PostgreSQL Documentation](https://www.postgresql.org/docs/)