gorm exec return ID from insert transaction with raw sql

Question

I am using GORM to run SQL queries. I am trying to Exec an INSERT statement as a TRANSACTION because I need to be able to return the latest inserted ID.

Here is a code snippet:

query := `
        START TRANSACTION;
            INSERT INTO something (a, b, c)
            VALUES (@arg1, @arg2, @arg3);
            SELECT LAST_INSERT_ID();
        COMMIT;`
    result := DB.Exec(
        query,
        sql.Named("arg1", "aaa"),
        sql.Named("arg2", "bbb"),
        sql.Named("arg3", "ccc"),
    )

I've read through the GORM transactions docs, but the examples are wildly different than my use case.

My goal is to create an INSERT statement and finally return me the latest ID from that create. Using MySQL.

Any ideas?

Answer 1

While I don't know your exact usecase, it seems that here you're not really taking full advantage of GORM, and could benefit by using its methods.

type Something struct {
    ID int
    A  string
    B  string
}

something := Something{
    A: "Foo",
    B: "Bar",
}

db.Transaction(func(tx *gorm.DB) error {
    // save your item in the transaction (use 'tx' from this point, not 'db')
    if err := tx.Create(&something).Error; err != nil {
        fmt.Println(err.Error())
        // returning any error will rollback
        return err
    }

    // do anything else within the transaction...

    // return nil will commit the whole transaction
    return nil
})

fmt.Printf("%+v\n", something)

Afterwards, the original something variable will contain the ID that was just created:

{
  "ID": 26,
  "A": "Foo",
  "B": "Bar"
}