Chaos and Order

💡 왼쪽 원문을 읽으면서 오른쪽에 따라 써보세요. Tab 키로 힌트를 받을 수 있습니다.

원문 렌더가 준비되기 전까지 텍스트 가이드로 표시합니다.

Introduction

There was a time when application code lived in Git while the database schema was changed by someone typing ALTER TABLE by hand. The result was predictable: the production schema drifted away from staging, and a new teammate's local environment ended up in yet another state. Schema migration tools exist precisely to solve this problem.

The core promise of a schema migration tool is simple. It says: "Version your schema changes like code, and apply them in the same order, reproducibly, in any environment." In this article we look at five of the most widely used tools — Flyway, Liquibase, golang-migrate, Alembic, and Atlas. We compare which model each one adopts, how they handle version control and checksums, and how far they go in supporting rollback and CI integration.

It helps to know one thing before choosing a tool. Migration tools fall into two broad philosophies. One stacks up discrete "change units" in a **versioned** approach. The other has you declare the final desired state and lets the tool compute the path to get there — the **declarative** approach. Understanding this distinction is the starting point for any tool choice.

Core Concepts

Versioned Migrations

Versioned migration is the most intuitive model. Whenever you want to change the schema, you write a new migration file, and the tool applies each file exactly once, in order. Whether a file has been applied is recorded in a tracking table.

Flyway, for example, creates a schema history table, while golang-migrate uses a schema_migrations table to record how far migrations have been applied. The next time you run the tool, it selects only the migrations that have not yet been applied and runs them.

[V1] create table -> applied (recorded in history)

[V2] add column -> applied (recorded in history)

[V3] add index -> not yet applied <- only this runs next time

The strength of this model is that the change history is preserved verbatim. The migration files themselves become the evidence of who changed what and when. The downside is that files accumulate forever, and two developers who create migrations with the same version number at the same time will collide.

Declarative Migrations

Declarative migration takes a different stance. The developer declares only the desired final state: "I want the schema to end up looking like this." The tool then diffs the current database against the target and automatically generates the migration that bridges the gap. Atlas and parts of the Prisma workflow adopt this approach.

current schema (DB) + target schema (declared file)

| |

+--------- diff --------+

auto-generated change plan

The appeal of the declarative approach is that a human no longer has to compute "what needs to change right now." That said, an auto-generated change is not always safe, so a human review step over the generated migration is mandatory.

Checksums and Integrity

Most tools record a **checksum** (typically a CRC32 or a hash) of the contents of each already-applied migration file. On the next run, if the file contents have changed, the checksum will differ and the tool raises an error. This enforces the invariant that you must not quietly edit a migration that has already been applied. The rule is simple: never modify an applied migration; add a new one instead.

Flyway — A Simple, SQL-First Tool

Flyway is built on the philosophy that "SQL is the source of truth." You write most migrations as ordinary SQL files, and the filename convention itself becomes the version.

Filename Convention

Flyway's versioned migrations follow a strict naming rule that begins with V.

V1__create_users_table.sql

V2__add_email_to_users.sql

V2.1__add_index_on_email.sql

R__refresh_reporting_view.sql

V denotes a versioned migration, and R denotes a repeatable one (re-run whenever its checksum changes). After the version number come two underscores, then a description.

A Real Example

-- V1__create_users_table.sql

CREATE TABLE users (

id BIGSERIAL PRIMARY KEY,

email VARCHAR(255) NOT NULL UNIQUE,

created_at TIMESTAMPTZ NOT NULL DEFAULT now()

);

-- V2__add_status_to_users.sql

ALTER TABLE users

ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

Applying them is a single command.

flyway -url=jdbc:postgresql://localhost:5432/app \

-user=app -password=secret migrate

Flyway records the checksum, application time, and execution duration of each version in its flyway_schema_history table. The Community edition is free, but automatic rollback (undo) is available only in the paid Teams and Enterprise editions. On the free version, you handle rollback by writing a corrective migration and applying it forward.

Baseline

When introducing Flyway to an existing database that already holds data, you set a starting point with the baseline command. This treats the prior schema as already present and applies only later migrations.

flyway -baselineVersion=1 baseline

Liquibase — A Changelog-Centric, Multi-Format Tool

Instead of writing SQL directly, Liquibase describes the schema through an abstract change unit called a **changelog**. A changelog can be written in XML, YAML, JSON, or SQL. Each change unit is called a changeSet and carries a unique id and author.

XML Changelog Example

xmlns="http://www.liquibase.org/xml/ns/dbchangelog"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

YAML Changelog Example

The same change written in YAML looks like this.

databaseChangeLog:

- changeSet:

id: 1

author: youngju

changes:

- createTable:

tableName: users

columns:

- column:

name: id

type: BIGINT

autoIncrement: true

constraints:

primaryKey: true

nullable: false

The biggest advantage of an abstract changelog is **database independence**. A single createTable line becomes BIGSERIAL on PostgreSQL and AUTO_INCREMENT on MySQL, translated appropriately. If your product must support several kinds of databases at once, this is a major draw.

Rollback

Liquibase treats rollback as a first-class citizen. For changes like createTable it can infer the inverse operation automatically, and for complex changes you can specify a rollback block explicitly.

liquibase --changeLogFile=changelog.xml rollbackCount 1

Liquibase manages applied history in a DATABASECHANGELOG table and prevents concurrent runs with a lock held in a DATABASECHANGELOGLOCK table.

golang-migrate — Lightweight and Explicit up/down

golang-migrate is a lightweight tool widely used in the Go ecosystem. Its philosophy is clear: every migration consists of two SQL files, up and down, and the tool simply runs that SQL as written.

File Structure

migrations/

000001_create_users.up.sql

000001_create_users.down.sql

000002_add_status.up.sql

000002_add_status.down.sql

-- 000001_create_users.up.sql

CREATE TABLE users (

id BIGSERIAL PRIMARY KEY,

email VARCHAR(255) NOT NULL UNIQUE

);

-- 000001_create_users.down.sql

DROP TABLE users;

Running It

migrate -database "postgres://app:secret@localhost:5432/app?sslmode=disable" \

-path ./migrations up

migrate -database "postgres://app:secret@localhost:5432/app?sslmode=disable" \

-path ./migrations down 1

golang-migrate records the current version and a dirty flag in a schema_migrations table. If a migration fails partway through, it is left in a dirty state, and you must clean it up with the force command before retrying. Rollback is only as good as the down files you wrote, so the habit of writing down SQL diligently matters. Automatic checksum verification is not provided out of the box, so the discipline of never editing an applied migration is one the team must uphold itself.

Alembic — ORM Migrations for SQLAlchemy

Alembic is the migration tool that pairs with Python's SQLAlchemy ORM. You write migrations as Python code, and its most powerful feature is autogenerate, which compares the ORM models against the actual database and **auto-generates** a migration.

A Revision File

Each migration forms a chain linked by a revision identifier and a down_revision. This chain determines the order of application.

"""add status to users

Revision ID: a1b2c3d4

Revises: 9f8e7d6c

"""

from alembic import op

revision = "a1b2c3d4"

down_revision = "9f8e7d6c"

def upgrade():

op.add_column(

"users",

sa.Column("status", sa.String(20), nullable=False, server_default="active"),

)

def downgrade():

op.drop_column("users", "status")

autogenerate

After editing your models, running the following commands lets Alembic detect the difference and draft a migration for you.

alembic revision --autogenerate -m "add status to users"

alembic upgrade head

alembic downgrade -1

autogenerate is convenient but not perfect. It can miss some changes, such as column type changes or certain constraints, so the generated migration must always be reviewed by a human. Alembic records the current revision in an alembic_version table.

A Comparison with Prisma Migrate

Prisma Migrate, from the Node ecosystem, is another good example of ORM-based migration. The schema.prisma file becomes the single source of truth for the models, and the prisma migrate dev command generates the SQL migrations. Where Alembic expresses changes as Python code, Prisma manages a declarative schema file alongside the generated SQL.

Atlas — Declarative Schema with Automatic diff

Atlas is a relatively new tool that puts declarative migration front and center. The developer declares the desired final schema in HCL (or SQL, or an ORM definition), and Atlas compares it against the current state to generate the migration.

Declaring Schema in HCL

schema "public" {}

table "users" {

schema = schema.public

column "id" {

type = bigint

null = false

}

column "email" {

type = varchar(255)

null = false

}

primary_key {

columns = [column.id]

}

index "idx_users_email" {

unique = true

columns = [column.email]

}

Generating a Migration with diff

After editing the schema file, running migrate diff makes Atlas compute the difference between the existing migration directory and the target schema, producing a new migration file.

atlas migrate diff add_status \

--dir "file://migrations" \

--to "file://schema.hcl" \

--dev-url "docker://postgres/16/dev"

atlas migrate apply \

--dir "file://migrations" \

--url "postgres://app:secret@localhost:5432/app?sslmode=disable"

Atlas computes an accurate diff by actually applying the schema to a throwaway database specified via dev-url. It also supports both versioned and declarative workflows, so you can pick the one that matches your team's maturity. Static analysis such as atlas migrate lint, which catches dangerous changes (a column drop, an irreversible change) ahead of time, is another strength.

Tool Comparison Table

Here is the character of all five tools at a glance. The cells use plain text only.

| --- | --- | --- | --- | --- | --- |

| Primary ecosystem | JVM | JVM | Go | Python | Go, multi |

| Automatic diff | No | Partial | No | Yes (autogenerate) | Yes |

| Rollback support | Paid only | Yes | Manual down | Yes | Yes |

| Checksum verification | Yes | Yes | No | No | Yes |

| Baseline | Yes | Yes | Partial | Partial | Yes |

| Declarative mode | No | No | No | No | Yes |

In the table above, "Partial" means partial support or that a workaround is needed.

CI Integration

Whichever tool you use, migrations should be applied automatically in the CI pipeline, or at the very least verified there. A common pattern looks like this.

[code push] -> [CI build] -> [spin up throwaway DB]

-> [apply migrations] -> [run tests]

-> [deploy on pass] -> [apply migrations to production DB]

In production you must carefully decide the order of application deployment and migration. A backward-compatible change such as adding a column is safe to apply first, but a compatibility-breaking change such as dropping a column must proceed in stages during a zero-downtime deploy where old and new versions coexist.

The Expand-Contract Pattern for Zero-Downtime Deploys

The expand-contract pattern is the standard technique for changing a schema without breaking compatibility.

Step 1 (expand): add the new column or table (both old and new versions work)

Step 2 (dual write): the new version writes to the new structure as well

Step 3 (backfill): populate the new structure with historical data

Step 4 (cut over): move all reads to the new structure

Step 5 (contract): remove the old column that is no longer used

Conflict Resolution

The most common operational accident with versioned tools is a **version conflict**. If two developers each create a migration with the same next version number on their own branches, merging them produces two migrations with the same version.

The resolution differs slightly by tool. Flyway does not permit duplicate version numbers, so at merge time one side's number must be bumped. Alembic uses a revision graph instead of a linear version, so branches can occur, and you reconcile them with the alembic merge command, which creates a merge revision joining the two branches into one. With Atlas, the declarative schema is the single source of truth, so conflicts happen at the schema-file level rather than in SQL migrations, making them relatively easy to resolve via a Git merge.

The most practical prevention is to use timestamp-based filenames. Using the creation time (for example, 20260616T101500) instead of a version number means that even two developers working simultaneously will not collide. Both golang-migrate and Alembic support this approach by default or as an option.

Selection Guide

We can summarize by situation as follows.

If you are on a JVM-based project and want to keep direct control over your SQL, **Flyway** is the simplest choice. Once you learn the filename convention you can use it immediately, and the barrier to entry is low.

If you need to support several kinds of databases at once, or you want to make rollback a standard part of your workflow, **Liquibase** is strong. The changelog abstraction absorbs the differences between databases.

If you run a Go service and want to minimize dependencies while keeping full control over SQL, **golang-migrate** fits well. Just be aware that the team must own the down files and the integrity discipline.

If you use Python and SQLAlchemy, **Alembic** is effectively the standard. autogenerate makes you productive, but reviewing its output is essential. If you use Node and Prisma, Prisma Migrate occupies the same slot.

If you want to try a declarative workflow, or to filter out dangerous changes ahead of time with migration static analysis, **Atlas** is the most modern choice. It also works well introduced alongside an existing versioned tool.

Common Pitfalls

Finally, here are the pitfalls you will run into in common, no matter which tool you use.

First, **editing a migration that has already been applied**. A tool with checksum verification will stop you with an error, but in a tool without it, this becomes a silent disaster where the schema drifts per environment. Never touch what has been applied; add a new migration instead.

Second, **overtrusting rollback**. A down migration that reverses DROP COLUMN can recreate the column, but it cannot bring back the data that vanished. Irreversible changes must be guarded against not by rollback but by prior backups and staged deployment.

Third, **changes that lock a huge table**. Some ALTER TABLE statements lock the entire table and cause an outage. For large tables you should apply online DDL or a staged migration strategy.

Fourth, **not testing your migrations**. A migration is code too. It is safer to apply it against a throwaway DB in CI, and where possible, to rehearse with production-like data.

Conclusion

In the end, schema migration tools all aim at the same goal: treating schema changes like code so that they can be applied safely and reproducibly in any environment. Flyway's simplicity, Liquibase's multi-database abstraction, golang-migrate's explicitness, Alembic's ORM integration, and Atlas's declarative model each carry a different strength.

The right answer depends on your team's language ecosystem, the diversity of your databases, and your appetite for a declarative workflow. What matters more than the tool choice itself is the discipline of versioning your migrations, verifying them in CI, and treating applied changes as immutable. With that discipline in place, any of these tools can run safely enough.

References

- Flyway official documentation: https://flywaydb.org/documentation/

- Liquibase official documentation: https://docs.liquibase.com/

- golang-migrate repository: https://github.com/golang-migrate/migrate

- Alembic official documentation: https://alembic.sqlalchemy.org/

- Atlas official documentation: https://atlasgo.io/

- Prisma Migrate documentation: https://www.prisma.io/docs/orm/prisma-migrate

- PostgreSQL ALTER TABLE documentation: https://www.postgresql.org/docs/current/sql-altertable.html

- MySQL official documentation: https://dev.mysql.com/doc/