Golang自动从结构体生成SQL查询的强大构建器插件Patcher的使用

Patcher是一个Go库，提供了一种从结构体生成SQL补丁的简单方法。这个库的诞生源于为数据库生成补丁的需求：当向结构体添加新字段时，会导致代码库中出现大量新的if检查。Patcher旨在通过自动生成SQL补丁来解决这个问题。

什么是Patcher？

• 自动SQL生成：自动从结构体生成SQL UPDATE查询，减少手动编写和维护SQL语句的需求
• 代码简化：减少了处理不同结构体字段所需的样板代码和if-else条件，使代码更简洁易维护
• 结构体差异：允许将一个结构体的更改注入到另一个结构体，并根据差异生成更新脚本
• JOIN支持：通过创建实现Joiner接口的结构体来生成SQL JOIN

为什么使用Patcher？

• 节省时间：自动从结构体生成SQL查询，减少手动编写和维护SQL语句的时间
• 减少错误：根据结构体字段自动生成SQL查询，消除结构体字段变更时需要手动更新查询的风险
• 简化代码：减少处理不同结构体字段所需的样板代码和if-else条件
• 简化数据同步：通过结构体差异比较生成更新脚本
• 支持JOIN：通过Joiner接口简化多表关联查询
• 灵活配置：提供自定义SQL生成过程的选项
• 易于集成：可轻松集成到现有项目中
• 开源：基于Apache 2.0许可证

使用示例

不使用Patcher的情况

package main

import (
	"database/sql"
	"encoding/json"
	"errors"
	"net/http"
	"strconv"
	"strings"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"github.com/gorilla/mux"
)

type User struct {
	ID    int     `json:"id"`
	Name  *string `json:"name"`
	Email *string `json:"email"`
}

func updateUser(db *sql.DB, user User) error {
	query := "UPDATE users SET"
	var updates []string
	var args []any

	if user.Name != nil {
		updates = append(updates, "name = ?")
		args = append(args, *user.Name)
	}
	if user.Email != nil {
		updates = append(updates, "email = ?")
		args = append(args, *user.Email)
	}

	if len(updates) == 0 {
		return errors.New("no fields to update")
	}

	query += " " + strings.Join(updates, ", ") + " WHERE id = ?"
	args = append(args, user.ID)

	_, err := db.Exec(query, args...)
	return err
}

func patchHandler(w http.ResponseWriter, r *http.Request) {
	db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname")
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	defer db.Close()

	vars := mux.Vars(r)
	userIDStr := vars["id"]
	userID, err := strconv.Atoi(userIDStr)
	if err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	user := User{ID: userID}
	if err := json.NewDecoder(r.Body).Decode(&user); err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	if err := updateUser(db, user); err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}

	w.WriteHeader(http.StatusOK)
}

func main() {
	r := mux.NewRouter()
	r.HandleFunc("/users/{id}", patchHandler).Methods("PATCH")

	server := &http.Server{
		Addr:              ":8080",
		Handler:           r,
		ReadHeaderTimeout: 10 * time.Second,
	}

	if err := server.ListenAndServe(); err != nil {
		panic(err)
	}
}

使用Patcher的情况

package main

import (
	"database/sql"
	"encoding/json"
	"net/http"
	"strconv"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"github.com/gorilla/mux"
	"github.com/jacobbrewer1/patcher"
)

type User struct {
	ID    *int    `db:"id" json:"id,omitempty"`
	Name  *string `db:"name" json:"name,omitempty"`
	Email *string `db:"email" json:"email,omitempty"`
}

type UserWhere struct {
	ID *int `db:"id"`
}

func NewUserWhere(id int) *UserWhere {
	return &UserWhere{ID: &id}
}

func (u *UserWhere) Where() (sqlStr string, sqlArgs []any) {
	return "id = ?", []any{*u.ID}
}

func patchHandler(w http.ResponseWriter, r *http.Request) {
	db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname")
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	defer db.Close()

	vars := mux.Vars(r)
	idStr := vars["id"]
	parsedID, err := strconv.Atoi(idStr)
	if err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	user := new(User)
	if err := json.NewDecoder(r.Body).Decode(user); err != nil {
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}

	user.ID = &parsedID
	condition := NewUserWhere(parsedID)

	sqlStr, args, err := patcher.GenerateSQL(
		user,
		patcher.WithTable("users"),
		patcher.WithWhere(condition),
	)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}

	_, execErr := db.Exec(sqlStr, args...)
	if execErr != nil {
		http.Error(w, execErr.Error(), http.StatusInternalServerError)
		return
	}

	w.WriteHeader(http.StatusOK)
}

func main() {
	r := mux.NewRouter()
	r.HandleFunc("/users/{id}", patchHandler).Methods("PATCH")

	server := &http.Server{
		Addr:              ":8080",
		Handler:           r,
		ReadHeaderTimeout: 5 * time.Second,
	}

	if err := server.ListenAndServe(); err != nil {
		panic(err)
	}
}

基本用法

基本示例

package main

import (
	"encoding/json"
	"fmt"

	"github.com/jacobbrewer1/patcher"
)

type Person struct {
	ID   *int    `db:"-"`
	Name *string `db:"name"`
}

type PersonWhere struct {
	ID *int `db:"id"`
}

func NewPersonWhere(id int) *PersonWhere {
	return &PersonWhere{
		ID: &id,
	}
}

func (p *PersonWhere) Where() (string, []any) {
	return "id = ?", []any{*p.ID}
}

func main() {
	const jsonStr = `{"id": 1, "name": "john"}`

	person := new(Person)
	if err := json.Unmarshal([]byte(jsonStr), person); err != nil {
		panic(err)
	}

	condition := NewPersonWhere(*person.ID)

	sqlStr, args, err := patcher.GenerateSQL(
		person,
		patcher.WithTable("people"),
		patcher.WithWhere(condition),
	)
	if err != nil {
		panic(err)
	}

	fmt.Println(sqlStr)
	fmt.Println(args)
}

输出SQL:

UPDATE people
SET name = ?
WHERE (1 = 1)
  AND (
    id = ?
    )

参数:

["john", 1]

结构体差异比较

package main

import (
	"fmt"

	"github.com/jacobbrewer1/patcher"
)

type Something struct {
	Number       int
	Text         string
	PrePopulated string
	NewText      string
}

func main() {
	s := Something{
		Number:       5,
		Text:         "Hello",
		PrePopulated: "PrePopulated",
	}

	n := Something{
		Number:  6,
		Text:    "Old Text",
		NewText: "New Text",
	}

	// 将n的变更应用到s
	if err := patcher.LoadDiff(&s, &n); err != nil {
		panic(err)
	}

	fmt.Println(s.Number)
	fmt.Println(s.Text)
	fmt.Println(s.PrePopulated)
	fmt.Println(s.NewText)
}

输出:

6
Hello
PrePopulated
New Text

从差异生成SQL更新

package main

import (
	"fmt"

	"github.com/jacobbrewer1/patcher"
)

type Something struct {
	Number       int
	Text         string
	PrePopulated string
	NewText      string
}

type SomeWhere struct {
	id int
}

func NewSomeWhere(id int) *SomeWhere {
	return &SomeWhere{id: id}
}

func (s *SomeWhere) Where() (string, []any) {
	return "id = ?", []any{s.id}
}

func main() {
	s := Something{
		Number:       5,
		Text:         "Old Text",
		PrePopulated: "PrePopulated",
		NewText:      "New Text",
	}

	n := Something{
		Number:       5,
		Text:         "Old Text",
		PrePopulated: "PrePopulatedDifferent",
		NewText:      "New Text",
	}

	wherer := NewSomeWhere(5)

	// 从差异创建SQL补丁
	patch, err := patcher.NewDiffSQLPatch(
		&s,
		&n,
		patcher.WithTable("table_name"),
		patcher.WithWhere(wherer),
	)
	if err != nil {
		panic(err)
	}

	sqlStr, sqlArgs, err := patch.GenerateSQL()
	if err != nil {
		panic(err)
	}

	fmt.Println(sqlStr)
	fmt.Println(sqlArgs)
}

输出SQL:

UPDATE table_name
SET pre_populated = ?
WHERE (1 = 1)
  AND (
    id = ?
    )

参数:

["PrePopulatedDifferent", 5]

安装

使用以下命令安装Patcher库:

go get github.com/jacobbrewer1/patcher

配置选项

LoadDiff选项

• includeZeroValues: 设置为true以在差异中包含零值
• includeNilValues: 设置为true以在差异中包含nil值

GenerateSQL选项

• WithTable(tableName string): 指定SQL查询的表名
• WithWhere(whereClause Wherer): 提供SQL查询的WHERE子句
  • 可以传递实现WhereTyper接口的结构体以在WHERE子句中使用OR
• WithJoin(joinClause Joiner): 添加JOIN子句到SQL查询
• includeZeroValues: 设置为true以在补丁中包含零值
• includeNilValues: 设置为true以在补丁中包含nil值

贡献

欢迎贡献！请按照以下步骤进行贡献:

1. Fork仓库
2. 为你的功能或错误修复创建新分支
3. 为你的变更编写测试
4. 运行测试确保一切正常
5. 提交拉取请求

运行测试:

go test ./...