Custom database errors
Tags: go, postgres
Published: 2021-06-24 18:11:10

Custom validation errors are added in previous chapter, but what about database errors? If you try to create account with already existing username, you will get error `ERROR #23505 duplicate key value violates unique constraint "users_username_key"`. Unfortunately, there is no validator involved here and `pg` module returns most of the errors as `map[byte]string` so this can be little tricky.

One way to do it is manually check for every error case by doing database query. For example, to check if user with given username already exists in database we could do this before trying to create new user:

```go
func AddUser(user *User) error {
  err = db.Model(user).Where("username = ?", user.Username).Select()
  if err != nil {
    return errors.New("Username already exists.")
  }
  ...
}
```

Problem is that this can become quite tedious. It needs to be done for every error case in every function which communicates with database. And on top of that, we are unnecessarily multiplying database queries. In this simple case there will be now 2 database queries instead of 1 for every successful user creation. There is another way, and that is to try to do query once, and then parse error if it happens. And that is tricky part, since we need to handle every error type using regex to extract relevant data needed to create more user friendly custom error messages. So let's start. As mentioned, `pg` errors are mostly of type `map[byte]string`, so for this particular error when you try to create user account with already existing username, you will get map on picture below:

![go-pg errors map](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zzowdnkk4kc3ysma2d7n.png)

To extract relevant data, we will use fields `82 and 110`. Error type will be read from field `82` and we will extract column name from field `110`. Let's add these functions to `internal/store/store.go`:

```go
func dbError(_err interface{}) error {
  if _err == nil {
    return nil
  }
  switch _err.(type) {
  case pg.Error:
    err := _err.(pg.Error)
    switch err.Field(82) {
    case "_bt_check_unique":
      return errors.New(extractColumnName(err.Field(110)) + " already exists.")
    }
  case error:
    err := _err.(error)
    switch err.Error() {
    case "pg: no rows in result set":
      return errors.New("Not found.")
    }
    return err
  }
  return errors.New(fmt.Sprint(_err))
}

func extractColumnName(text string) string {
  reg := regexp.MustCompile(`.+_(.+)_.+`)
  if reg.MatchString(text) {
    return strings.Title(reg.FindStringSubmatch(text)[1])
  }
  return "Unknown"
}
```

With that in place we can call this `dbError()` function from `internal/store/users.go`:

```go
func AddUser(user *User) error {
  ...

  _, err = db.Model(user).Returning("*").Insert()
  if err != nil {
    log.Error().Err(err).Msg("Error inserting new user")
    return dbError(err)
  }
  return nil
}
```

If we now try to create new account with already existing username, we will get nice error message:

![User friendly database error](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5zpeuthkb9z5hc0ezyy1.png)

Of course, this is only the beginning. You need to handle every type of error separately, but that handling is now in one place, and there is no need for additional queries. You can try handle rest of the error cases you wish to handle, or check [RGB GitHub repo](https://github.com/matijakrajnik/rgb) for my solution.