GORM Error Handling for Not Found and Constraint Failures

Every handler that touches a database has to answer the same question after a failed call: is this the caller's fault (400, 404, 409), or is it ours (500)? GORM returns an error, and what is inside it depends on the driver, the database and a configuration flag. This article makes the failures happen on PostgreSQL 18.6, prints what GORM returns for each, and builds the translation function that turns them into a small set of domain errors the rest of the program can use.

It builds on the GORM tutorial. The same questions without an ORM are in database error handling in Go, unique violation errors and ErrNoRows, and the code table for pgx is in pgx error codes.

Domain errors first

The store layer should not leak GORM to its callers. A handler that imports gorm.io/gorm to compare with ErrRecordNotFound couples HTTP code to the persistence library. The lab defines five sentinels the application owns: ErrNotFound, ErrConflict, ErrInvalid, ErrInUse and ErrTimeout. Handlers map those to status codes (404, 409, 422, 409, 504), and only the store knows how they arise.

The translation function

package main

import (
	"context"
	"errors"
	"fmt"
	"github.com/jackc/pgx/v5/pgconn"
	"log"
	"strings"
	"time"

	"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 User struct {
	ID    uint
	Email string `gorm:"uniqueIndex"`
	Age   int    `gorm:"check:age_range,age between 0 and 150"`
	Name  string `gorm:"not null"`
}

type Post struct {
	ID     uint
	UserID uint
	User   User `gorm:"constraint:OnDelete:RESTRICT"`
}

// Domain errors the rest of the program uses, so handlers never import gorm.
var (
	ErrNotFound = errors.New("not found")
	ErrConflict = errors.New("conflict")
	ErrInvalid  = errors.New("invalid")
	ErrInUse    = errors.New("in use")
	ErrTimeout  = errors.New("timeout")
)

func translate(err error) error {
	if err == nil {
		return nil
	}
	var pg *pgconn.PgError
	switch {
	case errors.Is(err, gorm.ErrRecordNotFound):
		return fmt.Errorf("%w", ErrNotFound)
	case errors.Is(err, gorm.ErrDuplicatedKey):
		return fmt.Errorf("%w: %v", ErrConflict, constraint(err))
	case errors.As(err, &pg) && pg.Code == "23001":
		return fmt.Errorf("%w: %s", ErrInUse, pg.ConstraintName)
	case errors.Is(err, gorm.ErrForeignKeyViolated):
		if errors.As(err, &pg) && strings.HasPrefix(pg.Message, "update or delete") {
			return fmt.Errorf("%w: %s", ErrInUse, pg.ConstraintName)
		}
		return fmt.Errorf("%w: unknown reference (%s)", ErrInvalid, constraint(err))
	case errors.Is(err, context.DeadlineExceeded):
		return ErrTimeout
	case errors.As(err, &pg) && pg.Code == "23514":
		return fmt.Errorf("%w: check %s", ErrInvalid, pg.ConstraintName)
	case errors.As(err, &pg) && pg.Code == "23502":
		return fmt.Errorf("%w: %s is required", ErrInvalid, pg.ColumnName)
	}
	return err
}

func constraint(err error) string {
	var pg *pgconn.PgError
	if errors.As(err, &pg) {
		return pg.ConstraintName
	}
	return "?"
}

func main() {
	db := connect("l0731", &gorm.Config{Logger: logger.Discard, TranslateError: true})
	must(db.AutoMigrate(&User{}, &Post{}))
	ada := User{Email: "[email protected]", Age: 36, Name: "Ada"}
	must(db.Create(&ada).Error)
	must(db.Create(&Post{UserID: ada.ID}).Error)

	show := func(label string, err error) {
		fmt.Printf("%-22s raw: %-12s -> %v\n", label, kind(err), translate(err))
	}

	show("missing row", db.First(&User{}, 999).Error)
	show("duplicate email", db.Create(&User{Email: "[email protected]", Age: 1, Name: "Dup"}).Error)
	show("check violation", db.Create(&User{Email: "[email protected]", Age: 200, Name: "Old"}).Error)
	show("not null", db.Exec("insert into users (email, age) values ('[email protected]', 1)").Error)
	show("foreign key insert", db.Create(&Post{UserID: 4242}).Error)
	show("foreign key delete", db.Delete(&User{}, ada.ID).Error)

	ctx, cancel := context.WithTimeout(context.Background(), 50*time.Millisecond)
	defer cancel()
	show("deadline", db.WithContext(ctx).Exec("select pg_sleep(1)").Error)

	// Errors that are not errors from the database.
	show("no where clause", db.Delete(&User{}).Error)
	res := db.Model(&User{}).Where("id = ?", 999).Update("name", "Ghost")
	fmt.Println("update of a missing row: error", res.Error, "| rows affected", res.RowsAffected)

	// Find never returns not found; First does.
	var users []User
	fmt.Println("Find with no match:", db.Where("age > ?", 500).Find(&users).Error, len(users))

	// A unique violation inside a transaction: the transaction is dead afterwards.
	err := db.Transaction(func(tx *gorm.DB) error {
		if e := tx.Create(&User{Email: "[email protected]", Age: 1, Name: "Dup"}).Error; e != nil {
			fmt.Println("  inside tx, handled:", translate(e))
		}
		return tx.Create(&User{Email: "[email protected]", Age: 1, Name: "Next"}).Error
	})
	fmt.Println("next statement after a handled conflict:", translate(err) != nil)
}

func kind(err error) string {
	switch {
	case errors.Is(err, gorm.ErrRecordNotFound):
		return "NotFound"
	case errors.Is(err, gorm.ErrDuplicatedKey):
		return "DuplicatedKey"
	case errors.Is(err, gorm.ErrForeignKeyViolated):
		return "ForeignKey"
	case errors.Is(err, gorm.ErrMissingWhereClause):
		return "NoWhere"
	}
	var pg *pgconn.PgError
	if errors.As(err, &pg) {
		return "pg " + pg.Code
	}
	if err != nil {
		return "other"
	}
	return "nil"
}

missing row            raw: NotFound     -> not found
duplicate email        raw: DuplicatedKey -> conflict: idx_users_email
check violation        raw: pg 23514     -> invalid: check age_range
not null               raw: pg 23502     -> invalid: name is required
foreign key insert     raw: ForeignKey   -> invalid: unknown reference (fk_posts_user)
foreign key delete     raw: pg 23001     -> in use: fk_posts_user
deadline               raw: other        -> timeout
no where clause        raw: NoWhere      -> WHERE conditions required
update of a missing row: error <nil> | rows affected 0
Find with no match: <nil> 0
  inside tx, handled: conflict: idx_users_email
next statement after a handled conflict: true

The translate function is a switch over the ways an error can be recognised, in order. It uses errors.Is for GORM's sentinels, errors.As into a *pgconn.PgError for PostgreSQL codes, and returns anything else unchanged, so an unexpected failure stays visible as a 500.

TranslateError

TranslateError: true in the config is what makes ErrDuplicatedKey and ErrForeignKeyViolated exist. Without it, a unique violation is a *pgconn.PgError with code 23505 and nothing else, and every call site compares codes. With it, GORM maps a handful of dialect errors to its sentinels, and the original error stays in the chain, so errors.As still finds the PgError and its ConstraintName. The lab's duplicate case shows both: raw: DuplicatedKey -> conflict: idx_users_email. The constraint name is how you tell which field collided, and the right message to show is "that email is taken", not "that name is taken". Name your constraints, and map the name to a field in one place.

What each failure returned

Three of these deserve a closer look.

Check and not null are not translated by GORM. They arrived as raw PostgreSQL errors with codes 23514 and 23502, and the PgError carries the useful detail: ConstraintName for the check and ColumnName for the not null. The lab's translate reads them with errors.As. A check constraint is a business rule you chose to enforce in the database, so a violation is almost always a 422 with the rule's name, and naming checks (age_range) makes that message possible.

Foreign keys have two directions and two codes. An insert that points at a missing parent returned GORM's ErrForeignKeyViolated, from SQLSTATE 23503. A delete of a parent that still has children, on a constraint declared ON DELETE RESTRICT, returned SQLSTATE 23001 (restrict_violation), which GORM did not translate. With the default NO ACTION the same delete fails with 23503, with a message that begins update or delete, and an insert failure begins insert or update. The lab distinguishes them by code and by that message prefix. Production code should do the same, because "the id you gave does not exist" and "this record is still used" are different answers for a client.

Timeouts are context errors. A deadline surfaces as context.DeadlineExceeded wrapped by the driver, and errors.Is finds it. Map it to a 504, and a cancel (context.Canceled) to nothing. See context timeouts.

Errors that are not errors

Some outcomes that look like failures return no error at all, and a handler that checks only err misses them:

• Update of a missing row returned error <nil> and rows affected 0. If "no such record" should be a 404, check RowsAffected. The same goes for Delete.
• Find with no match returned <nil> 0: a nil error and an empty slice. Find never reports not found; only First, Take and Last do. A handler that expects a single record from Find into a struct gets a zero struct and no error.
• Delete with no Where returns ErrMissingWhereClause, a guard that refuses to empty a table. It is a programming error, so let it surface as a 500.

A conflict does not rescue a transaction

The last block of the lab creates a duplicate inside db.Transaction, handles the error (it prints inside tx, handled: conflict: idx_users_email) and then tries another insert. That insert failed too, and the closure returned an error. PostgreSQL aborts a transaction after any statement error, so a handled conflict does not let the transaction continue. The ways out are to avoid the error with ON CONFLICT DO NOTHING, to wrap the risky statement in a nested Transaction (a savepoint), or to check first and accept the race. The transactions article shows each.

Where to put the translation

One function, called in one layer. Two designs work:

1. Wrap at the end of each store method. return translate(err). Explicit, and a method can add context: fmt.Errorf("create user: %w", translate(err)).
2. A GORM callback. Register an After callback on create, update and delete that rewrites tx.Error with translate. Every call is covered with no change to call sites, and the cost is that the translation is invisible and the original error must still be reachable (keep it wrapped).

Option 1 is easier to follow and test. Test the translation with a table of real failures, as the lab does, because the codes are the part that you would otherwise get wrong from memory.

Wrapping and messages

Keep the original error in the chain with %w, so logs show the PostgreSQL text and errors.As still works, but never put the raw error in a response body. It contains table and constraint names, and sometimes the offending value (Key (email)=([email protected]) already exists). Log it, and send the client the domain message. A constraint that names a unique column on a table with personal data can leak whether an address is registered, so a sign up endpoint may choose to respond the same way for a duplicate and a success.

Checklist

• Set TranslateError: true and match sentinels with errors.Is.
• Use errors.As to *pgconn.PgError for check, not null, restrict and any code GORM does not translate.
• Name every constraint, and map names to fields and messages in one table.
• Check RowsAffected after Update and Delete when absence matters.
• Never return the raw error to a client.
• Do not expect a transaction to continue after a handled error.

The sentinel errors are listed in the GORM errors documentation, and the code classes in the PostgreSQL error codes appendix.