Writing Schema Changes and Rollback Scripts

Goal

You will be able to change a schema with the expand-migrate-contract pattern and build a complete change management set: rollback script, batch backfill, verification script, and work procedure document.

Why it matters

"There is a down script, so it is safe" is the most common illusion. A down that reverses DROP COLUMN restores only the column structure, not the values. Because reversibility is not a property of code but a combination of code, data, and time, the same change is reversible if deployed yesterday and irreversible after a million rows have piled up. And if you run a bulk backfill in a single transaction, locks and logs explode, the read replica falls behind, and the lookup service shows stale data. This lab is training to avoid those traps by hand.

Steps

1. Preparation: the dbo-schema lab must have left a /root/db/si.db.

   If you do not have it, you can create it from /opt/lab/fixtures/dbo/si-base.sql.

2. Save the current schema as /root/db/schema_v1.sql and create /root/db/baseline.txt. It has three lines.

   orders_cnt=<ORDERS 행 수>
   orders_amt=<ORDERS 의 ORD_AMT 합계>
   item_qty=<ORDER_ITEM 의 QTY 합계>
   

3. Create /root/db/changelog.csv. The first line is chg_id,date,object,ddl_file,rollback_file,est_sec,approver. Register in advance the 3 changes you will make in steps 3–5. ddl_file, rollback_file, and approver must not be empty, and est_sec must contain a number.

4. Create /root/db/mig/V2__add_dlvr_sts.sql.

   • To ORDERS, add a DLVR_STS_CD column (default '01')
   • Fill all existing rows with '01' After applying it, there must be 0 rows where DLVR_STS_CD is NULL.

5. Create /root/db/mig/V2__rollback.sql. After applying it, the schema must be the same as schema_v1.sql. (Run up → down on a copy of si.db to confirm.)

6. Create /root/db/mig/V3__item_qty_check.sql. Strengthen the CHECK on ORDER_ITEM.QTY to 1 or more and 9999 or less. sqlite does not support changing constraints, so you need a table rebuild procedure. After applying it, the ORDER_ITEM row count and QTY total must still equal the step 1 baseline, and the foreign key relationships must be preserved.

7. Create /root/db/batch-update.sh. It takes two arguments (DB파일 배치크기, that is, the DB file and the batch size), changes the rows of ORDERS whose DLVR_STS_CD is '01' to '02', processing repeatedly one batch size at a time. On the last line, print batches=<반복횟수> updated=<총건수> (the number of iterations and the total row count). (To avoid dirtying the original, apply it only to the DB passed as an argument.)

8. Create /root/db/mig-verify.sh. It takes two arguments (기준선파일 DB파일, that is, the baseline file and the DB file), compares the baseline's three metrics with the current DB, and if all are equal prints OK on the first line, and if not, a line starting with NG along with which metrics differ. The exit codes are 0 and non-zero respectively.

9. Write /root/db/mig-runbook.md. It must have six h2 headings, ## 작업 개요, ## 사전 백업, ## 적용 절차, ## 검증, ## 롤백 기준, and ## 롤백 절차 (in order: overview, pre-backup, procedure, verification, rollback criteria, rollback procedure), and the body must include the backup file path and a numeric rollback criterion (duration or number of verification failures).

Notes

• Schema dump: sqlite3 /root/db/si.db .schema > schema_v1.sql
• Table rebuild order: PRAGMA foreign_keys=off → create a new table → INSERT ... SELECT → DROP the old table → RENAME → recreate indexes → PRAGMA foreign_keys=on
• Affected row count: SELECT changes();
• Common mistake 1: testing on the original without making a copy and making it irreversible.
• Common mistake 2: not recreating indexes during a table rebuild. When you DROP, the indexes disappear with it.
• Common mistake 3: a batch backfill loop with no exit condition, becoming an infinite loop. It must stop when the affected row count is 0.

Schema snapshot and baseline

Save the current schema as /root/db/schema_v1.sql and create /root/db/baseline.txt. It has three lines.

orders_cnt=<ORDERS 행 수>
orders_amt=<ORDERS 의 ORD_AMT 합계>
item_qty=<ORDER_ITEM 의 QTY 합계>

If you find yourself asking "how many were there originally?" after the work, it is already too late. Keep both the schema and the data metrics.

Change management ledger

Create /root/db/changelog.csv. The first line is chg_id,date,object,ddl_file,rollback_file,est_sec,approver. Register in advance the 3 changes you will make in steps 3–5. ddl_file, rollback_file, and approver must not be empty, and est_sec must contain a number.

If the rollback script path is a required item, then while filling in the ledger you realize "this cannot be undone" before deployment.

Expand script

Create /root/db/mig/V2__add_dlvr_sts.sql.

• To ORDERS, add a DLVR_STS_CD column (default '01')
• Fill all existing rows with '01' After applying it, there must be 0 rows where DLVR_STS_CD is NULL.

Add the column and fill in the existing rows. Think about the difference between adding it as nullable and then filling, and adding it as NOT NULL from the start.

Rollback script

Create /root/db/mig/V2__rollback.sql. After applying it, the schema must be the same as schema_v1.sql. (Run up → down on a copy of si.db to confirm.)

You must be able to verify that the schema after the rollback is the same as the original. Comparing snapshots is the surest way.

Table rebuild migration

Create /root/db/mig/V3__item_qty_check.sql. Strengthen the CHECK on ORDER_ITEM.QTY to 1 or more and 9999 or less. sqlite does not support changing constraints, so you need a table rebuild procedure. After applying it, the ORDER_ITEM row count and QTY total must still equal the step 1 baseline, and the foreign key relationships must be preserved.

To change a constraint, you need a procedure that creates a new table and moves the data over. If you do not follow the order, data or references break.

Batch backfill

Create /root/db/batch-update.sh. It takes two arguments (DB파일 배치크기, that is, the DB file and the batch size), changes the rows of ORDERS whose DLVR_STS_CD is '01' to '02', processing repeatedly one batch size at a time. On the last line, print batches=<반복횟수> updated=<총건수> (the number of iterations and the total row count). (To avoid dirtying the original, apply it only to the DB passed as an argument.)

If you do a bulk UPDATE in a single transaction, locks and logs explode. Structure it to repeat until the affected row count becomes 0.

Migration verification script

Create /root/db/mig-verify.sh. It takes two arguments (기준선파일 DB파일, that is, the baseline file and the DB file), compares the baseline's three metrics with the current DB, and if all are equal prints OK on the first line, and if not, a line starting with NG along with which metrics differ. The exit codes are 0 and non-zero respectively.

Row counts and totals alone cannot catch the situation where "one row went missing and another doubled." Use several metrics.

Work procedure document

Write /root/db/mig-runbook.md. It must have six h2 headings, ## 작업 개요, ## 사전 백업, ## 적용 절차, ## 검증, ## 롤백 기준, and ## 롤백 절차 (in order: overview, pre-backup, procedure, verification, rollback criteria, rollback procedure), and the body must include the backup file path and a numeric rollback criterion (duration or number of verification failures).

This is a document to be read at dawn. You must be able to copy and use the commands as they are, and when to stop and roll back must be written as numbers.