DEV Community

loading...

Custom database errors

Matija Krajnik
QA automation engineer experienced in writing GUI automated tests for Web and Mobile apps, Web REST API automated tests, load/performance tests, and developing custom testing tools from scratch.
Originally published at letscode.blog Updated on ・2 min read

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:

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

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

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:

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"
}
Enter fullscreen mode Exit fullscreen mode

With that in place we can call this dbError() function from internal/store/users.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
}
Enter fullscreen mode Exit fullscreen mode

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

User friendly database error

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 for my solution.

Discussion (0)