• Stars
    star
    1,613
  • Rank 29,009 (Top 0.6 %)
  • Language
    Go
  • License
    Apache License 2.0
  • Created almost 7 years ago
  • Updated 4 months ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

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

Manager

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

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 a 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 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

Scanner

For each response from MySQL, you want to map it with your well-defined structure. Scanner provides a straightforward 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 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

Tools

Besides APIs above, Gendry provides a [CLI tool](https://github.com/caibirdme/gforge) to help generating codes.

More Repositories

1

DoKit

一款面向泛前端产品研发全生命周期的效率平台。
Java
20,108
star
2

cube-ui

🔶 A fantastic mobile ui lib implement by Vue
JavaScript
9,141
star
3

chameleon

🦎 一套代码运行多端,一端所见即多端所见
JavaScript
9,016
star
4

VirtualAPK

A powerful and lightweight plugin framework for Android
Java
8,979
star
5

LogicFlow

A flow chart editing framework focusing on business customization. 专注于业务自定义的流程图编辑框架,支持实现脑图、ER图、UML、工作流等各种图编辑场景。
TypeScript
8,315
star
6

KnowStreaming

一站式云原生实时流数据平台,通过0侵入、插件化构建企业级Kafka服务,极大降低操作、存储和管理实时流数据门槛
Java
6,941
star
7

booster

🚀Optimizer for mobile applications
Kotlin
4,866
star
8

nightingale

An enterprise-level cloud-native monitoring system, which can be used as drop-in replacement of Prometheus for alerting and management.
Go
4,510
star
9

mpx

Mpx,一款具有优秀开发体验和深度性能优化的增强型跨端小程序框架
JavaScript
3,618
star
10

mand-mobile

💰 A mobile UI toolkit, based on Vue.js 2, designed for financial scenarios.
Vue
3,454
star
11

sharingan

Sharingan(写轮眼)是一个基于golang的流量录制回放工具,适合项目重构、回归测试等。
Go
2,276
star
12

tinyid

ID Generator id生成器 分布式id生成系统,简单易用、高性能、高可用的id生成系统
Java
2,265
star
13

xiaoju-survey

XIAOJUSURVEY is an enterprises form builder and analytics platform that allows users to create questionnaires, exams, polls, quizzes, and analyze data online.
TypeScript
2,065
star
14

ChineseNLP

Datasets, SOTA results of every fields of Chinese NLP
HTML
1,783
star
15

DiDiPrism

小桔棱镜,一款专注移动端操作行为的利器! A powerful tool (iOS & Android) that focuses on mobile operation behavior!
Objective-C
1,716
star
16

Hummer

一套移动端高性能高可用的动态化跨端开发框架
Objective-C
1,339
star
17

DDMQ

DDMQ is a distributed messaging product with low latency, high throughput and high availability.
Java
1,303
star
18

rdebug

Rdebug — Real Debugger
PHP
1,158
star
19

echo

Echo是一款桌面端调试工具,旨在提高客户端的研发调试效率
Objective-C
1,028
star
20

turbo

Turbo is a light-weight flow engine framework, support BPMN2.0. 一款轻量级流程引擎服务框架,可作为底层服务支持各类流程设计、低代码设计、工作流、审批流、服务编排等场景
Java
895
star
21

DroidAssist

A lightweight Android Studio gradle plugin based on Javassist for editing bytecode in Android.
Java
885
star
22

AoE

AoE (AI on Edge,终端智能,边缘计算) 是一个终端侧AI集成运行时环境 (IRE),帮助开发者提升效率。
C++
874
star
23

DRouter

Android Router Framework
Java
739
star
24

AgileTC

AgileTC is an agile test case management platform
JavaScript
673
star
25

falcon-log-agent

用于监控系统的日志采集agent,可无缝对接open-falcon
Go
583
star
26

super-jacoco

Java
473
star
27

epage

一款基于schema的低代码可视化页面配置工具
Vue
460
star
28

GateKeeper

A high-performance Golang gateway that supports rapid development and plug-inization
Go
436
star
29

sds

SDS是一个基于Java开发的简单、易用、高性能的服务降级系统,支持限流、熔断和降级等功能,服务端必备!!
Java
417
star
30

KnowSearch

面向Elasticsearch研发与运维人员,围绕集群、索引构建的零侵入、多租户的Elasticsearch GUI管控平台
Java
413
star
31

kemon

An Open-Source Pre and Post Callback-Based Framework for macOS Kernel Monitoring.
C
373
star
32

di18n

一种自动转换、基于配置的前端国际化方案
JavaScript
305
star
33

pile.js

pile.js components build with React.
CSS
267
star
34

ES-Fastloader

Quickly build large-scale ElasticSearch indices by using the fault tolerance and parallelism of Hadoop
Java
265
star
35

KnowAgent

基于日志模板构建,采集任务动态管控、数据质量精确度量,一站式日志采集平台
Java
255
star
36

Unify

An efficient, flexible Flutter-Native hybrid communication framework for seamless module integration and automatic code generation.
Dart
255
star
37

LogiCommon

Java版 认证、鉴权、管理、任务调度通用功能组件
Java
218
star
38

Tips

JavaScript
187
star
39

maskdetection

C++
179
star
40

DreamBox

DreamBox是一个为客户端开发者打造的统一视图开发框架,具有性能好、可动态更新的特点
Java
166
star
41

dlflow

DLFlow is a deep learning framework.
Python
157
star
42

didi.github.io

didi website
HTML
143
star
43

hetsann

Source Codes of HetSANN in the AAAI'20 paper: An Attention-based Graph Nerual Network for Heterogeneous Structural Learning.
Python
142
star
44

collection

go collection package for fasting coding
Go
127
star
45

athena

A release version for https://github.com/athena-team/athena
Python
124
star
46

JuShaTa

JuShaTa是一个Java容器,提供模块隔离及模块热加载能力。
Java
121
star
47

logbook

Java
107
star
48

heteta

HetETA: Heterogeneous Information Network Embedding for Estimating Time of Arrival
Python
105
star
49

levin

A Quick Way to Bulk Loading
C++
105
star
50

TrafficIndex

TTI ,Smart Transportation Industry Standard
C++
94
star
51

thrift-mock

A lightweight java test library for mocking thrift server
Java
92
star
52

mand-mobile-rn

💰 Mand Mobile for React Native, designed for financial scenarios.
TypeScript
88
star
53

vue-tmap

一个基于腾讯地图JavaScript API GL、TypeScript 封装适用于 Vue3 的高性能地图组件库
TypeScript
88
star
54

slime-validator

JavaScript library of validation based on Plugin system and make data validation be easy.
JavaScript
85
star
55

mtmc-vt

MTMC Vehicle Tracking For AI City challenge 2019
Jupyter Notebook
70
star
56

FeatureProbe

开源的高效可视化『功能』管理平台,提供灰度发布、AB实验、配置变更全功能。
66
star
57

sgt

didiyun super-agent daemon
Go
58
star
58

ALITA

ALITA is a layer-based data analysis tool. The front-end see
Java
51
star
59

react-tmap

一个基于腾讯地图JavaScript API GL、TypeScript 封装适用于 react 的高性能地图组件库
TypeScript
48
star
60

benchmark-thrift

An open source application designed to load test Thrift applications
Java
45
star
61

elastic-trib

elasticsearch cluster command line tool.
Go
39
star
62

mpx-cube-ui

基于"Mpx 小程序框架"的移动端基础组件库
TypeScript
27
star
63

yarc

基于 eBPF 的流量录制工具
C
26
star
64

tg-flow

Go
24
star
65

paladin

Scala
20
star
66

MEEP

End-to-end framework to build automatic agents (chatbots) for task-oriented dialogs
Python
18
star
67

WorkTrans

Python
15
star
68

ALITA_UI

ALITA is a layer-based data analysis tool. The back-end see
JavaScript
13
star
69

wmt2021_triangular_mt

The baseline model code for WMT 2021 Triangular MT
Python
13
star
70

MeetDot

Python
12
star
71

DAIBench

Shell
10
star
72

didi-tech-edu

9
star
73

daedalus

JavaScript
7
star
74

tmap-types

腾讯地图 js api 类型声明,主要用于 react-tmap 和 vue-tmap 组件库
5
star
75

Json-adapter

Java
3
star