Four Bars With the Same Name: An Import Key That Must Not Guess
A three-column natural key, a NULL-safe comparison, and a guard that fires before the first query.
TL;DR
Data was moved from an intake file into two tables. A key issue arose with duplicate organization names. The code was fixed to use a three-column key to prevent data loss.
I was writing a small CLI to move structural sports-club data from the intake file into two tables, organizations and organization_members. Each row's identity looked self-evident: club name plus category, three categories, 54 + 2 + 5 rows in total (figure from commit 7628acc). What that row count does not show: four rows in the intake file are all named "FASI", and the only thing separating them is the text inside parentheses.
My first guess landed on the most convenient key. nama + category_id is enough, the package header comment says so, and that was exactly the key I considered first because it is the shortest to write and the easiest to read. Short query, one SELECT, done. If a name is already there, doesn't that mean the row was imported before?
The Key That Collapses
The problem only became visible once I opened the parentheses. Those four FASI branches are four separate organizations with different long names; the short name is identical because the parent body is the same. With a key of nama alone, the second run would skip the first three branches and leave one row behind, and I would read "skipped (already exists)" and assume everything was fine. No error, no duplicate, just three organizations that were never stored. That is the hardest kind of data loss to notice: it looks like nothing happened.
On the schema side, MySQL does not object. The organizations table carries a primary key, a foreign key to the category table, and two plain indexes, one on category_id and one on status — no UNIQUE covering the name-and-category combination. Anyone relying on a constraint to hold duplicates back will not find one there. MySQL builds an index from the columns you list, and that concatenation becomes the index value [2], and a composite index may hold up to 16 columns [3]. With no unique index in place, the duplicate guard moves into the code, so the key became three columns:
SELECT COUNT(*) FROM organizations
WHERE nama = ? AND nama_panjang <=> ? AND category_id = ?nama_panjang is part of the key so the four FASI branches never collapse into one row (commit 7628acc). The principle matches what messaging systems use to absorb redelivery: the key must identify the same unit of work uniquely and consistently on every repetition [1]. One name means one unit of work only when the unit really is one.
The Silent NULL
The nama_panjang column is nullable, and most rows leave it empty. That is where = is dangerous: comparing NULL with = does not return true, it returns NULL, so a row that should match is treated as absent and inserted again. The <=> operator treats NULL as a comparable value, so empty on both sides still counts as equal.
A shorter alternative exists: INSERT ... ON DUPLICATE KEY UPDATE. MySQL only runs it when the incoming row collides with a UNIQUE index or a primary key [4], and this combination has neither. Without a schema change the path stays check-then-insert, a pattern that has to be disciplined because nothing enforces it in the database.
A Guard Before the Connection
One line at the top of main() refuses to run unless APP_ENV is exactly development, before sql.Open is called at all:
if os.Getenv("APP_ENV") != "development" {
fatal("guard APP_ENV: hanya boleh dijalankan dengan APP_ENV=development")
}The fatal message stays in Indonesian on purpose: it is a string literal, not a comment, so it ships as written. Environment variables as switches are separated from code so deploy-specific values never get baked into the binary [5]. For a one-file CLI that writes dozens of rows into a production table, this guard is the only brake; without it, one missing export is enough.
The most useful part was what I found on re-reading: the header comment still claims idempotency on nama + category_id while the code already compares three columns. The comment was left behind by its own fix. I corrected it, then stopped treating comments as evidence: what counts is the query that actually runs.
Sources: