Migration Workflows

3. Apply Migrations

# Apply all pending migrations
atlas migrate apply --env local

# Apply with verbose output
atlas migrate apply --env local --verbose

# Dry-run before applying
atlas migrate apply --env local --dry-run

# Apply to specific database
atlas migrate apply --url "mysql://user:pass@localhost/mydb"

4. Verify State

# Check migration status
atlas migrate status --env local

# Output shows applied and pending migrations
# Applied: 3 migrations
# Pending: 0 migrations

Versioned Migration Workflow

1. Create New Migration

# Create empty migration with timestamp
atlas migrate new create_users --env local

# Output:
# Created migration: migrations/20240115_120000_create_users.sql

# Edit the migration file and add SQL
cat > migrations/20240115_120000_create_users.sql << 'EOF'
-- Create users table
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
EOF

2. Plan Migrations from Schema

# Generate migration from schema definition
atlas migrate plan "add_posts_table" --env local

# Atlas compares schema.hcl with database state
# Creates migration file automatically

3. Lint Migrations

Validate migrations for errors and best practices:

# Lint all migrations
atlas migrate lint --env local

# Lint specific migration
atlas migrate lint --env local --latest 2

# Check for:
# - Syntax errors
# - Unsafe operations
# - Data loss risks
# - Best practice violations

Example lint output:

Migration 001_create_users.sql:
  ⚠ WARNING: Dropping column "temp" without checking if data exists
  ✓ All other checks passed

4. Apply Migrations

# Apply all pending migrations
atlas migrate apply --env local

# Apply latest 2 migrations
atlas migrate apply --env local --latest 2

# Apply with transaction wrapping
atlas migrate apply --env local --allow-dirty

5. Rollback Migrations

# Create down migration (SQL to revert changes)
atlas migrate down --env local

# Revert to specific migration version
atlas migrate down --env local --to 20240101120000

Project Configuration (atlas.hcl)

Declarative Setup

env "local" {
  # Database connection
  url = "postgres://user:password@localhost/mydb"

  # Development database for planning
  dev = "postgres://user:password@localhost/mydb_dev"

  # Schema definition
  migration {
    dir = "file://migrations"
    format = sql
  }

  # Desired schema source
  schema {
    src = "file://schema.hcl"
  }

  # Output formatting
  format {
    migrate {
      apply = "{{ sql . }}"
      plan = "{{ sql . }}"
    }
  }
}

Versioned Setup

env "prod" {
  url = "postgres://user:[email protected]/mydb"

  migration {
    dir = "file://migrations"
    format = sql
    auto_approve = false  # Require manual approval
  }

  format {
    migrate {
      apply = "-- Migration applied\n{{ sql . }}"
    }
  }
}

Common Commands Reference

Multi-Environment Setup

// atlas.hcl

env "dev" {
  url = "postgres://localhost/dev_db"
  migration { dir = "file://migrations" }
}

env "staging" {
  url = "postgres://staging.example.com/staging_db"
  migration { dir = "file://migrations" }
}

env "prod" {
  url = "postgres://prod.example.com/prod_db"
  migration {
    dir = "file://migrations"
    auto_approve = false
  }
}

Running migrations per environment:

atlas migrate apply --env dev
atlas migrate apply --env staging
atlas migrate apply --env prod

Migration Validation

Before applying migrations, Atlas validates:

1. Syntax - SQL is valid for the target database
2. Safety - Detects data loss (dropping columns, tables)
3. Uniqueness - Migration names don't conflict
4. Ordering - Migrations are applied in correct order
5. Idempotency - Migrations can run multiple times safely

Address validation warnings:

# Apply despite warnings
atlas migrate apply --allow-dirty --env local

# Review specific migration
atlas migrate lint --env local --latest 1

Rollback Strategies

Approach 1: Down Migrations (Versioned)

-- 001_create_users.sql
CREATE TABLE users (id INT PRIMARY KEY);

-- 001_create_users.down.sql
DROP TABLE users;

Apply down migration:

atlas migrate down --env local

Approach 2: State Comparison (Declarative)

Modify schema.hcl to previous state:

// schema.hcl - Remove table definition
// Atlas generates a DROP TABLE migration

Then plan and apply:

atlas migrate diff rollback --env local
atlas migrate apply --env local

Handling Errors

"Database version mismatch"

# Check migration status
atlas migrate status --env local

# Reset to specific version (careful!)
atlas migrate set --env local 20240101120000

"Column does not exist"

# Inspect actual database schema
atlas schema inspect --env local

# Compare with expected schema
atlas schema diff --env local

Best Practices

1. Always plan before applying - Use dry-run first
2. Test in dev environment - Run migrations locally first
3. Lint migrations - Check for issues before applying
4. Version control migrations - Commit migration files to git
5. Review migration SQL - Understand what changes are being made
6. Backup before production - Always backup production databases
7. Use transactions - Atlas wraps migrations in transactions by default
8. Document complex changes - Add comments explaining why changes are needed

Resources

• Atlas CLI Docs: https://atlasgo.io/cli-reference
• Migration Commands: https://atlasgo.io/docs/commands
• Best Practices: https://atlasgo.io/guides/migration-best-practices

Local References

For complete migration workflow documentation, see:

• references/atlas-docs-full/docs.md - Migration overview (declarative & versioned)
• references/atlas-docs-full/guides/modern-database-ci-cd.md - Modern CI/CD patterns
• references/atlas-docs-full/guides/migration-dirs/template-directory.md - Migration directory templates
• references/README.md - Full documentation index