a golang library for sql builder

Related tags

gendry
Overview

Gendry

Build Status Gitter Hex.pm GoDoc

gendry is a Go library that helps you operate database. Based on go-sql-driver/mysql, it provides a series of simple but useful tools to prepare parameters for calling methods in standard library database/sql.

The name gendry comes from the role in the hottest drama The Game of Throne, in which Gendry is not only the bastardy of the late king Robert Baratheon but also a skilled blacksmith. Like the one in drama, this library also forge something which is called SQL.

gendry consists of three isolated parts, and you can use each one of them partially:

Translation

Manager

manager is used for initializing database connection pool(i.e sql.DB), you can set almost all parameters for those mysql driver supported.For example, initializing a database connection pool:

var db *sql.DB
var err error
db, err = manager
		.New(dbName, user, password, host)
		.Set(
			manager.SetCharset("utf8"),
			manager.SetAllowCleartextPasswords(true),
			manager.SetInterpolateParams(true),
			manager.SetTimeout(1 * time.Second),
			manager.SetReadTimeout(1 * time.Second)
		).Port(3302).Open(true)

In fact, all things manager does is just for concatting the dataSourceName

the format of a dataSourceName is:

[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]

manager is based on go-mysql-driver/mysql, and if you don't know some of the manager.SetXXX series functions, see it on mysql driver's github home page.And for more details see manager's doc

Builder

builder as its name says, is for building sql. Writing sql manually is intuitive but somewhat difficult to maintain.And for `where in`, if you have huge amount of elements in the `in` set, it's very hard to write.

builder isn't an ORM, in fact one of the most important reasons we create Gendry is we don't like ORM. So Gendry just provides some simple APIs to help you building sqls:

where := map[string]interface{}{
	"city in": []string{"beijing", "shanghai"},
	"score": 5,
	"age >": 35,
	"address": builder.IsNotNull,
	"_or": []map[string]interface{}{
		{
			"x1":    11,
			"x2 >=": 45,
		},
		{
			"x3":    "234",
			"x4 <>": "tx2",
		},
	},
	"_orderby": "bonus desc",
	"_groupby": "department",
}
table := "some_table"
selectFields := []string{"name", "age", "sex"}
cond, values, err := builder.BuildSelect(table, where, selectFields)

//cond = SELECT name,age,sex FROM some_table WHERE (((x1=? AND x2>=?) OR (x3=? AND x4!=?)) AND score=? AND city IN (?,?) AND age>? AND address IS NOT NULL) GROUP BY department ORDER BY bonus DESC
//values = []interface{}{11, 45, "234", "tx2", 5, "beijing", "shanghai", 35}

rows, err := db.Query(cond, values...)

And, the library provide a useful API for executing aggregate queries like count, sum, max, min, avg

where := map[string]interface{}{
    "score > ": 100,
    "city in": []interface{}{"Beijing", "Shijiazhuang", }
}
// AggregateSum, AggregateMax, AggregateMin, AggregateCount, AggregateAvg are supported
result, err := AggregateQuery(ctx, db, "tableName", where, AggregateSum("age"))
sumAge := result.Int64()
result, err = AggregateQuery(ctx, db, "tableName", where, AggregateCount("*")) 
numberOfRecords := result.Int64()
result, err = AggregateQuery(ctx, db, "tableName", where, AggregateAvg("score"))
averageScore := result.Float64()

If you want to clear the zero value in the where map, you can use builder.OmitEmpty

where := map[string]interface{}{
		"score": 0,
		"age >": 35,
	}
finalWhere := builder.OmitEmpty(where, []string{"score", "age"})
// finalWhere = map[string]interface{}{"age >": 35}

// support: Bool, Array, String, Float32, Float64, Int, Int8, Int16, Int32, Int64, Uint, Uint8, Uint16, Uint32, Uint64, Uintptr, Map, Slice, Interface, Struct

For complex queries, NamedQuery may be helpful:

cond, vals, err := builder.NamedQuery("select * from tb where name={{name}} and id in (select uid from anothertable where score in {{m_score}})", map[string]interface{}{
	"name": "caibirdme",
	"m_score": []float64{3.0, 5.8, 7.9},
})

assert.Equal("select * from tb where name=? and id in (select uid from anothertable where score in (?,?,?))", cond)
assert.Equal([]interface{}{"caibirdme", 3.0, 5.8, 7.9}, vals)

slice type can be expanded automatically according to its length, thus these sqls are very convenient for DBA to review.
For critical system, this is recommended

For more detail, see builder's doc or just use godoc

Scanner

For each response from mysql, you want to map it with your well-defined structure. Scanner provides a very easy API to do this, it's based on reflection:
standard library
type Person struct {
	Name string
	Age int
}

rows, err := db.Query("SELECT age as m_age, name from g_xxx where xxx")
defer rows.Close()

var students []Person

for rows.Next() {
	var student Person
	rows.Scan(student.Age, student.Name)
	students = append(students, student)
}
using scanner
type Person struct {
	Name string `ddb:"name"`
	Age int `ddb:"m_age"`
}

rows, err := db.Query("SELECT age as m_age, name from g_xxx where xxx")
defer rows.Close()

var students []Person

scanner.Scan(rows, &students)

Types which implement the interface

type ByteUnmarshaler interface {
	UnmarshalByte(data []byte) error
}

will take over the corresponding unmarshal work.

type human struct {
	Age   int       `ddb:"ag"`
	Extra *extraInfo `ddb:"ext"`
}

type extraInfo struct {
	Hobbies     []string `json:"hobbies"`
	LuckyNumber int      `json:"ln"`
}

func (ext *extraInfo) UnmarshalByte(data []byte) error {
	return json.Unmarshal(data, ext)
}

//if the type of ext column in a table is varchar(stored legal json string) or json(mysql5.7)
var student human
err := scanner.Scan(rows, &student)
// ...

The extra tag of the struct will be used by scanner resolve data from response.The default tag name is ddb:"tagname", but you can specify your own such as:

scanner.SetTagName("json")
type Person struct {
	Name string `json:"name"`
	Age int `json:"m_age"`
}

// ...
var student Person
scanner.Scan(rows, &student)

scanner.SetTagName is a global setting and it can be invoked only once

ScanMap

rows, _ := db.Query("select name, age as m_age from person")
result, err := scanner.ScanMap(rows)
for _, record := range result {
	fmt.Println(record["name"], record["m_age"])
}

ScanMap scans data from rows and returns a []map[string]interface{}
int, float, string type may be stored as []uint8 by mysql driver, ScanMap just copy those value into the map. If you're sure that there's no binary data type in your mysql table(in most cases, this is true), you can use ScanMapDecode instead which will convert []uint8 to int, float64 or string

For more detail, see scanner's doc

PS:

  • Don't forget close rows if you don't use ScanXXXClose
  • The second parameter of Scan must be a reference

Tools

Besides APIs above, Gendry provide a [CLI tool](https://github.com/caibirdme/gforge) to help generating codes.
Issues
  • feat: propose OR where cond query.

    feat: propose OR where cond query.

    This PR is going to propose an OR operator implementation.

    opened by Colstuwjx 11
  • 你好 请问能支持offset吗

    你好 请问能支持offset吗

    feature 
    opened by mysterytree 11
  • 【Q】how to scan mysql DATE_TIME into golang  time.Time

    【Q】how to scan mysql DATE_TIME into golang time.Time

    how to scan mysql Date_Time into golang time.TIME

    CreateTime is int which is not AssignableBy []uint8

    bug 
    opened by arthurkiller 11
  • bug about regex

    bug about regex

    https://github.com/didi/gendry/blob/master/builder/builder.go#L394 this regex is greedy. It should be non-greedy. for example, "select {{name}},{{comment}},value from table" In mysql, this is ok. but gendry will parser it as "name}},{{comment". I must insert an space between }} and {{ .

    need more infomation 
    opened by jingyugao 9
  • 支持where 条件中的key 根据value判断是否op为in

    支持where 条件中的key 根据value判断是否op为in

    where:=map[string]interface{}{ "c":[]int{3,4} } 如果这种场景下c的有运算符的话默认 加一个in进去。 ps:参考"github.com/Masterminds/squirrel" 这个包的 where的实现。

    opened by gopherhw 8
  • about manager.Open

    about manager.Open

    简单点就是Open(true)会创建一个脱缰的野马,没有 max life time...,默认交互式超时为:30S,没有意外你会遇到 invalid connection

    建议 1、Open(false) 2、SetConnMaxLifetime 3、Ping()

    question 
    opened by zplzpl 7
  • 增加多个 tag 支持

    增加多个 tag 支持

    可以通过自定义 tag 和默认 tag 进行映射

    opened by haozibi 7
  • can gendry supprot

    can gendry supprot "not in"?

    I konw "not in" is ineffective, it cant optimized by index. But I really want to use "not in".

    developing feature request 
    opened by jingyugao 7
  • like语句支持空值判断?

    like语句支持空值判断?

    例如: ` where := builder.OmitEmpty(map[string]interface{}{ "I_STATUS": params.Status, "CH_BIZ_ID": params.BizId, "I_TYPE": params.Type, "I_CONTENT_TYPE": params.ContentType, "B_IS_DELETE": constant.IsNotDelete, "D_CREATE_DATE >": params.CreateBeginAt, "D_CREATE_DATE <=": params.CreateEndAt, }, []string{"I_TYPE", "I_CONTENT_TYPE", "D_CREATE_DATE >", "D_CREATE_DATE <="})

    if params.Title != "" {
    	where["CH_TITLE like"] = ...
    }`
    

    builder.OmitEmpty 无法判断空值,目前只能手动判断

    opened by Wintic 7
  • 是否支持MySQL关键词

    是否支持MySQL关键词

    是否支持MySQL关键词,比如 long char procudure

    opened by bsync-tech 1
  • NamedQuery support map

    NamedQuery support map

    opened by xiao-xiao-xiao 4
  • NamedQuery 支持嵌套 map[string]interface{},  实现过滤条件

    NamedQuery 支持嵌套 map[string]interface{}, 实现过滤条件

    /*
    {
        sql: `select {{foo}},{{bar}} from tb where address in {{addr}} and {{where}}`,
            data: map[string]interface{}{
    		"foo":  "f1",
    		"bar":  "f2",
    		"addr": []string{"beijing", "shanghai", "chengdu"},
    		"where": map[string]interface{}{
    			"name in": []string{"1", "2"},
                             "age": 10,
    		 },
    	},
    	cond: `select ?,? from tb where address in (?,?,?) and (age=? AND name IN (?,?))`,,
    	vals: []interface{}{"f1", "f2", "beijing", "shanghai", "chengdu", 10, "1", "2"},
    }
    */
    
    where := map[string]interface{}{
    		"foo":  "f1",
    		"bar":  "f2",
    		"addr": []string{"beijing", "shanghai", "chengdu"},
    		"where": map[string]interface{}{
    			"name in": []string{"1", "2"},
                              "age": 10,
    		 },
    	},
    
    where["where"] = builder.Omity(where["where"],  []string{"age"})
    // 使用此种方式来实现,复杂SQL, 部分条件需要过滤
    
    
    
    opened by xiao-xiao-xiao 0
  • AggregateMax  聚合函数当 求某个时间(datetime)的最大值时,怎么获取结果

    AggregateMax 聚合函数当 求某个时间(datetime)的最大值时,怎么获取结果

    AggregateMax 聚合函数当 求某个时间(datetime)的最大值时,怎么获取结果

    opened by MichealJl 0
  • user collection 用户征集

    user collection 用户征集

    To make gendry better and better, we want to know how many of you used it in your project and what functions gendry could provide to ease your burden. Please tell us about it, your company name, or your personal project. A long list could make gendry more convincing and attractive. eg:

    company | project | suggestion --------- | --------- | --------- Didi滴滴出行 | widely used in many projects | could it be faster personal | myproject_foo | could you support sql.NullXXX type in scanner

    Don't mind about the format, just share it with us!

    为了让gendry更好的升级和维护,我们想要知道有多少项目正在使用它。如果你在公司项目或者个人项目中使用了gendry,请告诉我们,同时你对gendry的建议对我们来说也很重要!如果我们能收集一份长长的用户列表,也会使项目更具有说服力,吸引更多用户。

    help wanted 
    opened by caibirdme 38
Releases(v1.7.0)
Owner
DiDi
滴滴出行
DiDi
golang orm and sql builder

gosql gosql is a easy ORM library for Golang. Style: var userList []UserModel err := db.FetchAll(&userList, gosql.Columns("id","name"), gosql.

RushTeam 144 Jul 20, 2021
Type safe SQL query builder and struct mapper for Go

sq (Structured Query) ?? ?? sq is a code-generated, type safe query builder and struct mapper for Go. ?? ?? Documentation • Reference • Examples This

null 110 Jul 18, 2021
A Go (golang) package that enhances the standard database/sql package by providing powerful data retrieval methods as well as DB-agnostic query building capabilities.

ozzo-dbx Summary Description Requirements Installation Supported Databases Getting Started Connecting to Database Executing Queries Binding Parameters

Ozzo Framework 536 Jul 19, 2021
a golang library for sql builder

Gendry gendry is a Go library that helps you operate database. Based on go-sql-driver/mysql, it provides a series of simple but useful tools to prepar

DiDi 1.2k Jul 23, 2021
SQL builder and query library for golang

__ _ ___ __ _ _ _ / _` |/ _ \ / _` | | | | | (_| | (_) | (_| | |_| | \__, |\___/ \__, |\__,_| |___/ |_| goqu is an expressive SQL bu

Doug Martin 1.1k Jul 23, 2021
Go database query builder library for PostgreSQL

buildsqlx Go Database query builder library Installation Selects, Ordering, Limit & Offset GroupBy / Having Where, AndWhere, OrWhere clauses WhereIn /

Arthur Kushman 36 Jul 4, 2021
Additions to Go's database/sql for super fast performance and convenience. (fork of gocraft/dbr)

dbr (fork of gocraft/dbr) provides additions to Go's database/sql for super fast performance and convenience. Getting Started // create a connection (

Free and open source software developed at Mail.Ru 143 Jul 18, 2021
Command line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server

About xo xo is a command-line tool to generate Go code based on a database schema or a custom query. xo works by using database metadata and SQL intro

XO 2.8k Jul 25, 2021
Fast SQL query builder for Go

sqlf A fast SQL query builder for Go. sqlf statement builder provides a way to: Combine SQL statements from fragments of raw SQL and arguments that ma

Vlad Glushchuk 20 Jul 6, 2021
Fluent SQL generation for golang

sqrl - fat-free version of squirrel - fluent SQL generator for Go Non thread safe fork of squirrel. The same handy fluffy helper, but with extra lette

Ivan Kirichenko 226 Jul 10, 2021
igor is an abstraction layer for PostgreSQL with a gorm like syntax.

igor igor is an abstraction layer for PostgreSQL, written in Go. Igor syntax is (almost) compatible with GORM. When to use igor You should use igor wh

Paolo Galeone 85 Jun 27, 2021
Query git repositories with SQL. Generate reports, perform status checks, analyze codebases. 🔍 📊

askgit askgit is a command-line tool for running SQL queries on git repositories. It's meant for ad-hoc querying of git repositories on disk through a

Augmentable 2.3k Jul 20, 2021
Document-oriented, embedded SQL database

Genji Document-oriented, embedded, SQL database Table of contents Table of contents Introduction Features Installation Usage Using Genji's API Using d

Genji 688 Jul 25, 2021
A Golang library for using SQL.

dotsql A Golang library for using SQL. It is not an ORM, it is not a query builder. Dotsql is a library that helps you keep sql files in one place and

Gustavo Chaín 591 Jul 14, 2021