Database migrations

Introduction

Database migrations are a way to version your database schema. If you need to add a new table, column or index to your database, you should create a migration file to do so. This way, you can easily upgrade — or downgrade — you database in an organized way.

Expanse relies on Alembic to manage database migrations. Alembic is a database migration tool that pairs seamlessly with SQLAlchemy that powers Expanse's own database component.

Creating a migration

To create a new migration, you can use the ./beam make migration command. The new migration file will be located in the database/migrations directory. The migration filename is timestamped and includes the revision identifier.

./beam make migration "Create the posts table"

If this is the first time you're creating a migration, you should see something like this:

  - Creating directory database/migrations... Done
  - Creating directory database/migrations/versions... Done
  - Generating database/migrations/script.py.mako... Done
  - Generating database/migrations/env.py... Done
  - Generating database/migrations/alembic.ini... Done
  - Generating database/migrations/versions/2024_09_03_202358_4bea4cb4b79a_initial_migration.py... Done

On the first run of the make migrate command, Expanse will create the necessary directories and files for Alembic to run without additional configuration.

Migration file

The migration file will contain two methods: upgrade and downgrade. The upgrade method is used to apply any changes to the database, while the downgrade method is used to revert those changes.

It's a standard Alembic migration file, so you can use the Alembic API to create tables, columns, indexes, etc.

"""
Create the posts table

Revision ID: e776feeeb86d
Revises:
Create Date: 2024-08-30 21:57:40.933029
"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision: str = "e776feeeb86d"
down_revision: str | None = None


def upgrade() -> None:
    op.create_table("posts")


def downgrade() -> None:
    op.drop_table("posts")

Most of the changes to your database schema can be done using the op object. To learn more about the available operations, refer to the corresponding Alembic documentation.

Auto-generating migration files

Expanse can automatically detect changes to your models and generate migration files for you. It's as simple as running the make migration command with the --auto option.

./beam make migration "Create the posts table" --auto

Let's say you added a Post model to your application:

from sqlalchemy.orm import Mapped, mapped_column

from app.models.model import Model, primary_key


class Post(Model):
    __tablename__ = "posts"

    id: Mapped[primary_key] = mapped_column(init=False)
    title: Mapped[str]
    content: Mapped[str]

The following migration file will be generated:

"""
Create the posts table

Revision ID: e776feeeb86d
Revises:
Create Date: 2024-08-30 21:57:40.933029
"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision: str = "e776feeeb86d"
down_revision: str | None = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "posts",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("title", sa.String(), nullable=False),
        sa.Column("content", sa.Text(), nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table("posts")
    # ### end Alembic commands ###

Auto-generated commands are clearly marked in the migration file, and you can any additional operations if needed. You should always review the generated migration file to ensure it meets your requirements.

Running migrations

To apply all pending migrations, execute the ./beam db migrate command.

./beam db migrate

If you prefer applying a specific number of migrations, you can use the --step option.

./beam db migrate --step 2

This command will apply the next two pending migrations.

If instead of running the migrations directly, you want to see the SQL commands that will be executed, you can use the --dry-run option.

./beam db migrate --dry-run

This will display the SQL commands that will be executed without actually applying them.

Rolling back migrations

If you need to roll back the last migration, you can use the ./beam db rollback command.

./beam db rollback

If you want to rollback a specific number of migrations, you can use the --step option.

./beam db rollback --step 2

This command will roll back the last two migrations.