Introduction

sqldb is a useful package which defines some common types and interfaces in manipulating data of models in sql database.

It also provides an implementation of the interfaces based on the GORM library.

Getting Started

A Model defined in sqldb.go contains a set of commonly used methods when handling data in a database.

type Model[T any] interface {
	Create(ctx context.Context, entity *T) error
	Get(ctx context.Context, opts []OpQueryOption) (*T, error)
	List(ctx context.Context, opts ListOptions) ([]*T, uint64, error)
	Update(ctx context.Context, query FilterOptions, opts []UpdateOption) (uint64, error)
	Delete(ctx context.Context, opts FilterOptions) error
}

Before using the Model you have to declaring your model, User for example:

import "github.com/YLonely/sqldb"

type User struct {
	ID      sqldb.Column[uint64] `gorm:"column:id;primaryKey"`
	Name    sqldb.Column[string] `gorm:"column:user_name"`
	Age     sqldb.Column[*int]
	CreatedAt sqldb.Column[time.Time]
	DeletedAt sqldb.Column[gorm.DeletedAt]
}

Here sqldb.Column is a generic type which represents a table column in the database, it contains the value of the corresponding field and also the real column name of it.

Now we can initialize a Model type for User:

import (
	"context"

	"github.com/YLonely/sqldb"
	sqlgorm "github.com/YLonely/sqldb/gorm"
)

func main(){
	// Open db and create the gorm instance `db`.
	var Users sqldb.Model[User] = sqlgorm.NewModel[User](db)
	ctx := context.Background()

	// To create a new user
	age := 10
	u := &User{
		Name: sqldb.NewColumn("test"),
		Age: sqldb.NewColumn(&age),
	}
	_ = Users.Create(ctx, u)

	// To get the user
	u, err := Users.Get(ctx, []sqldb.OpQueryOption{
		// No more string literals, use .Columns() instead.
		sqldb.NewEqualOption(Users.Columns().Name, "test"),
	})
}

It is worth noting that you do not write string literals of columns when constructing query options, every Model[T] type has a method Columns() which returns a instance of type T, all fields of type sqldb.Column are populated with column name during initialization.

sqldb.go also defines a function type which abstracts transactions:

type TransactionFunc func(ctx context.Context, run func(context.Context) error) error

To create a TransactionFunc implemented by GORM and process models in the transaction:

Transaction := gorm.NewTransactionFunc(db)

Transaction(context.Background(), func(ctx context.Context) error {
	if err := Users.Delete(ctx, sqldb.FilterOptions{
		InOptions: []sqldb.RangeQueryOption{
			Column: Users.Age,
			Values: []any{10, 11, 12}
		}
	}); err != nil {
		return err
	}

	// nested transaction.
	Transaction(ctx, func(ctx context.Context) error {
	})
})

GitHub

View Github