mysql

README

mysql

mysql library wrapped in gorm, with added features such as tracer, paging queries, etc.

Example of use

Initializing the connection

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

    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 := mysql.Init(
        dsn,
        mysql.WithLogging(logger.Get()),  // print log
        mysql.WithLogRequestIDKey("request_id"),  // print request_id
        mysql.WithMaxIdleConns(5),
        mysql.WithMaxOpenConns(50),
        mysql.WithConnMaxLifetime(time.Minute*3),
        // mysql.WithSlowThreshold(time.Millisecond*100),  // only print logs that take longer than 100 milliseconds to execute
        // mysql.WithEnableTrace(),  // enable tracing
        // mysql.WithRWSeparation(SlavesDsn, MastersDsn...)  // read-write separation
        // mysql.WithGormPlugin(yourPlugin)  // custom gorm plugin
    )

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

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

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

Documentation

Overview

Package mysql is a library wrapped on top of gorm.io/gorm, with added features such as link tracing, paging queries, etc.

Index

• func Count(ctx context.Context, db *gorm.DB, table interface{}, ...) (int64, error)
• func Create(ctx context.Context, db *gorm.DB, table interface{}) error
• func Delete(ctx context.Context, db *gorm.DB, table 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{}, ...) 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 NewCustomGormLogger(o *options) logger.Interface
• 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 Model2
• type Option
  • func WithConnMaxLifetime(t time.Duration) Option
  • func WithEnableForeignKey() Option
  • func WithEnableTrace() Option
  • func WithGormPlugin(plugins ...gorm.Plugin) Option
  • func WithLog() Option
  • func WithLogRequestIDKey(key string) Option
  • func WithLogging(l *zap.Logger, level ...logger.LogLevel) Option
  • func WithMaxIdleConns(size int) Option
  • func WithMaxOpenConns(size int) Option
  • func WithRWSeparation(slavesDsn []string, mastersDsn ...string) Option
  • func WithSlowThreshold(d time.Duration) Option

Functions

func Count

func Count(ctx context.Context, db *gorm.DB, table interface{}, queryCondition 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{}, queryCondition 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{}, queryCondition 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 Deprecated: will be moved to package pkg/gorm InitMysql

func List

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

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

func NewCustomGormLogger

func NewCustomGormLogger(o *options) logger.Interface

NewCustomGormLogger custom gorm logger

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{}, queryCondition 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, queryCondition 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:"createdAt"`
	UpdatedAt time.Time      `gorm:"column:updated_at" json:"updatedAt"`
	DeletedAt gorm.DeletedAt `gorm:"column:deleted_at;index" json:"-"`
}

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

type Model2

type Model2 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:"-"`
}

Model2 embedded structs, json tag named is snake case

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 WithGormPlugin

func WithGormPlugin(plugins ...gorm.Plugin) Option

WithGormPlugin setting gorm plugin

func WithLog

func WithLog() Option

WithLog set log sql Deprecated: will be replaced by WithLogging

func WithLogRequestIDKey

func WithLogRequestIDKey(key string) Option

WithLogRequestIDKey log request id

func WithLogging

func WithLogging(l *zap.Logger, level ...logger.LogLevel) Option

WithLogging set log sql, If l=nil, the gorm log library will be used

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 WithRWSeparation

func WithRWSeparation(slavesDsn []string, mastersDsn ...string) Option

WithRWSeparation setting read-write separation

func WithSlowThreshold

func WithSlowThreshold(d time.Duration) Option

WithSlowThreshold Set sql values greater than the threshold