Handling PostgreSQL Errors in Go `sqlx` and `pq` packages

For a while now, I've been programming in Go, and oftentimes I've confused about how to handle errors from database drivers. Hopefully, by writing this, it would be helpful not only for me, but also for others. Note that the database driver I will be using is github.com/lib/pq, other drivers might have a different way of handling errors. I'll also be using sqlx library.

Reminder: errors.Is method to compare error

To compare an error, it's possible to use the errors.Is method, instead of manually comparing them with == operator.

Built-in database/sql.ErrNoRows error for empty row

Say, you have an API call expecting to return exactly one item.

func GetOne(db *sqlx.DB, id string) err {
    var result User
    err := db.Get(&result, `select * from users where id = $1 limit 1;`, id)
    // TODO: handle errors
}

According to sqlx's documentation, the method sqlx.Get calls the Go's built-in sql.Scan method to then be scanned. Regardless of the database drivers, if database responded with no rows at all, the built-in sql.Scan will returns an error sql.ErrNoRows.

This is useful for an API call that expects at least or exactly one item to be returned, so it can be handled like this:

func Handler(w http.ResponseWriter, r *http.Request) {
    id := r.PathValue("userID")

    var result User
    err := db.Get(&result, `select * from users where id = $1 limit 1;`, id)
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            slog.Error("user not found")
            w.WriteHeader(http.StatusNotFound)
            return
        }
        // TODO: handle other errors
    }
    // TODO: else, return user
}

Errors from the driver

The errors that will be returned by sqlx at first is the built-in error interface. To obtain more detailed error, we must cast the err value into github.com/lib/pq.Error type, like this:

var ErrDuplicateUsername = errors.New("username should be unique")
func Insert(db *sqlx.DB, id string) err {
    var result User
    _, err := db.Exec(&result, `insert into users (username) values ($1)`, id)
    if err != nil {
        //                 ⬇️ do not forget the `*`, as it is an error interface
        if err, ok := err.(*pq.Error); ok {
            switch err.Code {
            case "23505": // code 23505 is a unique violation
                return ErrDuplicateUsername
            // TODO: handle other error codes
            }
        }
        // TODO: else handle other error types
    }
    return nil
}

If you wish to handle other types of errors, you might first want to consult the error codes section from the official manual book of PostgreSQL. After that, take a look at the github.com/lib/pq.Error type.