bible/rules/patterns/go-database

Database Pattern Notes

This file keeps examples and rationale. The normative rules live in contract.md.

Cursor Safety

Wrong:

rows, _ := tx.Query("SELECT id FROM machines")
for rows.Next() {
    var id string
    rows.Scan(&id)
    tx.Exec("UPDATE machines SET processed=1 WHERE id=?", id)
}

Correct:

rows, _ := tx.Query("SELECT id FROM machines")
var ids []string
for rows.Next() {
    var id string
    rows.Scan(&id)
    ids = append(ids, id)
}
rows.Close()

for _, id := range ids {
    tx.Exec("UPDATE machines SET processed=1 WHERE id=?", id)
}

GORM Virtual Fields

Count int `gorm:"-"`
DisplayName string `gorm:"-:migration"`

SQL Header Example

-- Tables affected: supplier, lot_log
-- recovery.not-started: No action required.
-- recovery.partial:     DELETE FROM parts_log WHERE created_by = 'migration';
-- recovery.completed:   Same as partial.
-- verify: No orphaned supplier_code | SELECT supplier_code FROM parts_log pl LEFT JOIN supplier s ON s.supplier_code = pl.supplier_code WHERE s.supplier_code IS NULL AND pl.supplier_code IS NOT NULL AND pl.supplier_code != '' LIMIT 1

Docker Validation Example

docker run -d --name pf_test \
  -e MYSQL_ROOT_PASSWORD=test -e MYSQL_DATABASE=RFQ_LOG \
  mariadb:11.8 --character-set-server=utf8mb4 --collation-server=utf8mb4_uca1400_ai_ci

docker exec -i pf_test mariadb -uroot -ptest RFQ_LOG < prod_dump.sql

./pfs -migrate-dsn "root:test@tcp(127.0.0.1:3306)/RFQ_LOG?parseTime=true&charset=utf8mb4&multiStatements=true" \
      -no-backup -verbose

Legacy FK Repair Pattern

INSERT IGNORE INTO parent (name)
SELECT DISTINCT c.fk_col FROM child c
LEFT JOIN parent p ON p.name = c.fk_col
WHERE p.name IS NULL AND c.fk_col IS NOT NULL AND c.fk_col != '';