Skip to content

Latest commit

 

History

History
144 lines (96 loc) · 4.29 KB

README_dev.md

File metadata and controls

144 lines (96 loc) · 4.29 KB
Raw

Developer Guide: Database Migrations

This guide explains how to manage database migrations in the LLM App Boilerplate project.

Prerequisites

  • Docker and Docker Compose installed on your system
  • Make utility installed

Understanding the Migration Setup

  1. The project uses Alembic for database migrations. Alembic is a lightweight database migration tool for SQLAlchemy, which allows you to incrementally change your database schema over time.
  2. Alembic migration files are stored in recommender-be/alembic/versions/.
  3. The initial database schema is defined in 20230921_001_initial_database_schema.py.

Initial Database Schema

The initial migration (20230921_001_initial_database_schema.py) creates the following tables:

  1. users table:

    • id (UUID, primary key)
    • email (String, unique)
    • hashed_password (String)
    • is_active (Boolean)
    • is_superuser (Boolean)
    • google_id (String, unique)
    • created_at (DateTime)
    • updated_at (DateTime)

  2. sessions table:

    • id (UUID, primary key)
    • user_id (UUID, foreign key to users.id)
    • session_token (String, unique)
    • expires_at (DateTime)
    • created_at (DateTime)

Note: This initial migration is crucial as it sets up the foundational schema for the application. Make sure to apply this migration before running the application for the first time.

Step-by-Step Guide

1. Creating a New Database Schema

To create a new database schema:

  1. Open recommender-be/models/models.py.
  2. Define your new models using SQLAlchemy ORM.
  3. Example:
from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.dialects.postgresql import UUID
import uuid

class NewModel(Base):
    __tablename__ = "new_table"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = Column(String, index=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

2. Creating a New Migration

After modifying your models, create a new migration:

make migrate-create name="describe your changes"

This will:

  1. Run the backend service using Docker Compose.
  2. Execute Alembic's autogenerate feature to create a new migration file in recommender-be/alembic/versions/.

3. Reviewing the Migration

  1. Navigate to recommender-be/alembic/versions/.
  2. Open the newly created migration file.
  3. Review the changes to ensure they accurately reflect your intended schema modifications.

4. Applying Migrations

To apply all pending migrations:

make migrate-up

This will:

  1. Run the backend service using Docker Compose.
  2. Execute Alembic's upgrade command to apply all pending migrations to the database.

5. Verifying the Changes

After applying migrations, verify the changes:

docker compose exec db psql -U llm_user -d llm-boilerplate -c "\dt"

This will list all tables in the database.

To see the structure of a specific table:

docker compose exec db psql -U llm_user -d llm-boilerplate -c "\d table_name"

6. Reverting Migrations

To revert the last applied migration:

make migrate-down

7. Viewing Migration History

To view the migration history:

make migrate-history

Best Practices

  1. Always review autogenerated migration files before applying them.
  2. Keep your database models in recommender-be/models/models.py up-to-date.
  3. Commit migration files to version control.
  4. Test migrations thoroughly in a non-production environment before applying them to production.
  5. When making changes to the database schema, always create a new migration rather than modifying existing ones.
  6. Use descriptive names for your migrations to make it easier to understand the purpose of each change.

Troubleshooting

If you encounter issues with migrations:

  1. Ensure your database is in a consistent state by running make migrate-history to check the current migration status.
  2. If there are inconsistencies, you may need to manually adjust the migration history or revert to a known good state.
  3. For complex schema changes, consider breaking them down into multiple smaller migrations to reduce the risk of errors.

For more detailed information about Alembic and its commands, refer to the Alembic documentation.