golang中使用sqlx + squirrel作为orm

依赖

import (
	sq "github.com/Masterminds/squirrel"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

定义数据实体

type User struct {
	Id       string `json:"id" db:"id"`
	Username string `json:"username" db:"username"`
	Password string `json:"password" db:"password"`
	Sex      int8   `json:"sex" db:"sex"`
}

连接数据库

db, err := sqlx.Connect("mysql", "user:pass@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local")
if err != nil {
    log.Fatalln(err)
}

使用squirrel构造sql

sql, _, err := sq.Select("*").From("t_user").ToSql()
if err != nil {
    log.Fatalln(err)
}

使用sqlx执行sql

users := []User{}
err = db.Select(&users, sql)
if err != nil {
    log.Fatalln(err)
}
for _, it := range users {
    fmt.Println(it.Username)
}

整体代码如下

package main

import (
	"fmt"
	sq "github.com/Masterminds/squirrel"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	"log"
)

type User struct {
	Id       string `json:"id" db:"id"`
	Username string `json:"username" db:"username"`
	Password string `json:"password" db:"password"`
	Sex      int8   `json:"sex" db:"sex"`
}

func main() {
	db, err := sqlx.Connect("mysql", "user:pass@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local")
	if err != nil {
		log.Fatalln(err)
	}

	sql, _, err := sq.Select("*").From("t_user").ToSql()
	if err != nil {
		log.Fatalln(err)
	}
	users := []User{}
	err = db.Select(&users, sql)
	if err != nil {
		log.Fatalln(err)
	}
	for _, it := range users {
		fmt.Println(it.Username)
	}
}

带参数的查询

sql, args, err := sq.
Select("*").
From("t_user").
Where(sq.Eq{
    "username": "sbfzl",
}).
ToSql()
if err != nil {
    t.Log(err)
}
users := []model.User{}
err = db.Select(&users, sql, args...)

事务

func TestSquirrelTransaction(t *testing.T) {
	tx := dal.SquirrelDB.MustBegin()
	insertSql, insertArgs, err := dal.Psql.Insert("user2").
		Columns("username", "password").
		Values("tmd", "xxxx").
		ToSql()
	if err != nil {
		t.Fatal(err)
	}
	findSql, findArgs, err := dal.Psql.Select("*").From("user2").Where(sq.Eq{"username": "tmd"}).ToSql()
	if err != nil {
		t.Fatal(err)
	}
	tx.MustExecContext(ctx, insertSql, insertArgs...)
	user := squirrel.User2{}
	err = tx.Get(&user, findSql, findArgs...)
	if err != nil {
		t.Fatal(err)
	}
	updateSql, updateArgs, err := dal.Psql.Update("user2").
		Set("password", "okok").
		Where(sq.Eq{"id": user.Id}).
		ToSql()
	if err != nil {
		t.Fatal(err)
	}
	tx.MustExecContext(ctx, updateSql, updateArgs...)
	err = tx.Commit()
	if err != nil {
		t.Fatal(err)
	}
}

联表查询

func TestSquirrelJoin(t *testing.T) {
	type Result struct {
		Title string `json:"title"`
		Sales int64  `json:"sales"`
	}
	sql, args, err := dal.Psql.Select("title").
		Column(sq.Alias(sq.Expr("SUM(domestic_sales + international_sales)"), "sales")).
		From("movies").
		Join("boxoffice ON boxoffice.movie_id = movies.id").
		GroupBy("title").
		OrderBy("sales DESC").
		ToSql()
	if err != nil {
		t.Fatal(err)
	}
	t.Log(sql)
	res := []Result{}
	err = dal.SquirrelDB.SelectContext(ctx, &res, sql, args...)
	if err != nil {
		t.Fatal(err)
	}
	for _, it := range res {
		t.Log(it)
	}
}

postgres

如果使用pg作为数据库则需要使用sq.StatementBuilder.PlaceholderFormat(sq.Dollar)返回的sql构造器

func TestSquirrelInsert(t *testing.T) {
	psql := sq.StatementBuilder.PlaceholderFormat(sq.Dollar)
	sql, args, err := psql.Insert("user2").
		Columns("username", "password").
		Values("hahah", "xxxx").
		Values("hahah2", "ccxx").
		ToSql()
	if err != nil {
		t.Error(err)
		return
	}
	t.Log(sql)
	t.Logf("%+v", args)
	rowsAffected, err := dal.SquirrelDB.MustExec(sql, args...).RowsAffected()
	if err != nil {
		t.Error(err)
		return
	}
	t.Logf("插入了%d条数据", rowsAffected)
}

func TestSquirrelUpdate(t *testing.T) {
	sql, args, err := dal.Psql.Update("user2").
		Set("username", "okok").
		Where(sq.Eq{"id": "880305507169828865"}).
		ToSql()
	if err != nil {
		t.Error(err)
		return
	}
	affected, err := dal.SquirrelDB.MustExec(sql, args...).RowsAffected()
	if err != nil {
		t.Error(err)
		return
	}
	t.Logf("修改了%d条数据", affected)
}

func TestSquirrelDelete(t *testing.T) {
	sql, args, err := dal.Psql.Delete("user2").
		Where(sq.Eq{"id": "880305507169828865"}).
		ToSql()
	if err != nil {
		t.Error(err)
		return
	}
	affected, err := dal.SquirrelDB.MustExec(sql, args...).RowsAffected()
	if err != nil {
		t.Error(err)
		return
	}
	t.Logf("删除了%d条数据", affected)
}

参考

Illustrated Guide to SQLX (jmoiron.github.io)

Masterminds/squirrel: Fluent SQL generation for golang (github.com)