Search code examples
postgresqlgogo-gorm

Get existing and inserted IDs in upsert operation (db.Clauses clause.OnConflict)

I have a scenario where I need to insert into a table some array of data, In which case if the combination of name and version already exists (composite unique constrain), I need to get those IDs, else get inserted IDs, if both case exist get inserted and existing ids

Models and code I tried are given below:

Model Dependency

type Dependency struct {
    gorm.Model
    ID      string `gorm:"primaryKey; not null"`
    Name    string `gorm:"not null; UniqueIndex:idx_name_version"`
    Version string `gorm:"not null; UniqueIndex:idx_name_version"`
}

go code with gorm query to insert dependencies

var saveDependencyData []models.Dependency

// Dependecies are read form api input 
// [
//   {
//       "name": "node",
//       "version": "16.0.0"
//   },
//   {
//       "name": "node",
//       "version": "18.0.0"
//   }
// ]

for _, dep := range Dependecies {
    saveDependencyData = append(saveDependencyData, models.Dependency{
        ID:      nanoid.New(),
        Name:    dep.Name,
        Version: dep.Version,
    })
}

res :=  db.Clauses(clause.OnConflict{
            Columns:   []clause.Column{{Name: "name"}, {Name: "version"}},
            DoUpdates: clause.AssignmentColumns([]string{"name"}),
        }).Create(saveDependencyData)

gorm query output

INSERT INTO "dependencies" ("id","created_at","updated_at","deleted_at","name","version") VALUES ('QanL-nfNFrOGdxG2iXdoQ','2022-10-06 19:21:13.079','2022-10-06 19:21:13.079',NULL,'react','16.0.0'),('Yw1YyQ-aBqrQtwZ72GNtB','2022-10-06 19:21:13.079','2022-10-06 19:21:13.079',NULL,'react','18.0.0') ON CONFLICT ("name","version") DO UPDATE SET "name"="excluded"."name" RETURNING "id"

This query returns the list of ids I needed, but could not find a way to retrieve that.

using Scan() gets all the datas in that table.

Either you can help with a way to retrieve the returning IDs form the above GORM db.Clauses(), Or any other optimized method to get those (inserted & existing) ids with a upsert query.

Solution

  • As indicated in the comments: Several functions of GORM expect a pointer as argument and will update the variable with information.

    That's obviously the case for all functions whose main purpose is to retrieve information (First, Find, ..., cf. https://gorm.io/docs/query.html).

    But it's also the case for functions that modify data like

    So, the solution in this case is to pass Create(&saveDependencyData) instead of Create(saveDependencyData).

    The up-to-date information corresponding to the database will then be available in the saveDependencyData after the call.