KAHIBARO
Discord Login Register ☰
☰

12.13. Database Migrations

Table of Contents

Why Database Migrations Matter

When you start a new backend project, the database schema feels simple. You create a few tables, run some CREATE TABLE statements, and everything works.

Then the application grows.

You add new features, change how data is stored, rename columns, split tables, or remove fields that are no longer used. In production you already have real data that you must not lose.

This is where database migrations become essential.

A database migration is a controlled change to your database schema, stored as code, that you can apply, track, and if needed, roll back.

In this chapter, we focus on what migrations are, why they exist, and how they are typically used with ORMs. Detailed usage of Alembic itself is covered later in the Alembic chapter, so here we stay at the conceptual and practical level.

The Core Idea of Migrations

A database schema is not static. It evolves with your code.

If you treat the schema as something you edit manually in a database console, you quickly run into problems:

Migrations solve this by:

You can think of it as Git for your database schema.

Schema-as-Code vs Manual Changes

Without migrations, you might do something like:

sql 
ALTER TABLE users ADD COLUMN is_admin BOOLEAN DEFAULT FALSE;

You run this directly in your production database console. It works, but:

With migrations, you instead:

  1. Create a migration file (manually or auto generated).
  2. Put the ALTER TABLE statement inside that file.
  3. Commit the migration file to version control.
  4. Run a migration command in each environment to apply it.

Now the change is:

Typical Migration Workflow with an ORM

Most ORMs support a similar workflow, even though tools differ.

Here is a common pattern:

  1. Define or update models in code

For example, using SQLAlchemy models:

python 
   from sqlalchemy import Column, Integer, String, Boolean
   from sqlalchemy.orm import declarative_base
   Base = declarative_base()
   class User(Base):
       __tablename__ = "users"
       id = Column(Integer, primary_key=True)
       email = Column(String, unique=True, nullable=False)
       is_active = Column(Boolean, default=True)
  1. Generate a migration

A migration tool (like Alembic) compares the current database schema with the models, and generates a migration file, for example:

python 
   def upgrade():
       op.create_table(
           'users',
           sa.Column('id', sa.Integer, primary_key=True),
           sa.Column('email', sa.String(), nullable=False),
           sa.Column('is_active', sa.Boolean(), nullable=True),
       )
   def downgrade():
       op.drop_table('users')
  1. Apply the migration

You run a command that applies new migrations to your database:

bash 
   alembic upgrade head

The database now has the users table.

  1. Evolve the schema

Later you decide to add a full_name column:

python 
   class User(Base):
       __tablename__ = "users"
       id = Column(Integer, primary_key=True)
       email = Column(String, unique=True, nullable=False)
       full_name = Column(String, nullable=True)  # new
       is_active = Column(Boolean, default=True)

You create a new migration that contains something like:

python 
   def upgrade():
       op.add_column('users', sa.Column('full_name', sa.String(), nullable=True))
   def downgrade():
       op.drop_column('users', 'full_name')

Then you apply it again with upgrade.

The important thing here is that your schema changes are synchronized with your code and every change is captured in a migration.

Versioning and Migration History

Migrations are usually stored as files, each with a unique identifier.

A typical directory might look like:

text 
migrations/
    20230915_01_create_users_table.py
    20230920_02_add_full_name_to_users.py
    20231001_03_create_products_table.py

Inside the database, a special table keeps track of which migrations have run, for example:

version_idapplied_at
20230915_01_create_users...2023-09-15 10:15:22
20230920_02_add_full_name..2023-09-20 17:02:10

This lets your migration tool know:

Always apply migrations in sequence and never manually edit previously applied migrations in a shared project. If you change an old migration that has already run in production, you risk corrupting the schema or losing data.

Upgrades and Downgrades

Each migration typically has two main operations:

Example, adding a column:

python 
def upgrade():
    op.add_column("users", sa.Column("full_name", sa.String(), nullable=True))
def downgrade():
    op.drop_column("users", "full_name")

Example, renaming a column:

You might simulate renaming by using ALTER TABLE operations appropriate to your database.

For PostgreSQL via Alembic operations:

python 
def upgrade():
    op.alter_column("users", "username", new_column_name="login")
def downgrade():
    op.alter_column("users", "login", new_column_name="username")

Not all schema changes are easily reversible. For example, dropping a column will lose data.

In those cases:

You should design migrations carefully when data must be preserved.

Types of Schema Changes Managed by Migrations

Migrations typically handle:

Change typeExamples
Creating / droppingCreate or drop tables, indexes, constraints
Modifying columnsAdd, remove, rename, change type, change nullability
ConstraintsAdd or drop primary keys, foreign keys, unique constraints
DefaultsAdd or change default values
RelationshipsAdd foreign keys for relationships
Data fixesOne time data updates or backfills

A migration file is not only for structure. It can also include data migrations, where you transform existing rows.

Example, splitting a name column into first_name and last_name:

  1. Schema change:
python 
   def upgrade():
       op.add_column("users", sa.Column("first_name", sa.String(), nullable=True))
       op.add_column("users", sa.Column("last_name", sa.String(), nullable=True))
  1. Data migration:

In the same migration you might, in pseudocode:

python 
   from sqlalchemy import table, column, String
   from sqlalchemy.orm import Session
   users_table = table(
       "users",
       column("id"),
       column("name", String),
       column("first_name", String),
       column("last_name", String),
   )
   def upgrade():
       # add columns first (schema)
       op.add_column("users", sa.Column("first_name", sa.String(), nullable=True))
       op.add_column("users", sa.Column("last_name", sa.String(), nullable=True))
       bind = op.get_bind()
       session = Session(bind=bind)
       for user in session.execute(sa.select(users_table)).all():
           if user.name and " " in user.name:
               first, last = user.name.split(" ", 1)
           else:
               first, last = user.name, None
           session.execute(
               users_table.update()
               .where(users_table.c.id == user.id)
               .values(first_name=first, last_name=last)
           )
       session.commit()
  1. Optionally drop the old name column in a later migration.

This combination of structure and data changes is powerful but also risky, so you must test migrations thoroughly.

Migrations in Different Environments

You usually have at least these environments:

You want the same database schema in all of them.

Typical process:

  1. Create or modify models locally.
  2. Create a new migration file.
  3. Apply the migration locally.
  4. Run tests.
  5. Commit and push the migration.
  6. In CI or deployment scripts, run migrations automatically on the staging and production databases.

Example deployment command:

bash 
# Inside your deployment script
alembic upgrade head

If the migration fails in staging, you fix it before it ever touches production.

Never manually apply schema changes to production that are not represented by a migration file under version control. This leads to schema drift, which is very hard to fix later.

Common Migration Scenarios

Here are some common change scenarios and how migrations help.

Adding a new optional column

You add a phone_number column that is not required.

python 
  op.add_column("users", sa.Column("phone_number", sa.String(), nullable=True))

Adding a new required column

You add a username column that must be unique and not null.

This is more complex because existing rows do not have a username yet.

A safer approach:

  1. Add the column as nullable, without a uniqueness constraint.
  2. Backfill data in a data migration.
  3. Add constraints and make it non nullable in a later migration.

Example sequence:

Migration 1:

python 
def upgrade():
    op.add_column("users", sa.Column("username", sa.String(), nullable=True))

Migration 2:

python 
def upgrade():
    # backfill usernames, for example copy from email prefix
    # (example, actual code depends on your ORM and tool)
    pass

Migration 3:

python 
def upgrade():
    op.create_unique_constraint("uq_users_username", "users", ["username"])
    op.alter_column("users", "username", nullable=False)

This multi step approach reduces the risk of breaking production when data already exists.

Renaming a column

Some databases have a direct rename operation, others need a workaround.

If direct rename is not available, a typical pattern:

  1. Add new column.
  2. Copy data from old column to new.
  3. Update code to use new column.
  4. Drop old column in a later migration, after deployment is stable.

Changing column type

For example, from Integer to BigInteger or from String to Text.

You must:

Migrations give you a central place to implement and test such changes.

Auto Generated vs Manual Migrations

Many tools can auto generate migrations by comparing your ORM models with the actual database schema.

Pros:

Cons:

A good approach:

  1. Run auto generation.
  2. Carefully inspect the migration file.
  3. Edit it if necessary.
  4. Add or refine downgrade logic.
  5. Write data migrations by hand if needed.

Example review questions:

Migrations and Application Deployment

Migrations affect how you deploy your backend.

Safe pattern:

  1. Build new application version.
  2. Run migrations on the database.
  3. Start new application version.

Why run migrations before starting the new code?

Because the new code often expects the new schema. If the schema is not ready, the code might crash.

Sometimes you need backward compatible migrations. That means:

Typical backward compatible sequence for a breaking change:

  1. Add new columns or tables without removing old ones.
  2. Update code to write to both old and new columns.
  3. Once all instances run new code, migrate data completely.
  4. In a later migration, remove old columns.

Migrations are a key part of safe, zero downtime deployment strategies.

Testing Migrations

You should test migrations, especially in larger systems.

Useful tests:

Example simple test flow:

bash 
# Start with empty test database
alembic upgrade head
# Run application tests
pytest

Some projects also snapshot database schemas and compare them before and after migrations.

Practical Tips and Best Practices

Here are practical guidelines that help when working with migrations.

Keep migrations small and focused

Each migration should represent a clear, limited change.

Bad:

Better:

This makes:

Never edit applied migrations in a shared project

If you already ran migration 20230915_01 on any shared database:

Editing old migrations breaks the logic of the migration history.

Use meaningful names

Name migrations by what they do, not just timestamps.

Compare:

The first is much easier to understand when scanning history.

Beware of destructive operations

Dropping tables or columns removes data forever.

Before you:

Make sure:

Consider long running operations

Some operations can lock large tables for a long time, for example:

For big databases you might:

How Migrations Fit with the ORM Layer

Migrations and ORM models must stay synchronized.

Typical checklist when changing models:

  1. Modify ORM models.
  2. Generate and inspect migration.
  3. Apply migration to your local database.
  4. Run tests.
  5. Commit models and migrations together.

If you change models but forget to update migrations:

So in a mature project:

Summary

Database migrations are a foundational part of backend development with ORMs.

They let you:

In practice, you will create, review, and run migrations regularly. Mastering this workflow is essential for building reliable, maintainable backend systems.

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3706 / 24h
Impressum / Imprint
Datenschutz / Data Policy