Need the ID of the current row inserted within a transaction

Question

Within a transaction I'm inserting a row.

How can I access and return the ID of the inserted row. As you can see in the code below(See under comment // Return last Inserted ID.) I tried to use the LastInsertedId() function, but it gives me an error back.

Btw, I'm using Postgres.

What am I missing here? Thx!

/**
 * Creates order and returns its ID.
 */
func createOrder(w http.ResponseWriter, r *http.Request) (orderID int) {

    // Begin.
    tx, err := db.Begin()
    if err != nil {
        log.Fatal(err)
    }

    // Db query.
    sqlQuery := `INSERT INTO ORDER_CUSTOMER
        (CUSTOMER_ID)
        VALUES ($1)
        RETURNING ID`

    // Prepare.
    stmt, err := tx.Prepare(sqlQuery)
    if err != nil {
        log.Fatal(err)
        return
    }

    // Defer Close.
    defer stmt.Close()

    customerEmail := validateSession(r)
    ID := getIDFromCustomer(customerEmail)
    order := order{}
    order.CustomerID = ID

    // Exec.
    ret, err := stmt.Exec(order.CustomerID)

    // Rollback.
    if err != nil {
        tx.Rollback()
        e := errors.New(err.Error())
        msg.Warning = e.Error()
        tpl.ExecuteTemplate(w, "menu.gohtml", msg)
        return
    }

    // Return last Inserted ID.
    lastID, err := ret.LastInsertId()
    if err != nil {
        orderID = 0
    } else {
        orderID = int(lastID)
    }

    // Commit.
    tx.Commit()

    return orderID
} // createOrder

Here is a working solution for now, further improvement is welcomed.

/**
 * Creates order and returns its ID.
 */
func createOrder(w http.ResponseWriter, r *http.Request) (orderID int) {

    // Begin.
    tx, err := db.Begin()
    if err != nil {
        log.Fatal(err)
    }

    // Db query.
    sqlQuery := `INSERT INTO ORDER_CUSTOMER
        (CUSTOMER_ID)
        VALUES ($1)
        RETURNING ID`

    // Prepare.
    stmt, err := tx.Prepare(sqlQuery)
    if err != nil {
        log.Fatal(err)
        return
    }

    // Defer Close.
    defer stmt.Close()

    customerEmail := validateSession(r)
    ID := getIDFromCustomer(customerEmail)
    order := order{}
    order.CustomerID = ID

    // Exec.
    _, err = stmt.Exec(order.CustomerID)

    // Rollback.
    if err != nil {
        tx.Rollback()
        e := errors.New(err.Error())
        msg.Warning = e.Error()
        tpl.ExecuteTemplate(w, "menu.gohtml", msg)
        return
    }

    // Return last Inserted ID.
    //lastID, err := ret.LastInsertId()
    err = stmt.QueryRow(order.CustomerID).Scan(&orderID)
    if err != nil {
        orderID = 0
    }

    // Commit.
    tx.Commit()

    return orderID
} // createOrder

Answer 1

This happens because the postgresql driver you are using for go doesn't supports the LastInsertedId() function. You didn't say which driver you are using but I have had this issue working with github.com/lib/pq.

The answer to this is to use QueryRow insted of Exec in your original example. Just make sure you are using RETURNING ID on your query and treat it as if it was a select.

Here is an example (I didn't test this and I might be missing something but you get the idea):

func createOrder(w http.ResponseWriter, r *http.Request) (orderID int) {

    // Begin.
    tx, err := db.Begin()
    if err != nil {
        log.Fatal(err)
    }

    // Db query.
    sqlQuery := `INSERT INTO ORDER_CUSTOMER
        (CUSTOMER_ID)
        VALUES ($1)
        RETURNING ID`

    // Prepare.
    stmt, err := tx.Prepare(sqlQuery)
    if err != nil {
        log.Fatal(err)
        return
    }

    // Defer Close.
    defer stmt.Close()

    customerEmail := validateSession(r)
    ID := getIDFromCustomer(customerEmail)
    order := order{}
    order.CustomerID = ID

    // Exec.
    var orderID int // Or whatever type you are using
    err := stmt.QueryRow(order.CustomerID).Scan(&orderID)

    // Rollback.
    if err != nil {
        //if something goes wrong set the orderID to 0 as in your original code
        orderID = 0
        tx.Rollback()
        e := errors.New(err.Error())
        msg.Warning = e.Error()
        tpl.ExecuteTemplate(w, "menu.gohtml", msg)
        return
    }

    // Commit.
    tx.Commit()

    return orderID
} // createOrder