gorm: How to check if error type is DUPLICATE KEY

If you want to check type of error, just do it.

if err := db.Create(&user).Error; err != nil {

  if errors.Is(err, gorm.ErrRecordNotFound) {
    fmt.Println(err.Error())
  }

  return

}

I think better would be easier to have such common error in predefined errors emitted by gorm.

use mysql as example, check it like:

var mysqlErr *mysql.MySQLError
if errors.As(err, &mysqlErr) && mysqlErr.Number == 1062 {
}

Currently i am able to solve this issue with a hack (even i expect gorm should solve it in a better way)

import "github.com/jackc/pgconn"
....
....
if strings.Contains(err.(*pgconn.PgError).Message, "duplicate key value violates unique constraint") {}

//even you can change the condition to check on `Code` as well instead of `Message`

why are this closed?

Try this, it works for me!

first you have to defined gorm error struct, your can find the format by json.Marshal(err)

if err := db.AddUser(&r).Error; err != nil {
  b, _ := json.Marshal(err)
  fmt.Println(string(b))
}

then, marshal the error to json then unmarshal it back

type GormErr struct {
  Number  int    `json:"Number"`
  Message string `json:"Message"`
}

if err := db.AddUser(&r).Error; err != nil {
  byteErr, _ := json.Marshal(err)
  var newError GormErr
  json.Unmarshal((byteErr), &newError)
  fmt.Println(newError)
}

you can check the error type by newError.Number !

switch newErr.Number{
case 1062:
  fmt.Println("Duplicate Key !")
}

if you got duplicate entry error, format may be like this

{
  "Number": 1062,
  "Message": "Duplicate entry 'XXXXX' for key 'users.XXX'
}

error is an interface, you can get the mysqlErr by type asseration

Makes sense. FYI, for others that need this for postgres, since gorm uses pgx, you can do the following:

err := db.Create(&user).Error
if err != nil {
  var pgErr *pgconn.PgError
  if errors.As(err, &pgErr) {
    fmt.Println(pgErr.Message) 
    fmt.Println(pgErr.Code)
  }
}

you then can use this to match error codes: https://github.com/jackc/pgerrcode/blob/master/errcode.go

I created a naive function to handle errors from DB (SQLite/PostgreSQL only), and the unique violation handling part is:

import (
	"errors"

	"github.com/jackc/pgconn"
	"github.com/jackc/pgerrcode"
	"github.com/mattn/go-sqlite3"

...

func TranslateErrors(value *gorm.DB) error {
	if value.Error != nil {
		switch value.Dialector.Name() {
		case "sqlite":
			if err, ok := value.Error.(sqlite3.Error); ok {
				if err.ExtendedCode == sqlite3.ErrConstraintUnique {
					return errors.New("record already exists")

				}
				// other errors handling from sqlite3
			}
		case "postgres":
			if err, ok := value.Error.(*pgconn.PgError); ok {
				if err.Code == pgerrcode.UniqueViolation {
					return errors.New("record already exists")
				}
				// other errors handling from pgconn
			}
		}
	}
	return value.Error
}

Could still use a more universal solution to this than explicitly using psql/mysql error codes.

error is an interface, you can get the mysqlErr by type asseration

err := db.Create(&user).Error mysqlErr := err.(*mysql.MySQLError) switch mysqlErr.Number { case 1062: fmt.Println(“Duplicate Key”) }

@jinzhu How do i do that for postgres it’s not working

var psqlErr postgres.PostgreError  // ERROR postgres package has not type or method 
if errors.As(err, &psqlErr) && psqlErr.Number == 1062 {
}

Does the TranslateError flag also work when using gorm.Open() with an existing *sql.DB?

TranslateError:true works just fine when instantiating my *gorm.DB with:

	connStr := fmt.Sprintf("host=%v port=%v user=%v password=%v dbname=%v sslmode=%v", dbHost, dbPort, dbUser, dbPass, dbName, dbSSL)
	db, err := gorm.Open(postgres.Open(connStr), &gorm.Config{TranslateError: true})

But when I try to open a gorm connection with an existing sql connection (specifically during a TestMain() function that includes a dockertest setup), the errors don’t get translated (for example, the Unique Key Violation). Here is how I’m opening it in my TestMain():

	databaseUrl := fmt.Sprintf("postgres://testpguser:testpgpassword@%s/testpgdb?sslmode=disable", hostPort)

	// exponential backoff-retry, because the application in the container might not be ready to accept connections yet
	pool.MaxWait = 120 * time.Second
	var sqlDB *sql.DB
	if err = pool.Retry(func() error {
		sqlDB, err = sql.Open("postgres", databaseUrl)
		if err != nil {
			return err
		}

		return sqlDB.Ping()
	}); err != nil {
		log.Fatalf("Could not connect to docker: %s", err)
	}

	gormDB, err := gorm.Open(postgres.New(postgres.Config{Conn: sqlDB}), &gorm.Config{TranslateError: true})

use mysql as example, check it like:

var mysqlErr mysql.MySQLError
if errors.As(err, &mysqlErr) && mysqlErr.Number == 1062 {
}

It will panic ：

panic: errors: target must be a non-nil pointer

@khanakia

Hope it helps:

if results :=  db.Create(&account); results.Error != nil {
			if pgError := results.Error.(*pgconn.PgError); errors.Is(results.Error, pgError) {
				var httpStatus int
				switch pgError.Code {
				case "23505":
					httpStatus = 409
				}
				
				c.Status(httpStatus)
				
				return nil
			}

			c.Status(fiber.StatusBadRequest)
			return nil
		}

Sqlite3

e := errors.New("UNIQUE constraint failed")
if errors.As(err, &e) {}

+1 for an elegant way to handle this that doesn’t break multi-database support for the official options; i.e. a GORM error var. I’ll submit a PR if I can get work approval.

@jinzhu Any update on this ? I have check everywhere there is no solution i found for this.

Just in case if anyone else face with the same issue - here is what works for me (SQLite):

// setup gorm
cfg := &gorm.Config{
    TranslateError: true,
}

// ...

// try to create item doesn't meet unique constraint
result := db.Create(&myEntity)
if result.Error == gorm.ErrDuplicatedKey {
    // ...
}

For Postgres

import (
	"github.com/jackc/pgerrcode"
	"github.com/jackc/pgx/v5/pgconn"
)

...

_, err := s.db.ExecContext(ctx, query, args...)
if err != nil {
  var pgErr *pgconn.PgError
  if errors.As(err, &pgErr) && pgErr.Code == pgerrcode.UniqueViolation {
	  // duplicate key error
  }

  return err
}

...

Use this function to check for errors in SQLite 3

func gormErrUniqueViolation(err error) bool {
	e := errors.New("UNIQUE constraint failed")
	return !errors.Is(err, e)
}

Example after the INSERT request:

// ...
err = db.Create(&data).Error
	if err != nil { // Check SQL Errors
		if gormErrUniqueViolation(err) { // UNIQUE constraint failed
                        // ...
			return
		}
		// ...
	}
// ...

@silvioprog you forgot to mention which package you’re using to get the error constants.

https://github.com/jackc/pgerrcode

if you use postgresql you can check the error code whether equals to pgerrcode.UniqueViolation. before you check the error code, you should convert error type to *pgconn.PgError first, it works.

pgx error code reference

any future plan to create predefined error for this case ?

Also curious about this. Seems like a pretty common error case that developers might want to handle gracefully

any future plan to create predefined error for this case ?