sqlc Overrides for Custom Types Nulls and UUIDs in Go

sqlc picks a Go type for every column from a built in table, and that table is right more often than wrong. It is wrong in a few places that matter. A uuid becomes pgtype.UUID, which no other library in your program accepts. A numeric becomes pgtype.Numeric, which is exact but awkward to add or print. A nullable column becomes a Valid flag wrapper that leaks into JSON. Overrides replace those choices once, in configuration, and every query that touches the column picks up the change at the next sqlc generate.

This article runs four overrides against PostgreSQL 18.6 with sqlc v1.30.0, pgx v5.11.0 and Go 1.25.6, and reports the generated types. It continues the sqlc tutorial and relates to the pgx custom types article, where the same problem is solved at the driver level instead.

Four overrides

The configuration holds the whole story:

version: "2"
sql:
  - engine: postgresql
    schema: schema.sql
    queries: query.sql
    gen:
      go:
        package: main
        out: .
        sql_package: pgx/v5
        emit_pointers_for_null_types: true
        overrides:
          - db_type: "uuid"
            go_type: "github.com/google/uuid.UUID"
          - db_type: "uuid"
            nullable: true
            go_type: "github.com/google/uuid.NullUUID"
          - db_type: "pg_catalog.numeric"
            go_type: "github.com/shopspring/decimal.Decimal"
          - db_type: "timestamptz"
            go_type: "time.Time"
          - column: "accounts.email"
            go_type: "example.test/lab/types.Email"

The table has a UUID primary key, a nullable UUID parent, a text email, a numeric balance, a nullable text note and two timestamps:

create table accounts (
    id      uuid primary key default gen_random_uuid(),
    parent  uuid references accounts (id),
    email   text not null,
    balance numeric(12,2) not null default 0,
    note    text,
    seen    timestamptz,
    created timestamptz not null default now()
);

Each override entry has a selector and a target. The selector is either db_type (every column of that database type) or column (one column, written table.column). The target is go_type, a package path and a name, such as github.com/google/uuid.UUID. An optional nullable: true narrows the selector to the nullable form of the type.

• db_type: "uuid" with google/uuid.UUID changes every non null UUID column.
• db_type: "uuid", nullable: true with uuid.NullUUID handles the nullable one, because a non null uuid.UUID cannot represent absence.
• db_type: "pg_catalog.numeric" with shopspring/decimal.Decimal changes the money type. The name is pg_catalog.numeric and not numeric, which is how sqlc spells built in PostgreSQL types that have a schema.
• db_type: "timestamptz" with time.Time replaces the pgtype wrapper.
• column: "accounts.email" with a custom type applies to that one column.

The queries insert and fetch an account, so the generated struct has every field:

-- name: Open :one
insert into accounts (email, balance, parent, note) values ($1, $2, $3, $4) returning *;

-- name: Get :one
select * from accounts where id = $1;

A custom type goes in its own package

The Email type is a string that lower cases itself when scanned:

package types

import (
	"fmt"
	"strings"
)

// Email is a string that has been checked and lower cased; sqlc uses it for the accounts.email column.
type Email string

func (e *Email) Scan(src any) error {
	switch v := src.(type) {
	case string:
		*e = Email(strings.ToLower(v))
	case []byte:
		*e = Email(strings.ToLower(string(v)))
	default:
		return fmt.Errorf("cannot scan %T into Email", src)
	}
	return nil
}

The first version of this lab put it in the program's own main package, and generation produced code that did not compile: import cycle not allowed. The generated models.go is in package main and must import the type named in the override, and the type's package is the one that imports the generated code. A custom type has to sit in a package that does not import the generated code. In a real project that is a small internal/types or internal/domain package, which is a better home for a domain type anyway.

The type needs two things to work with pgx: it must implement sql.Scanner for reading (Scan above), and pgx writes a string kind through its normal path, so a defined type whose underlying type is string encodes without more code. If your type is a struct, add driver.Valuer as well.

What came out

The program opens an account, then reads it back:

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/shopspring/decimal"
)

const dsn = "postgres://postgres:[email protected]:55432/sqlc0690?sslmode=disable"

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

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	must(err)
	defer pool.Close()
	q := New(pool)

	bal, _ := decimal.NewFromString("19.99")
	note := "first account"
	root, err := q.Open(ctx, OpenParams{Email: "[email protected]", Balance: bal, Note: &note})
	must(err)
	fmt.Printf("1. generated Account fields: ID %T, Parent %T, Email %T, Balance %T, Note %T, Seen %T, Created %T\n",
		root.ID, root.Parent, root.Email, root.Balance, root.Note, root.Seen, root.Created)
	fmt.Println("   the uuid is a real uuid.UUID:", root.ID != uuid.Nil, "| the NULL parent is Valid =", root.Parent.Valid, "| the NULL seen time is Valid =", root.Seen.Valid)

	child, err := q.Open(ctx, OpenParams{Email: "[email protected]", Balance: decimal.RequireFromString("0.10"), Parent: uuid.NullUUID{UUID: root.ID, Valid: true}})
	must(err)
	fmt.Println("2. a child account points at its parent:", child.Parent.UUID == root.ID, "| its note is nil:", child.Note == nil)

	got, err := q.Get(ctx, root.ID)
	must(err)
	fmt.Printf("3. read back: balance %s (exact, no float), email %q (lower cased by Email.Scan)\n", got.Balance.String(), got.Email)
	sum := decimal.RequireFromString("0.1").Add(decimal.RequireFromString("0.2"))
	f1, f2 := 0.1, 0.2
	fmt.Println("4. decimal 0.1 + 0.2 =", sum.String(), "| float64 0.1 + 0.2 =", f1+f2, "| the stored balance", got.Balance.String(), "and the child's", child.Balance.String(), "add to", got.Balance.Add(child.Balance).String())
}

set -e
export PGOPTIONS='-c client_min_messages=warning'
DB=sqlc0690
docker exec -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -qc "drop database if exists $DB" -c "create database $DB"
docker exec -i -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -d $DB -q < schema.sql
"$HOME/go/bin/sqlc" generate
go run .

1. generated Account fields: ID uuid.UUID, Parent uuid.NullUUID, Email types.Email, Balance decimal.Decimal, Note *string, Seen pgtype.Timestamptz, Created time.Time
   the uuid is a real uuid.UUID: true | the NULL parent is Valid = false | the NULL seen time is Valid = false
2. a child account points at its parent: true | its note is nil: true
3. read back: balance 19.99 (exact, no float), email "[email protected]" (lower cased by Email.Scan)
4. decimal 0.1 + 0.2 = 0.3 | float64 0.1 + 0.2 = 0.30000000000000004 | the stored balance 19.99 and the child's 0.1 add to 20.09

The fields have the types you asked for. ID uuid.UUID, Parent uuid.NullUUID, Email types.Email, Balance decimal.Decimal and Created time.Time. The note was *string, from emit_pointers_for_null_types, which the configuration also sets.

One type did not change. Seen, the nullable timestamptz, stayed pgtype.Timestamptz. The override db_type: "timestamptz" has no nullable flag, so it matches only non null columns, and the nullable form is a separate selector. To change it, add a second entry with nullable: true and a pointer target such as *time.Time; this lab did not run that variant, so check the generated field after adding it. It is easy to miss, because the generated code compiles either way.

The scanner ran. The account was inserted with [email protected] and read back as [email protected], because Email.Scan lower cased it on the way out. That is a convenient place for normalisation and a poor one for validation: scanning cannot reject a bad value without making a read fail.

A child points at its parent. The second account's Parent was a uuid.NullUUID with Valid true and the parent's id, and its Note was nil. A root account's parent was Valid = false, the way a missing parent looks in this representation.

Decimal arithmetic is exact. The last line printed decimal 0.1 + 0.2 = 0.3 and float64 0.1 + 0.2 = 0.30000000000000004. That difference, small in one addition, accumulates over a ledger. The stored balance 19.99 and the child's 0.1 added to 20.09 with no rounding.

Nulls: two ways to say absent

Overrides interact with the null handling you chose in the configuration, and it is worth deciding the two together. With emit_pointers_for_null_types: true, a nullable text column is a *string, and the nil pointer is absence. That reads well and marshals to JSON null, but it cannot tell "the column is NULL" from "this struct has not been filled in", and a forgotten field in a partial update silently writes NULL.

With the default wrappers (pgtype.Text and friends), absence is a Valid flag, which is explicit and verbose. The lab's Parent field uses a third form, uuid.NullUUID, from the standard database/sql style, because that is what the UUID library provides. A project can end up with three representations of "maybe" unless the overrides pick one. A short rule works: choose pointers for fields that go straight into JSON, choose the wrappers for fields that stay inside the data layer, and write a conversion function at the one boundary where they meet.

The same care applies to errors from a missing row. The ErrNoRows article covers why a nullable column and an absent row are different kinds of absence, and why they should not share a representation.

When to override and when not to

Override for types that cross a boundary. A UUID that is passed to a library, an HTTP handler or a message queue should be the library's UUID type. A decimal that feeds a pricing function should be the decimal the function takes.

Leave defaults where the type stays inside the data layer. If a pgtype.Timestamptz is only ever converted to a time.Time in one place, an override that does it everywhere simplifies the callers, but it also hides the infinity values the wrapper can hold, which a time.Time cannot represent. If your data never uses them, the override is safe.

Three habits keep overrides manageable:

• Prefer db_type selectors and add column ones only for domain types such as Email. A long list of per column overrides is a sign that the column types need attention.
• Keep the list in one place. Two entries in one sqlc.yaml with different overrides generate different types for the same table, and a function that takes both breaks.
• Run a build after changing one. Every call site that used the old type stops compiling, which is the list of places to edit.

The selector syntax and the nullable flag are described in sqlc's overrides reference. The types themselves are documented at google/uuid and shopspring/decimal.