How to get uuid of last inserted row using sqlx in postgres

Question

I am inserting some data in table using SQLx like this

func (*RideRepositoryImpl) insert(entity interface{}, tx persistence.Transaction) (sql.Result, error) {
    ride := entity.(*model.Ride)

    placeHolders := repository.InsertPlaceholders(len(rideColumns))
    sql := fmt.Sprintf("INSERT INTO %s(%s) VALUES(%s)", TableName, strings.Join(Columns, ","), placeHolders)
    return tx.Exec(sql, ride.ID.String(), ride.DeviceIotID, ride.VehicleID.String(), ride.UserID.String(),ride.AdditionComments)
}

and calling this function like this

func (p *RideRepositoryImpl) Save(ride *model.Ride, tx persistence.Transaction) error {
    return repository.Save(ride, p.insert, tx)

Now I want to get UUID of saved record instantly after saving this record . Is there any clean way to do this instantly ?

Answer 1

PostgreSQL has the RETURNING clause for this.

Sometimes it is useful to obtain data from modified rows while they are being manipulated. The INSERT, UPDATE, and DELETE commands all have an optional RETURNING clause that supports this. Use of RETURNING avoids performing an extra database query to collect the data, and is especially valuable when it would otherwise be difficult to identify the modified rows reliably.

// add the RETURNING clause to your INSERT query
sql := fmt.Sprintf("INSERT INTO %s(%s) VALUES(%s) RETURNING <name_of_uuid_column>", TableName, strings.Join(Columns, ","), placeHolders)

// use QueryRow instead of Exec
row := tx.QueryRow(sql, ride.ID.String(), ride.DeviceIotID, ride.VehicleID.String(), ride.UserID.String(),ride.AdditionComments)

// scan the result of the query
var uuid string
if err := row.Scan(&uuid); err != nil {
    panic(err)
}

// ...

For additional INSERT-specific info related to RETURNING you can go to the INSERT documentation and search the page for "returning" with CTRL/CMD+F.

---

If, in addition, you need your function to still return an sql.Result value to satisfy some requirement, then you can return your own implementation.

var _ sql.Result = sqlresult{} // compiler check

type sqlresult struct { lastid, nrows int64 }

func (r sqlresult) LastInsertId() (int64, error) { return r.lastid, nil }
func (r sqlresult) RowsAffected() (int64, error) { return r.nrows, nil }

func (*RideRepositoryImpl) insert(entity interface{}, tx persistence.Transaction) (sql.Result, error) {
    ride := entity.(*model.Ride)

    placeHolders := repository.InsertPlaceholders(len(rideColumns))
    sql := fmt.Sprintf("INSERT INTO %s(%s) VALUES(%s) RETURNING <name_of_uuid_column>", TableName, strings.Join(Columns, ","), placeHolders)
    row := tx.QueryRow(sql, ride.ID.String(), ride.DeviceIotID, ride.VehicleID.String(), ride.UserID.String(),ride.AdditionComments)
    if err := row.Scan(&ride.<NameOfUUIDField>); err != nil {
        return nil, err
    }
    return sqlresult{0, 1}, nil
}