Yesql v1.1.5 发布了，从 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 }

使用 Yesql 的项目

• examples - 项目自带examples
• paopao-ce - 一个清新文艺的微社区