ProductPromotion
Logo

Go.Lang

made by https://0x3d.site

GitHub - didi/gendry: a golang library for sql builder
a golang library for sql builder. Contribute to didi/gendry development by creating an account on GitHub.
Visit Site

GitHub - didi/gendry: a golang library for sql builder

GitHub - didi/gendry: a golang library for sql builder

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 forges something called SQL.

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

Translation

The manager is used for initializing the database connection pool(i.e., sql.DB). You can set almost all parameters for those MySQL drivers 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 to generate 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. If you don't know some of the manager and SetXXX series functions, see it on mysql driver's github home page. For more details see manager's doc

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 build SQLs:

where := map[string]interface{}{
	"city": []string{"beijing", "shanghai"},
	// The in operator can be omitted by default,
	// which is equivalent to:
	// "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...)


// support builder.Raw in where & update
where := map[string]interface{}{"gmt_create <": builder.Raw("gmt_modified")}
cond, values, err := builder.BuildSelect(table, where, selectFields)
// SELECT * FROM x WHERE gmt_create < gmt_modified

update = map[string]interface{}{
    "code": builder.Raw("VALUES(code)"), // mysql 8.x  builder.Raw("new.code")
    "name": builder.Raw("VALUES(name)"), // mysql 8.x  builder.Raw("new.name")
}
cond, values, err := builder.BuildInsertOnDuplicate(table, data, update)
// INSERT INTO country (id, code, name) VALUES (?,?,?),(?,?,?),(?,?,?) 
// ON DUPLICATE KEY UPDATE code=VALUES(code),name=VALUES(name)

In the where param, in operator is automatically added by value type(reflect.Slice).

where := map[string]interface{}{
	"city": []string{"beijing", "shanghai"},
}

the same as

where := map[string]interface{}{
	"city in": []string{"beijing", "shanghai"},
}

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

where := map[string]interface{}{
    "score > ": 100,
    "city": []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()

multi or condition can use multi _or prefix string mark

where := map[string]interface{}{
    // location
    "_or_location": []map[string]interface{}{{
        "subway": "beijing_15", 
    }, {
        "district": "Chaoyang", 
    }},
    // functions
    "_or_functions": []map[string]interface{}{{
         "has_gas": true,
    }, {
        "has_lift": true,
}}}

// query = (((subway=?) OR (district=?)) AND ((has_gas=?) OR (has_lift=?)))
// args = ["beijing_15", "Chaoyang", true, true]

If you want to clear the value '0' 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

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 that 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 copies those values 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

Articles
to learn more about the golang concepts.

Resources
which are currently available to browse on.

mail [email protected] to add your project or resources here 🔥.

FAQ's
to know more about the topic.

mail [email protected] to add your project or resources here 🔥.

Queries
or most google FAQ's about GoLang.

mail [email protected] to add more queries here 🔍.

More Sites
to check out once you're finished browsing here.

0x3d
https://www.0x3d.site/
0x3d is designed for aggregating information.
NodeJS
https://nodejs.0x3d.site/
NodeJS Online Directory
Cross Platform
https://cross-platform.0x3d.site/
Cross Platform Online Directory
Open Source
https://open-source.0x3d.site/
Open Source Online Directory
Analytics
https://analytics.0x3d.site/
Analytics Online Directory
JavaScript
https://javascript.0x3d.site/
JavaScript Online Directory
GoLang
https://golang.0x3d.site/
GoLang Online Directory
Python
https://python.0x3d.site/
Python Online Directory
Swift
https://swift.0x3d.site/
Swift Online Directory
Rust
https://rust.0x3d.site/
Rust Online Directory
Scala
https://scala.0x3d.site/
Scala Online Directory
Ruby
https://ruby.0x3d.site/
Ruby Online Directory
Clojure
https://clojure.0x3d.site/
Clojure Online Directory
Elixir
https://elixir.0x3d.site/
Elixir Online Directory
Elm
https://elm.0x3d.site/
Elm Online Directory
Lua
https://lua.0x3d.site/
Lua Online Directory
C Programming
https://c-programming.0x3d.site/
C Programming Online Directory
C++ Programming
https://cpp-programming.0x3d.site/
C++ Programming Online Directory
R Programming
https://r-programming.0x3d.site/
R Programming Online Directory
Perl
https://perl.0x3d.site/
Perl Online Directory
Java
https://java.0x3d.site/
Java Online Directory
Kotlin
https://kotlin.0x3d.site/
Kotlin Online Directory
PHP
https://php.0x3d.site/
PHP Online Directory
React JS
https://react.0x3d.site/
React JS Online Directory
Angular
https://angular.0x3d.site/
Angular JS Online Directory