sqly

package module
v1.3.3 Latest Latest
Warning

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

 Go to latest Published: Feb 24, 2021 License: MIT Imports: 11 Imported by: 0

README

sqly

sqly 是基于 golang s数据库操作的标准包 database/sql 的扩展。

Build Status Go Report Coverage Status

主要目标（功能)：

  • 是实现类似于 json.Unmarshal 类似的功能，将数据库查询结果反射成为 struct 对象。 简化 database/sql 原生的 span 书写方法。

  • 通过回调函数的形式封装了事务操作，简化原生包关于事务逻辑的操作

  • 封装了原生 database/sql 包不具有的, 更新（Update), 插入(Insert), 删除（DELETE), 通用sql 执行(Exec) 等方法

使用

安装依赖

go get github.com/FeifeiyuM/sqly

连接数据库

连接配置 func New(opt *sqly.Option) (*SqlY, error)

    opt := &sqly.Option{
		Dsn:             "test:mysql123@tcp(127.0.0.1:3306)/test_db?charset=utf8mb4&collation=utf8mb4_unicode_ci&parseTime=true&loc=Local",
		DriverName:      "mysql",
		MaxIdleConns:    0,
		MaxOpenConns:    0,
		ConnMaxLifeTime: 0,
	}
    db, err := sqly.New(opt)
	if err != nil {
		fmt.Println("test error")
	}
    // 数据库连接成功

Dsn: 格式化的数据库服务访问参数 例如：mysql 格式化方式如下 [username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]

DriverName: 使用的数据库驱动类型 例如： mysql, postgres, sqlite3 等

MaxIdleConns: 最大空闲连接数

MaxOpenConns: 最大连接池大小

ConnMaxLifeTime: 连接的生命周期

详细配置可以查看 【Go database/sql tutorial](http://go-database-sql.org/connection-pool.html), go-sql-driver/mysql 等。

数据库操作
  • 通用执行操作, 执行一次命令（包括查询、删除、更新、插入, 建表等）

func (s *SqlY) Exec(query string, args ...interface{}) (*Affected, error)

func (s *SqlY) ExecCtx(ctx context.Context, query string, args ...interface{}) (*Affected, error)

    // 创建表
    query := "CREATE TABLE `account` (" +
    		"`id` int(10) unsigned NOT NULL AUTO_INCREMENT," +
    		"`nickname` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL," +
    		"`avatar` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'avatar url'," +
    		"`mobile` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'mobile number'," +
    		"`email` varchar(320) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'email'," +
    		"`password` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'password'," +
    		"`role` tinyint(4) DEFAULT NULL COMMENT 'role'," +
    		"`expire_time` datetime DEFAULT NULL COMMENT 'expire_time'," +
    		"`is_valid` tinyint(4) DEFAULT NULL COMMENT 'is_valid'," +
    		"`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP," +
    		"PRIMARY KEY (`id`)," +
    		"UNIQUE KEY `mobile_index` (`mobile`)," +
    		"KEY `email_index` (`email`)" +
    		") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;"
    _, err = db.Exec(query)
    if err != nil {
    	fmt.Println("create table error")
    }
  • 插入一条数据

func (s *SqlY) Insert(query string, args ...interface{}) (*Affected, error) func (s *SqlY) InsertCtx(ctx context.Context, query string, args ...interface{}) (*Affected, error)

    query := "INSERT INTO `account` (`nickname`, `mobile`, `email`, `role`) VALUES (?, ?, ?, ?);"
	aff, err := db.Insert(query, "nick_test3", "18812311235", "test@foxmail.com", 1)
	if err != nil {
		fmt.Println("failed to insert data")
	}
    if aff != nil {
        fmt.Printf("auto_id: %v, affected_rows: %v\n", aff.LastId, aff.RowsAffected)
    }
   // Affected 结构体返回的值，不保证值(LastId, RowsAffected)不为空，根据不同数据库和其对应的驱动确定
   // lastId 表示最后一条插入数据对应有数据生成的一个数字id(自增id), 
   // RowsAffected 表示 update, insert, or delete 操作影响的行数。
  • 插入多条数据

func (s *SqlY) InsertMany(query string, args [][]interface{}) (*Affected, error)

func (s *SqlY) InsertManyCtx(ctx context.Context, query string, args [][]interface{}) (*Affected, error)

    query := "INSERT INTO `account` (`nickname`, `mobile`, `email`, `role`) VALUES (?, ?, ?, ?);"
    var vals = [][]interface{}{
        {"testq1", "18112342345", "testq1@foxmail.com", 1},
        {"testq2", "18112342346", "testq2@foxmail.com", nil},
    }
    aff, err = db.InsertMany(query, vals)
    if err != nil {
        fmt.Sprintln("create account error")
    }
    if err != nil {
        fmt.Sprintln("create accounts error")
    }
    fmt.Println(aff)
  • 更新一条数据

func (s *SqlY) Update(query string, args ...interface{}) (*Affected, error)

func (s *SqlY) ExecCtx(ctx context.Context, query string, args ...interface{}) (*Affected, error)

    query := "UPDATE `account` SET `nickname`=? WHERE `mobile`=?;"
	aff, err := db.Update(query, "lucy", "18812311231")
	if err != nil {
		fmt.Sprintln("update accounts error")
	}
	fmt.Println(aff)
  • 执行多条更新语句

func (s *SqlY) UpdateMany(query string, args [][]interface{}) (*Affected, error) func (s *SqlY) UpdateManyCtx(ctx context.Context, query string, args [][]interface{}) (*Affected, error)

    query = "UPDATE `account` SET `password`=? WHERE `id`=?"
	var params [][]interface{}
	for _, id := range ids {
		hash := sha1.New()
		_, _ = hash.Write([]byte(strconv.FormatInt(id.ID, 10)))
		passwd := hex.EncodeToString(hash.Sum(nil))
		params = append(params, []interface{}{passwd, id.ID})
	}
	_, err := db.UpdateMany(query, params)
	if err != nil {
		t.Error(err)
		return
	}
  • 删除操作

func (s *SqlY) Delete(query string, args ...interface{}) (*Affected, error)

func (s *SqlY) DeleteCtx(ctx context.Context, query string, args ...interface{}) (*Affected, error)

    query := "DELETE FROM `account` WHERE `mobile`=?;"
	aff, err := db.Delete(query, "18812311231")
	if err != nil {
		fmt.Sprintln("delete account error")
	}
	fmt.Println(aff)
  • 查询一条数据

func (s *SqlY) Get(dest interface{}, query string, args ...interface{}) error

func (s *SqlY) GetCtx(ctx context.Context, dest interface{}, query string, args ...interface{}) error

	type Account struct {
		ID         int64      `sql:"id" json:"id"`
		Nickname   string     `sql:"nickname" json:"nickname"`
		Avatar     sqly.NullString `sql:"avatar" json:"avatar"`
		Email      string     `sql:"email" json:"email"`
		Mobile     string     `sql:"mobile" json:"mobile"`
		Role       sqly.NullInt32     `sql:"role" json:"role"`
		Password   string     `sql:"password" json:"password"`
		ExpireTime sqly.NullTime `sql:"expire_time" json:"expire_time"`
		IsValid sqly.NullBool `sql:"is_valid" json:"is_valid"`
		CreateTime time.Time  `sql:"create_time" json:"create_time"`
	}
	acc := new(Account)
	query = "SELECT * FROM `account` WHERE `mobile`=?"
	err = db.Get(acc, query, "18812311235")
	if err != nil {
		fmt.Println("query account error")
	}
	accStr, err := json.Marshal(acc1)
	if err != nil {
		fmt.Println("marshal acc error")
	}
    fmt.Println(accStr)

参数 dest 必须为实例化的 struct 对象指针

  • 查询数据

func (s *SqlY) Query(dest interface{}, query string, args ...interface{}) error

func (s *SqlY) QueryCtx(ctx context.Context, dest interface{}, query string, args ...interface{}) error

    type Account struct {
        ID         int64      `sql:"id" json:"id"`
        Nickname   string     `sql:"nickname" json:"nickname"`
        Avatar     sqly.NullString `sql:"avatar" json:"avatar"`
        Email      string     `sql:"email" json:"email"`
        Mobile     string     `sql:"mobile" json:"mobile"`
        Role       sqly.NullInt32     `sql:"role" json:"role"`
        Password   string     `sql:"password" json:"password"`
        ExpireTime sqly.NullTime `sql:"expire_time" json:"expire_time"`
        IsValid sqly.NullBool `sql:"is_valid" json:"is_valid"`
        CreateTime time.Time  `sql:"create_time" json:"create_time"`
    }

    query = "SELECT * FROM `account` WHERE `mobile` IN ?"
	var mobiles = []string{"18812311235", "18112342346"}
	var accs []*Account  // 必须是 struct array
	err = db.Query(&accs, query, mobiles)
	if err != nil {
		fmt.Printf("query accounts error")
	}
	accsStr, err := json.Marshal(accs)
	if err != nil {
		fmt.Println("marshal acc error")
	}
    fmt.Println(accsStr)

参数 dest 必须为实例化的 struct 对象(或对象指针)数组的指针

数据库事务
  • 事务开启 提交，回滚

func (s *SqlY) NewTrans() (*Trans, error) 开启

  • 事务提交

func (t *Trans) Commit() error 提交

  • 事务回滚

func (t *Trans) Rollback() error 回滚

  • 事务通用执行

func (t *Trans) Exec(query string, args ...interface{}) (*Affected, error)

func (t *Trans) ExecCtx(ctx context.Context, query string, args ...interface{}) (*Affected, error)

  • 事务插入

func (t *Trans) Insert(query string, args ...interface{}) (*Affected, error)

func (t *Trans) InsertCtx(ctx context.Context, query string, args ...interface{}) (*Affected, error)

  • 事务插入多条

func (t *Trans) InsertMany(query string, args [][]interface{}) (*Affected, error)

func (t *Trans) InsertManyCtx(ctx context.Context, query string, args [][]interface{}) (*Affected, error)

  • 事务更新

func (t *Trans) Update(query string, args ...interface{}) (*Affected, error)

func (t *Trans) UpdateCtx(ctx context.Context, query string, args ...interface{}) (*Affected, error)

  • 事务更新多条

func (t *Trans) UpdateMany(query string, args [][]interface{}) (*Affected, error) func (t *Trans) UpdateManyCtx(ctx context.Context, query string, args [][]interface{}) (*Affected, error)

  • 事务删除

func (t *Trans) Delete(query string, args ...interface{}) (*Affected, error)

func (t *Trans) DeleteCtx(ctx context.Context, query string, args ...interface{}) (*Affected, error)

  • 事务查询单条

func (t *Trans) Get(dest interface{}, query string, args ...interface{}) error

func (t *Trans) GetCtx(ctx context.Context, dest interface{}, query string, args ...interface{}) error 参数 dest 必须为实例化的 struct 对象指针

  • 事务查询

func (t *Trans) Query(dest interface{}, query string, args ...interface{}) error

func (t *Trans) QueryCtx(ctx context.Context, dest interface{}, query string, args ...interface{}) error 参数 dest 必须为实例化的 struct 对象(或对象指针)数组的指针

    ctx := context.TODO()
    db, err := sqly.New(opt)
	if err != nil {
		fmt.Println("test error")
	}
    // 开始事务
	tx, err := sy.NewTrans()
    if err != nil {
		fmt.Printf("failed to begin transaction")
		return
	}
    // 回滚
	defer func() {
		_ = tx.Rollback()
    }()
    
    type Account struct {
        ID         int64      `sql:"id" json:"id"`
        Nickname   string     `sql:"nickname" json:"nickname"`
        Avatar     sqly.NullString `sql:"avatar" json:"avatar"`
        Email      string     `sql:"email" json:"email"`
        Mobile     string     `sql:"mobile" json:"mobile"`
        Role       sqly.NullInt32     `sql:"role" json:"role"`
        Password   string     `sql:"password" json:"password"`
        ExpireTime sqly.NullTime `sql:"expire_time" json:"expire_time"`
        IsValid sqly.NullBool `sql:"is_valid" json:"is_valid"`
        CreateTime time.Time  `sql:"create_time" json:"create_time"`
    }
    // 执行事务
    // 查
    acc := new(Account)
	query = "SELECT * FROM `account` WHERE `mobile`=?"
	err = tx.GetCtx(ctx, acc, query, "18812311235")
	if err != nil {
		fmt.Printf("get accout error")
		return
	}
    // 更新
    query = "UPDATE `account` SET `is_valid`=? WHERE id=?"
	aff, err := tx.UpdateCtx(ctx, query, true, acc.ID)
	if err != nil {
		fmt.Println("update account error")
	}
    fmt.Println(aff)
    // 删除
    query = "DELETE FROM `account` WHERE id!=?"
    _, err = tx.DeleteCtx(ctx, query, acc.ID)
    if err != nil {
    	fmt.Println("delete accounts error")
    }
    // 插入
    query = "INSERT INTO `account` (`nickname`, `mobile`, `email`, `role`) VALUES (?, ?, ?, ?);"
	aff, err = tx.InsertCtx(ctx, query, "nick_ruby", "13565656789", nil)
	if err != nil {
		fmt.Println("insert account error")
	}
    fmt.Println(aff)
    // 提交
	_ = tx.Commit()
  • 事务封装(封装事务开启，关闭，回滚操作)

type TxFunc func(tx *Trans) (interface{}, error)

func (s *SqlY) Transaction(txFunc TxFunc) (interface{}, error)

    ctx := context.TODO()
    db, err := sqly.New(opt)
    if err != nil {
    	fmt.Println("test error")
    }
    res, err := db.Transaction(func(tx *sqly.Trans) (i interface{}, e error) {
        // 不需要手动开启，关闭，回滚事务
        // 查
        acc := new(Account)
        query = "SELECT * FROM `account` WHERE `mobile`=?"
        err = tx.GetCtx(ctx, acc, query, "18812311235")
        if err != nil {
            fmt.Printf("get accout error")
            return
        }
        // 更新
        query = "UPDATE `account` SET `is_valid`=? WHERE id=?"
        aff, err := tx.UpdateCtx(ctx, query, true, acc.ID)
        if err != nil {
            fmt.Println("update account error")
        }
        fmt.Println(aff)
        // 删除
        query = "DELETE FROM `account` WHERE id!=?"
        _, err = tx.DeleteCtx(ctx, query, acc.ID)
        if err != nil {
            fmt.Println("delete accounts error")
        }
        // 插入
        query = "INSERT INTO `account` (`nickname`, `mobile`, `email`, `role`) VALUES (?, ?, ?, ?);"
        aff, err = tx.InsertCtx(ctx, query, "nick_ruby", "13565656789", nil)
        if err != nil {
            fmt.Println("insert account error")
        }
        fmt.Println(aff)
    })
    if err := nil {
        fmt.Println("do transaction error")
    }   
    fmt.Println(res)
胶囊操作

在执行事务操作的时候，我们需要显式得去初始化和传递事务句柄 tx, 常常不注意就会出现事务和非事务操作混用的问题，严重时还会出现查询连接池耗尽产生死锁（在事务内，申请非事务查询线程，在高并发的时候会尝试该死锁）
为了减少在开发过程中减少对事务和非事务的关注，sqly 采用回调函数的方式封装一系列数据库操作，并采用 context 的方式在函数之间传递事务句柄，只需要在初始化的时候确认是否开始事务。

胶囊操作相关方法
  • 初始化胶囊句柄

func NewCapsule(sqlY *SqlY) *Capsule

  • 开启胶囊操作

type CapFunc func(ctx context.Context) (interface{}, error)
func (c *Capsule) StartCapsule(ctx context.Context, isTrans bool, capFunc CapFunc) (interface{}, error)
StartCapsule 开启胶囊，参数 ctx 上下文用于携带胶囊句柄，isTrans 是否开始事务 true 开启。 CapFunc 回调函数，所有逻辑都在该回调内实现

  • 通用执行

func (c *Capsule) Exec(ctx context.Context, query string, args ...interface{}) (*Affected, error)

  • 插入

func (c *Capsule) Insert(ctx context.Context, query string, args ...interface{}) (*Affected, error)

  • 插入多条

func (c *Capsule) InsertMany(ctx context.Context, query string, args [][]interface{}) (*Affected, error)

  • 更新

func (c *Capsule) Update(ctx context.Context, query string, args ...interface{}) (*Affected, error)

  • 更新多条

func (c *Capsule) UpdateMany(ctx context.Context, query string, args [][]interface{}) (*Affected, error)

  • 删除

func (c *Capsule) Delete(ctx context.Context, query string, args ...interface{}) (*Affected, error)

  • 查询单条数据

func (c *Capsule) Get(ctx context.Context, dest interface{}, query string, args ...interface{}) error 参数 dest 必须为实例化的 struct 对象指针

  • 查询

func (c *Capsule) Query(ctx context.Context, dest interface{}, query string, args ...interface{}) error 参数 dest 必须为实例化的 struct 对象(或对象指针)数组的指针

tips: 以上 sql 操作会根据开启胶囊时是否开启事务（isTrans) 设置来自动选择采用事务查询，还是非事务查询。

胶囊例1、事务操作

func TestCapsule_trans2(t *testing.T) {
	db, err := New(opt)  // 初始化sqly(数据库连接）
	if err != nil {
		t.Error(err)
	}
	capsule := NewCapsule(db)  // 创建一个胶囊句柄
	ctx := context.TODO()  // 胶囊查询必须携带 context 参数
    // isTrans=true 开始事务查询
	ret, err := capsule.StartCapsule(ctx, true, func(ctx context.Context) (interface{}, error) {
        // 在回调函数内执行相关数据库操作
		var accs []*Account
		query := "SELECT `id`, `nickname`, `avatar`, `email`, `mobile`, `password`, `role` " +
			"FROM `account`"
		err := capsule.Query(ctx, &accs, query, "18812311232")  // 执行查询
		if err != nil {
			return nil, err
		}
		query = "UPDATE `account` SET `nickname`=? WHERE `id`=?" 
		_, err = capsule.Update(ctx, query, "nick_trans2", accs[0].ID)  // 更新
		if err != nil {
			return nil, err
		}
		query = "UPDATE `account` SET `avatar`=? WHERE `id`=?"
		aff, err := capsule.Update(ctx, query, "test2.png", accs[1].ID) // 更新
		if err != nil {
			return nil, err
		}
		query = "INSERT INTO `account` (`nickname`, `mobile`, `email`, `role`) " +
			"VALUES (?, ?, ?, ?);"
		aff, err = capsule.Insert(ctx, query, "nick_test2", "18712311235", "testx1@foxmail.com", 1)  // 插入
		if err != nil {
			t.Error(err)
		}
		if aff != nil {
			return nil, errors.New("error")
		}
		return aff, nil
	})
	if err.Error() != "error" {
		t.Error(err)
	}
	fmt.Sprintln(ret)
}

胶囊例2、非事务操作

func TestCapsule_raw(t *testing.T) {
	db, err := New(opt)  // 初始化sqly(数据库连接）
	if err != nil {
		t.Error(err)
	}
	capsule := NewCapsule(db)  // 创建一个胶囊句柄
	ctx := context.TODO()  // 胶囊查询必须携带 context 参数
    // isTrans=false 不开启事务
	ret, err := capsule.StartCapsule(ctx, false, func(ctx context.Context) (interface{}, error) {
        // 在回调函数内执行相关数据库操作
		var accs []*Account
		query := "SELECT `id`, `nickname`, `avatar`, `email`, `mobile`, `password`, `role` " +
			"FROM `account`"
		err := capsule.Query(ctx, &accs, query, "18812311232")  // 查询
		if err != nil {
			return nil, err
		}
		query = "UPDATE `account` SET `nickname`=? WHERE `id`=?"
		_, err = capsule.Update(ctx, query, "nick_trans3", accs[0].ID) // 更新
		if err != nil {
			return nil, err
		}
		query = "UPDATE `account` SET `avatar`=? WHERE `id`=?"
		aff, err := capsule.Update(ctx, query, "test3.png", accs[1].ID)  // 更新
		if err != nil {
			return nil, err
		}
		query = "INSERT INTO `account` (`nickname`, `mobile`, `email`, `role`) " +
			"VALUES (?, ?, ?, ?);"
		aff, err = capsule.Insert(ctx, query, "nick_test3", "18712311235", "testx1@foxmail.com", 1)  // 插入
		if err != nil {
			t.Error(err)
		}
		if aff != nil {
			return nil, errors.New("error")
		}
		return aff, nil
	})
	if err.Error() != "error" {
		t.Error(err)
	}
	fmt.Sprintln(ret)
}

从胶囊例1和胶囊例2中可以发现，采用胶囊操作时，事务操作和非事务操作区别仅在 StartCapsule 中决定是否开始事务，在实现业务逻辑的查询，更新查询等操作的过程中都无需关注是否开启事务。

支持类型

  • struct 中定义的字段类型须是 database/sql 中能够被 Scan 的类型 (int64, float64, bool, []byte, string, time.Time, nil)

  • 为了支持更好为空(NULL)的字段，sqly 扩展了 sql.NullTime, sql.NullBool, sql.NullFloat64, sql.NullInt64, sql.NullInt32, sql.NullString, 分别为 sqly.NullTime, sqly.NullBool, sqly.NullFloat64, sqly.NullInt64, sqly.NullInt32, sqly.NullString。

  • 使用 sqly 扩展的空字段类型，对象在使用 json.Marshal 时 对应字段为空的会自动解析为 null; json 字符串使用 json.UnMarshal 时，会自动解析为对应的 sqly.NullTime 等扩展类型

  • 如果使用 tinyint 或 int 类表示 bool 字段类型，例如：0 为 false, 1或其它为 true, 在定义字段类型时，可以使用 sqly.Boolean 类型来支持，在 scan 的时候会字段将 int 类型转换成 bool, 如果值只有 0 或 1 可以使用原生 bool

  • struct 嵌套支持

    db, err := New(opt)
	if err != nil {
		return
	}
	type Contact struct {
		Email  string `sql:"email" json:"email"`
		Mobile string `sql:"mobile" json:"mobile"`
	}
	type Base struct {
		Contact  Contact    `json:"contact"`
		Nickname string     `sql:"nickname" json:"nickname"`
		Avatar   NullString `sql:"avatar" json:"avatar"`
	}
	type Acc struct {
		ID         int64     `sql:"id" json:"id"`
		Role       NullInt32 `sql:"role" json:"role"`
		Base       Base      `json:"base"`
		Password   string    `sql:"password" json:"password"`
		IsValid    NullBool  `sql:"is_valid" json:"is_valid"`
        CreateTime time.Time `sql:"create_time" json:"create_time"`
	}
	var accs []*Acc
	query := "SELECT `id`, `avatar`, `email`, `mobile`, `nickname`, `password`, `role`, `create_time`, `is_valid` FROM `account`;"
	err = db.Query(&accs, query)
	if err != nil {
		fmt.Println("query account error")
        reutrn 
	}
	resStr, _ := json.Marshal(accs)
	fmt.Println(string(resStr))
  • map[string]inteface{} 类型支持(目前支持只 MySQL)
	db, err := New(opt)
	if err != nil {
		t.Error(err)
	}
	var accs []map[string]interface{}
	query := "SELECT * FROM `account`;"

	err = db.Query(&accs, query, nil)
	if err != nil {
		t.Error(err)
	}
	accStr, _ := json.Marshal(accs)
	fmt.Printf("rows %s", accStr)
  • 可scan 类型支持 int, int32, int64, string, time.Time, 空字段类型 (及其他们的数组结构)
    db, err := New(opt)
	if err != nil {
		t.Error(err)
	}
	query := "SELECT COUNT(*) FROM `account`;"
	var num int
	err = db.Get(&num, query)
	if err != nil {
		t.Error(err)
	}
	fmt.Println("num", num)

    db, err := New(opt)
	if err != nil {
		t.Error(err)
	}
	query := "SELECT `create_time` FROM `account` limit 1;"
	create := &NullTime{}
	err = db.Get(create, query)
	if err != nil {
		t.Error(err)
	}
	fmt.Println("create", create)

    db, err := New(opt)
	if err != nil {
		t.Error(err)
	}

	query := "SELECT `nickname` FROM `account` ORDER BY `id`;"
	var vals []string
	err = db.Query(&vals, query)
	if err != nil {
		t.Error(err)
	}
	fmt.Println(vals)
tips
  • 如果要使用 time.Time 的字段类型, 连接数据库的 dsn 配置中加上 parseTime=true

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var (
	// ErrQueryFmt sql statement format error
	ErrQueryFmt = errors.New("query can't be formatted")

	// ErrArgType sql statement format type error
	ErrArgType = errors.New("invalid variable type for argument")

	// ErrStatement sql syntax error
	ErrStatement = errors.New("sql statement syntax error")

	// ErrContainer  container for results
	ErrContainer = errors.New("invalid container for scanning (struct pointer, not nil)")

	// ErrFieldsMatch fields not match
	ErrFieldsMatch = errors.New("queried fields not match with struct fields")

	// ErrMultiRes multi result for get
	ErrMultiRes = errors.New("get more than one results for get query")

	// ErrEmpty empty
	ErrEmpty = errors.New("no result for get query ")

	// ErrCapsule Invalid Capsule
	ErrCapsule = errors.New("query capsule is not available")

	ErrEmptyArrayInStatement = errors.New("has empty array in query arguments")
)

errors

Functions

func QueryFmt 

func QueryFmt(fmtStr string, args ...interface{}) (string, error)

QueryFmt sql statement assemble public

Types

type Affected 

type Affected struct {
	LastId       int64
	RowsAffected int64
}

Affected to record lastId for insert, and affected rows for update, inserts, delete statement

type Boolean 

type Boolean bool

Boolean boolean

func (*Boolean) Scan 

func (b *Boolean) Scan(val interface{}) error

Scan boolean Scan

type CapFunc added in v1.3.0 

type CapFunc func(ctx context.Context) (interface{}, error)

CapFunc 胶囊闭包函数

type Capsule added in v1.3.0 

type Capsule struct {
	// contains filtered or unexported fields
}

Capsule 胶囊对象

func NewCapsule added in v1.3.0 

func NewCapsule(sqlY *SqlY) *Capsule

NewCapsule new capsule

func (*Capsule) Close added in v1.3.1 

func (c *Capsule) Close() error

Close close connection

func (*Capsule) Delete added in v1.3.0 

func (c *Capsule) Delete(ctx context.Context, query string, args ...interface{}) (*Affected, error)

Delete delete

func (*Capsule) Exec added in v1.3.0 

func (c *Capsule) Exec(ctx context.Context, query string, args ...interface{}) (*Affected, error)

Exec exec

func (*Capsule) ExecMany added in v1.3.0 

func (c *Capsule) ExecMany(ctx context.Context, queries []string) error

ExecMany exec multi queries

func (*Capsule) Get added in v1.3.0 

func (c *Capsule) Get(ctx context.Context, dest interface{}, query string, args ...interface{}) error

Get query one

func (*Capsule) Insert added in v1.3.0 

func (c *Capsule) Insert(ctx context.Context, query string, args ...interface{}) (*Affected, error)

Insert insert

func (*Capsule) InsertMany added in v1.3.0 

func (c *Capsule) InsertMany(ctx context.Context, query string, args [][]interface{}) (*Affected, error)

InsertMany insert many

func (*Capsule) IsTrans added in v1.3.1 

func (c *Capsule) IsTrans(ctx context.Context) (bool, error)

IsTrans is enable transaction

func (*Capsule) Query added in v1.3.0 

func (c *Capsule) Query(ctx context.Context, dest interface{}, query string, args ...interface{}) error

Query query

func (*Capsule) StartCapsule added in v1.3.0 

func (c *Capsule) StartCapsule(ctx context.Context, isTrans bool, capFunc CapFunc) (interface{}, error)

StartCapsule 开启查询胶囊

func (*Capsule) Update added in v1.3.0 

func (c *Capsule) Update(ctx context.Context, query string, args ...interface{}) (*Affected, error)

Update update

func (*Capsule) UpdateMany added in v1.3.0 

func (c *Capsule) UpdateMany(ctx context.Context, query string, args [][]interface{}) (*Affected, error)

UpdateMany update many

type NullBool 

type NullBool sql.NullBool

NullBool is an alias for sql.NullBool

func (*NullBool) MarshalJSON 

func (ns *NullBool) MarshalJSON() ([]byte, error)

MarshalJSON for NullBool

func (*NullBool) Scan 

func (ns *NullBool) Scan(val interface{}) error

Scan implements the Scanner interface for NullBool

func (*NullBool) UnmarshalJSON 

func (ns *NullBool) UnmarshalJSON(b []byte) error

UnmarshalJSON for NullBool

type NullFloat64 

type NullFloat64 sql.NullFloat64

NullFloat64 is an alias for sql.NullFloat64

func (*NullFloat64) MarshalJSON 

func (ns *NullFloat64) MarshalJSON() ([]byte, error)

MarshalJSON for NullFloat64

func (*NullFloat64) Scan 

func (ns *NullFloat64) Scan(val interface{}) error

Scan implements the Scanner interface for NullFloat64

func (*NullFloat64) UnmarshalJSON 

func (ns *NullFloat64) UnmarshalJSON(b []byte) error

UnmarshalJSON for NullFloat64

type NullInt32 

type NullInt32 sql.NullInt32

NullInt32 is an alias for sql.NullInt32

func (*NullInt32) MarshalJSON 

func (ns *NullInt32) MarshalJSON() ([]byte, error)

MarshalJSON for NullInt32

func (*NullInt32) Scan 

func (ns *NullInt32) Scan(val interface{}) error

Scan implements the Scanner interface for NullInt32

func (*NullInt32) UnmarshalJSON 

func (ns *NullInt32) UnmarshalJSON(b []byte) error

UnmarshalJSON for NullInt32

type NullInt64 

type NullInt64 sql.NullInt64

NullInt64 is an alias for sql.NullInt64

func (*NullInt64) MarshalJSON 

func (ns *NullInt64) MarshalJSON() ([]byte, error)

MarshalJSON for NullInt64

func (*NullInt64) Scan 

func (ns *NullInt64) Scan(val interface{}) error

Scan implements the Scanner interface for NullInt64

func (*NullInt64) UnmarshalJSON 

func (ns *NullInt64) UnmarshalJSON(b []byte) error

UnmarshalJSON for NullInt64

type NullString 

type NullString sql.NullString

NullString is an alias for sql.NullString

func (*NullString) MarshalJSON 

func (ns *NullString) MarshalJSON() ([]byte, error)

MarshalJSON for NullString

func (*NullString) Scan 

func (ns *NullString) Scan(val interface{}) error

Scan implements the Scanner interface for NullString

func (*NullString) UnmarshalJSON 

func (ns *NullString) UnmarshalJSON(b []byte) error

UnmarshalJSON for NullString

type NullTime 

type NullTime sql.NullTime

NullTime is an alias for sql.NullTime

func (*NullTime) MarshalJSON 

func (ns *NullTime) MarshalJSON() ([]byte, error)

MarshalJSON for NullTime

func (*NullTime) Scan 

func (ns *NullTime) Scan(val interface{}) error

Scan implements the Scanner interface for NullTime

func (*NullTime) UnmarshalJSON 

func (ns *NullTime) UnmarshalJSON(b []byte) error

UnmarshalJSON for NullTime

type Option 

type Option struct {
	Dsn             string        `json:"dsn"`                // database server name
	DriverName      string        `json:"driver_name"`        // database driver
	MaxIdleConns    int           `json:"max_idle_conns"`     // limit the number of idle connections
	MaxOpenConns    int           `json:"max_open_conns"`     // limit the number of total open connections
	ConnMaxLifeTime time.Duration `json:"conn_max_life_time"` // maximum amount of time a connection may be reused
}

Option sqly config option

type SqlY 

type SqlY struct {
	// contains filtered or unexported fields
}

SqlY struct

func New 

func New(opt *Option) (*SqlY, error)

New init SqlY to database

func (*SqlY) Close added in v1.1.3 

func (s *SqlY) Close() error

Close close connection

func (*SqlY) Delete 

func (s *SqlY) Delete(query string, args ...interface{}) (*Affected, error)

Delete delete item from database

func (*SqlY) DeleteCtx 

func (s *SqlY) DeleteCtx(ctx context.Context, query string, args ...interface{}) (*Affected, error)

DeleteCtx delete with context

func (*SqlY) Exec 

func (s *SqlY) Exec(query string, args ...interface{}) (*Affected, error)

Exec general sql statement execute

func (*SqlY) ExecCtx 

func (s *SqlY) ExecCtx(ctx context.Context, query string, args ...interface{}) (*Affected, error)

ExecCtx general sql statement execute with context

func (*SqlY) ExecMany 

func (s *SqlY) ExecMany(queries []string) error

ExecMany execute multi sql statement

func (*SqlY) ExecManyCtx 

func (s *SqlY) ExecManyCtx(ctx context.Context, queries []string) error

ExecManyCtx execute multi sql statement with context

func (*SqlY) Get 

func (s *SqlY) Get(dest interface{}, query string, args ...interface{}) error

Get query the database working with one result

func (*SqlY) GetCtx 

func (s *SqlY) GetCtx(ctx context.Context, dest interface{}, query string, args ...interface{}) error

GetCtx query the database working with one result

func (*SqlY) Insert 

func (s *SqlY) Insert(query string, args ...interface{}) (*Affected, error)

Insert insert into the database

func (*SqlY) InsertCtx 

func (s *SqlY) InsertCtx(ctx context.Context, query string, args ...interface{}) (*Affected, error)

InsertCtx insert with context

func (*SqlY) InsertMany 

func (s *SqlY) InsertMany(query string, args [][]interface{}) (*Affected, error)

InsertMany insert many values to database

func (*SqlY) InsertManyCtx 

func (s *SqlY) InsertManyCtx(ctx context.Context, query string, args [][]interface{}) (*Affected, error)

InsertManyCtx insert many with context

func (*SqlY) NewTrans 

func (s *SqlY) NewTrans() (*Trans, error)

NewTrans start transaction

func (*SqlY) Ping 

func (s *SqlY) Ping() error

Ping ping test

func (*SqlY) Query 

func (s *SqlY) Query(dest interface{}, query string, args ...interface{}) error

Query query the database working with results

func (*SqlY) QueryCtx 

func (s *SqlY) QueryCtx(ctx context.Context, dest interface{}, query string, args ...interface{}) error

QueryCtx query the database working with results

func (*SqlY) Transaction 

func (s *SqlY) Transaction(txFunc TxFunc) (interface{}, error)

Transaction start transaction with callback function

func (*SqlY) Update 

func (s *SqlY) Update(query string, args ...interface{}) (*Affected, error)

Update update value to database

func (*SqlY) UpdateCtx 

func (s *SqlY) UpdateCtx(ctx context.Context, query string, args ...interface{}) (*Affected, error)

UpdateCtx update with context

func (*SqlY) UpdateMany added in v1.2.1 

func (s *SqlY) UpdateMany(query string, args [][]interface{}) (*Affected, error)

UpdateMany update many

func (*SqlY) UpdateManyCtx added in v1.2.1 

func (s *SqlY) UpdateManyCtx(ctx context.Context, query string, args [][]interface{}) (*Affected, error)

UpdateManyCtx update many

type Trans 

type Trans struct {
	// contains filtered or unexported fields
}

Trans sql struct for transaction

func (*Trans) Commit 

func (t *Trans) Commit() error

Commit commit transaction

func (*Trans) Delete 

func (t *Trans) Delete(query string, args ...interface{}) (*Affected, error)

Delete delete

func (*Trans) DeleteCtx 

func (t *Trans) DeleteCtx(ctx context.Context, query string, args ...interface{}) (*Affected, error)

DeleteCtx delete

func (*Trans) Exec 

func (t *Trans) Exec(query string, args ...interface{}) (*Affected, error)

Exec general sql statement execute

func (*Trans) ExecCtx 

func (t *Trans) ExecCtx(ctx context.Context, query string, args ...interface{}) (*Affected, error)

ExecCtx general sql statement execute

func (*Trans) ExecMany 

func (t *Trans) ExecMany(queries []string) error

ExecMany execute multi sql statement

func (*Trans) ExecManyCtx 

func (t *Trans) ExecManyCtx(ctx context.Context, queries []string) error

ExecManyCtx execute multi sql statement

func (*Trans) Get 

func (t *Trans) Get(dest interface{}, query string, args ...interface{}) error

Get query one row

func (*Trans) GetCtx 

func (t *Trans) GetCtx(ctx context.Context, dest interface{}, query string, args ...interface{}) error

GetCtx query one row

func (*Trans) Insert 

func (t *Trans) Insert(query string, args ...interface{}) (*Affected, error)

Insert insert

func (*Trans) InsertCtx 

func (t *Trans) InsertCtx(ctx context.Context, query string, args ...interface{}) (*Affected, error)

InsertCtx insert

func (*Trans) InsertMany 

func (t *Trans) InsertMany(query string, args [][]interface{}) (*Affected, error)

InsertMany insert many rows

func (*Trans) InsertManyCtx 

func (t *Trans) InsertManyCtx(ctx context.Context, query string, args [][]interface{}) (*Affected, error)

InsertManyCtx insert many rows

func (*Trans) Query 

func (t *Trans) Query(dest interface{}, query string, args ...interface{}) error

Query query results

func (*Trans) QueryCtx 

func (t *Trans) QueryCtx(ctx context.Context, dest interface{}, query string, args ...interface{}) error

QueryCtx query results

func (*Trans) Rollback 

func (t *Trans) Rollback() error

Rollback abort transaction

func (*Trans) Update 

func (t *Trans) Update(query string, args ...interface{}) (*Affected, error)

Update update

func (*Trans) UpdateCtx 

func (t *Trans) UpdateCtx(ctx context.Context, query string, args ...interface{}) (*Affected, error)

UpdateCtx update

func (*Trans) UpdateMany added in v1.2.1 

func (t *Trans) UpdateMany(query string, args [][]interface{}) (*Affected, error)

UpdateMany update many

func (*Trans) UpdateManyCtx added in v1.2.1 

func (t *Trans) UpdateManyCtx(ctx context.Context, query string, args [][]interface{}) (*Affected, error)

UpdateManyCtx update many trans

type TxFunc 

type TxFunc func(tx *Trans) (interface{}, error)

TxFunc callback function definition

Source Files

View all Source files

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.