sqldb

package module
v0.2.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Apr 18, 2023 License: Apache-2.0 Imports: 13 Imported by: 0

README

Introduction

Go Report Card Go Reference

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

The Model interface

The Model and Executor defined in model.go contains a set of commonly used methods when handling data in a database.

// Model is an interface defines commonly used methods to manipulate data.
type Model[T any] interface {
	// DB returns the db instance.
	DB(context.Context) *gorm.DB
	// Table returns the table name in the database.
	Table() string
	// Columns returns a instance of type T,
	// all fields of type sqldb.Column[U] in the instance are populated with corresponding column name.
	Columns() T
	// ColumnNames returns all column names the model has.
	ColumnNames() []ColumnNameGetter
	// Create creates an new entity of type T.
	Create(ctx context.Context, entity *T) error
	Query(queries ...FilterOption) Executor[T]
}

// Executor is an interface wraps operations related to db queries.
type Executor[T any] interface {
	Get(ctx context.Context) (T, error)
	List(ctx context.Context, opts ListOptions) ([]T, uint64, error)
	Update(ctx context.Context, opts ...UpdateOption) (uint64, error)
	Delete(ctx context.Context) error
}

Declaring models

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

import (
	"gorm.io/gorm"
	"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.PtrColumn[int]
	CreatedAt sqldb.Column[time.Time]
	DeletedAt sqldb.Column[gorm.DeletedAt]
}

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

Operating the model

Now we can initialize a Model type for User:

import (
	"context"

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

func main(){
	// Use gorm to open the database.
	dsn := "user:pass@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local"
  	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil{
		panic(err)
	}

	if err := db.AutoMigrate(User{}); err != nil {
		panic(err)
	}
	m := sqldb.NewModel[User](db)
	cols := m.Columns()
	ctx := context.Background()

	u := &User{
		ID:   sqldb.NewColumn(uint64(1)),
		Name: sqldb.NewColumn("lazy"),
		Age:  sqldb.NewPtrColumn(10),
	}
	if err := m.Create(ctx, u); err != nil {
		panic(err)
	}

	u.ID.V = 2
	u.Name.V = "jump"
	if err := m.Create(ctx, u); err != nil {
		panic(err)
	}

	users, _, err := m.Query(
		cols.Name.NE("lazy"),
		cols.Age.In([]int{10, 11, 12}),
		// not recommended
		sqldb.NewOpQueryOption(
			sqldb.NewColumnName("user_name"),
			sqldb.OpEq,
			"jump",
		),
	).List(ctx, sqldb.ListOptions{})
	if err != nil {
		panic(err)
	}
	for _, u := range users {
		fmt.Printf("id: %v\tname: %s\tage: %v\n",
			u.ID.V, u.Name.V, *u.Age.V)
	}
}

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 or sqldb.PtrColumn in type T provide a bunch of useful methods for users to construct query options.

func EQ(value any) OpOption {}
func NE(value any) OpOption {}
func GT(value any) OpOption {}
func LT(value any) OpOption {}
func GTE(value any) OpOption {}
func LTE(value any) OpOption {}
func In(values []T) RangeQueryOption {}
func NotIn(values []T) RangeQueryOption {}
func FuzzyIn(values []T) FuzzyQueryOption {}
func Update(value any) UpdateOption {}

You can also use the option structs directly, but you have to confirm the column name by yourself, which is extremely not recommended.

Transactions

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 := sqldb.NewTransactionFunc(db)

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

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

Joining tables

sqldb provides a more convenient way to join tables. The complexity of renaming duplicate column names and writing lengthy sql statements is hidden in the internal processing of sqldb. All you need to do is to call the encapsulated join functions.

import (
	"context"

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

type User struct {
	Name sqldb.Column[string]
	Age  sqldb.Column[int]
}

type Class struct {
	Name    sqldb.Column[string]
	Address sqldb.Column[string]
	Age     sqldb.Column[int]
}

func main(){
	// Use gorm to open the database.
	users := sqldb.NewModel[User](db)
	classes := sqldb.NewModel[Class](db)
	ctx := context.Background()

	results, total, err := Join(ctx, users, classes, 
		NewJoinOptions(
			append(users.ColumnNames(), classes.ColumnNames()...),
			users.Columns().Name.EQ(classes.Columns().Name),
		),
	).Query().List(ctx, sqldb.ListOptions{})

	for _, result := range results {
		fmt.Printf("user.name: %s, class.name: %s\n", result.Left.Name, result.Right.Name)
	}
}

The join functions also return a Model type, which allows you to concatenate other complex query operations. The type JoinedEntity contains both Model types that are joined which provides a view of the joined tables.

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func MapErr added in v0.0.7 

func MapErr[T any, R any](collection []T, iteratee func(T, int) (R, error)) ([]R, error)

func TransactionFrom added in v0.0.5 

func TransactionFrom(ctx context.Context) *gorm.DB

func WithTransaction added in v0.0.5 

func WithTransaction(ctx context.Context, tx *gorm.DB) context.Context

Types

type Column 

type Column[T any] struct {
	// contains filtered or unexported fields
}

Column represents a column of a table.

func NewColumn 

func NewColumn[T any](v T) Column[T]

NewColumn creates a new Column of type T.

func (Column) EQ added in v0.2.0 

func (c Column) EQ(value any) OpOption

func (Column) FuzzyIn added in v0.2.0 

func (c Column) FuzzyIn(values []T) FuzzyQueryOption

func (Column) GT added in v0.2.0 

func (c Column) GT(value any) OpOption

func (Column) GTE added in v0.2.0 

func (c Column) GTE(value any) OpOption

func (Column) In added in v0.2.0 

func (c Column) In(values []T) RangeQueryOption

func (Column) LT added in v0.2.0 

func (c Column) LT(value any) OpOption

func (Column) LTE added in v0.2.0 

func (c Column) LTE(value any) OpOption

func (Column) NE added in v0.2.0 

func (c Column) NE(value any) OpOption

func (Column) NotIn added in v0.2.0 

func (c Column) NotIn(values []T) RangeQueryOption

func (Column) Update added in v0.2.0 

func (c Column) Update(value any) UpdateOption

type ColumnName 

type ColumnName struct {
	Name string
	// contains filtered or unexported fields
}

func NewColumnName added in v0.1.0 

func NewColumnName(name string) ColumnName

func (ColumnName) Full added in v0.1.0 

func (cn ColumnName) Full() string

func (ColumnName) GetColumnName 

func (cn ColumnName) GetColumnName() ColumnName

func (ColumnName) Sort added in v0.2.0 

func (cn ColumnName) Sort(order SortOrder) sortOption

func (ColumnName) String added in v0.1.0 

func (cn ColumnName) String() string

type ColumnNameGetter added in v0.2.0 

type ColumnNameGetter interface {
	GetColumnName() ColumnName
}

type ColumnValue 

type ColumnValue[T any] struct {
	V T
}

func (ColumnValue[T]) CreateClauses 

func (cv ColumnValue[T]) CreateClauses(f *gormschema.Field) []clause.Interface

CreateClauses implements the CreateClausesInterface interface from GORM.

func (ColumnValue[T]) DeleteClauses 

func (cv ColumnValue[T]) DeleteClauses(f *gormschema.Field) []clause.Interface

DeleteClauses implements the DeleteClausesInterface interface from GORM.

func (ColumnValue[T]) MarshalJSON 

func (cv ColumnValue[T]) MarshalJSON() ([]byte, error)

func (ColumnValue[T]) QueryClauses 

func (cv ColumnValue[T]) QueryClauses(f *gormschema.Field) []clause.Interface

QueryClauses implements the QueryClausesInterface interface from GORM.

func (*ColumnValue[T]) Scan 

func (cv *ColumnValue[T]) Scan(src any) error

Scan implements the Scanner interface.

func (*ColumnValue[T]) UnmarshalJSON 

func (cv *ColumnValue[T]) UnmarshalJSON(data []byte) error

func (ColumnValue[T]) UpdateClauses 

func (cv ColumnValue[T]) UpdateClauses(f *gormschema.Field) []clause.Interface

UpdateClauses implements the UpdateClausesInterface interface from GORM.

func (ColumnValue[T]) Value 

func (cv ColumnValue[T]) Value() (driver.Value, error)

Value implements the driver Valuer interface.

type Executor added in v0.2.0 

type Executor[T any] interface {
	Get(ctx context.Context) (T, error)
	List(ctx context.Context, opts ListOptions) ([]T, uint64, error)
	Update(ctx context.Context, opts ...UpdateOption) (uint64, error)
	Delete(ctx context.Context) error
}

Executor is an interface wraps operations related to db queries.

type FilterOption added in v0.2.0 

type FilterOption interface {
	GetFilterOptionType() FilterOptionType
}

type FilterOptionType added in v0.2.0 

type FilterOptionType string
const (
	FilterOptionTypeOpQuery    FilterOptionType = "OpQuery"
	FilterOptionTypeRangeQuery FilterOptionType = "RangeQuery"
	FilterOptionTypeFuzzyQuery FilterOptionType = "FuzzyQuery"
)

type FuzzyQueryOption 

type FuzzyQueryOption interface {
	FilterOption
	ValuesOption
}

FuzzyQueryOption represents a query that find data that match given patterns approximately.

func NewFuzzyQueryOption 

func NewFuzzyQueryOption[T any](name ColumnName, values []T) FuzzyQueryOption

type JoinOptions added in v0.1.0 

type JoinOptions struct {
	SelectedColumns []ColumnNameGetter
	Conditions      []OpOption
}

func NewJoinOptions added in v0.1.0 

func NewJoinOptions(selectedColumns []ColumnNameGetter, conditions ...OpOption) JoinOptions

type JoinedEntity added in v0.1.0 

type JoinedEntity[L, R any] struct {
	Left  L `gorm:"embedded"`
	Right R `gorm:"embedded"`
}

type ListOptions 

type ListOptions struct {
	Offset      uint64
	Limit       uint64
	SortOptions []SortOption
}

ListOptions contains options and parameters that related to data listing.

type Model 

type Model[T any] interface {
	// DB returns the db instance.
	DB(context.Context) *gorm.DB
	// Table returns the table name in the database.
	Table() string
	// Columns returns a instance of type T,
	// all fields of type sqldb.Column[U] in the instance are populated with corresponding column name.
	Columns() T
	// ColumnNames returns all column names the model has.
	ColumnNames() []ColumnNameGetter
	// Create creates an new entity of type T.
	Create(ctx context.Context, entity *T) error
	Query(queries ...FilterOption) Executor[T]
}

Model is an interface defines commonly used methods to manipulate data.

func Join added in v0.1.0 

func Join[L, R any](ctx context.Context, left Model[L], right Model[R], opts JoinOptions) Model[JoinedEntity[L, R]]

func LeftJoin added in v0.1.0 

func LeftJoin[L, R any](ctx context.Context, left Model[L], right Model[R], opts JoinOptions) Model[JoinedEntity[L, R]]

func NewModel added in v0.0.5 

func NewModel[T any](db *gorm.DB, opts ...ModelOption) Model[T]

NewModel returns a new Model.

type ModelOption added in v0.1.0 

type ModelOption func(*modelConfig)

func WithDBInitialFunc added in v0.1.0 

func WithDBInitialFunc(initial func(*gorm.DB) *gorm.DB) ModelOption

type OpJoinOption added in v0.1.0 

type OpJoinOption interface {
	GetLeftColumnName() ColumnName
	GetRightColumnName() ColumnName
	QueryOp() QueryOp
}

type OpOption added in v0.2.0 

type OpOption struct {
	mo.Either[OpJoinOption, OpQueryOption]
}

func NewOpJoinOption added in v0.1.0 

func NewOpJoinOption(left ColumnName, op QueryOp, right ColumnName) OpOption

func NewOpQueryOption 

func NewOpQueryOption[T any](name ColumnName, op QueryOp, v T) OpOption

func (OpOption) GetFilterOptionType added in v0.2.0 

func (opt OpOption) GetFilterOptionType() FilterOptionType

type OpQueryOption 

type OpQueryOption interface {
	Option
	FilterOption
	QueryOp() QueryOp
}

OpQueryOption represents a query which use the given query operator to search data.

type Option 

type Option interface {
	ColumnNameGetter
	// GetValue returns the value the option carries. It is used by the operation to query or update the target column.
	GetValue() any
}

Option wraps basic methods of options.

type PtrColumn 

type PtrColumn[T any] struct {
	// contains filtered or unexported fields
}

PtrColumn is used when declaring models with pointer fields, for example: 

type Model struct{
	Name PtrColumn[string]
}

equals to 

type Model struct{
	Name *string
}

func NewPtrColumn 

func NewPtrColumn[T any](v T) PtrColumn[T]

NewPtrColumn creates a new PtrColumn of type T.

func (PtrColumn) EQ added in v0.2.0 

func (c PtrColumn) EQ(value any) OpOption

func (PtrColumn[T]) FuzzyIn added in v0.2.0 

func (c PtrColumn[T]) FuzzyIn(values []T) FuzzyQueryOption

func (PtrColumn) GT added in v0.2.0 

func (c PtrColumn) GT(value any) OpOption

func (PtrColumn) GTE added in v0.2.0 

func (c PtrColumn) GTE(value any) OpOption

func (PtrColumn[T]) In added in v0.2.0 

func (c PtrColumn[T]) In(values []T) RangeQueryOption

func (PtrColumn) LT added in v0.2.0 

func (c PtrColumn) LT(value any) OpOption

func (PtrColumn) LTE added in v0.2.0 

func (c PtrColumn) LTE(value any) OpOption

func (PtrColumn) NE added in v0.2.0 

func (c PtrColumn) NE(value any) OpOption

func (PtrColumn[T]) NotIn added in v0.2.0 

func (c PtrColumn[T]) NotIn(values []T) RangeQueryOption

func (PtrColumn) Update added in v0.2.0 

func (c PtrColumn) Update(value any) UpdateOption

type QueryOp 

type QueryOp string
const (
	OpEq  QueryOp = "="
	OpNe  QueryOp = "!="
	OpGt  QueryOp = ">"
	OpLt  QueryOp = "<"
	OpGte QueryOp = ">="
	OpLte QueryOp = "<="
)

type RangeQueryOption 

type RangeQueryOption interface {
	ValuesOption
	FilterOption
	Exclude() bool
}

RangeQueryOption represents a query that find data from a given range of values.

func NewRangeQueryOption 

func NewRangeQueryOption[T any](name ColumnName, values []T, exclude bool) RangeQueryOption

type SortOption 

type SortOption interface {
	ColumnNameGetter
	GetSortOrder() SortOrder
}

SortOption represents an sort operation.

func NewSortOption 

func NewSortOption(name ColumnName, order SortOrder) SortOption

type SortOrder 

type SortOrder string
const (
	SortOrderAscending  SortOrder = "asc"
	SortOrderDescending SortOrder = "desc"
)

type TransactionFunc 

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

A TransactionFunc starts a transaction.

func NewTransactionFunc added in v0.0.5 

func NewTransactionFunc(db *gorm.DB) TransactionFunc

NewTransactionFunc returns a TransactionFunc.

type UpdateOption 

type UpdateOption interface {
	Option
}

UpdateOption represents an update operation that updates the target column with given value.

func NewUpdateOption 

func NewUpdateOption[T any](name ColumnName, value T) UpdateOption

type ValuesOption 

type ValuesOption interface {
	ColumnNameGetter
	// GetValues returns the values the option carries. Those values are used to query data.
	GetValues() []any
}

ValuesOption wraps basic method of options which carry multiple values.

Source Files

View all Source files

Directories

Path Synopsis
internal
sql
Package sql provides a generic interface around SQL (or SQL-like) databases.
 Package sql provides a generic interface around SQL (or SQL-like) databases.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.