北野

那年江湖，遇见你

Yesql - Sqlx的好搭挡

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

安装

go get github.com/alimy/yesql

使用 GoDoc

创建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"
	"strings"

	"github.com/alimy/yesql"
)

//go:generate go run $GOFILE
func main() {
	log.Println("[Yesql] generate code start")
	yesql.SetDefaultQueryHook(func(query *yesql.Query) (*yesql.Query, error) {
		query.Query = strings.TrimRight(query.Query, ";")
		return query, nil
	})
	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 的项目

后记： Yesql 是我两个通宵熬出来的成果，起于一时兴起，效果还是远超预期。一开始是在 paopao-ce 与 sqlx 集成的过程中，总感觉很别扭，代码不是很美观，想着肯定还有更好的方式来写sql查询语句，于是就有了 Yesql 的诞生，心情一下就舒畅了。对嘛，sqlx 优雅的使用方式就应该是这样的，这才是我想要的感觉😎

 development  database  Go
 迷时师度 悟了自度 (侯捷) 关于我 

Comments