GORM With PostgreSQL Types Such as JSONB Arrays and UUIDs

PostgreSQL has column types that other databases do not: jsonb documents, arrays, uuid, ranges, inet, enums. GORM maps Go types to the common ones on its own, and for these it needs help. The help is small: a type in the tag and a type that knows how to read and write itself. This article maps three of them on PostgreSQL 18.6, queries inside them with the operators that make them worth using, and shows the details that cause surprises, such as NULL versus an empty array.

It builds on models and tags. The same types in pgx directly are in JSONB and UUID with pgx and custom types with pgx, and the sqlc mapping is in sqlc JSON and enums.

The model

The Profile struct uses one Go type per PostgreSQL type:

• ID uuid.UUID with gorm:"type:uuid;default:gen_random_uuid();primaryKey". The google/uuid type implements Scanner and Valuer, so no wrapper is needed.
• Tags pq.StringArray with type:text[]. lib/pq ships array helpers (StringArray, Int64Array, BoolArray) that implement the interfaces for any driver, including pgx behind GORM.
• Prefs Prefs with type:jsonb, where Prefs is a struct with a Value method that marshals to JSON and a Scan method that unmarshals. This is the pattern for any JSON column: a named type, two small methods.

package main

import (
	"database/sql/driver"
	"encoding/json"
	"errors"
	"fmt"
	"github.com/google/uuid"
	"github.com/lib/pq"
	"log"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

func must(err error) {
	if err != nil {
		log.SetFlags(0)
		log.Fatal(err)
	}
}

// connect recreates a schema and returns a connection whose search_path points at it.
func connect(schema string, cfg *gorm.Config) *gorm.DB {
	base := "host=127.0.0.1 port=55432 user=postgres password=secret dbname=lab sslmode=disable"
	admin, err := gorm.Open(postgres.Open(base), &gorm.Config{Logger: logger.Discard})
	must(err)
	must(admin.Exec("drop schema if exists " + schema + " cascade").Error)
	must(admin.Exec("create schema " + schema).Error)
	if cfg == nil {
		cfg = &gorm.Config{Logger: logger.Discard}
	}
	db, err := gorm.Open(postgres.Open(base+" search_path="+schema), cfg)
	must(err)
	return db
}

type Prefs struct {
	Theme string   `json:"theme"`
	Beta  bool     `json:"beta"`
	Langs []string `json:"langs"`
}

// Prefs is stored as jsonb through the Valuer and Scanner interfaces.
func (p Prefs) Value() (driver.Value, error) { return json.Marshal(p) }

func (p *Prefs) Scan(v any) error {
	b, ok := v.([]byte)
	if !ok {
		s, ok2 := v.(string)
		if !ok2 {
			return errors.New("unsupported type")
		}
		b = []byte(s)
	}
	return json.Unmarshal(b, p)
}

type Profile struct {
	ID    uuid.UUID `gorm:"type:uuid;default:gen_random_uuid();primaryKey"`
	Name  string
	Tags  pq.StringArray `gorm:"type:text[]"`
	Prefs Prefs          `gorm:"type:jsonb"`
}

func main() {
	db := connect("l0722", nil)
	must(db.AutoMigrate(&Profile{}))
	must(db.Exec("create index on profiles using gin (prefs)").Error)
	must(db.Exec("create index on profiles using gin (tags)").Error)

	a := Profile{Name: "ada", Tags: pq.StringArray{"go", "db"}, Prefs: Prefs{Theme: "dark", Beta: true, Langs: []string{"en", "vi"}}}
	b := Profile{Name: "bob", Tags: pq.StringArray{"ops"}, Prefs: Prefs{Theme: "light"}}
	must(db.Create(&a).Error)
	must(db.Create(&b).Error)
	fmt.Println("uuid filled by the database:", a.ID != uuid.Nil, "version", a.ID.Version())

	var got Profile
	must(db.First(&got, "id = ?", a.ID).Error)
	fmt.Println("round trip:", got.Name, got.Tags, got.Prefs.Theme, got.Prefs.Langs)

	var cols []string
	db.Raw(`select column_name || ' ' || udt_name from information_schema.columns where table_schema = 'l0722' and table_name = 'profiles' order by ordinal_position`).Scan(&cols)
	fmt.Println("column types:", cols)

	// Query inside the document and the array.
	var dark []Profile
	db.Where("prefs @> ?", `{"theme":"dark"}`).Find(&dark)
	fmt.Println("jsonb containment:", len(dark))

	var inArray []Profile
	db.Where("tags @> ?", pq.StringArray{"go"}).Find(&inArray)
	fmt.Println("array containment:", len(inArray))

	var anyOf []Profile
	db.Where("? = any(tags)", "ops").Find(&anyOf)
	fmt.Println("any(tags):", len(anyOf))

	var theme string
	db.Model(&Profile{}).Where("name = ?", "ada").Select("prefs ->> 'theme'").Scan(&theme)
	fmt.Println("prefs ->> 'theme':", theme)

	// Partial update of one key inside the document, in the database.
	db.Model(&Profile{}).Where("name = ?", "bob").Update("prefs", gorm.Expr("jsonb_set(prefs, '{beta}', 'true')"))
	var bob Profile
	db.Where("name = ?", "bob").Take(&bob)
	fmt.Println("after jsonb_set:", bob.Prefs.Beta, "theme kept:", bob.Prefs.Theme)

	// A nil array stores NULL and an empty one stores {}.
	c := Profile{Name: "cy"}
	db.Create(&c)
	var isNull bool
	db.Raw("select tags is null from profiles where name = 'cy'").Scan(&isNull)
	fmt.Println("nil StringArray stored as NULL:", isNull)
}

uuid filled by the database: true version VERSION_4
round trip: ada [go db] dark [en vi]
column types: [id uuid name text tags _text prefs jsonb]
jsonb containment: 1
array containment: 1
any(tags): 1
prefs ->> 'theme': dark
after jsonb_set: true theme kept: light
nil StringArray stored as NULL: true

What the database got

The lab's information_schema query printed [id uuid name text tags _text prefs jsonb]. Each Go type became the column type from its tag: uuid, text, _text (PostgreSQL's internal name for text[]), and jsonb. Without the type: tag, GORM would have guessed text for the UUID and bytea or text for the JSON, and everything would still seem to work until you wanted an operator.

UUID keys

default:gen_random_uuid() lets PostgreSQL generate the key. gen_random_uuid() is built in from PostgreSQL 13. GORM saw a zero UUID, left the column out of the insert, and read the value back through RETURNING. The lab printed uuid filled by the database: true version VERSION_4.

The alternative is to generate in Go with uuid.New() in a BeforeCreate hook, which sets the id before the insert and lets you know it ahead of the write. Prefer the database default for simplicity, and the Go side when the id is needed to build related rows before they are saved. For index locality, time ordered UUIDs (version 7) avoid the random insert pattern of version 4 and are worth considering for large tables; PostgreSQL 18 adds uuidv7().

JSONB documents

The Prefs type round tripped: ada [go db] dark [en vi]. Reading it back needed Scan to accept both []byte and string, because the driver may hand over either, and the lab's version handles both.

Store JSON when the shape varies per row or evolves faster than migrations; keep a column for anything you filter, sort or join on. A jsonb column that holds email and created_at is a table that lost its schema.

Querying inside the types

The point of mapping these types is the operators. All three need a raw fragment in Where, and values still go through placeholders:

• JSON containment. Where("prefs @> ?", {"theme":"dark"}) found 1 profile. @> asks whether the left document contains the right one. A GIN index on prefs (the lab creates one) lets PostgreSQL answer it without scanning the table.
• Array containment. Where("tags @> ?", pq.StringArray{"go"}) found 1. The pq.StringArray argument is sent as an array, so it compares properly.
• Membership. Where("? = any(tags)", "ops") found 1. = any(array) tests a single value against an array.
• Extraction. Select("prefs ->> 'theme'") returned dark. The ->> operator gives text, -> gives jsonb.

An index must match the operator. A GIN index with the default operator class supports @>, ? and ?&, and not ->> comparisons. To filter on prefs->>'theme' = 'dark' with an index, create an expression index on (prefs->>'theme'). Use EXPLAIN (the guide) to confirm.

GORM has an official package, gorm.io/datatypes, with a JSON type and query helpers such as datatypes.JSONQuery. It is convenient, and it is one more dependency; the hand written type here is about twenty lines and does exactly what you read. For typed access to the document, a struct like Prefs gives you compile time field names.

Updating one key

Loading a document, changing a field in Go and saving the whole thing races with other writers and rewrites keys the code never touched. jsonb_set changes one key inside the database:

db.Model(&Profile{}).Where("name = ?", "bob").
	Update("prefs", gorm.Expr("jsonb_set(prefs, '{beta}', 'true')"))

The lab ran it for bob and read the row back: after jsonb_set: true theme kept: light. The beta flag changed and the theme was untouched. The path is a text array literal ('{beta}', or '{a,b}' for nested keys), and the new value must be valid JSON, so strings need their quotes inside ('"dark"'). Use || to merge several keys at once: prefs || ?::jsonb with a JSON object parameter.

NULL versus empty

pq.StringArray has a subtle rule. A nil array is stored as NULL, and an empty but non nil one as {}. The lab created a profile without tags and queried tags is null: the answer was true. Code that tests array_length(tags, 1) > 0 or cardinality(tags) sees NULL for the first and 0 for the second, and tags @> '{go}' is false for both. Pick one convention (always initialise to an empty slice, or always allow NULL), and add a not null default '{}' to the column if you want the database to hold it. The same applies to a Prefs value: its zero value marshals to a document with null keys, not to SQL NULL.

Other types in the family

• Enums. Declare the type in a migration (create type status as enum (...)) and map the Go field as a string type with gorm:"type:status". AutoMigrate cannot create the enum, so it belongs in a migration.
• inet, cidr. Use netip.Addr through a small Valuer and Scanner, or string with the column type set.
• Ranges, tsvector. Use raw fragments for queries and a string or custom type for the field.
• numeric. Map money to decimal.Decimal from shopspring/decimal, which implements both interfaces, rather than float64.

Checklist

• Put type: in the tag for uuid, jsonb and arrays, so migrations create the right column.
• Implement Value and Scan for JSON types, and handle []byte and string in Scan.
• Use pq.StringArray or a similar helper for arrays, and decide how NULL is treated.
• Match the index to the operator: GIN for @>, an expression index for ->>.
• Change one key with jsonb_set or || in SQL, not by loading and saving the document.

The mappings are described in the GORM data types guide, and the operators in the PostgreSQL JSON functions and operators chapter.