Golang postgresql query using transactions and squirrel

Question content

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? If not, where did I go 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 am trying to rollback or commit after executing a query in postgresql

Correct Answer

Your efforts are great. But ....runwith(db.repo.getdatabase()) is incorrect in this case. Because you should pass transaction connection tx. Instructs squirrel to use a transaction object as the database connection for the query.

If you use a database connection instead of a transactional connection, the squirrel query will not be part of the transaction. Each query will be executed individually and submitted to the database immediately.

We can also update the rollback and commit statements with the defer statement, which will ensure that the transaction is properly processed and completed before the function exits.

This 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.

See also

• database/sql tx - detect commit or rollback李>
• Use defer

The above is the detailed content of Golang postgresql query using transactions and squirrel. For more information, please follow other related articles on the PHP Chinese website!