[Question] Does Bun support optimistic locking?

[takaaa220]

Hi, maintainers!

I’m considering replacement into Bun in my project and want to check if it supports optimistic locking.

If not, is there any plan to support it in the future?
If there is no plan to support it, I’m thinking of implementing it using model query hooks(BeforeInsert, BeforeUpdate and BeforeDelete), but I’d love to hear if there are better approaches.

Thanks!

[j2gg0s]

What is optimistic locking?
A field like version?

UPDATE users 
SET balance = balance - 100, version = version + 1 
WHERE id = 1 AND version = 3;

If I understand correctly, we dont cureently support it.
But I think supporting this feature is a good idea.
The challenge is how to design the interface for users. Do you have any ideas or existing examples?

[takaaa220]

@j2gg0s

What is optimistic locking?
A field like version?

Yes, you understand correctly.

If I understand correctly, we dont cureently support it.

Thank your for reply, i got it.

The challenge is how to design the interface for users. Do you have any ideas or existing examples?

I think the following interface changes would be necessary to support optimistic locking:

• Add an interface to specify whether a model should use optimistic locking and which column to use (e.g., bun:",optimisticlock" for versioned field).
• Modify UPDATE and DELETE queries to include the version field in the condition when updating and deleting rows of tables that use optimistic locking. And then, return an error indicating a conflict if the number of affected rows does not match the expected count.
• Increment version when updating a record.
• (Optional) Set an initial version value when inserting a record.

An alternative approach could be providing a plugin that utilizes Model Query Hooks.
I created a PoC, but I found that RowsAffected is not accessible in AfterUpdateHook. To work around this, I had to perform an additional SELECT inside AfterUpdate.

Poc (Maybe this works only in limited usecase)

package optimisticlocking

import (
	"context"
	"fmt"

	"github.com/uptrace/bun"
)

// OptimisticLockingModel is a model that supports optimistic locking.
//
// How to implement OptimisticLockingModel is as follows:
//
//	type User struct {
//		ID      int64 `bun:",pk,autoincrement"`
//		Name    string
//		Version int
//	}
//
//	var _ optimisticlocking.OptimisticLockingModel = (*User)(nil)
//
//	func (u *User) VersionColumn() string {
//		return "version"
//	}
//
//	func (u *User) GetVersion() int {
//		return u.Version
//	}
//
//	func (u *User) IncrementVersion() {
//		u.Version++
//	}
//
//	func (u *User) BeforeAppendModel(ctx context.Context, query bun.Query) error {
//		return optimisticlocking.BeforeAppendModel(ctx, query)
//	}
//
//	func (u *User) BeforeUpdate(ctx context.Context, query *bun.UpdateQuery) error {
//		return optimisticlocking.BeforeUpdate(ctx, query)
//	}
//
//	func (u *User) BeforeDelete(ctx context.Context, query *bun.DeleteQuery) error {
//		return optimisticlocking.BeforeDelete(ctx, query)
//	}
//
//	func (u *User) AfterUpdate(ctx context.Context, query *bun.UpdateQuery) error {
//		return optimisticlocking.AfterUpdate(ctx, query)
//	}
//
//	func (u *User) AfterDelete(ctx context.Context, query *bun.DeleteQuery) error {
//		return optimisticlocking.AfterDelete(ctx, query)
//	}
type OptimisticLockingModel interface {
	VersionColumn() string
	GetVersion() int
	IncrementVersion()

	bun.BeforeAppendModelHook
	bun.BeforeUpdateHook
	bun.BeforeDeleteHook
	bun.AfterUpdateHook
	bun.AfterDeleteHook
}

// BeforeAppendModel is a hook that is called before the model is appended to the query.
// It is used to set the version column to the current version.
func BeforeAppendModel(ctx context.Context, query bun.Query) error {
	model := getOptimisticLockingModelFromQuery(query)
	if model == nil {
		return nil
	}

	switch query.(type) {
	case *bun.InsertQuery:
		if model.GetVersion() == 0 {
			model.IncrementVersion()
		}
	case *bun.UpdateQuery:
		model.IncrementVersion()
	}
	return nil
}

// BeforeUpdate is a hook that is called before the model is updated.
// It is used to check if the version column is out of date.
func BeforeUpdate(ctx context.Context, query *bun.UpdateQuery) error {
	model := getOptimisticLockingModelFromQuery(query)
	if model == nil {
		return nil
	}

	query.Where("? = ?", query.FQN(model.VersionColumn()), model.GetVersion())

	return nil
}

// BeforeDelete is a hook that is called before the model is deleted.
// It is used to check if the version column is out of date.
func BeforeDelete(ctx context.Context, query *bun.DeleteQuery) error {
	model := getOptimisticLockingModelFromQuery(query)
	if model == nil {
		return nil
	}

	query.Where("? = ?", bun.Ident(model.VersionColumn()), model.GetVersion())

	return nil
}

// AfterUpdate is a hook that is called after the model is updated.
// It is used to check if the version column is out of date.
func AfterUpdate(ctx context.Context, query *bun.UpdateQuery) error {
	model := getOptimisticLockingModelFromQuery(query)
	if model == nil {
		return nil
	}

	// Since cannot access the update result in AfterUpdateHook,
	// detect version conflicts by checking if a record with the updated version exists.
	exists, err := bun.NewSelectQuery(query.DB()).
		Model(model).
		ColumnExpr("1").
		WherePK().
		Where("? = ?", query.FQN(model.VersionColumn()), model.GetVersion()).
		Exists(ctx)
	if err != nil {
		return err
	}
	if !exists {
		return &OptimisticLockingError{
			TableName:  query.GetTableName(),
			OldVersion: model.GetVersion() - 1,
		}
	}

	return nil
}

func AfterDelete(ctx context.Context, query *bun.DeleteQuery) error {
	model := getOptimisticLockingModelFromQuery(query)
	if model == nil {
		return nil
	}

	// Since cannot access the delete result in AfterDeleteHook,
	// detect version conflicts by checking if a record with the updated version exists.
	exists, err := bun.NewSelectQuery(query.DB()).
		Model(model).
		ColumnExpr("1").
		WherePK().
		Where("? = ?", model.VersionColumn(), model.GetVersion()).
		Exists(ctx)
	if err != nil {
		return err
	}
	if !exists {
		return &OptimisticLockingError{
			TableName:  query.GetTableName(),
			OldVersion: model.GetVersion(),
		}
	}

	return nil
}

func getOptimisticLockingModelFromQuery(query bun.Query) OptimisticLockingModel {
	model := query.GetModel()
	optimisticLockingModel, ok := model.Value().(OptimisticLockingModel)
	if !ok {
		return nil
	}
	return optimisticLockingModel
}

type OptimisticLockingError struct {
	TableName  string
	OldVersion int
}

func (e *OptimisticLockingError) Error() string {
	return fmt.Sprintf("bun: version %d is out of version on table %s", e.OldVersion, e.TableName)
}

usage:

Do you have any thoughts on these approaches? I’m open to discussing potential improvements or alternative designs.

[takaaa220]

existing examples?

Here are some examples of ORMs that implement optimistic locking:

• Gorm (Go): GitHub Repository
• Gorp (Go): GitHub Repository
• ActiveRecord (Ruby): Documentation
• Entity Framework (C#): Documentation

Gorm provides optimistic locking as a plugin. It offers a custom type for the version column, which is used for optimistic locking. The column type is an integer.

Gorp allows specifying the version column by calling SetVersionCol("COLUMN_NAME"). The column type used for versioning is an integer.

In ActiveRecord, optimistic locking is automatically enabled if the table contains a lock_version column. The column name used for optimistic locking can be customized. The column type is an integer.

[opstoken]

dbdriver uses duckdb, dialect with pgdialect, and fix some field type errors, my app runs ok

[emoss08]

Here is an example of how I do optimistic locking in my application, may not be the best apporach ,but it works for my use case.

func (cr *commodityRepository) Update(ctx context.Context, com *commodity.Commodity) (*commodity.Commodity, error) {
	dba, err := cr.db.DB(ctx)
	if err != nil {
		return nil, eris.Wrap(err, "get database connection")
	}

	log := cr.l.With().
		Str("operation", "Update").
		Str("id", com.GetID()).
		Int64("version", com.Version).
		Logger()

	err = dba.RunInTx(ctx, nil, func(c context.Context, tx bun.Tx) error {
		ov := com.Version

		com.Version++

		results, rErr := tx.NewUpdate().
			Model(com).
			WherePK().
			Where("com.version = ?", ov).
			Returning("*").
			Exec(c)
		if rErr != nil {
			log.Error().
				Err(rErr).
				Interface("commodity", com).
				Msg("failed to update commodity")
			return eris.Wrap(rErr, "update commodity")
		}

		rows, roErr := results.RowsAffected()
		if roErr != nil {
			log.Error().
				Err(roErr).
				Interface("commodity", com).
				Msg("failed to get rows affected")
			return eris.Wrap(roErr, "get rows affected")
		}

		if rows == 0 {
			return errors.NewValidationError(
				"version",
				errors.ErrVersionMismatch,
				fmt.Sprintf("Version mismatch. The Commodity (%s) has either been updated or deleted since the last request.", com.GetID()),
			)
		}

		return nil
	})
	if err != nil {
		log.Error().Err(err).Msg("failed to update commodity")
		return nil, eris.Wrap(err, "update commodity")
	}

	return com, nil
}