Database & Migrations

Koneksi

DSN disusun dari environment variables (DB_TYPE, DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME, DB_SSLMODE). Contoh DSN Postgres:

host=localhost port=5432 user=postgres dbname=app_db password=secret sslmode=disable

Koneksi dibuat di internal/db/gorm.go. Utamakan injeksi instance DB ke service daripada pakai global variable.

Pooling & tuning

• DB_MAX_OPEN_CONNS, DB_MAX_IDLE_CONNS, DB_CONN_MAX_LIFETIME_SEC
• Pantau pg_stat_activity (Postgres) untuk hindari connection exhaustion saat scaling.

Migrasi

File migrasi ada di migrations/{db_type} (core) dan plugins/{plugin_id}/migrations/{db_type} (plugin).

# buat pasangan up/down (timestamp otomatis)
go run ./cmd/console migrate make add_users_table

# apply pending (core lalu plugin)
go run ./cmd/console migrate up

# apply hanya untuk plugin tertentu
go run ./cmd/console migrate --plugin myplugin up

# rollback satu langkah / semua
go run ./cmd/console migrate down
go run ./cmd/console migrate down-all

# status per target
go run ./cmd/console migrate list

State dilacak di tabel migrations dan migration_targets (auto-create). Jika target dirty, CLI menolak lanjut — perbaiki dulu file/DB, lalu clear dirty.

Transaksi & context

Terapkan transaksi di boundary handler dan teruskan *gorm.DB ke service secara eksplisit. Gunakan db.WithContext(ctx) agar cancellation/timeout ter-propagasi.

err := db.WithTransaction(ctx, deps.DB, func(tx *gorm.DB) error {
    if err := yourService.CreateItem(ctx, tx, req); err != nil {
        return err
    }
    return nil
})

Helper WithTransaction ada di internal/db/tx.go (wrap begin/commit/rollback + panic handling).

Best practices:

• Jaga transaksi singkat & deterministik
• Pastikan Rollback() dipanggil kecuali Commit() sukses
• Untuk read-only, pakai deps.DB langsung tanpa Begin()