The Wizard's Guide to GORM and PostgreSQL: Upsert with Ease

Roman — Wed, 05 Jun 2024 11:13:45 +0000

Interesting story. You have two PostgreSQL tables related to each other in a one-to-many relationship.

type Availability struct {
    ID        int64    `gorm:"column:id;primaryKey"`
    ProductID int64    `gorm:"column:product_id"`
    Enabled   bool     `gorm:"column:enabled;not null"`
    Dates     []*Dates `gorm:"foreignKey:AvailabilityID"`
}

type Dates struct {
    ID             int64  `gorm:"column:id;primaryKey"`
    AvailabilityID int64  `gorm:"column:availability_id;foreignKey:Availability" json:"availability_id"`
    Date           string `gorm:"column:date"`
}

And such an index product_availability_dates_uindex(AvailabilityID, date) in the Dates table. You need to update the Enabled field in the Availability table using GORM. If such a record does not exist, create it.

func (that *AvailabilityRepo) Upsert(ctx context.Context, pa []*model.ProductAvailability) error {
    for _, a := range pa {
        tx := that.db.WithContext(ctx).Model(&model.ProductAvailability{}).
            Where("product_id = ?", a.ProductID).
            Update("enabled = ?", a.Enabled)
        if tx.Error != nil {
            return fmt.Errorf("%w: failed to update product availability", tx.Error)
        }

        if tx.RowsAffected == 0 {
            if err := tx.Create(&a).Error; err != nil {
                return fmt.Errorf("%w: failed to create product availability", err)
            }
        }

        return nil
    }
}

Problem 1. We need to return the ID if the field is updated. Currently, the ID is not being returned. We try to pass the instance of the object directly in the Model. This method seems to work, but not for me.

func (that *AvailabilityRepo) Upsert(ctx context.Context, pa []*model.ProductAvailability) error {
    for _, a := range pa {
        tx := that.db.WithContext(ctx).Model(&a).
            Where("product_id = ?", a.ProductID).
            Update("enabled = ?", a.Enabled)
        if tx.Error != nil {
            return fmt.Errorf("%w: failed to update product availability", tx.Error)
        }

        if tx.RowsAffected == 0 {
            if err := tx.Create(&a).Error; err != nil {
                return fmt.Errorf("%w: failed to create product availability", err)
            }
        }

        return nil
    }
}

Why the above update did not work for me. The first reason, it makes me want to wash my hands after such an approach. The second reason is that the tables are connected through a unique index. And GORM, by some internal magic, returns an error because the uniqueness index is triggered. You didn't touch the dates field, but the error will be related to this index.

How I solved the problem for myself:

func (that *AvailabilityRepo) Upsert(ctx context.Context, pa []*model.ProductAvailability) error {
    for _, a := range pa {
        idContainer := model.ProductAvailability{}
        tx := updatesBuilder(that.db.WithContext(ctx).Model(&idContainer).
            Clauses(clause.Returning{Columns: []clause.Column{
                {Name: "id"},
            }}).
            Where("product_id = ?", a.ProductID), deprecate, a)
        if tx.Error != nil {
            return fmt.Errorf("%w: failed to update product availability", tx.Error)
        }

        if tx.RowsAffected == 0 {
            if err := tx.Create(&a).Error; err != nil {
                return fmt.Errorf("%w: failed to create product availability", err)
            }
        }

        if a.ID == 0 {
            a.ID = idContainer.ID
        }
    }

    return nil
}

DEV Community: Roman

The Wizard's Guide to GORM and PostgreSQL: Upsert with Ease