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

PRATHEESH PC · Accepted Answer · 2023-06-21T00:01:39.800

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

Thanks, but I don't really understand what the difference is)) You just moved the rollback and commit to the beginning, and using "defer" call them at the end, which in general will not change anything. What? I do not understand?) — volnistii11, Jun 20 '23 at 07:37
And explain about RunWIth() please. Did I understand correctly that I should transfer the transaction there? To be like this: sb := squirrel.StatementBuilder.Insert("dependencies").Columns("correlation_id", "name", "age").PlaceholderFormat(squirrel.Dollar)..RunWith(tx)? — volnistii11, Jun 20 '23 at 07:39
The `RunWith()` method in the Squirrel library is used to execute queries. We can specify the execution context by providing a `database connection (db)` or an active `transaction (tx)`. 1. `RunWith(db)`: With this, the query will be executed and committed to the database instantly(the changes made by the query immediately applied). 2. `RunWith(tx)`: With this, the changes made by the query will not be applied to the database until an explicit `commit` statement is executed(`tx.commit`). — PRATHEESH PC, Jun 20 '23 at 08:01
Yeah, thanks, you just left "RunWith(db.repo.GetDatabase())" in the solution without replacing it with tx, so I didn't understand a bit =) — volnistii11, Jun 20 '23 at 16:00
Hhaa...! That's right, I updated the solution with tx. Why didn't I notice it? :( Thanks for pointing it out :) — PRATHEESH PC, Jun 21 '23 at 00:27

Golang postgresql query with transactions and squirrel

1 Answers1