bulk upserts within a sql transaction in golang

Question

I've been messing around with golang's sql package with transactions, and I'm trying to understand how to do bulk upserts without the "per insert" round trip communication for each row. The examples here don't really show how any bulk queries would be done.

updateMoney, err := db.Prepare("INSERT INTO balance set money=?, id=? ON DUPLICATE UPDATE balance SET money=money+? WHERE id=?")
...
tx, err := db.Begin()
...
res, err := tx.Stmt(updateMoney).Exec(123.45, 1, 123.45, 1)
res, err := tx.Stmt(updateMoney).Exec(67.89, 2, 67.89, 2)
res, err := tx.Stmt(updateMoney).Exec(10.23, 3, 10.23, 3)
...
tx.Commit()

Ideally, I'd be able to take a prepared query, and build up a list of upserts to be sent at the same time... but here, we get a result back from the database after each execution. Any suggestions on how to go about this?

Edit: My coworker found this open ticket that describes the problem... it looks to be a larger concern than strictly within the context of a transaction.

Answer 1

It depends on which driver you are using, some drivers / databases don't support transactions at all.

For example go-sql-driver/mysql supports transactions just fine.

Your code should work, or you could change it a little to:

tx, err := db.Begin()
...
stmt, err := tx.Prepare(`INSERT INTO balance set money=?, id=? ON DUPLICATE UPDATE balance SET money=money+? WHERE id=?`)
res, err := stmt.Exec(123.45, 1, 123.45, 1)
res, err := stmt.Exec(67.89, 2, 67.89, 2)
res, err := stmt.Exec(10.23, 3, 10.23, 3)
...
tx.Commit()

Also check this answer which goes into a lot of details about transactions.