Gorm How to handle conflict clause in association upsert?

Context and question

I'm using Gorm v1 in my project and I'm currently migrating from the v1 to the v2 (v1.20.2). I've migrated my code quite smoothly so far, but now I'm struggling with associations.

Here is my use case:

package main

import (
    "gorm.io/driver/mysql"
    "gorm.io/gorm" // v1.20.2
)

type User struct {
    ID        string    `gorm:"primaryKey"`
    Emails    []Email   `gorm:"foreignKey:User"`
}

type Email struct {
    ID       string `gorm:"primaryKey`
    User     string
    Address  string `gorm:"not null;uniqueIndex"`
}

func main() {
    entity := User{
        Emails: []Email{
            Email{Address: "foo@bar"},
        },
    }
    db, _ = gorm.Open(
        mysql.New(mysql.Config{DSN: "<my.db.info>"}),
        &gorm.Config{},
    )
    db.Debug().Create(&entity)
}

When I'm creating a new User{}, with 1 Email{}, I'm expecting the upsert query to add 1 user and 1 emails in my database, which is actually done as expected. And since I've defined the Address field of my Email{} model as a unique index, I can't have 2 emails with the same address. Hence, running the Create() method twice with this same entity as parameter needs to return an error.

But Gorm v2 adds a ON DUPLICATE KEY UPDATE clause while doing an upsert on associations (in my case that's a has-many association, but I've noticed the same behaviour on other kind of associations). Therefore, where I'm running the Create() method twice with this same entity as parameter, a new user is created and the existing emails are associated to this new user.

INSERT INTO `emails` (`id`,`user`,`address``) VALUES ('email_1','user_1','foo@bar') ON DUPLICATE KEY UPDATE `user`=VALUES(`user`)
INSERT INTO `users` (`id`) VALUES ('user_1') ON DUPLICATE KEY UPDATE `id`=`id`

Here is my question: how can I get an error while creating an entity that have conflicting associations instead of resolving the conflict by updating the foreign key?

Documents and experimentations

According to the documentation:

GORM V2 will use upsert to save associations when creating/updating a record

As described in the upsert section, I tried to used clause.OnConflict:

db.Debug().Clauses(clause.OnConflict{DoNothing: true}).Create(&entity)

But I got the same results:

INSERT INTO `emails` (`id`,`user`,`address``) VALUES ('email_1','user_1','foo@bar') ON DUPLICATE KEY UPDATE `user`=VALUES(`user`)
INSERT INTO `users` (`id`) VALUES ('user_1') ON DUPLICATE KEY UPDATE `id`=`id`

I don't know if that's a bug, or I'm not using it correctly.

Should I use .Omit() method described in this documentation instead? 1. Begin new transaction 2. Create the user tx.Omit("Emails").Create(&entity) 3. Manually set the user ID in my emails 4. Bulk insert my emails 5. Commit the transaction

Thank you.

Comment From: jinzhu

how can I get an error while creating an entity that have conflicting associations instead of resolving the conflict by updating the foreign key?

Which is not supported and I think most cases, this is the correct behaviour or you should remove the unique check or check the data issue before the inserting...

---

But for your case, you could disable the Emails field's create permission in your user struct definition and create them in user's after create hooks.

Comment From: phanirithvij

@jinzhu I've got a similar issue

The issue is in the insert statement the On Conflict clause is generated with only a single primaryKey

// Main struct
type User struct {
    ID string `gorm:"primaryKey;not null"`
    Buckets []*xfs.Bucket `gorm:"polymorphic:Entity"`
    EntityType string `gorm:"-"`
        ...
}

// Association struct
type Bucket struct {
    CreatedAt   time.Time
    UpdatedAt   time.Time
    DeletedAt   gorm.DeletedAt `gorm:"index"`
    XFileSystem `gorm:"embedded"`
        // Have a composite primary key with two fields ID, EntityID
    ID         string `gorm:"uniqueIndex:buk_ent_idx;primaryKey"`
    EntityID   string `gorm:"uniqueIndex:buk_ent_idx;primaryKey"`
    EntityType string `gorm:"uniqueIndex:buk_ent_idx;"`
}

But the generated SQL is this

When I'm creating a main object db.Create(&BaseEntity{}) with three elements in the associate array

INSERT INTO "buckets" ("created_at","updated_at","deleted_at","id","entity_id","entity_type")
VALUES
(........., 'default','phano1605697835','users') , 
(........,'default-1','phano1605697835','users'),
(........,'default-2','phano1605697835','users')
ON CONFLICT ("id") // here
DO UPDATE SET "entity_type"="excluded"."entity_type","entity_id"="excluded"."entity_id"

It says ON CONFLICT ("id") but shouldn't it say ON CONFLICT ("id", "entity_id")? Because ID, EntityID together is the primary key

Comment From: phanirithvij

I was able to find a work around for now

func (b *Bucket) BeforeCreate(tx *gorm.DB) (err error) {
    cols := []clause.Column{}
    colsNames := []string{}
    for _, field := range tx.Statement.Schema.PrimaryFields {
        cols = append(cols, clause.Column{Name: field.DBName})
        colsNames = append(colsNames, field.DBName)
    }
    tx.Statement.AddClause(clause.OnConflict{
        Columns:   cols,
        // DoUpdates: clause.AssignmentColumns(colsNames),
        DoNothing: true,
    })
    return nil
}

Thankfully Hooks work for Associations (Which seems obvious now after many hours of me hacking around)

Comment From: geoff-maddock

I found this issue in trying to investigate a similar problem - but found that even though the BeforeCreate is called, when the entity that has the many-to-many relationship is persisted, the DoNothing clauses aren't applied. Did you have to do anything else to get the hook to work?

Comment From: t2wu

I filed a PR via Playground and created an issue.