Go Repository Pattern: Nullable Model, One List

One Struct for Two Different Worlds

The organization module in KotaPortal stores columns that may be empty: long name, description, contact, address, tenure, type. The fastest guess is one struct for everything, database columns and JSON shape at once, with omitempty everywhere to keep responses tidy. That guess breaks at two points: the database driver needs explicit null types at Scan time, and one JSON shape cannot serve internal and public needs at once.

The model struct holds the database row as-is, with column tags and null types from the standard library:

type Organization struct {
    ID          int64          `db:"id"`
    CategoryID  int64          `db:"category_id"`
    Nama        string         `db:"nama"`
    NamaPanjang sql.NullString `db:"nama_panjang"`
    Deskripsi   sql.NullString `db:"deskripsi"`
    Kontak      sql.NullString `db:"kontak"`
    Alamat      sql.NullString `db:"alamat"`
    MasaBakti   sql.NullString `db:"masa_bakti"`
    SkHabis     bool           `db:"sk_habis"`
    TipeCabor   sql.NullString `db:"tipe_cabor"`
    NoUrut      sql.NullInt64  `db:"no_urut"`
    Status      string         `db:"status"`
}

Required columns use plain Go types, nullable ones use sql.NullString and sql.NullInt64. The reason is mechanical: the null string type implements Scanner so it can be a scan destination [1], and every nullable database column must be scanned into a null-supporting type [2]. The JSON response struct lives separately with pointers and omitempty, which drops empty values during encoding [4]:

type OrganizationResponse struct {
    ID          int64            `json:"id"`
    Nama        string           `json:"nama"`
    NamaPanjang *string          `json:"nama_panjang,omitempty"`
    Deskripsi   *string          `json:"deskripsi,omitempty"`
    Kontak      *string          `json:"kontak,omitempty"`
    Alamat      *string          `json:"alamat,omitempty"`
    MasaBakti   *string          `json:"masa_bakti,omitempty"`
    Status      string           `json:"status"`
    Members     []MemberResponse `json:"members,omitempty"`
}

One conversion function maps between them, filling pointers only when the column is valid. The database shape and the JSON shape never share a struct, so schema changes do not leak into the API contract. The pattern also keeps responses consistent: columns that are null in the database disappear from the JSON instead of surfacing as misleading empty strings.

One List for Admin and Public

The second need is the same listing with different visibility rules: admins see every status, the public sees only active rows. Instead of two queries, one List method builds its WHERE clause from a flag:

switch {
case q.AllStatuses:
    // Admin listing: no status filter.
case q.Status != "":
    where = append(where, "o.status = ?")
    args = append(args, q.Status)
default:
    where = append(where, "o.status = 'AKTIF'")
}

The same method runs two paired queries: a count with the identical clause for page totals, then a row fetch with ordering plus limit and offset. An empty result is not an error but an empty slice with a zero total, so blank pages stay valid responses. The clause used by both is assembled once from the same fragments, so the count filter and the data filter cannot diverge. The pattern sits on top of sqlx, whose handles wrap the database/sql equivalents so usage feels identical [5]. One branch, three behaviors, no duplicated query.

Every User LIKE Through One Door

Name search uses LIKE across two columns at once, and in MySQL the underscore matches exactly one character while percent matches any number including zero [3]. User input containing either character would act as a wildcard and return unintended rows. The fix is one central helper that escapes all three then wraps the pattern for contains matching:

var likeEscaper = strings.NewReplacer(`\`, `\\`, `%`, `\%`, `_`, `\_`)

func LikePattern(q string) string {
    return "%" + likeEscaper.Replace(q) + "%"
}

Every LIKE built from user input must pass through this helper. The rule is simple and auditable with one text search: no LIKE pattern assembled anywhere else.

Three decisions survive from this commit: model and DTO never share a struct, one List serves two audiences through a flag, and one helper owns all LIKE escaping. Each eliminates one class of silent bug: nulls failing at scan, admin and public queries diverging, and wildcards sneaking in from the search box.

Sources: [1] database/sql NullString [2] Go SQL wiki: dealing with NULL [3] MySQL 8.4 pattern matching (archived snapshot 2026-09-03) [4] encoding/json omitempty [5] sqlx illustrated guide