Database Migrations Guide

This guide explains how to organize, run, and automate database schema migrations across your dev, stg, and prod environments, using Bytebase and your CDK‑driven CI/CD pipeline.

1. Branch → Environment Mapping

Git Branch	CDK Context `-c environment`	Bytebase Environment
`develop`	`dev`	Dev
`staging`	`stg`	Stg
`main`	`prod`	Prod

Your CI/CD workflow picks the branch name, sets both CDK and Bytebase contexts accordingly, then deploys infrastructure and applies migrations to the matching environment.

2. Directory Layout

.
├── infra/                  # CDK application
│   ├── bytebase/           # BytebaseStack definition
│   ├── core/               # CoreStack definition
│   ├── migration/          # MigrationStack (if applicable)
│   └── api/                # ApiStack definition
└── db/
    └── migrations/         # SQL migration scripts, versioned
        ├── 001_create_users.sql
        ├── 002_add_email_idx.sql
        └── 003_create_posts.sql

db/migrations/ holds ordered, id‑prefixed .sql files.

Prefixes (001_, 002_, …) determine execution order.

3. Local Workflow

Write a migration
Create a new SQL file in db/migrations/, e.g.:

Commit & push
Push your feature branch off develop.

Apply to Dev locally
(requires the Bytebase CLI bb and a valid $BYTEBASE_TOKEN in your environment)

bb ddl apply \
  --project myapp \
  --environment dev \
  --file db/migrations/004_add_last_login.sql

Verify
In Bytebase UI under Dev, confirm the change is applied and the forward/rollback scripts look correct.

4. CI/CD‑Driven Migrations

We integrate Bytebase’s GitOps into GitHub Actions so that on each push to a branch:

develop → migrations imported/applied in Bytebase Dev

staging → migrations imported/applied in Bytebase Stg

main → migrations imported/applied in Bytebase Prod

`.github/workflows/migrations.yml`

5. CDK CI/CD Integration

Your existing CDK CI/CD pipeline now includes the bytebase stack:

Diff step runs:

for STACK in core migration api bytebase; do
  npx cdk diff nova-${ENV}-$STACK … --fail-on-destructive
done

Deploy step runs:

for STACK in core migration api bytebase; do
  npx cdk deploy nova-${ENV}-$STACK … --no-fail-on-empty-changeset
done

After deployment succeeds:

CloudFormation URLs for all four stacks are printed.

Bytebase service is live in the target environment.

6. Manual Promotion & Rollback

Promote: merge develop → staging or staging → main.

Approval: Bytebase enforces your configured review policies (e.g. “2 approvers for Prod”).

Rollback: In Bytebase UI, find a Change Issue and click Rollback to execute the rollback script.

7. Validation & Auditing

Audit trail: Bytebase logs every DDL change with timestamp, author, and SQL.

SQL Review: enforce linting and security policies via Bytebase’s built‑in SQL Review.

Drift Detection: Bytebase periodically scans for out‑of‑band schema changes and alerts when drift is detected.

Monitoring: check CloudWatch logs under the Bytebase log group for errors; verify EFS Infrequent Access transitions.

1. Branch → Environment Mapping#

2. Directory Layout#

3. Local Workflow#

4. CI/CD‑Driven Migrations#

.github/workflows/migrations.yml#

5. CDK CI/CD Integration#

6. Manual Promotion & Rollback#

7. Validation & Auditing#