Database Migrations

This document describes how to work with database migrations in the Byte Bot project after the microservices migration.

Overview

Database migrations are managed using Alembic through Advanced Alchemy, which provides async-aware migration support integrated with SQLAlchemy 2.0.

Location

  • Migration files: services/api/src/byte_api/lib/db/migrations/versions/

  • Alembic config: services/api/src/byte_api/lib/db/alembic.ini

  • Environment: services/api/src/byte_api/lib/db/migrations/env.py

Migration History

The project contains the following migrations (in order):

  1. initial.py (43165a559e89) - Initial schema with guild, user, GitHub, and SO tags configuration

  2. 002_simplify_models.py (feebdacfdd91) - Simplified model structure, consolidated configuration tables

  3. 003_forum_models.py (73a26ceab2c4) - Added forum configuration support

  4. 004_snowflake_fixes.py (f32ee278015d) - Fixed Discord snowflake ID handling and foreign key relationships

Running Migrations

Prerequisites

Before running migrations, ensure:

  1. All required environment variables are configured (see .env.example)

  2. Database connection string is valid (DB_URL)

  3. GitHub App credentials are configured (required by settings validation)

Minimal .env for migrations:

DB_URL=postgresql+asyncpg://byte:bot@localhost:5432/byte
SECRET_KEY=your-secret-key-here
GITHUB_APP_ID=123456
GITHUB_APP_PRIVATE_KEY=/path/to/private-key.pem
GITHUB_APP_CLIENT_ID=Iv1.xxxxx
GITHUB_APP_CLIENT_SECRET=xxxxx

Database Setup

PostgreSQL database running (use docker-compose):

docker compose -f docker-compose.infra.yml up -d db

Upgrade to Latest

To apply all pending migrations:

from advanced_alchemy.alembic.commands import AlembicCommands
from byte_api.lib.db.base import config

cmds = AlembicCommands(sqlalchemy_config=config)
cmds.upgrade()

Or from shell:

uv run python -c "
from advanced_alchemy.alembic.commands import AlembicCommands
from byte_api.lib.db.base import config

AlembicCommands(sqlalchemy_config=config).upgrade()
"

Check Current Version

from advanced_alchemy.alembic.commands import AlembicCommands
from byte_api.lib.db.base import config

cmds = AlembicCommands(sqlalchemy_config=config)
cmds.current()

Downgrade

To rollback one migration:

from advanced_alchemy.alembic.commands import AlembicCommands
from byte_api.lib.db.base import config

cmds = AlembicCommands(sqlalchemy_config=config)
cmds.downgrade(revision="-1")

To rollback to a specific version:

cmds.downgrade(revision="<revision_id>")

Creating New Migrations

Auto-generate from Model Changes

When you modify models in byte-common/src/byte_common/models.py:

from advanced_alchemy.alembic.commands import AlembicCommands
from byte_api.lib.db.base import config

cmds = AlembicCommands(sqlalchemy_config=config)
cmds.revision(message="description of changes", autogenerate=True)

Manual Migration

For data migrations or complex schema changes:

cmds.revision(message="description of changes", autogenerate=False)

Then edit the generated file in services/api/src/byte_api/lib/db/migrations/versions/.

Migration File Structure

Each migration file follows this pattern:

# type: ignore
"""Revision ID: <id>
Revises: <parent_id>
Create Date: <timestamp>
"""

from __future__ import annotations

import warnings
import sqlalchemy as sa
from advanced_alchemy.types import GUID, ORA_JSONB, DateTimeUTC
from alembic import op

# revision identifiers, used by Alembic
revision = "<id>"
down_revision = "<parent_id>"
branch_labels = None
depends_on = None


def upgrade() -> None:
    with warnings.catch_warnings():
        warnings.filterwarnings("ignore", category=UserWarning)
        with op.get_context().autocommit_block():
            schema_upgrades()
            data_upgrades()


def downgrade() -> None:
    with warnings.catch_warnings():
        warnings.filterwarnings("ignore", category=UserWarning)
        with op.get_context().autocommit_block():
            data_downgrades()
            schema_downgrades()


def schema_upgrades() -> None:
    """Schema upgrade migrations go here."""
    # ### commands auto generated by Alembic ###
    pass


def schema_downgrades() -> None:
    """Schema downgrade migrations go here."""
    # ### commands auto generated by Alembic ###
    pass


def data_upgrades() -> None:
    """Add any optional data upgrade migrations here!"""
    pass


def data_downgrades() -> None:
    """Add any optional data downgrade migrations here!"""
    pass

Configuration

Alembic.ini

The Alembic configuration uses %(here)s to resolve paths relative to the config file location:

[alembic]
prepend_sys_path = .
script_location = %(here)s/migrations
file_template = %%(year)d-%%(month).2d-%%(day).2d_%%(slug)s_%%(rev)s
timezone = UTC
truncate_slug_length = 40

Database Settings

Migration paths are configured in services/api/src/byte_api/lib/settings.py:

class DatabaseSettings(BaseSettings):
    MIGRATION_CONFIG: str = f"{BASE_DIR}/lib/db/alembic.ini"
    MIGRATION_PATH: str = f"{BASE_DIR}/lib/db/migrations"
    MIGRATION_DDL_VERSION_TABLE: str = "ddl_version"

Model Location

All database models are defined in the byte-common package:

  • Location: packages/byte-common/src/byte_common/models/ (directory with separate model files)

  • Import: from byte_common.models import Guild, User, GitHubConfig, ForumConfig, SOTagsConfig, AllowedUsersConfig

Models are organized as follows:

  • guild.py - Guild configuration

  • user.py - User model

  • github_config.py - GitHub integration settings

  • forum_config.py - Forum channel configuration

  • sotags_config.py - Stack Overflow tags

  • allowed_users_config.py - User permissions

This allows models to be shared between the API service and bot service.

Troubleshooting

“Path doesn’t exist” Error

If you see this error when running alembic commands directly, it’s because alembic.ini uses relative paths. Use the Python API shown above instead.

“Cannot import name ‘get_settings’”

Use load_settings() instead, which returns a tuple of settings objects.

Database Connection Refused

Ensure PostgreSQL is running:

docker compose -f docker-compose.infra.yml ps

Import Errors for Models

Ensure you’re importing from byte_common.models, not the old byte_bot.server.domain.db.models path.

Production Deployment

On Railway, migrations are run programmatically before the service starts using the migration script:

# From railway.json or nixpacks.toml
uv run python -m byte_api.scripts.migrate && uv run litestar run --app byte_api.app:create_app --host 0.0.0.0 --port $PORT

The migration script (services/api/src/byte_api/scripts/migrate.py) uses the Advanced Alchemy Python API:

from advanced_alchemy.alembic.commands import AlembicCommands
from byte_api.lib.db.base import config

if __name__ == "__main__":
    print("Running database migrations...")
    AlembicCommands(sqlalchemy_config=config).upgrade()
    print("Migrations complete!")

Note

The monolithic app CLI was removed in Phase 1.4 of the microservices migration. All migrations now use the Advanced Alchemy Python API directly.

Rollback Plan

In case of migration issues:

  1. Identify the problem revision:

    cmds.current()  # Shows current version

  2. Downgrade to previous stable version:

    cmds.downgrade(revision="<previous_revision_id>")

  3. For complete rollback:

    cmds.downgrade(revision="base")  # WARNING: Drops all tables

  4. Re-apply migrations after fixes:

    cmds.upgrade()

Best Practices

  1. Always test migrations locally before deploying to production

  2. Use transactions - Alembic automatically wraps migrations in transactions

  3. Backup production data before running migrations

  4. Write reversible migrations - Always implement both upgrade() and downgrade()

  5. Test rollback - Verify that downgrade works as expected

  6. Document data migrations - Add comments explaining complex transformations

  7. Avoid destructive changes - Use separate migrations for adding and dropping columns

  8. Keep migrations small - One logical change per migration file

References