Gorm: Batch Insert - db.Create([]interface{})

[nitishnference]

Throwing error http: panic serving [::1]:57534: reflect: call of reflect.Value.Interface on zero Value

[charleslxh]

+1

[charleslxh]

@nitishnference

Use this code:

package app

import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jinzhu/gorm"
    "github.com/sirupsen/logrus"
    "test/configs"
    "test/lib/logger"
    "strings"
)

type OwnDb struct {
    *gorm.DB
}

var mysqlConn *OwnDb

func (db *OwnDb) BatchInsert(objArr []interface{}) (int64, error) {
    // If there is no data, nothing to do.
    if len(objArr) == 0 {
        return 0, nil
    }

    mainObj := objArr[0]
    mainScope := db.NewScope(mainObj)
    mainFields := mainScope.Fields()
    quoted := make([]string, 0, len(mainFields))
    for i := range mainFields {
        // If primary key has blank value (0 for int, "" for string, nil for interface ...), skip it.
        // If field is ignore field, skip it.
        if (mainFields[i].IsPrimaryKey && mainFields[i].IsBlank) || (mainFields[i].IsIgnored) {
            continue
        }
        quoted = append(quoted, mainScope.Quote(mainFields[i].DBName))
    }

    placeholdersArr := make([]string, 0, len(objArr))

    for _, obj := range objArr {
        scope := db.NewScope(obj)
        fields := scope.Fields()
        placeholders := make([]string, 0, len(fields))
        for i := range fields {
            if (fields[i].IsPrimaryKey && fields[i].IsBlank) || (fields[i].IsIgnored) {
                continue
            }
            var vars  interface{}
            if (fields[i].Name == "CreatedAt" || fields[i].Name == "UpdatedAt") && fields[i].IsBlank {
                vars = gorm.NowFunc()
            } else {
                vars = fields[i].Field.Interface()
            }
            placeholders = append(placeholders, scope.AddToVars(vars))
        }
        placeholdersStr := "(" + strings.Join(placeholders, ", ") + ")"
        placeholdersArr = append(placeholdersArr, placeholdersStr)
        // add real variables for the replacement of placeholders' '?' letter later.
        mainScope.SQLVars = append(mainScope.SQLVars, scope.SQLVars...)
    }

    mainScope.Raw(fmt.Sprintf("INSERT INTO %s (%s) VALUES %s",
        mainScope.QuotedTableName(),
        strings.Join(quoted, ", "),
        strings.Join(placeholdersArr, ", "),
    ))

    //Execute and Log
    if err :=mainScope.Exec().DB().Error;err != nil {
        return 0, err
    }

    return mainScope.DB().RowsAffected, nil
}

func OpenMysql() {
    if mysqlConn != nil {
        return
    }

    mysqlConfig := configs.CONFIGS.Mysql
    if !mysqlConfig.Enable {
        return
    }

    dsn := fmt.Sprintf(
        "%v:%v@tcp(%v:%v)/%v?charset=utf8&parseTime=True&loc=Local",
        mysqlConfig.User, mysqlConfig.Pass,
        mysqlConfig.Host, mysqlConfig.Port, mysqlConfig.Database,
    );

    conn, err := gorm.Open("mysql", dsn)
    if err != nil {
        logger.GetLogger("app.mysql").WithFields(logrus.Fields{
            "err": err.Error(),
        }).Fatal("connected to mysql database failed")
    }

    if configs.CONFIGS.DEBUG {
        conn.LogMode(true)
    }

    logger.GetLogger("app.mysql").Info("mysql connected")

    mysqlConn = &OwnDb{DB: conn}
}

func CloseMysql() {
    if mysqlConn != nil {
        err := mysqlConn.Close()
        mysqlConn = nil
        if err != nil {
            logger.GetLogger("app.mysql").WithFields(logrus.Fields{
                "err": err.Error(),
            }).Warn("close mysql connection failed")
        }
    }
}

func GetMysql() *OwnDb {
    if mysqlConn == nil {
        OpenMysql()
    }

    return mysqlConn
}

[siosphere]

I'm running into this now. Here are some code snippets. Starting with what doesn't work:

func CreateNodes(ctx context.Context, nodes []*WorkflowNode) error {
    db, err := db.Default(ctx)
    if err != nil {
        return err
    }

    if err = db.Create(&nodes).Error; err != nil {
        return errors.Wrap(err, "failed to create nodes")
    }

    return nil
}

Fails with reflect: call of reflect.Value.Interface on zero Value
I've tried passing the slice by reference, not by reference, having the items in the slice not be pointers, no combination makes that error go away.
Switching it to loop through all items 1 by 1 and do a single insert for each, works:

func CreateNodes(ctx context.Context, nodes []*WorkflowNode) error {
    db, err := db.Default(ctx)
    if err != nil {
        return err
    }

    for _, node := range nodes {
        if err = db.Create(&node).Error; err != nil {
            return errors.Wrap(err, "failed to create node")
        }
    }

    return nil
}

Used structs and types:

type NodeType string
type Model struct {
    ID        uint       `gorm:"primary_key" json:"id"`
    CreatedAt time.Time  `json:"created_at"`
    UpdatedAt time.Time  `json:"updated_at"`
    DeletedAt *time.Time `sql:"index" json:"deleted_at,omitempty"`
}

type WorkflowNode struct {
    Model
    WorkflowID uint     `json:"workflow_id"`
    Type       NodeType `json:"type"`
    UUID       string   `json:"uuid"`
    Params     db.JSON  `sql:"type:json" json:"params"`
}

[mattstrayer]

Also experiencing this. Same use-case as @siosphere . Anyone find a solution yet?

[jinzhu]

Need to use V2. https://github.com/go-gorm/gorm/wiki/GORM-V2-Release-Note-Draft

[mattstrayer]

I am using v2 @jinzhu

[jinzhu]

This should be covered by tests, could you check out https://github.com/go-gorm/gorm/blob/e77e7bb842499e58a9f4b53631bb3ce9c72d6d5a/tests/create_test.go#L65-L108 and try to break the tests with your situations?

[mattstrayer]

@jinzhu Thanks for the help!

Eventually figured out my error.
I was using an array of model pointers(models []*MyORMModel), instead of an array of models models []MyORMModel. Now I can db.Create(&models) without issue.