Handle sql ErrNoRows in Go Without Hiding Real Errors

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"log"

	_ "github.com/jackc/pgx/v5/stdlib"
)

var ErrNotFound = errors.New("user not found")

type User struct {
	ID    int64
	Name  string
	Phone sql.NullString
}

// GetUser turns "no row" into the program's own not found error and passes every other failure through.
func GetUser(ctx context.Context, db *sql.DB, id int64) (User, error) {
	var u User
	err := db.QueryRowContext(ctx, "select id, name, phone from users660 where id = $1", id).Scan(&u.ID, &u.Name, &u.Phone)
	switch {
	case errors.Is(err, sql.ErrNoRows):
		return User{}, ErrNotFound
	case err != nil:
		return User{}, fmt.Errorf("get user %d: %w", id, err)
	}
	return u, nil
}

// Exists answers a yes or no question without scanning a row at all.
func Exists(ctx context.Context, db *sql.DB, id int64) (bool, error) {
	var ok bool
	err := db.QueryRowContext(ctx, "select exists (select 1 from users660 where id = $1)", id).Scan(&ok)
	return ok, err
}

func main() {
	ctx := context.Background()
	db, err := sql.Open("pgx", "postgres://postgres:[email protected]:55432/lab?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	for _, q := range []string{
		"drop table if exists users660",
		"create table users660 (id bigint primary key, name text not null, phone text)",
		"insert into users660 values (1, 'ada', null)",
	} {
		if _, err := db.ExecContext(ctx, q); err != nil {
			log.Fatal(err)
		}
	}

	u, err := GetUser(ctx, db, 1)
	fmt.Printf("1. existing user: %+v, phone valid=%v, err=%v\n", u, u.Phone.Valid, err)
	_, err = GetUser(ctx, db, 2)
	fmt.Printf("2. missing user: err=%v, ErrNotFound=%v, sql.ErrNoRows=%v\n", err, errors.Is(err, ErrNotFound), errors.Is(err, sql.ErrNoRows))

	// A real failure must not look like "not found".
	_, err = GetUser(ctx, db, 3)
	_, _ = db.ExecContext(ctx, "alter table users660 rename column name to full_name")
	_, err = GetUser(ctx, db, 1)
	fmt.Printf("3. a broken query (renamed column): err=%v\n   mistaken for not found: %v\n", err, errors.Is(err, ErrNotFound))
	_, _ = db.ExecContext(ctx, "alter table users660 rename column full_name to name")

	// QueryRow reports ErrNoRows from Scan, never from the call itself.
	row := db.QueryRowContext(ctx, "select id from users660 where id = 99")
	fmt.Println("4. QueryRow itself returned a row object; the error appears at Scan:", row.Scan(new(int64)))

	// Query returns an empty set, not an error.
	rows, err := db.QueryContext(ctx, "select id from users660 where id = 99")
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("5. Query with no matching rows: err", err, "| Next:", rows.Next(), "| Err:", rows.Err())
	rows.Close()

	// A yes or no question: exists, no error to interpret.
	e1, _ := Exists(ctx, db, 1)
	e2, _ := Exists(ctx, db, 2)
	fmt.Println("6. Exists(1):", e1, "| Exists(2):", e2)

	// An aggregate always returns a row, so ErrNoRows cannot happen; the value is NULL when nothing matched.
	var total sql.NullInt64
	err = db.QueryRowContext(ctx, "select sum(id) from users660 where id > 100").Scan(&total)
	fmt.Println("7. sum over no rows: err", err, "| valid:", total.Valid)
}

1. existing user: {ID:1 Name:ada Phone:{String: Valid:false}}, phone valid=false, err=<nil>
2. missing user: err=user not found, ErrNotFound=true, sql.ErrNoRows=false
3. a broken query (renamed column): err=get user 1: ERROR: column "name" does not exist (SQLSTATE 42703)
   mistaken for not found: false
4. QueryRow itself returned a row object; the error appears at Scan: sql: no rows in result set
5. Query with no matching rows: err <nil> | Next: false | Err: <nil>
6. Exists(1): true | Exists(2): false
7. sum over no rows: err <nil> | valid: false

switch {
case errors.Is(err, sql.ErrNoRows):
    return User{}, ErrNotFound
case err != nil:
    return User{}, fmt.Errorf("get user %d: %w", id, err)
}

if err != nil {
    return User{}, ErrNotFound
}