Run one-off tasks as reviewed scripts

How to do it

A query typed by hand on the production database has no review, no test and no record. A fix to data itself follows fix production data with a reviewed script. This rule covers every other one-off task. Commit the script and review it like any change. Ship it with the release, so it runs with the same code, dependencies and settings as the app. Make it print what it would change by default, and write only with a flag such as --apply. Run the write in one transaction, and stop when the count differs from the dry run. Log the script, the person and the count. Take a backup first, as encrypted backups describes. Schema changes go through migrations instead.

Examples

JavaScript example

// scripts/fix-order-totals.js, run with: node scripts/fix-order-totals.js [--apply]
const isApply = process.argv.includes('--apply');
const rows = db.prepare('SELECT id FROM orders WHERE total_cents < 0').all();
console.log(`${rows.length} orders to fix`);
if (isApply) {
  db.transaction(() => {
    const { changes } = db.prepare('UPDATE orders SET total_cents = 0 WHERE total_cents < 0').run();
    if (changes !== rows.length) throw new Error(`Expected ${rows.length}, changed ${changes}`);
  })();
  log.info({ task: 'fix-order-totals', user: process.env.USER, changed: rows.length });
}

Python example

# scripts/fix_order_totals.py, run with: python -m scripts.fix_order_totals [--apply]
import os, sys
is_apply = "--apply" in sys.argv
ids = [row[0] for row in db.execute("SELECT id FROM orders WHERE total_cents < 0")]
print(f"{len(ids)} orders to fix")
if is_apply:
    with db:
        changed = db.execute("UPDATE orders SET total_cents = 0 WHERE total_cents < 0").rowcount
        if changed != len(ids):
            raise RuntimeError(f"Expected {len(ids)}, changed {changed}")
    log.info("fix-order-totals", extra={"user": os.environ["USER"], "changed": changed})

How to check

Every script in the admin folder changes 0 rows when run without --apply against a test copy. The database audit log for the last 30 days shows no write from a person's own account, only from the task runner's account.

Sources

• The Twelve-Factor App: Admin processes

Related rules

• Fix production data with reviewed scripts in Data
• Wrap multi-step changes in a transaction in Data
• Encrypt backups in Data
• Change the schema only with migrations in Data
• Keep manual operations work under half the time in Operations