39 · Schema Migrations

Implement safe schema migrations in HDI using expand/contract. Understand cds migrate and the undeploy list.

Episode 39 — Schema Evolution & Migrations

What you'll learn

The schema change lifecycle in CAP + HDI
Safe vs breaking changes — the complete decision tree
CAP migration tooling: cds migrate and change sets
Zero-downtime deployment strategies
Emergency rollback procedures

Why this matters

A schema change that works in dev can bring down production if it removes a column that a running app version still reads. Schema evolution is one of the highest-risk operations in enterprise software — these patterns make it safe.

Safe changes (additive — zero risk)

// All of these deploy without any data risk:
entity Orders {
  key ID      : UUID;
  status      : String;
  // ✓ Add nullable column
  notes       : String;
  // ✓ Add association
  assignee    : Association to Users;
  // ✓ Increase String length
  description : String(500);  // was String(200)
}

# HDI handles these automatically
cds build --for hana && cds deploy --to hana

Breaking changes — decision tree

Is the column used by the running app?
  YES → Don't remove yet → deprecate first
  NO  → Safe to remove → add to .hdiconfig undeploy list

Is the column type changing?
  Compatible (e.g., String(200) → String(500)) → Safe
  Incompatible (e.g., String → Integer)        → Manual migration required

Is a NOT NULL constraint being added?
  Existing rows have nulls? → Set a default first, then add constraint
  All rows are non-null?    → Safe with test confirmation

The two-phase deprecation pattern

Phase 1 (deploy v2): Add new column, keep old column, copy data
Phase 2 (deploy v3): Remove old column after all app instances updated

// v2 — both columns exist
entity Books {
  key ID          : UUID;
  title           : String;
  authorName      : String;   // OLD — deprecated
  author_ID       : UUID;     // NEW — references Authors entity
}

// v2 handler — read from old, write to new
this.before('CREATE', 'Books', (req) => {
  // Dual-write during transition
  if (req.data.authorName && !req.data.author_ID) {
    const author = await findOrCreateAuthor(req.data.authorName)
    req.data.author_ID = author.ID
  }
})

// v3 — remove old column (only after all consumers updated)
entity Books {
  key ID     : UUID;
  title      : String;
  author_ID  : UUID;
}

CAP migration tables

CAP 7+ tracks schema versions:

# Generate migration change files
cds migrate --from <old-version> --to <new-version>

# Creates:
db/migrations/
└── 20260917_add_author_ID.json

// migration change file
{
  "up": [
    "ALTER TABLE BOOKS ADD (AUTHOR_ID NVARCHAR(36))",
    "UPDATE BOOKS SET AUTHOR_ID = (SELECT ID FROM AUTHORS WHERE NAME = AUTHORNAME LIMIT 1)"
  ],
  "down": [
    "ALTER TABLE BOOKS DROP (AUTHOR_ID)"
  ]
}

Zero-downtime deployment strategy

1. Deploy v2 (adds new_column, old_column still present)
   → Both v1 and v2 app instances run simultaneously
   → Blue-green: v2 CF app, v1 still receives traffic

2. Validate v2 health checks pass

3. Switch traffic to v2 (update CF route)

4. Wait for v1 to drain (all in-flight requests complete)

5. Deploy v3 in next release cycle (removes old_column)

HDI undeploy (controlled column removal)

// gen/db/src/.hdiconfig
{
  "file_suffixes": { ... },
  "undeploy": [
    "src/gen/my.bookshop.Books.hdbcds"   // HDI will drop and recreate (DANGER: data loss!)
  ]
}

Never add production tables to undeploy. Use additive migrations instead.

Emergency rollback

# HANA Cloud point-in-time recovery (up to 15 days retention)
# In HANA Cloud Cockpit → Recovery → Point-in-Time Recovery
# Stops the instance and recovers to a prior state

# For CF app rollback (without DB rollback)
cf push bookshop --docker-image <previous-version-image>
# Or use blue-green: switch CF route back to old app

Pre-deployment checklist

□ Test migration script on a HANA snapshot copy
□ Verify additive changes: no columns removed, no type changes
□ Backup production HDI container before deploy (snapshots)
□ Coordinate with all app consumers about deprecated columns
□ Monitor error rate during and after deployment
□ Document rollback plan with estimated time to recovery

Hands-on exercise

Add a new nullable notes column to your Books entity — deploy it
Then add a NOT NULL category column with a default value — deploy it
Deprecate stock by adding stockCount alongside it
Write a BEFORE handler that syncs both fields during the transition

Checkpoint ✓

[ ] Additive changes (add columns, increase length) are safe and need no special handling
[ ] Breaking changes follow the two-phase deprecation pattern
[ ] Zero-downtime uses blue-green deployment with overlapping old/new column lifespan
[ ] Never add active production tables to the HDI undeploy list