Skip to main content
Follow these guidelines to write maintainable, safe, and effective database migrations.

​
Version Numbering Strategies

20250120143000__add_user_email.sql
20250121091500__create_orders_table.sql
Format: YYYYMMDDHHmmss Advantages:
  • ✅ No merge conflicts with parallel development
  • ✅ Chronological ordering
  • ✅ Supports distributed teams
Disadvantages:
  • ⚠️ Less human-readable
  • ⚠️ Doesn’t convey significance
Best for: Teams with frequent parallel development

​
Strategy 2: Semantic Versioning

v1.0.0__initial_release.sql
v1.1.0__add_user_profiles.sql
v1.1.1__fix_profile_constraint.sql
v2.0.0__redesign_authentication.sql
Format: v<major>.<minor>.<patch> Advantages:
  • ✅ Conveys change significance
  • ✅ Aligns with application versioning
  • ✅ Clear breaking change indication
Disadvantages:
  • ⚠️ Requires coordination
  • ⚠️ Merge conflicts possible
Best for: Teams with coordinated releases

​
Strategy 3: Sequential with Milestones

001__initial_schema.sql
002__add_users.sql
...
100__v1_0_release.sql
101__add_analytics.sql
...
200__v2_0_release.sql
Advantages:
  • ✅ Simple and clear
  • ✅ Milestones mark releases
Disadvantages:
  • ⚠️ Merge conflicts in parallel work
  • ⚠️ Gaps can be confusing
Best for: Small teams, sequential development

​
Migration File Best Practices

​
Keep Migrations Small

-- 005__add_user_email.sql
ALTER TABLE users ADD COLUMN email TEXT;
  • Single, clear purpose
  • Fast execution
  • Easy to review
  • Simple to rollback
Guideline: One logical change per file

​
Include Rollback Planning

Document rollback approach in comments: 
-- 010__add_payment_methods_table.sql
-- Rollback: DROP TABLE payment_methods;
-- Impact: Requires app deployment v2.5.0+

CREATE TABLE payment_methods (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    card_last_four TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

​
Transaction Handling

Bytebase Automatic Transactions: Bytebase automatically wraps all SQL statements in a migration file within a single transaction. You typically don’t need to add explicit BEGIN/COMMIT statements.
When Bytebase’s automatic transactions are sufficient:
  • Single-file migrations with multiple statements
  • Standard DDL operations (CREATE, ALTER, DROP)
  • Simple DML operations (INSERT, UPDATE, DELETE)
When you might need explicit transaction control:
  • Batched operations requiring commits between chunks
  • Long-running data migrations that need progress checkpoints
  • Statements that cannot run in a transaction block — Bytebase auto-detects the common PostgreSQL ones (CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, VACUUM, DROP DATABASE) and runs them outside the transaction for you. For others, disable transaction mode. See Statements that cannot run in a transaction block
Example of batched migration (when needed): 
-- 015__batch_archive_logs_dml.sql
-- Note: This uses explicit batching for large data operation
DO $$
DECLARE
    batch_size INT := 10000;
    deleted_count INT;
BEGIN
    LOOP
        DELETE FROM logs
        WHERE id IN (
            SELECT id FROM logs
            WHERE created_at < '2024-01-01'
            LIMIT batch_size
        );

        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        EXIT WHEN deleted_count < batch_size;

        COMMIT; -- Commit each batch
        PERFORM pg_sleep(0.1); -- Throttle
    END LOOP;
END $$;
Database-specific transaction support:
  • ✅ PostgreSQL: DDL in transactions (except CONCURRENTLY operations)
  • ❌ MySQL: DDL commits immediately (implicit commit)
  • ✅ SQL Server: DDL in transactions

​
Add Comments for Complex Logic

-- 020__migrate_legacy_permissions_dml.sql
-- Migrates old role system to new permission model
-- Old: roles.name -> New: permissions.scope + permissions.action
-- Mapping:
--   'admin' -> 'database:*'
--   'editor' -> 'database:write'
--   'viewer' -> 'database:read'

UPDATE permissions
SET
    scope = CASE
        WHEN roles.name = 'admin' THEN 'database'
        WHEN roles.name = 'editor' THEN 'database'
        WHEN roles.name = 'viewer' THEN 'database'
    END,
    action = CASE
        WHEN roles.name = 'admin' THEN '*'
        WHEN roles.name = 'editor' THEN 'write'
        WHEN roles.name = 'viewer' THEN 'read'
    END
FROM roles
WHERE permissions.role_id = roles.id;

​
Documentation

​
Maintain Migration Changelog

Document significant schema changes: 
# Database Changelog

## v2.0.0 (2025-01-20)
- Added `user_preferences` table for customization
- Migrated legacy role system to new permissions model
- **Breaking**: Removed deprecated `user_settings` table

## v1.5.0 (2025-01-15)
- Added email column to users table
- Created indexes on frequently queried columns

​
Document Schema Dependencies

Track dependencies between application and schema: 
# Schema Dependencies

## users.email Column
- Added: v1.5.0 (migration 010)
- Required by: Auth Service v2.0+
- Can remove after: All instances upgraded to v2.1+
This helps coordinate schema cleanup with application deployments.

​
Common Anti-Patterns to Avoid

​
❌ Modifying Applied Migrations

Don’t: 
-- 005__add_email.sql (already deployed)
ALTER TABLE users ADD COLUMN email TEXT; -- Changed to VARCHAR
Do: 
-- 006__fix_email_type.sql
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);

​
❌ Skipping Version Numbers

Don’t: 
001__init.sql
002__add_users.sql
010__add_products.sql  ← Why skip 003-009?
Do: 
001__init.sql
002__add_users.sql
003__add_products.sql

​
❌ Mixing DDL and DML Without Suffix

Don’t: 
-- 015__add_orders.sql
CREATE TABLE orders (...);
INSERT INTO orders VALUES (...);  -- DML without _dml suffix
Do: 
-- 015__add_orders.sql (DDL only)
CREATE TABLE orders (...);

-- 016__seed_orders_dml.sql (DML separate)
INSERT INTO orders VALUES (...);

​
❌ Long-Running Migrations in Production

Don’t: 
-- 020__huge_migration.sql
UPDATE users SET legacy_field = NULL; -- Locks 10M rows for 10 minutes
Do: 
-- 020__migrate_users_batch_dml.sql
-- Batch processing with throttling (see Performance section)

​
Next Steps

Git and CI/CD

Learn branching strategies and testing patterns

Performance

Optimize migration performance