Proposal: Add Transaction abstraction to detach from sql DB

[maranqz]

Hello,

sqlUnit is strongly connected with database/sql because of *sql.Tx. However, sql.Tx could be placed in an interface with Begin, Commit and Rollback functions. That gives the ability to change databases without changes the unit implementation.

What do you think about the idea?

interface{} version

package main

import (
    "context"
    "database/sql"
    "github.com/DATA-DOG/go-sqlmock"
)

type TrM interface {
    Do(ctx context.Context, fn func(ctx context.Context, tx interface{}) error) error
}

type Mapper interface {
    Insert(ctx context.Context, tx interface{}, additions ...interface{}) error
}

type trm struct {
    db *sql.DB
}

func (t trm) Do(ctx context.Context, fn func(ctx context.Context, tx interface{}) error) error {
    tx, _ := t.db.BeginTx(ctx, nil)
    defer tx.Commit()

    return fn(ctx, tx)
}

type sqlMapper struct{}

func (s sqlMapper) Insert(_ context.Context, txAny interface{}, _ ...interface{}) error {
    tx, _ := txAny.(*sql.Tx)

    _ = tx
    // tx.Exec(...)

    return nil
}

type unit struct {
    trm       TrM
    mapper    Mapper
    additions []interface{}
}

func (u *unit) Save(ctx context.Context) error {
    return u.trm.Do(ctx, func(ctx context.Context, tx interface{}) error {
        return u.mapper.Insert(ctx, tx, u.additions...)
    })
}

func main() {
    db, mock, _ := sqlmock.New()
    mock.ExpectBegin()
    mock.ExpectCommit()

    u := &unit{
        trm:    trm{db: db},
        mapper: sqlMapper{},
    }

    u.Save(context.Background())

    if err := mock.ExpectationsWereMet(); err != nil {
        panic(err)
    }
}

generic version

//go:build go1.18
// +build go1.18

package main

import (
	"context"
	"database/sql"
	"github.com/DATA-DOG/go-sqlmock"
)

type TrM[Tx any] interface {
	Do(ctx context.Context, fn func(ctx context.Context, tx Tx) error) error
}

type Mapper[Tx any] interface {
	Insert(ctx context.Context, tx Tx, additions ...interface{}) error
}

type trm struct {
	db *sql.DB
}

func (t trm) Do(ctx context.Context, fn func(ctx context.Context, tx *sql.Tx) error) error {
	tx, _ := t.db.BeginTx(ctx, nil)
	defer tx.Commit()

	return fn(ctx, tx)
}

type record struct {
	ID int
}

type recordMapper struct{}

func (r recordMapper) Insert(_ context.Context, _ *sql.Tx, _ ...interface{}) error {
	return nil
}

type unit[Tx any] struct {
	trm       TrM[Tx]
	mapper    Mapper[Tx] // replace on map
	additions []interface{}
}

func (u *unit[Tx]) Save(ctx context.Context) error {
	return u.trm.Do(ctx, func(ctx context.Context, tx Tx) error {
		return u.mapper.Insert(ctx, tx, u.additions...)
	})
}

func main() {
	db, mock, _ := sqlmock.New()
	mock.ExpectBegin()
	mock.ExpectCommit()

	u := &unit[*sql.Tx]{
		trm:    trm{db: db},
		mapper: recordMapper{},
	}

	u.Save(context.Background())

	if err := mock.ExpectationsWereMet(); err != nil {
		panic(err)
	}
}

[maranqz]

Hello, if I add the solution code, could you review and commit the code to the repository?

[fr33r]

hey @maranqz! appreciate the reach out and ideation here!

thanks for the examples - admittedly i'm still a little fuzzy on the use cases being presented here, given the sqlUnit is meant to be tied to the sql standard package. not only is there a dependency on *sql.Tx, but also the work.UnitDB option requires a *sql.DB instance, and the sqlUnit itself is responsible for managing *sql.DB and the transaction handling, where your example implementations above remove that as well (it's shifted to trm).

That gives the ability to change databases without changes the unit implementation.

It's my understanding that database/sql defines an interface that supports dozens of database implementations and even more drivers. For example, this resource lists out 50+ drivers that are compatible with database/sql.

[maranqz]

Hello, you are right about SQL.

I could add an use case with NoSQL DBs, which support the transaction, but it is not so frequent.