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?

I don't have experience with GORM but with plain SQL statements you can just get use `result.LastInsertId()`. See https://github.com/hectorcorrea/hectorcorrea.com/blob/main/models/blog.go#L115-L130 for an example. — Hector Correa, Mar 10 '21 at 19:43
why dont you use query builder instead of raw? examle: https://stackoverflow.com/a/28776926/9984295 — Mohiuddin Khan, Mar 15 '21 at 12:43

score 1 · Answer 1 · answered Apr 13 '21 at 16:59

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"
}

score 0 · Answer 2 · answered Jun 18 '22 at 05:10

just do this example

     var ID int64
     tx.Raw("INSERT INTO yourTable (yourColumn) VALUES ('testInsertValue') RETURNING 
     id").Scan(&ID)

I use tx in assumption you have set your transaction into tx, you can do the transaction in many ways. And for the return value, you can use your model's struct instead of just the var ID like the example.

thank you.

gorm exec return ID from insert transaction with raw sql

2 Answers2