英文:
Golang postgresql query with transactions and squirrel
问题
我理解如何分别使用squirrel和事务，但我不理解如何将它们结合起来使用。什么时候应该回滚或提交呢？
我的尝试是正确的吗？如果不正确，我错在哪里...
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
}
据我理解，我在执行postgresql查询后尝试回滚或提交。
英文:
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
答案1
得分: 2
你的努力很棒。但是在这种情况下，....RunWith(db.repo.GetDatabase()) 是不正确的。你应该传递事务连接 tx。将 Squirrel 指示使用事务对象作为查询的数据库连接。
如果你使用的是数据库连接而不是事务连接，Squirrel 查询将不会成为事务的一部分。每个查询将被单独执行并立即提交到数据库。
此外，我们可以使用 defer 语句更新 RollBack 和 Commit，它将确保在函数退出之前正确处理和完成事务。
以下是更新后的代码：
tx, err := db.repo.GetDatabase().Begin()
if err != nil {
return nil, err
}
// 添加了 defer rollback 和 commit
defer func() {
if err != nil {
fmt.Println("在执行查询时发生错误 - ", 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,
)
}
// 错误将由 defer 处理
_, err = sb.Exec()
// 你可以使用事务执行多个查询
for _, human := range someOtheSlice {
sb = sb.Values(
human.CorrelationID,
human.Name,
human.Age,
)
}
_, err = sb.Exec()
// 如果发生任何错误，所有查询都将在 defer 中回滚
希望对你有所帮助。
另外，请参考以下链接：
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论