What is the best way to implement a transaction?

Question

go

There is a table where you need to save information about photos. I wrote this code, can you see it? Or is it better to save one photo at a time, but I think it's too expensive and better for transactions.

func (db *Requests) StorePhotos(photos []Photo) error {
  tx, err := db.Begin()

  if err != nil {
    return err
  }
  for _, photo := range photos {
    ctx, cancel := context.WithTimeout(context.Background(), time.Minute*2)
    stmnt, err := tx.PrepareContext(ctx, "INSERT INTO photos (id, owner_id, req_id, url) VALUES ($1, $2, $3, $4)")

    if err != nil {
      cancel()
      return err
    }

    _, err = stmnt.Exec(photo.ID, photo.OwnerID, photo.ReqID, photo.URL)

    if err != nil {
      cancel()
      return err
    }

    cancel()
  }
  tx.Commit()

}

Answer 1

In general, everything looks not bad, but it will be better if we take the context as input. It was also not bad to cancel the transaction in case of an error, and it would not hurt to close prepare either. For each iteration of the loop, it is not at all necessary to create your own prepare, one is enough

func (db *Requests) StorePhotos(ctx context.Context, photos []Photo) error {
  ctx, cancel := context.WithTimeout(ctx, time.Minute*2)
  defer cancel()

  tx, err := db.BeginTx(ctx, nil)
  if err != nil {
    return err
  }
  defer tx.Rollback()

  stmnt, err := tx.PrepareContext(ctx, "INSERT INTO photos (id, owner_id, req_id, url) VALUES ($1, $2, $3, $4)")
  if err != nil {
    return err
  }
  defer stmnt.Close()

  for _, photo := range photos {
    if _, err = stmnt.ExecContext(ctx, photo.ID, photo.OwnerID, photo.ReqID, photo.URL); err != nil {
      return err
    }
  }

  return tx.Commit()
}

Answer 2

insert into table (x,y,z) values (?,?,?), (?,?,?), (?,?,?), ...

and pass the values ​​​​as usual, all at once, in an array, in the correct order