Database Migrations Development for Web Application

Database Migrations Development for Web Applications

Migrations are a managed way to change database schema along with code. Without them, schema changes are deployed manually, get lost in Slack, or cause production incidents.

Tools

Choice depends on your stack:

Universal option — golang-migrate or Flyway with plain SQL: independent of ORM, work from CI without the application, migrations are readable without ORM knowledge.

Migration Writing Principles

Each migration is atomic and reversible. If you can't write a correct down, it signals that the migration does too much.

Production migrations are never edited. If an error was applied — write a new migration.

No data migrations in schema migrations. Data migration is a separate script.

Example with golang-migrate

migrate create -ext sql -dir db/migrations -seq add_search_vector_to_products

Creates two files: 000003_add_search_vector_to_products.up.sql and ...down.sql.

-- 000003_add_search_vector_to_products.up.sql

BEGIN;

ALTER TABLE products
    ADD COLUMN IF NOT EXISTS search_vector TSVECTOR;

UPDATE products
SET search_vector = to_tsvector('russian', coalesce(title, '') || ' ' || coalesce(description, ''));

CREATE INDEX CONCURRENTLY idx_products_search ON products USING GIN (search_vector);

CREATE OR REPLACE FUNCTION products_search_vector_update() RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := to_tsvector('russian',
        coalesce(NEW.title, '') || ' ' || coalesce(NEW.description, '')
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER products_search_vector_trigger
    BEFORE INSERT OR UPDATE ON products
    FOR EACH ROW EXECUTE FUNCTION products_search_vector_update();

COMMIT;

-- 000003_add_search_vector_to_products.down.sql

BEGIN;
DROP TRIGGER IF EXISTS products_search_vector_trigger ON products;
DROP FUNCTION IF EXISTS products_search_vector_update();
DROP INDEX IF EXISTS idx_products_search;
ALTER TABLE products DROP COLUMN IF EXISTS search_vector;
COMMIT;

CREATE INDEX CONCURRENTLY can't run inside a transaction. For such migrations, wrap in a separate step without BEGIN/COMMIT, or use Flyway with executeInTransaction = false.

Zero-Downtime Migrations

Main rule: each migration must be compatible with the previous and next code version simultaneously.

Because deployment looks like: migration runs first, then new application instances start, old ones gradually shut down. At this moment both code generations work simultaneously.

Adding a column:

-- Safe: nullable without DEFAULT or with constant
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Unsafe in old PostgreSQL: NOT NULL with DEFAULT — table rewrite
-- Safe in PostgreSQL 11+: NOT NULL with DEFAULT constant (no rewrite)
ALTER TABLE users ADD COLUMN is_verified BOOLEAN NOT NULL DEFAULT false;

Renaming a column — always in 3 stages:

Deploy 1: add new column, code writes to both
Deploy 2: migrate data, code reads from new
Deploy 3: drop old column

Changing column type:

-- PostgreSQL 12+: instant for varchar(n) -> varchar(m) when m > n
-- table rewrite for most other type changes

-- Safe path: add new, copy, switch
ALTER TABLE products ADD COLUMN price_cents BIGINT;
UPDATE products SET price_cents = (price * 100)::BIGINT;
-- next code deploy: read price_cents
-- next migration: DROP COLUMN price

Dropping a column:

-- First code stops reading/writing column (deploy)
-- Then migration
ALTER TABLE products DROP COLUMN old_field;

Versioning and Naming

Good name format: {timestamp}_{verb}_{object}_{detail}.

20240315_001_create_users.sql
20240315_002_add_email_index_to_users.sql
20240320_001_add_status_to_products.sql
20240401_001_create_orders.sql

Timestamp-based versions (like in Flyway) are better than sequence-based (Alembic --autogenerate) when multiple developers work in parallel: no version conflicts.

CI/CD Integration

# .github/workflows/deploy.yml (fragment)
- name: Run migrations
  run: |
    migrate -path db/migrations \
            -database "$DATABASE_URL" \
            -verbose up
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}

- name: Deploy application
  run: ./deploy.sh

Migrations apply before new code deployment. Rollback: migrate down N — rollback N latest migrations.

Flyway for Java/Any Stack

<!-- pom.xml -->
<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <configuration>
        <url>${DB_URL}</url>
        <user>${DB_USER}</user>
        <password>${DB_PASSWORD}</password>
        <locations>classpath:db/migration</locations>
        <outOfOrder>false</outOfOrder>
        <validateOnMigrate>true</validateOnMigrate>
    </configuration>
</plugin>

mvn flyway:migrate
mvn flyway:info      # status of all migrations
mvn flyway:validate  # check checksums

Flyway stores a checksum of each migration in the flyway_schema_history table. If the file changes after application — validate throws an error.

Timelines

Setting up migration infrastructure (tool, naming, CI step, rollback procedure) for a new project: half a day. Writing a set of initial migrations for an existing schema (reverse engineering): 1 day.