Skillversitas

Migrations and Schema Changes

A schema is not static: fields are added, renamed, dropped, and constraints changed as the app grows. create_table() handles the first creation, but after that the schema changes have to be managed deliberately - that is what migrations are, and Peewee's playhouse.migrate module is the tool. The model is the same as every migration system: a migration is a named step that transforms the schema from one version to the next, the steps are recorded in the database, and the database is always advanced by replaying the steps that have not yet run. This chapter covers the migrate object, the migration operators, the script workflow, and the rules that keep a schema change reversible and safe.

The migrate Object and Operators

from peewee import *
from playhouse.migrate import (SqliteMigrator, PostgresqlMigrator,
                               MySQLMigrator, migrate)

# the migrator is dialect-specific - it knows the DDL for that backend
migrator = SqliteMigrator(db)

# the operators are the schema operations, expressed against the model:
operations = [
    # add a column to an existing table
    migrator.add_column("article", "summary",
                        CharField(max_length=300, null=True)),
    # drop a column
    migrator.drop_column("article", "legacy_flag"),
    # add / remove an index
    migrator.add_index("article", ["slug"], unique=True),
    migrator.drop_index("article", ["slug"]),
    # change a column's type / constraints
    migrator.alter_column_type("article", "body", TextField()),
    # create / drop a table from a model
    migrator.create_model(Tag),
    migrator.drop_model(Tag),
]

# apply them as ONE migration (a transaction on the supporting backends):
migrate(*operations)

The migrator is constructed per backend (SqliteMigrator, PostgresqlMigrator, MySQLMigrator) because the DDL for a schema change is dialect-specific - the same logical "add a column" is different SQL in each database. The operators (add_column, drop_column, alter_column_type, the index operations, create_model/drop_model) are the vocabulary, each expressed against the table name and the field definition. migrate(*operations) applies the given operations as a single migration step. The field passed to an operator is the same field object the model uses, so the column the migration adds is the column the model declares - the schema and the model stay in lockstep because they are built from the same definition.

The Script Workflow

# migrations/001_add_summary.py - one file per change, numbered
from peewee import *
from playhouse.migrate import SqliteMigrator, migrate
from database import db
from models import Article

migrator = SqliteMigrator(db)

def up():
    # the forward change
    migrate(
        migrator.add_column("article", "summary",
                            CharField(max_length=300, null=True)),
    )

def down():
    # the REVERSE change - a migration is only safe if it can be undone
    migrate(
        migrator.drop_column("article", "summary"),
    )

if __name__ == "__main__":
    db.connect()
    up()
    # down()   # uncomment to roll this one back

The workflow is one script per change, numbered in the order they were made (001_..., 002_...), each with an up() (the forward change) and a down() (the reversal). Applying the schema is running the scripts in order; rolling back is running the down() of the latest script. The migrate call records that the step ran, so a re-run does not re-apply it (the recorded state is what makes the apply idempotent across deploys). The model file and the migration must agree - when up() adds the summary column, the Article model gains the summary field in the same change, so the code and the schema move together. A change that is not expressible as an up/down pair (a data transformation with no clean inverse) is the case to treat as irreversible and document as such.

Data Migrations: When the Change Is Not Just DDL

# migrations/003_split_names.py - a change that moves DATA
def up():
    # add the new columns (nullable, so the backfill can run)
    migrate(
        migrator.add_column("author", "first_name", CharField(null=True)),
        migrator.add_column("author", "last_name", CharField(null=True)),
    )
    # backfill from the existing data, in the app's model language:
    for a in Author.select():
        parts = a.name.split(" ", 1)
        a.first_name = parts[0]
        a.last_name = parts[1] if len(parts) > 1 else ""
        a.save()
    # then tighten: make them not-null
    migrate(
        migrator.alter_column_type(
            "author", "first_name", CharField(max_length=60)),
    )

def down():
    # recombine, then drop the split columns
    for a in Author.select():
        a.name = a.first_name + (" " + a.last_name if a.last_name else "")
        a.save()
    migrate(
        migrator.drop_column("author", "first_name"),
        migrator.drop_column("author", "last_name"),
    )

Some changes move data, not just structure: splitting a name column into first_name/last_name, or populating a new required column from derived values. The pattern is the three-step - add the new column nullable, backfill it with a query loop in the model language (the ORM reading and writing the rows), then tighten the constraint - and the down() reverses the same three steps in reverse order. The backfill runs as ordinary model code between the DDL steps, which is the advantage of the ORM-based migration: the data movement is written against the model, not raw SQL, and it is testable as a script. The rule that makes a data migration safe: the intermediate state (new columns present, backfill in progress) must be a state the app can run in, because a deploy can pause between the DDL and the backfill - the nullable-first ordering is what keeps that pause safe.

The Working Rules

  • One script per change, numbered, with up and down. The number is the order; the down is what makes a rollback possible.
  • The model and the migration move together. A column added in up is a field added to the model in the same change - a schema the model does not describe is a bug waiting to surface in a query.
  • Nullable-first for additive changes. Add the column nullable, backfill, then constrain - the intermediate state is always one the running app tolerates.
  • Never edit an applied migration. A change to an already-run script is a new script - the history is the record, and rewriting it desynchronizes the databases that ran the old version.
  • Destructive changes are deliberate and tested. A drop_column loses data; run the up and the down on a copy of the real data before applying it to production.

Summary

Schema changes after creation are managed with playhouse.migrate: a dialect-specific migrator supplies the DDL operators (add_column, drop_column, alter_column_type, the index operations, the model create/drop), and migrate(*operations) applies a step, recording that it ran. The workflow is one numbered script per change with an up and a reversible down, applied in order, and the model file changes in the same step as the schema it describes. Data migrations follow the nullable-first pattern - add nullable, backfill with model code, then constrain - so the intermediate state is one the app can run in. The rules keep the history honest: never edit an applied migration, treat destructive changes as testable on a data copy, and keep the schema and the model in lockstep - the migration is the mechanism that makes the schema a managed artifact instead of an accident of create_table.