Automating Database Schema Migrations in CI/CD: Multi-Tenant Zero-Downtime Rollouts with Atlas and GitHub Actions — code snippets

snippet-1.hcl

env "local" {
  src = "file://schema.hcl"
  dev = "docker://postgres/15/dev?search_path=public"
  migration {
    dir = "file://migrations"
  }
  format {
    migrate {
      diff = "{{ sql . \"  \" }}"
    }
  }
  lint {
    destructive {
      error = true
    }
    data_depend {
      error = true
    }
    incompatible {
      error = true
    }
  }
}

snippet-2.hcl

schema "public" {}

table "users" {
  schema = schema.public
  column "id" {
    null = false
    type = bigint
    identity {
      generated = ALWAYS
    }
  }
  column "email" {
    null = false
    type = varchar(255)
  }
  column "status" {
    null    = false
    type    = varchar(50)
    default = "active"
  }
  primary_key {
    columns = [column.id]
  }
  index "idx_users_email" {
    unique  = true
    columns = [column.email]
  }
}

table "tenants" {
  schema = schema.public
  column "id" {
    null = false
    type = bigint
    identity {
      generated = ALWAYS
    }
  }
  column "domain" {
    null = false
    type = varchar(100)
  }
  primary_key {
    columns = [column.id]
  }
}

snippet-3.sql

-- atlas:txmode none
-- Set short lock timeout to prevent queueing behind long-running queries
SET lock_timeout = '2000';

-- Step 1: Add nullable column (avoiding exclusive table rewrites)
ALTER TABLE "users" ADD COLUMN "tenant_id" bigint NULL;

-- Step 2: Create index concurrently (does not block reads or writes)
CREATE INDEX CONCURRENTLY IF NOT EXISTS "idx_users_tenant_id" ON "users" ("tenant_id");

-- Step 3: Add foreign key constraint as NOT VALID to avoid validation lock
ALTER TABLE "users" ADD CONSTRAINT "fk_users_tenant_id" 
  FOREIGN KEY ("tenant_id") REFERENCES "tenants" ("id") 
  NOT VALID;

-- Step 4: Validate the constraint in a separate transaction (reads index, light lock)
-- (This should run in a subsequent migration step or command execution)
-- ALTER TABLE "users" VALIDATE CONSTRAINT "fk_users_tenant_id";

snippet-4.yaml

name: Database Migration Linting

on:
  pull_request:
    paths:
      - 'migrations/**'
      - 'schema.hcl'

jobs:
  lint:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout Code
        uses: actions/checkout@v4
        with:
          fetch-depth: 0

      - name: Setup Atlas CLI
        uses: ariga/setup-atlas@v0.1.0

      - name: Run Atlas Lint
        run: |
          atlas migrate lint \
            --env local \
            --latest 1 \
            --dev-url "docker://postgres/15/dev?search_path=public"

snippet-5.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"os"
	"os/exec"
	"sync"
	"time"

	_ "github.com/lib/pq"
)

type Tenant struct {
	ID               string
	ConnectionString string
}

func main() {
	ctx, cancel := context.WithTimeout(context.Background(), 15*time.Minute)
	defer cancel()

	tenants, err := discoverTenants()
	if err != nil {
		log.Fatalf("Failed to discover tenants: %v", err)
	}

	// Configure concurrency limits
	maxWorkers := 5
	errorBudget := 2 // Abort if more than 2 tenants fail
	
	tenantChan := make(chan Tenant, len(tenants))
	for _, t := range tenants {
		tenantChan <- t
	}
	close(tenantChan)

	var wg sync.WaitGroup
	var errMu sync.Mutex
	failedCount := 0
	errs := make(map[string]error)

	for i := 0; i < maxWorkers; i++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			for t := range tenantChan {
				// Check error budget
				errMu.Lock()
				if failedCount > errorBudget {
					errMu.Unlock()
					log.Printf("Error budget exceeded. Skipping tenant %s", t.ID)
					continue
				}
				errMu.Unlock()

				log.Printf("[Tenant %s] Starting migration rollout...", t.ID)
				if err := migrateTenant(ctx, t); err != nil {
					log.Printf("[Tenant %s] Migration failed: %v", t.ID, err)
					errMu.Lock()
					failedCount++
					errs[t.ID] = err
					errMu.Unlock()
				} else {
					log.Printf("[Tenant %s] Migration completed successfully.", t.ID)
				}
			}
		}()
	}

	wg.Wait()

	if len(errs) > 0 {
		log.Printf("Rollout completed with errors. Failures: %d", len(errs))
		os.Exit(1)
	}
	log.Println("All tenant migrations rolled out successfully.")
}

func migrateTenant(ctx context.Context, tenant Tenant) error {
	cmd := exec.CommandContext(ctx, "atlas", "migrate", "apply",
		"--url", tenant.ConnectionString,
		"--dir", "file://migrations",
	)
	cmd.Stdout = os.Stdout
	cmd.Stderr = os.Stderr
	return cmd.Run()
}

func discoverTenants() ([]Tenant, error) {
	// In production, fetch this from a configuration store or primary DB
	return []Tenant{
		{ID: "tenant_alpha", ConnectionString: "postgres://user:pass@db1.internal:5432/alpha?sslmode=disable"},
		{ID: "tenant_beta", ConnectionString: "postgres://user:pass@db2.internal:5432/beta?sslmode=disable"},
	}, nil
}

snippet-6.go

package main

import (
	"context"
	"fmt"
	"log"
	"os/exec"
	"time"
)

// VerifySchemaDrift checks if the live database schema deviates from the desired declarative state.
func VerifySchemaDrift(ctx context.Context, dbURL string, schemaHclPath string) error {
	ctx, cancel := context.WithTimeout(ctx, 30*time.Second)
	defer cancel()

	// Run atlas schema diff to compare live db against our HCL declaration
	cmd := exec.CommandContext(ctx, "atlas", "schema", "diff",
		"--from", dbURL,
		"--to", fmt.Sprintf("file://%s", schemaHclPath),
	)

	output, err := cmd.CombinedOutput()
	if err != nil {
		// Atlas exits with status code 1 if differences (drift) are found
		if _, ok := err.(*exec.ExitError); ok && len(output) > 0 {
			return fmt.Errorf("schema drift detected: %s", string(output))
		}
		return fmt.Errorf("failed to run schema diff check: %w, output: %s", err, string(output))
	}

	log.Println("Database schema is fully in sync. No drift detected.")
	return nil
}