Schema Migration — Essence, Types, and Database Schema Migration Tools

What is Schema Migration

Schema Migration is the practice of managing database structure changes through versioned files that are sequentially applied to different environments. Each file contains a set of SQL commands: creating a table, adding a column, modifying an index, or updating constraints. The migration tool tracks applied versions and ensures that each change is executed exactly once.

Unlike Data Migration, which transfers table contents, Schema Migration only manages structure — DDL operations. This is a fundamental distinction: Schema Migration runs before Data Migration, creating the target schema into which data is then loaded. According to Redgate (2024), 62% of incidents in production databases are related to manual schema changes without migration scripts.

Migration Versioning

Each Schema Migration gets a unique identifier — typically a version (V1, V2) or a timestamp. The tool stores a list of applied migrations in a special table (flyway_schema_history, alembic_version). On startup, it compares the list with files in the classpath and applies only new ones. Idempotence is a key property: re-running does not cause side effects.

Types of Schema Changes

Typical Schema Migration operations: creating tables (CREATE TABLE), adding columns (ALTER TABLE ADD COLUMN), changing types, creating indexes, adding foreign keys, and updating sequences. More complex migrations include renaming columns while preserving data, splitting a table into multiple ones, and replicating a schema across shards.

Why Database Schema Migration is Needed

Without Schema Migration, developers manually change the database — edit DDL in the console, add columns in the dev environment, and copy them to staging from memory. The result: schema drift between environments, lost changes during deployment, and broken migrations on production. Schema Migration solves three key problems: consistency, reproducibility, and audit.

Consistency Across Environments

When the database structure is described in code, it is identical across dev, staging, and production. A developer cannot forget to apply a change — the tool will execute all missed migrations sequentially. If a column is missing on production but the code requires it, the application will fail with an error. Automatic verification eliminates this scenario.

Reproducibility for New Developers

A new team member runs flyway migrate and gets the current schema in seconds — without a production dump or manual DDL queries. This is especially important in a microservice architecture, where each service has its own database and the schema is built from dozens of migrations. Full reproducibility reduces onboarding from days to minutes.

Change Audit

Each Schema Migration is stored in the version control system alongside the application code. You can open a Pull Request, see the exact SQL commands for the schema change, and conduct a code review. In the event of an incident, it is easy to determine which migration was applied last and who authored it. Git history provides a complete trail of database changes over the entire project lifetime.

Schema Migration Tools

There are dozens of Schema Migration tools available for different languages and platforms. The choice depends on the technology stack, migration description format, and rollback requirements. Let’s look at the main categories and popular tools.

How to Choose a Tool

For Java/Kotlin stacks — Flyway as the lightest and most predictable. For projects with frequent rollbacks — Liquibase, which has rollback built into its architecture. For Python/Django — Alembic as the standard SQLAlchemy tool. For startups without a DevOps engineer — choose the tool with the least configuration: Flyway only requires a script file and the migrate command.

Commercial Solutions

Besides open-source tools, there are commercial ones: Redgate SQL Change Automation, Datical DB, and DBmaestro. They provide visual schema comparison, automatic conflict resolution, and CI/CD pipeline integration. However, for most projects, Flyway or Alembic cover 100% of needs without additional licenses.

Migration with Flyway

Let’s look at a practical Schema Migration example in Kotlin with Flyway. We will create a migration that adds an orders table to a PostgreSQL database. Flyway automatically creates the flyway_schema_history table and tracks applied versions.

-- V1__Create_Orders_Table.sql
CREATE TABLE orders (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL,
    total_amount    DECIMAL(10,2) NOT NULL,
    currency        VARCHAR(3) NOT NULL DEFAULT 'USD',
    status          VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
);

Connecting Flyway in a Kotlin project via the dataSource configuration. After setup, the flyway:migrate command will apply all new migrations from the classpath.

// FlywayConfig.kt — Flyway configuration in Spring Boot
import org.springframework.context.annotation.Bean
import org.springframework.context.annotation.Configuration
import org.flywaydb.core.Flyway

@Configuration
class FlywayConfig {
    @Bean
    fun flyway(dataSource: DataSource): Flyway {
        return Flyway.configure()
            .dataSource(dataSource)
            .locations("classpath:db/migration")
            .baselineOnMigrate(true)
            .load()
    }
}

Alembic for Python Projects

Alembic is a Schema Migration tool for Python built on top of SQLAlchemy. Its key feature is auto-generating scripts by comparing the SQLAlchemy model with the current database schema. Alembic is suitable for Django, FastAPI, and Flask projects.

Initialization and Creating a Migration

After initialization (alembic init alembic) and configuring the connection string, the alembic revision --autogenerate command scans SQLAlchemy models and generates a migration script. The developer only needs to review the generated code and apply it via alembic upgrade head. Autogenerate saves hours of manual DDL writing.

# models.py — SQLAlchemy model for auto-generation
from sqlalchemy import Column, Integer, String, DateTime, Enum
from sqlalchemy.orm import DeclarativeBase
import enum

class OrderStatus(enum.Enum):
    pending = "pending"
    paid = "paid"
    shipped = "shipped"

class Base(DeclarativeBase):
    pass

class Order(Base):
    __tablename__ = "orders"
    id = Column(Integer, primary_key=True)
    status = Column(Enum(OrderStatus), nullable=False)
    created_at = Column(DateTime, nullable=False)

Best Practices for Schema Migration

Experienced teams have developed a set of Schema Migration rules that reduce the risk of failures and simplify debugging. Following these practices is a sign of a mature engineering culture. Five key rules cover design, testing, and deployment of migrations.

One Migration — One Change

Each Schema Migration should make exactly one logical change: create a table, add a column, or modify an index. Mixing multiple operations in one migration complicates rollback — if the second operation fails, the first has already been applied and needs to be rolled back separately. Small steps are the foundation of reliable migrations.

Never Edit Published Migrations

Once a migration has been applied to production, it cannot be modified — only a new migration that fixes the issue can be created. Editing a published migration breaks the tracker: developers with a different database will see a hash mismatch. Immutable migrations guarantee predictable behavior across all environments.

Test on a Production Copy

Before applying a Schema Migration to production, run it on a copy of production data. The goal is to check execution speed, table lock presence, and correctness of changes. For large tables, ALTER TABLE can block writes for hours — testing will identify this in advance. Staging with a production dump is a mandatory step.

Avoid NOT NULL for New Columns

A new column without a default value with NOT NULL is a common cause of migration failure. In existing records, the value will be NULL, and NOT NULL will cause an error. Best practice: create the column with a default and nullable, then add NOT NULL in a separate migration after populating the data.