Sql 在Golang中管理数据库事务的简洁方法

您拆分服务和存储库的方法是一个非常好的开始.下面这些对我来说很管用:

• 利用contextAPI.使服务和存储库中的所有方法都接受context作为第一个参数.
• 创建如下所示的新界面Session:

type Session interface {
    Begin(ctx context.Context) (Session, error)
    Transaction(ctx context.Context, f func(context.Context) error) error
    Rollback() error
    Commit() error
    Context() context.Context
}

• 使用这样的接口允许您在服务中使用任何类型的交易系统(无论它是不是数据库，或者任何类型的数据库).
• 创建包装pgxpool的Session的实现.
• Begin和Transaction应该向context.Context注入一个DB实例.
• 创建一个从上下文返回pgxpool的函数，如下所示:

// Use dbKey as the context value key
type dbKey struct{}

func DB(ctx context.Context, fallback *pgxpool.Pool) (pgx.Tx, error) {
    db := ctx.Value(dbKey{})
    if db == nil {
        return fallback.Begin()
    }
    return db.(pgx.Tx), nil
}

• 在您的所有存储库中，始终使用此函数来检索数据库(并使用repo.pool.Begin()作为备用).这样，存储库就不知道(也不必知道)操作是否在事务内部.服务可以调用多个不同的存储库和多个方法，而根本不用担心底层机制.它还有助于为您的服务编写测试，而无需依赖您的存储库或数据库.这应该也能很好地处理嵌套事务.
• 将Session参数添加到需要它们的服务的构造函数中.
• 在您的服务中，只有在需要执行多个存储库操作(即:拥有业务事务)时才使用Session.对于单个操作，您只需立即调用存储库，由于后备，它将使用没有TX的数据库.

我在GORM上使用了这种方法，而不是直接在PGx上使用，所以这可能不会很好地工作，因为您需要使用pgx.Tx，而不是单个类型*gorm.DB.我认为这应该会很好地工作，我希望这会帮助你继续前进.祝好运!

完整的示例

下面是一个更完整的示例，它基于我在项目中使用的实现(使用GORM).在本例中，我们有一个用户和一个用户操作历史.当用户注册时，我们希望在用户记录旁边创建一条"注册"操作记录.

我知道您没有使用GORM，但逻辑保持不变，您只需以不同的方式实现您的会话和存储库.

会话实施

package session

import (
    "context"
    "database/sql"

    "gorm.io/gorm"
)

// Session aims at facilitating business transactions while abstracting the underlying mechanism,
// be it a database transaction or another transaction mechanism. This allows services to execute
// multiple business use-cases and easily rollback changes in case of error, without creating a
// dependency to the database layer.
//
// Sessions should be constituted of a root session created with a "New"-type constructor and allow
// the creation of child sessions with `Begin()` and `Transaction()`. Nested transactions should be supported
// as well.
type Session interface {
    // Begin returns a new session with the given context and a started transaction.
    // Using the returned session should have no side-effect on the parent session.
    // The underlying transaction mechanism is injected as a value into the new session's context.
    Begin(ctx context.Context) (Session, error)

    // Transaction executes a transaction. If the given function returns an error, the transaction
    // is rolled back. Otherwise it is automatically committed before `Transaction()` returns.
    // The underlying transaction mechanism is injected into the context as a value.
    Transaction(ctx context.Context, f func(context.Context) error) error

    // Rollback the changes in the transaction. This action is final.
    Rollback() error

    // Commit the changes in the transaction. This action is final.
    Commit() error

    // Context returns the session's context. If it's the root session, `context.Background()` is returned.
    // If it's a child session started with `Begin()`, then the context will contain the associated
    // transaction mechanism as a value.
    Context() context.Context
}

// Gorm session implementation.
type Gorm struct {
    db        *gorm.DB
    TxOptions *sql.TxOptions
    ctx       context.Context
}

// GORM create a new root session for Gorm.
// The transaction options are optional.
func GORM(db *gorm.DB, opt *sql.TxOptions) Gorm {
    return Gorm{
        db:        db,
        TxOptions: opt,
        ctx:       context.Background(),
    }
}

// Begin returns a new session with the given context and a started DB transaction.
// The returned session has manual controls. Make sure a call to `Rollback()` or `Commit()`
// is executed before the session is expired (eligible for garbage collection).
// The Gorm DB associated with this session is injected as a value into the new session's context.
// If a Gorm DB is found in the given context, it will be used instead of this Session's DB, allowing for
// nested transactions.
func (s Gorm) Begin(ctx context.Context) (Session, error) {
    tx := DB(ctx, s.db).WithContext(ctx).Begin(s.TxOptions)
    if tx.Error != nil {
        return nil, tx.Error
    }
    return Gorm{
        ctx:       context.WithValue(ctx, dbKey{}, tx),
        TxOptions: s.TxOptions,
        db:        tx,
    }, nil
}

// Rollback the changes in the transaction. This action is final.
func (s Gorm) Rollback() error {
    return s.db.Rollback().Error
}

// Commit the changes in the transaction. This action is final.
func (s Gorm) Commit() error {
    return s.db.Commit().Error
}

// Context returns the session's context. If it's the root session, `context.Background()`
// is returned. If it's a child session started with `Begin()`, then the context will contain
// the associated Gorm DB and can be used in combination with `session.DB()`.
func (s Gorm) Context() context.Context {
    return s.ctx
}

// dbKey the key used to store the database in the context.
type dbKey struct{}

// Transaction executes a transaction. If the given function returns an error, the transaction
// is rolled back. Otherwise it is automatically committed before `Transaction()` returns.
//
// The Gorm DB associated with this session is injected into the context as a value so `session.DB()`
// can be used to retrieve it.
func (s Gorm) Transaction(ctx context.Context, f func(context.Context) error) error {
    tx := DB(ctx, s.db).WithContext(ctx).Begin(s.TxOptions)
    if tx.Error != nil {
        return tx.Error
    }
    c := context.WithValue(ctx, dbKey{}, tx)
    err := f(c)
    if err != nil {
        tx.Rollback()
        return err
    }
    return tx.Commit().Error
}

// DB returns the Gorm instance stored in the given context. Returns the given fallback
// if no Gorm DB could be found in the context.
func DB(ctx context.Context, fallback *gorm.DB) *gorm.DB {
    db := ctx.Value(dbKey{})
    if db == nil {
        return fallback
    }
    return db.(*gorm.DB)
}

服务实施

package user

import (
    "context"
    "example-module/database/model"
    "example-module/dto"

    "example-module/session"
    "example-module/typeutil"
)

type Repository interface {
    Create(ctx context.Context, user *model.User) (*model.User, error)
    CreateHistory(ctx context.Context, history *model.History) (*model.History, error)
}

type Service struct {
    session    session.Session
    repository Repository
}

func NewService(session session.Session, repository Repository) *Service {
    return &Service{
        session:    session,
        repository: repository,
    }
}

// Register create a new user with an associated "register" history.
func (s *Service) Register(ctx context.Context, user *dto.RegisterUser) (*dto.User, error) {

    // Model mapping from DTO to model (using the `copier` library)
    u := typeutil.Copy(&model.User{}, user)

    err := s.session.Transaction(ctx, func(ctx context.Context) error {
        // You can also call another service from here, not necessarily a repository.
        var err error
        u, err = s.repository.Create(ctx, u)
        if err != nil {
            return err
        }

        history := &model.History{
            UserID: u.ID,
            Action: "register",
        }
        _, err = s.repository.CreateHistory(ctx, history)
        return err
    })

    // Convert back to a DTO user using json marshal/unmarshal
    return typeutil.MustConvert[*dto.User](u), err
}

存储库实施

package repository

import (
    "context"
    "example-module/database/model"

    "gorm.io/gorm"
    "gorm.io/gorm/clause"
    "example-module/session"
)

type User struct {
    DB *gorm.DB
}

func NewUser(db *gorm.DB) *User {
    return &User{
        DB: db,
    }
}

func (r *User) Create(ctx context.Context, user *model.User) (*model.User, error) {
    db := session.DB(ctx, r.DB).Omit(clause.Associations).Create(&user)
    return user, db.Error
}

func (r *User) CreateHistory(ctx context.Context, history *model.History) (*model.History, error) {
    db := session.DB(ctx, r.DB).Omit(clause.Associations).Create(&history)
    return history, db.Error
}

Main(初始化)

session := session.GORM(myDB, nil)
userRepository := repository.NewUser(myDB)
userService := user.NewService(session, userRepository)