mysql

README

mysql

A library wrapped on top of gorm, with added features such as link tracing, paging queries, etc.

Example of use

Initializing the connection

    var dsn = "root:123456@(192.168.1.6:3306)/test?charset=utf8mb4&parseTime=True&loc=Local"

    // (1) connect to the database using the default settings
    db, err := mysql.Init(dsn)

    // (2) customised settings to connect to the database
	db, err := Init(
		dsn,
		//WithLog(), // print all logs
		WithSlowThreshold(time.Millisecond*100), // only print logs that take longer than 100 milliseconds to execute
		WithEnableTrace(),                       // enable tracing
		WithMaxIdleConns(5),
		WithMaxOpenConns(50),
		WithConnMaxLifetime(time.Minute*3),
	)

Model

package model

import (
	"github.com/zhufuyi/sponge/pkg/mysql"
)

// UserExample object fields mapping table
type UserExample struct {
	mysql.Model `gorm:"embedded"`

	Name   string `gorm:"type:varchar(40);unique_index;not null" json:"name"`
	Age    int    `gorm:"not null" json:"age"`
	Gender string `gorm:"type:varchar(10);not null" json:"gender"`
}

// TableName get table name
func (table *UserExample) TableName() string {
	return mysql.GetTableName(table)
}

Transaction

func createUser() error {
	// note that you should use tx as the database handle when you are in a transaction
	tx := db.Begin()
	defer func() {
		if err := recover(); err != nil { // rollback after a panic during transaction execution
			tx.Rollback()
			fmt.Printf("transaction failed, err = %v\n", err)
		}
	}()

	var err error
	if err = tx.Error; err != nil {
		return err
	}

	if err = tx.Where("id = ?", 1).First(table).Error; err != nil {
		tx.Rollback()
		return err
	}

	panic("mock panic")

	if err = tx.Create(&userExample{Name: "Mr Li", Age: table.Age + 2, Gender: "male"}).Error; err != nil {
		tx.Rollback()
		return err
	}

	return tx.Commit().Error
}

gorm User Guide

• https://gorm.io/zh_CN/docs/index.html
• https://learnku.com/docs/gorm/v2

Documentation

Index

• func Count(ctx context.Context, db *gorm.DB, table interface{}, query interface{}, ...) (int64, error)
• func Create(ctx context.Context, db *gorm.DB, table interface{}) error
• func Delete(ctx context.Context, db *gorm.DB, table interface{}, query interface{}, ...) error
• func DeleteByID(ctx context.Context, db *gorm.DB, table interface{}, id interface{}) error
• func Get(ctx context.Context, db *gorm.DB, table interface{}, query interface{}, ...) error
• func GetByID(ctx context.Context, db *gorm.DB, table interface{}, id interface{}) error
• func GetTableName(object interface{}) string
• func Init(dns string, opts ...Option) (*gorm.DB, error)
• func List(ctx context.Context, db *gorm.DB, tables interface{}, page *query.Page, ...) error
• func TableName(table interface{}) string
• func Update(ctx context.Context, db *gorm.DB, table interface{}, column string, ...) error
• func Updates(ctx context.Context, db *gorm.DB, table interface{}, update KV, ...) error
• type KV
• type Model
• type Option
  • func WithConnMaxLifetime(t time.Duration) Option
  • func WithEnableForeignKey() Option
  • func WithEnableTrace() Option
  • func WithLog() Option
  • func WithMaxIdleConns(size int) Option
  • func WithMaxOpenConns(size int) Option
  • func WithSlowThreshold(d time.Duration) Option

Functions

func Count

func Count(ctx context.Context, db *gorm.DB, table interface{}, query interface{}, args ...interface{}) (int64, error)

Count number of records the param of 'table' must be pointer, eg: &StructName

func Create

func Create(ctx context.Context, db *gorm.DB, table interface{}) error

Create a new record the param of 'table' must be pointer, eg: &StructName

func Delete

func Delete(ctx context.Context, db *gorm.DB, table interface{}, query interface{}, args ...interface{}) error

Delete record the param of 'table' must be pointer, eg: &StructName

func DeleteByID

func DeleteByID(ctx context.Context, db *gorm.DB, table interface{}, id interface{}) error

DeleteByID delete record by id the param of 'table' must be pointer, eg: &StructName

func Get

func Get(ctx context.Context, db *gorm.DB, table interface{}, query interface{}, args ...interface{}) error

Get one record the param of 'table' must be pointer, eg: &StructName

func GetByID

func GetByID(ctx context.Context, db *gorm.DB, table interface{}, id interface{}) error

GetByID get record by id

func GetTableName

func GetTableName(object interface{}) string

GetTableName get table name

func Init

func Init(dns string, opts ...Option) (*gorm.DB, error)

Init mysql

func List

func List(ctx context.Context, db *gorm.DB, tables interface{}, page *query.Page, query interface{}, args ...interface{}) error

List multiple records, starting from page 0 the param of 'tables' must be slice, eg: []StructName

func TableName

func TableName(table interface{}) string

TableName get table name

func Update

func Update(ctx context.Context, db *gorm.DB, table interface{}, column string, value interface{}, query interface{}, args ...interface{}) error

Update record the param of 'table' must be pointer, eg: &StructName

func Updates

func Updates(ctx context.Context, db *gorm.DB, table interface{}, update KV, query interface{}, args ...interface{}) error

Updates record the param of 'table' must be pointer, eg: &StructName

Types

type KV

type KV = map[string]interface{}

KV map type

type Model

type Model struct {
	ID        uint64         `gorm:"column:id;AUTO_INCREMENT;primary_key" json:"id"`
	CreatedAt time.Time      `gorm:"column:created_at" json:"created_at"`
	UpdatedAt time.Time      `gorm:"column:updated_at" json:"updated_at"`
	DeletedAt gorm.DeletedAt `gorm:"column:deleted_at;index" json:"-"`
}

Model embedded structs, add `gorm: "embedded"` when defining table structs

type Option

type Option func(*options)

Option set the mysql options.

func WithConnMaxLifetime

func WithConnMaxLifetime(t time.Duration) Option

WithConnMaxLifetime set conn max lifetime

func WithEnableForeignKey

func WithEnableForeignKey() Option

WithEnableForeignKey use foreign keys

func WithEnableTrace

func WithEnableTrace() Option

WithEnableTrace use trace

func WithLog

func WithLog() Option

WithLog set log sql

func WithMaxIdleConns

func WithMaxIdleConns(size int) Option

WithMaxIdleConns set max idle conns

func WithMaxOpenConns

func WithMaxOpenConns(size int) Option

WithMaxOpenConns set max open conns

func WithSlowThreshold

func WithSlowThreshold(d time.Duration) Option

WithSlowThreshold Set sql values greater than the threshold