Golang postgresql query with transactions and squirrel

Question

I understand how to use squirrel and transactions separately, but I don't understand how to use them together. When should I rollback or commit? Is my attempt correct or not? If not, where am I wrong...

tx, err := db.repo.GetDatabase().Begin()
if err != nil {
    return nil, err
}

sb := squirrel.StatementBuilder.
    Insert("dependencies").
    Columns("correlation_id", "name", "age").
    PlaceholderFormat(squirrel.Dollar).
    RunWith(db.repo.GetDatabase())

for _, human:= range humans{
    sb = sb.Values(
        human.CorrelationID,
        human.Name,
        human.Age,
    )
}

_, err = sb.Exec()
if err != nil {
    if err := tx.Rollback(); err != nil {
        return nil, err
    }
}

if err := tx.Commit(); err != nil {
    return nil, err
}

As I understand it, I'm trying to rollback or commit after the query is executed in postgresql

Answer 1

Your efforts are great. But ....RunWith(db.repo.GetDatabase()) is incorrect in this case. As you should pass the transaction connection tx instead. Directing Squirrel to use the transaction object as the query's database connection.

If you use the DB connection instead of the transaction connection, the Squirrel queries will not be part of the transaction. Each query will be executed individually and committed to the database instantly.

Also we can update the RollBack and Commit with defer statement, It will ensure transaction is properly handled and finalised before the function exits.

Here is the updated code..

tx, err := db.repo.GetDatabase().Begin()
if err != nil {
    return nil, err
}

// added defer rollback and commit
defer func() {
    if err != nil {
        fmt.Println("An error happened while executing the queries - ", err)
        tx.Rollback()
        return
    }
    err = tx.Commit()
}()

response := make([]storage.URLStorage, 0, len(urls))

sb := squirrel.StatementBuilder.
    Insert("dependencies").
    Columns("correlation_id", "name", "age").
    PlaceholderFormat(squirrel.Dollar).
    RunWith(tx)

for _, human := range humans {
    sb = sb.Values(
        human.CorrelationID,
        human.Name,
        human.Age,
    )
}

// the error will be handled by the defer
_, err = sb.Exec()

// you can execute multiple queries with the transaction
for _, human := range someOtheSlice {
    sb = sb.Values(
        human.CorrelationID,
        human.Name,
        human.Age,
    )
}

_, err = sb.Exec()

// If any error happened this query executions, all will be roll backed with the defer

Hope this helps.

Also see

• database/sql Tx - detecting Commit or Rollback
• Use of defer