交易后返回由CockroachDB创建的行ID

After executing a cockroachDB transaction which creates a row, I would like to return that id to my application.

I am currently defining a transaction using the database/sql golang package. I then use the crdb client to execute the transaction against CockroachDB.

I cannot return anything from the crdb transaction except for an error relating to the crdb transaction itself. How do people retrieving their most recently created rows if the interface does not provide a way to return a new row id?

I have some transaction that executes two inserts. I get the resulting ids of those inserts and return a response with both of those id values.

func myTransaction(ctx context.Context, tx *sql.Tx) (MyTxnResponse, error) {
  var aResult string
  if err := tx.QueryRowContext(ctx, `INSERT INTO foo (a,b,c) VALUES (1,2,3) RETURNING foo_id`).Scan(&aResult); err != nil { ... }

  var bResult string
  if err := tx.QueryRowContext(ctx, `INSERT INTO bar (d,e,f) VALUES (4,5,6) RETURNING bar_id`).Scan(&bResult); err != nil { ... }

  return MyTxnResponse{A: aResult, B: bResult}
}

Now, I want to execute the transaction against CRDB

var responseStruct MyTxnResponse
err := crdb.ExecuteTx(ctx.Background(), db, nil, func(tx *sql.Tx) error {
    res, err := myTransaction(ctx,tx)

    // This is what I am currently doing... it seems bad
    responseStruct.A = res.A
    responseStruct.B = res.B

    return err
}
... use responseStruct for something

I am able to get the result of the transaction by using this external var solution and just mutating it's state in the closure, but that seems like a really bad pattern. I'm sure I'm missing something that will blow up in my face if I keep this pattern.

Is there a standard way to return something from a transaction?

You've got the right idea: declare variables outside the transaction and assign them inside the transaction closure. This is the idiomatic way to "return" things in Go from a closure like this, since the only alternative is to have the function return an interface{} which would need a runtime cast to be useful.

There are a couple of shortcuts available here: since responseStruct and res are the same type, you don't need to list all the fields. You can simply say responseStruct = res.

You can even assign the result of myTransaction directly to responseStruct, avoiding the temporary res entirely. However, you have to be careful with your variable scopes here - you can't use := because that would create a new responseStruct inside the closure. Instead, you must declare all other variables (here, err) so that you can use the plain = assignment:

var responseStruct MyTxnResponse
err := crdb.ExecuteTx(context.Background(), db, nil, func(tx *sql.Tx) error {
    var err error
    responseStruct, err = myTransaction(ctx, tx)
    return err
})
// use responseStruct (but only if err is nil)