sqlx

sqlx

sqlx是标准库sql的升级版， 在sqlx的基础上线封装一套新的api以适应业务，实现以下目标

• 支持多db实例
• 记录sql执行日志和cost耗时
• 上报opentracing追踪数据
• 汇总prometheus监控指标

Warning:

1. 建表时，所有的数据库字段都应该设置NOT NULL和DEFAULT值

2. orm对象的字段应该使用db tag映射

3. 避免使用select * from table_name

   • 注: 如果一定要使用select *，必须在创建db实例的时候设置db.Unsafe()(v0.3.31已以后已经默认开启)选项，否则在业务迭代时，先加数据库字段，再上线服务，中间存在空窗期，会报错。参考https://fengqi.me/go/629.html

doc: https://horizonrobotics.feishu.cn/file/boxcnFhwQp6OTZkCaKSnYHDWhic

使用示例

1. 数据库配置

# https://github.com/go-sql-driver/mysql#dsn-data-source-name
db_${name}_dsn: "username:password@protocol(address)/dbname?param=value"
db_${name}_max_open: 30
db_${name}_max_idle: 10

model定义

type User struct {
    ID         int        `db:"id"`
    Name       string     `db:"name"`
    Age        int        `db:"age"`
    CreateTime time.Time  `db:"ctime"`
}

// TableName 返回表名，必须实现
func (u User) TableName() string {
    return "t_user"
}

// KeyName 返回主键，必须实现
func (u User) KeyName() string {
    return "id"
}

1. 单行查询

func QueryByID(ctx context.Context, id int64) (u User, err error) {
    // 选择某个数据库，可以支持多实例
    conn := sqlx.Get(ctx, "db1")
    
    err = conn.GetContext(ctx, &u, "select id,a,b,c... from users where id = ?", id)
    if err != nil {
    	if 
        return
    }
}

1. 多行查询

func ListAll(ctx context.Context) (users []User, err error) {
    conn := sqlx.Get(ctx, "db2")
    err = conn.SelectContext(ctx, &users, "select id,a,b,c... from users order by id desc")
    if err != nil {
        return
    }
}

1. insert

func AddOne(ctx context.Context) (id int64, err error) {
    conn := sqlx.Get(ctx, "db3")

    u := User{
        Name: "demo",
        Age:  18,
    }
    result, err := conn.InsertContext(ctx, u)
    if err != nil {
        return
    }

    id, err = result.LastInsertId()
    return
}

1. update更新操作

func UpdateByID(ctx context.Context, u User) (err error) {
    conn := sqlx.Get(ctx, "db1")
    
    // 📢📢📢 这里update的条件只有id且是所有字段的更新 
    // 📢📢📢 这里update的条件只有id且是所有字段的更新 
    // 📢📢📢 这里update的条件只有id且是所有字段的更新 
    u.Name = "bar"
    u.ID = int(id)

    _, err = conn.UpdateContext(ctx, u)
    if err != nil {
        return
    }	
}

1. delete删除

func DeleteByID(ctx context.Context, id int64) (err error) {
    conn := sqlx.Get(ctx, "db2")
    
    u := User{ID: id}
    _, err = conn.DeleteContext(ctx, u)
    if err != nil {
        return
    }	
}

1. 复杂查询，对于复杂的查询，例如连表操作，需要使用类似原生的api

   6.1 复杂查询

type AliseItem struct {
	AID int64 // a表的id字段
	BID int64 // b表的id字段
	// ....
}

func QueryJoin(ctx context.Context) (items []AliseItem, err error) {
    conn := sqlx.Get(ctx, "db2")

    rows, err := conn.QueryContext(ctx, "select a.t, a.B, b.T, b.N from t_test as a left join t_demo as b on a.id = b.xx_id")
    if err != nil {
        return
    }
    defer rows.Close()
    
    for rows.Next() {
    	var item AliseItem
    	// 这里的scan顺序需要跟SQL中的顺序一致
    	if err = rows.Scan(&item.AID, &item.BID); err != nil {
    		return
        }
        items = append(items, item)
    }
}

6.2 增删改

func UpdateByUsername(ctx context.Context, u User) (err error) {
    db := sqlx.Get(ctx, "db2")
    _, err = db.ExecContext(ctx, "update t_test set age=? where username=?", u.Age, u.Username)
    return
}

func DeleteByUsername(ctx context.Context, username string) (err error) {
    db := sqlx.Get(ctx, "db1")
    _, err = db.ExecContext(ctx, "delete from t_test where username=?", username)
    return
}

1. 事务

func TxFunc(ctx context.Context) (err error){
   conn := Get(ctx, "")
   tx, err := conn.Beginx()
   if err != nil {
      return
   }

   defer func() {
      if p := recover(); p != nil {
         // 回滚，继续向上panic
         tx.Rollback()
         panic(p)
      } else if err != nil {
         // 回滚，向上抛 err
         tx.Rollback()
      } else {
         // 提交事务
         err = tx.Commit()
      }
   }()

   // 事务1
   u := user{ID: 11, Name: "lalala", Age: 100}
   if _, err = tx.UpdateContext(ctx, u); err != nil {
      return
   }

   // 事务2
   if err = trans(ctx, tx); err != nil {
      return
   }

   // 事务n...

   return
}

// trans 事务中的其他操作
func trans(ctx context.Context, conn *Tx) (err error) {
   u := user{ID: 1000, Name: "None", Age: 999}
   result, err := conn.UpdateContext(ctx, u)
   if err != nil {
      return
   }

   affect, err := result.RowsAffected()
   if err != nil {
      return err
   }

   if affect < 1 {
      err = fmt.Errorf("no affect")
      return
   }
   return
}