Database Migrations for Web Applications

Database Migrations for Web Applications

We build a database migration system that eliminates manual schema edits, lost changes, and deployment downtime. Instead of chaos with ALTER queries in chats and incidents due to code-database incompatibility—versioned scripts, automatic validation in CI/CD, and zero-downtime updates. Automated migrations reduce change approval time by 5x compared to manual requests. They also improve reliability and reduce incidents significantly. Automated migrations are 3x more reliable than manual schema changes.

Why Are Migrations Critical for Production?

Without migrations, teams often face schema drift between environments, data loss during manual ALTERs, and inability to roll back quickly. A configured migration system ensures the database always matches the code, and changes go through code review and testing. Over 90% of data-related incidents are caused by unverified schema changes—migrations eliminate this. With proper setup, over 95% of migrations execute without errors. The average rollback time for a failed migration is about 2 minutes.

How to Choose the Right Migration Tool?

Tool choice depends on the stack. We prefer universal solutions with pure SQL scripts, independent of ORM and runnable from CI without starting the application. For example, Flyway supports any stack and allows migrations via command line without Java environment if scripts are written in SQL.

Principles we follow:

• Each migration is atomic and reversible (down script required).
• Migrations in production are never edited—errors are fixed with new migrations.
• Data migrations are separate from schema migrations.

Example with golang-migrate

Create a migration to add a search vector:

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

-- 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; 

Important: CREATE INDEX CONCURRENTLY cannot be executed inside a transaction. For such operations, use a separate step without BEGIN/COMMIT, or configure Flyway with executeInTransaction = false.

Zero-downtime Migrations

The golden rule: each migration must be compatible with both the previous and next code versions simultaneously. The deployment looks like this: migration is applied first, then new instances are brought up, old ones are gradually taken down—both generations run side by side.

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