Gorm: Cannot catch duplicate primary key error for join table

[hit9]

Hello, I cannot catch the UNIQUE constraint failure error.

Test code is here:

package main

import (
        "github.com/jinzhu/gorm"
        _ "github.com/mattn/go-sqlite3"
)

type User struct {
        ID       int       `gorm:"primary_key"`
        Projects []Project `gorm:"many2many:project_users"`
}

type Project struct {
        ID    int    `gorm:"primary_key"`
        Users []User `gorm:"many2many:project_users"`
}

func main() {
        db, _ := gorm.Open("sqlite3", "test.db")
        defer db.Close()
        db.AutoMigrate(&User{}, &Project{})
        proj := Project{ID: 2}
        err := db.Debug().Model(&proj).Association("Users").Append(User{ID: 1}).Error
        if err != nil {
                // Should ok.
                panic(err)
        }
        // Add again. 
        err = db.Debug().Model(&proj).Association("Users").Append(User{ID: 1}).Error
        if err != nil {
                // Should UNIQUE constraint failed, but not
                panic(err)
        }
}

The SQL from gorm debug is:

INSERT INTO "project_users" ("project_id","user_id") SELECT '2','1'  WHERE NOT EXISTS (SELECT * FROM "project_users" WHERE "project_id" = '2' A
ND "user_id" = '1')

But why gorm adds the where not exists to the insert statement? Thanks

[jinzhu]

Hi @hit9

Why error? is it better to be just works?

---

Btw, if you really want to the error, you could overwrite the join table handler, refer https://github.com/jinzhu/gorm/blob/master/join_table_handler.go#L105-L114

[hit9]

Thank you. I just wanna to get the error if some duplicated record is going to insert.

Now I am using a select query at first and then do the insert. But this is not atomic.

[jinzhu]

For the join table, gorm is not going to insert duplicated foreign keys, for model, it would be good to use unique index to ensure no duplicated records.

[hit9]

Hmm.. But how do I validate the condition "Cannot add duplicate users to one project? "

[nkumar15]

Did anybody find how we can detect this error. Similar to above I am trying to insert a record which already exist which cause gorm to generate error "UNIQUE constraint failed".

How do I check this situation, any error code for this in gorm?

[cad]

@nkumar15 You can check in the end with db.NewRecord(&record) if it's still true after creating the record, that mean's it's somehow can't be created.

[sredxny]

@cad yes and no. Right now am dealing with the same thing, and the creation of a new register can be avoided by so many things, not only duplicated registers. And for what I understand @hit9 and me want to get the message that comes from the database in a better way and not infer what is happening

[khier996]

If I understand the question correctly, you just want to catch errors when you save an object to a database. I used db.Create(&record) method:

if dbObj := db.Create(&record); dbObj.Error != nil {
      c.JSON(http.StatusBadRequest, gin.H{"error": dbObj.Error.Error()})
} else {
      c.JSON(200, track)
}

[cmorent]

Any update here? @khier996 your example works fine but when you want to separate db/api logic, you want to know what kind of error you are dealing with and return a custom HTTP code depending on the error (e.g. 409 in case of duplicate and 400 for invalid SQL or whatever).

[iqDF]

These are the only errors I can catch in gorm:

// ErrRecordNotFound returns a "record not found error". Occurs only when attempting to query the database with a struct; querying with a slice won't return this error
ErrRecordNotFound = errors.New("record not found")

// ErrInvalidSQL occurs when you attempt a query with invalid SQL
ErrInvalidSQL = errors.New("invalid SQL")

// ErrInvalidTransaction occurs when you are trying to `Commit` or `Rollback`
ErrInvalidTransaction = errors.New("no valid transaction")

// ErrCantStartTransaction can't start transaction when you are trying to start one with `Begin`
ErrCantStartTransaction = errors.New("can't start transaction")

// ErrUnaddressable unaddressable value
ErrUnaddressable = errors.New("using unaddressable value")

You can import these error from "github.com/jinzhu/gorm/errors"