Skip to content
Menu

Documentation

Write a migration

Goal: a new revision in one module's migration chain that brings the database in line with the module's models, with row-level security on every new tenant table.

Use it after you add, change or remove a table, a column or an index in a module's models/.

Do not use it for the framework's core tables (outbox, audit, inbox, command log): they have their own chain, applied by sandloom migrate upgrade-core. Never put a module's table in the core chain or a core table in a module chain. To apply migrations, see Run migrations. Background: Migrations.

Prerequisites

  • The development services running and .env pointing at your development database.
  • The module's database at the head of the module's chain: autogenerate compares the models with the live schema, so an unapplied revision would be generated again. Bring it up first:
sandloom migrate upgrade-module notes --apply

Steps

1. Change the models

Edit or add a file in <module>/models/. A new per-tenant table inherits TenantScoped. Table names start with the module name. A deliberately cross-tenant table that the platform reads without a bound tenant sets __system_access__ = True.

2. Generate the revision

sandloom migrate new notes "add archived_at to notes"

The module is named by its short name (notes) or its dotted package (myapp.modules.notes). The command:

  1. runs Alembic autogenerate against the module's own chain: the module's migrations/ directory, its own version table (alembic_version_<chain>), and the database that hosts the module (--database <name> when the module declares several). It compares only the module's tables;
  2. adds row-level security for every TenantScoped table the revision creates: enable_tenant_rls at the end of upgrade() and disable_tenant_rls at the start of downgrade(), under the switch the migration runner sets from the database's configuration (system_access=True for a model with __system_access__ = True);
  3. prints the file it created and each table it covered.

A new tenant table gets these lines (here as rendered by sandloom add projection, which uses the same template):

def upgrade() -> None:
    op.create_table(
        "notes_board_rows",
        sa.Column("tenant_id", sa.Uuid(), nullable=False),
        sa.Column("board_id", sa.Uuid(), nullable=False),
        sa.Column("title", sa.String(length=255), nullable=True),
        sa.Column("version", sa.Integer(), nullable=True),
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
        sa.PrimaryKeyConstraint("tenant_id", "board_id"),
    )
    op.create_index(op.f("ix_notes_board_rows_tenant_id"), "notes_board_rows", ["tenant_id"])
    if op.get_context().config.attributes.get("sandloom_tenant_rls", False):
        enable_tenant_rls(op, "notes_board_rows")


def downgrade() -> None:
    if op.get_context().config.attributes.get("sandloom_tenant_rls", False):
        disable_tenant_rls(op, "notes_board_rows")
    op.drop_index(op.f("ix_notes_board_rows_tenant_id"), table_name="notes_board_rows")
    op.drop_table("notes_board_rows")

--rev-id <id> sets an explicit revision id.

3. Review the revision

Autogenerate is a draft, not a decision. Read the file and fix what it cannot know:

  • a renamed column appears as a drop and an add: replace them with op.alter_column(..., new_column_name=...);
  • a new NOT NULL column on a table with rows needs a server default or a data step;
  • data migrations (backfills) are yours to write; keep them idempotent;
  • RLS is added only for tables the revision creates. A table that becomes tenant-scoped later needs enable_tenant_rls by hand.

A money ledger (a model with the MoneyLedger mixin) is refused at import unless its operation-key column is NOT NULL and unique, so the constraint is in the model and autogenerate renders it.

4. Apply it

sandloom migrate upgrade-module notes --apply

Without --apply the command prints what it would do.

Other forms

  • sandloom migrate revision notes -m "<message>" --autogenerate is the same autogenerate without the RLS step. Prefer sandloom migrate new.
  • sandloom migrate revision notes -m "<message>" writes an empty revision, for a pure data migration.

When to edit a revision instead

Edit an existing revision, including the baseline 0001_<module>_baseline.py that sandloom add module renders, only before its first deployment: while it has run on no shared database, staging or production. Typical case: you replace the generated example table with your real tables before the module ships. Rebuild your development database afterwards (downgrade the module, or drop and re-create the database).

After a revision has run anywhere other than your machine, never edit it: add a new revision. Other databases already recorded the old one and would never run your change.

Verify

sandloom test notes

The tests build tables from the models, but the gates check the chain: a table or index that no revision creates fails (SANDLOOM-T002), and models that differ from the chain head fail (SANDLOOM-T006).

Errors you may hit

  • SANDLOOM-T002: a table or index of the models is not created by the module's chain.
  • SANDLOOM-T006: the models differ from the chain head; run sandloom migrate new.
  • SANDLOOM-C014: a per-tenant table does not inherit TenantScoped.
  • "Target database is not up to date" (only with --against-database): the module's database is behind the chain head; run sandloom migrate upgrade-module <module> --apply first. Without the flag, sandloom migrate new compares against a scratch database with every chain at head, on the test server of .env.test (+ SANDLOOM_TEST_*), and needs no database of yours to be migrated.
  • "Module not found": the module is not loaded by the composition root (SANDLOOM_COMPOSITION_ROOT, and SANDLOOM_MODULES if set).