How to create mysql database record dynamically

Question

How would it be possible to create a database record (in this case mysql) without literally specifying each variable to be passed in to the model, on or at the model (see following example). With having to create dozens of models, I am trying to avoid having to specify these vars manually with each and every model. I can dynamically collect this list on the controller and am hoping there is some way to just pass one object from the controller to the model (in this failed experiment: res []string).

// controller
_, err := policy.CreateItem(c.DB, res)

// model
func CreateItem(db Connection, res []string) (sql.Result, error) {
// res = ParentID, Title, Description, Sort, Date, StatusID, UserID
tfcim := format.TitlesForCreateInputModel(ItemMin{}) // parent_id, title, description, sort, date, status_id, user_id
phfcm := format.PlaceHoldersForCreateModel(ItemMin{}) // ?,?,?,?,?,?,?
result, err := db.Exec(fmt.Sprintf(`
        INSERT INTO %v
        (`+tfcim+`)
        VALUES
        (`+phfcm+`)
        `, table),
    strings.Join(res, ", ")) // <----------------- How can magic happen here?
return result, err
}

And logically, the output is: flight.go:138: sql: expected 7 arguments, got 1

To note, ItemMin{} is the table struct returning the table keys using 'reflect' in a package named format.

Any functional method, even an entirely different approach (except using an ORM/GORM) would be acceptable. Thanks in advance!

UPDATE: Complete working solution:

// controller
s := make([]interface{}, len(res))
for i, v := range res {
    s[i] = v
}
_, err := policy.CreateItem(c.DB, s)

// model
func CreateItem(db Connection, res []interface{}) (sql.Result, error) {
    tfcim := format.TitlesForCreateInputModel(ItemMin{})  // parent_id, title, description, sort, date, status_id, user_id
    phfcm := format.PlaceHoldersForCreateModel(ItemMin{}) // ?,?,?,?,?,?,?
    query := fmt.Sprintf("INSERT INTO %v (%v) VALUES (%v)", table, tfcim, phfcm)
    result, err := db.Exec(query, res...)
    return result, err
 }

Zombo · Accepted Answer · 2020-12-17 05:26:52Z

1

As the Exec function has this signature:

Exec(query string, args ...interface{})

You can use this syntax:

query := fmt.Sprintf("INSERT INTO %v (%v) VALUES (%v)", table, tfcim, phfcm)
result, err := db.Exec(query, res...)

answered Dec 17, 2020 at 5:26

Zombo

1

Sign up to request clarification or add additional context in comments.

4 Comments

iamtoc Over a year ago

I like where this headed!, however, in using your example exactly... it won't compile: "cannot use res (variable of type []string) as []interface{} value in argument to db.Exec". I'm presuming there is a simple fix? I'm presuming the three dots after res are needed? (not sure what those mean)

iamtoc Over a year ago

I removed the dots after res and got it to compile, but errored at runtime: flight.go:138: sql: converting argument $1 type: unsupported type []string, a slice of string

Zombo Over a year ago

@iamtoc golang.org/doc/faq#convert_slice_of_interface

iamtoc Over a year ago

That help doc completely solved the bug, thank you! After the modification on the controller, the model now receives res as 'res []interface{}' rather than res []string.

Collectives™ on Stack Overflow

How to create mysql database record dynamically

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related