Skip to content
HomeAboutActivityPortfolioBlogAsk AI

Moving a Category Between Modules Is a Two-Part Job

Adityo Guni Waluyo

One UPDATE sounds finished. If the importer map keeps the old value, the next import quietly resurrects stale data.

#mysql#database-migration#golang#data

TL;DR

Moving the souvenir category to tourism required more than a database update. The migration uses a guarded WHERE clause to stay idempotent and reversible, while the importer map was updated in the same commit to prevent the old value from resurrecting. Wrapping it in a transaction would not help since MySQL auto-commits DDL.

Moving a Category is a Two-Part Job

Meta (untuk sistem): Moving a database category isn't just an UPDATE query. Here is why I updated the importer map and added a WHERE guard in the same commit to prevent stale data resurrection.

Slug (untuk sistem): category-module-migration-two-places-en

I was reading a ticket that seemed trivial at first glance. One category, "Oleh-oleh Khas Depok", needed to move from the EKONOMI_KREATIF module to the PARIWISATA module. The KAK scope explicitly listed "Souvenirs of Depok" as a tourism category, so the database had to reflect that. My first instinct was to just write a quick UPDATE categories SET module = 'PARIWISATA' WHERE slug = 'oleh-oleh-khas-depok'. But that is exactly how you create a silent data resurrection bug.

The Silent Half

The real danger of moving a category is not the migration itself. It is the code that generates or imports those rows in the first place. If I only changed the database, the next time the importer ran, it would read the old mapping in importer.go and overwrite my migration, putting the category right back where it started. I had to update both the migration and the moduleMap in the exact same commit.

Idempotency and the WHERE Guard

For migration 000060, I wrote an UPDATE statement, but I made sure to include a WHERE clause checking for the old state. An UPDATE without a WHERE clause touches all rows, which is a disaster waiting to happen [4]. By restricting the update to rows still in the old module, the migration becomes idempotent [4]. If someone runs it twice, the second run does nothing. The down migration does the exact reverse, making the whole operation safely reversible, which is a core best practice for golang-migrate [9].

UPDATE categories
SET module = 'PARIWISATA'
WHERE slug = 'oleh-oleh-khas-depok' AND module = 'EKONOMI_KREATIF';

The Transaction Illusion

I initially thought about wrapping the migration in a BEGIN and COMMIT block to be extra safe. But MySQL has a quirk: Data Definition Language (DDL) operations implicitly commit the active transaction [10]. Even if I tried to wrap it, the moment the DDL executes, the transaction is gone. Relying on database transactions for migration safety here is a false sense of security. The real safety net is the idempotent WHERE guard and keeping the code mapping in sync.

I also updated importer_test.go to reflect the new module mapping. If the test suite passes, I know the importer won't accidentally resurrect the old category state during the next data sync.

Sources:

Related articles

database

A Cosmetic Request That Forced a Data Contract Overhaul

Lowercasing an enum looks trivial; the values are compared literally in scope enforcement, so DDL, rows, constants, spec, and label maps move together.

testing

When the Migration Table Lies in Bidang Integration Tests

Green suite, stale schema: schema_migrations only logs executed files, a leftover Docker volume quietly carries the old schema.

backend

Setting Another User's Bidang: A Privileged Action

Only a superadmin may set a user bidang; the ALL default keeps 19 legacy users through the migration unharmed.

backend

Announcement Scope: Writing a Value vs Mutating a Row

Two authorization functions for per-division announcements: resolveBidang for new values, canManage for existing rows, NULL as global.

backend

Extensible Entity Attributes: a TEXT Column Holding JSON

Adding a TEXT attributes column for per-module dynamic attributes, with validation moved into the application.

backend

COALESCE at the API Door: Keep the ALL Sentinel in the Query

A super-admin without a bidang returns NULL from the database. One COALESCE in the login query keeps the API contract total and the admin badge correct.

backend

Bidang RBAC Enforcement: The Middleware Gate and IN Filters

Enforcing the bidang scope in Go: middleware at the gate, IN filters in repositories, and subtests that read like the security policy.

backend

Two-Axis RBAC: Roles for Actions, Bidang for Data

Roles decide actions, bidang decides data. Notes from building a fail-closed scope resolver in Go: ModulesFor, ResolveBidang, and the NULL trap.