Yesql v1.1.5 发布了，从 SQL 文件自动生成 Go 结构体代码，实现查询语句与代码分离

Yesql 解析一个 SQL 文件，提取出查询语句，自动生成对应的 Go 结构体，实现查询语句与代码分离，方便编写数据库查询逻辑。

安装

 go get github.com/alimy/yesql

使用

创建sql文件

 -- sql file yesql.sql  -- name: newest_tags@topic -- get newest tag information SELECT t.id id, t.user_id user_id, t.tag tag, t.quote_num quote_num, u.id, u.nickname, u.username, u.status, u.avatar, u.is_admin  FROM @tag t JOIN @user u ON t.user_id = u.id  WHERE t.is_del = 0 AND t.quote_num > 0  ORDER BY t.id DESC  LIMIT ? OFFSET ?;  -- name: hot_tags@topic -- get get host tag information SELECT t.id id, t.user_id user_id, t.tag tag, t.quote_num quote_num, u.id, u.nickname, u.username, u.status, u.avatar, u.is_admin  FROM @tag t JOIN @user u ON t.user_id = u.id  WHERE t.is_del = 0 AND t.quote_num > 0  ORDER BY t.quote_num DESC  LIMIT ? OFFSET ?;  -- name: tags_by_keyword_a@topic -- get tags by keyword SELECT id, user_id, tag, quote_num FROM @tag WHERE is_del = 0 ORDER BY quote_num DESC LIMIT 6;  -- name: tags_by_keyword_b@topic SELECT id, user_id, tag, quote_num FROM @tag WHERE is_del = 0 AND tag LIKE ? ORDER BY quote_num DESC LIMIT 6;  -- name: insert_tag@topic INSERT INTO @tag (user_id, tag, created_on, modified_on, quote_num) VALUES (?, ?, ?, ?, 1);  -- name: tags_by_id_a@topic -- clause: in SELECT id FROM @tag WHERE id IN (?) AND is_del = 0 AND quote_num > 0;  -- name: tags_by_id_b@topic -- clause: in SELECT id, user_id, tag, quote_num FROM @tag WHERE id IN (?);  -- name: decr_tags_by_id@topic -- clause: in UPDATE @tag SET quote_num=quote_num-1, modified_on=? WHERE id IN (?);  -- name: tags_for_incr@topic -- clause: in SELECT id, user_id, tag, quote_num FROM @tag WHERE tag IN (?);  -- name: incr_tags_by_id@topic -- clause: in UPDATE @tag SET quote_num=quote_num+1, is_del=0, modified_on=? WHERE id IN (?);

使用Scan模式（方式一）

 // file: topics.go  package topics  import ( 	"context" 	_ "embed"  	"github.com/alimy/yesql" 	"github.com/jmoiron/sqlx" )  //go:embed yesql.sql var yesqlBytes []byte  type Topic struct { 	yesql.Namespace `yesql:"topic"` 	DecrTagsById    string     `yesql:"decr_tags_by_id"` 	IncrTagsById    string     `yesql:"incr_tags_by_id"` 	TagsByIdA       string     `yesql:"tags_by_id_a"` 	TagsByIdB       string     `yesql:"tags_by_id_b"` 	TagsForIncr     string     `yesql:"tags_for_incr"` 	HotTags         *sqlx.Stmt `yesql:"hot_tags"` 	InsertTag       *sqlx.Stmt `yesql:"insert_tag"` 	NewestTags      *sqlx.Stmt `yesql:"newest_tags"` 	TagsByKeywordA  *sqlx.Stmt `yesql:"tags_by_keyword_a"` 	TagsByKeywordB  *sqlx.Stmt `yesql:"tags_by_keyword_b"` }  func NewTopic(db *sqlx.DB) (*Topic, error) { 	// use *sqlx.DB as prepare context 	yesql.UseSqlx(db) 	// get sql query 	query := yesql.MustParseBytes(yesqlBytes) 	// scan object from sql query 	obj := &Topic{} 	if err := yesql.Scan(obj, query); err != nil { 		return nil, err 	} 	return obj, nil }

使用代码生成模式（方式二）

编写代码生成逻辑

 / file: gen.go  package main  import ( 	"log"  	"github.com/alimy/yesql" )  //go:generate go run $GOFILE func main() { 	log.Println("[Yesql] generate code start") 	if err := yesql.Generate("yesql.sql", "auto", "yesql"); err != nil { 		log.Fatalf("generate code occurs error: %s", err) 	} 	log.Println("[Yesql] generate code finish") }

自动生成Go代码

 % go generate gen.go 2023/03/31 19:34:44 [Yesql] generate code start 2023/03/31 19:34:44 [Yesql] generate code finish

生成代码如下（生成文件路径：auto/yesql.go）

 // Code generated by Yesql. DO NOT EDIT. // versions: // - Yesql v1.1.2  package yesql  import ( 	"context"  	"github.com/alimy/yesql" 	"github.com/jmoiron/sqlx" )  const ( 	_TagsByKeywordB_Topic = `SELECT id, user_id, tag, quote_num FROM @tag WHERE is_del = 0 AND tag LIKE ? ORDER BY quote_num DESC LIMIT 6` 	_InsertTag_Topic      = `INSERT INTO @tag (user_id, tag, created_on, modified_on, quote_num) VALUES (?, ?, ?, ?, 1)` 	_TagsByIdA_Topic      = `SELECT id FROM @tag WHERE id IN (?) AND is_del = 0 AND quote_num > 0` 	_TagsByIdB_Topic      = `SELECT id, user_id, tag, quote_num FROM @tag WHERE id IN (?)` 	_TagsForIncr_Topic    = `SELECT id, user_id, tag, quote_num FROM @tag WHERE tag IN (?)` 	_IncrTagsById_Topic   = `UPDATE @tag SET quote_num=quote_num+1, is_del=0, modified_on=? WHERE id IN (?)` 	_NewestTags_Topic     = `SELECT t.id id, t.user_id user_id, t.tag tag, t.quote_num quote_num, u.id, u.nickname, u.username, u.status, u.avatar, u.is_admin FROM @tag t JOIN @user u ON t.user_id = u.id WHERE t.is_del = 0 AND t.quote_num > 0 ORDER BY t.id DESC LIMIT ? OFFSET ?` 	_TagsByKeywordA_Topic = `SELECT id, user_id, tag, quote_num FROM @tag WHERE is_del = 0 ORDER BY quote_num DESC LIMIT 6` 	_DecrTagsById_Topic   = `UPDATE @tag SET quote_num=quote_num-1, modified_on=? WHERE id IN (?)` 	_HotTags_Topic        = `SELECT t.id id, t.user_id user_id, t.tag tag, t.quote_num quote_num, u.id, u.nickname, u.username, u.status, u.avatar, u.is_admin FROM @tag t JOIN @user u ON t.user_id = u.id WHERE t.is_del = 0 AND t.quote_num > 0 ORDER BY t.quote_num DESC LIMIT ? OFFSET ?` )  type Topic struct { 	yesql.Namespace `yesql:"topic"` 	DecrTagsById    string     `yesql:"decr_tags_by_id"` 	IncrTagsById    string     `yesql:"incr_tags_by_id"` 	TagsByIdA       string     `yesql:"tags_by_id_a"` 	TagsByIdB       string     `yesql:"tags_by_id_b"` 	TagsForIncr     string     `yesql:"tags_for_incr"` 	HotTags         *sqlx.Stmt `yesql:"hot_tags"` 	InsertTag       *sqlx.Stmt `yesql:"insert_tag"` 	NewestTags      *sqlx.Stmt `yesql:"newest_tags"` 	TagsByKeywordA  *sqlx.Stmt `yesql:"tags_by_keyword_a"` 	TagsByKeywordB  *sqlx.Stmt `yesql:"tags_by_keyword_b"` }  func BuildTopic(p yesql.PreparexBuilder, ctx ...context.Context) (obj *Topic, err error) { 	var c context.Context 	if len(ctx) > 0 && ctx[0] != nil { 		c = ctx[0] 	} else { 		c = context.Background() 	} 	obj = &Topic{ 		DecrTagsById: p.QueryHook(_DecrTagsById_Topic), 		IncrTagsById: p.QueryHook(_IncrTagsById_Topic), 		TagsByIdA:    p.QueryHook(_TagsByIdA_Topic), 		TagsByIdB:    p.QueryHook(_TagsByIdB_Topic), 		TagsForIncr:  p.QueryHook(_TagsForIncr_Topic), 	} 	if obj.HotTags, err = p.PreparexContext(c, p.Rebind(p.QueryHook(_HotTags_Topic))); err != nil { 		return 	} 	if obj.InsertTag, err = p.PreparexContext(c, p.Rebind(p.QueryHook(_InsertTag_Topic))); err != nil { 		return 	} 	if obj.NewestTags, err = p.PreparexContext(c, p.Rebind(p.QueryHook(_NewestTags_Topic))); err != nil { 		return 	} 	if obj.TagsByKeywordA, err = p.PreparexContext(c, p.Rebind(p.QueryHook(_TagsByKeywordA_Topic))); err != nil { 		return 	} 	if obj.TagsByKeywordB, err = p.PreparexContext(c, p.Rebind(p.QueryHook(_TagsByKeywordB_Topic))); err != nil { 		return 	} 	return }