gosql

gosql is a easy ORM library for Golang.

Style:

golangCopy
var userList []UserModel
err := db.FetchAll(&userList,
    gosql.Columns("id","name"),
    gosql.Where("status", 1),
    gosql.Where("[like]name", "j%"),
    gosql.OrWhere(func(s *gosql.Clause) {
        s.Where("[>]score", "90")
        s.Where("[<]score", "100")
    }),
    gosql.GroupBy("type"),
    gosql.OrderBy("score DESC"),
    gosql.Offset(0),
    gosql.Limit(10),
)
// select id,name from user where status =1 and name like 'j%' or (score > 90 and score <100) group by type order by score DESC limit 0,10;

Feature

• Functional Options Style SQL builder
• Unlimited nesting query
• Reading and Writing Separation
• Delay connection creation
• ORM mapping to struct
• Support transaction
• Versatile
• Clean Code
• Bulk Insert

Structure

• db.go: Basic struct definition
• pool.go: Manage DB pool
• session.go: Session and Model
• builder.go: Building SQL
• scanner/*: scan struct

Why build this wheels

I have read almost all open source operation database library implemented in golang on github. But never get the optimal solution.

Such as these:

1. gorm: Does not support read and write separation.

2. gendry: Occupy special keywords and partially ugly syntax.

3. sqlx: Mostly good, But the syntax is not simple enough, and does not support the separation of reading and writing.

This project refers to a large number of existing libs, refers to various documents, and uses golang style to achieve from scratch.

NOTE

NOTE: Only supports mysql driver.

Demo

Let's look a demo frist.

sqlCopy
SELECT DISTINCT *
FROM `tbl1`.`t1`
    JOIN `tbl3` ON `a` = `b`
WHERE (`t1`.`status` = ?
    AND `name` = ?
    AND `nick` != ?
    AND `role1` IN (?, ?, ?, ?)
    AND `role2` NOT IN (?, ?, ?, ?)
    AND `card1` IN (?)
    AND `card2` NOT IN (?)
    AND (`age` > ?
        AND `age` < ?)
    AND v1 = 1
    AND v2 = ?
    AND `desc` LIKE ?
    AND `desc` NOT LIKE ?
    AND EXISTS (
        SELECT 1
    )
    AND NOT EXISTS (
        SELECT *
        FROM `tbl2`.`t2`
        WHERE `t2`.`id` = ?
    ))
GROUP BY `class`, `group`
HAVING `class` = ?
ORDER BY `score` DESC, `name` ASC, `age`
LIMIT 10, 30
FOR UPDATE

golangCopy
    s := gosql.NewSQLSegment()
    s.Flag("DISTINCT")
    s.Field("*")
    s.Table("tbl1.t1")
    s.Where("t1.status", "0")
    s.Where("name", "jack")
    s.Where("[!=]nick", "tom")
    s.Where("[in]role1", []string{"1", "2", "3", "4"})
    s.Where("[!in]role2", []string{"1", "2", "3", "4"})
    s.Where("[in]card1", 1)
    s.Where("[!in]card2", 1)
    s.Where(func(s *gosql.Clause) {
        s.Where("[>]age", "20")
        s.Where("[<]", "50")
    })
    s.Where("v1 = 1")
    s.Where("[#]v2 = ?", 2)
    s.Join("tbl3", "a", "=", "b")
    s.Having("class", "one")
    s.Where("[~]desc", "student")
    s.Where("[!~]desc", "teacher")
    s.Where("[exists]my_card", "select 1")
    s.Where("[!exists]my_card2", func(s *SQLSegments) {
        s.Table("tbl2.t2")
        s.Where("t2.id", 10000)
    })
    s.GroupBy("class","group")
    s.OrderBy("score desc", "name asc","age")
    s.Limit(30)
    s.Offset(10)
    s.ForUpdate()
    fmt.Println(s.BuildSelect())

Getting Started

golangCopy
package main

import (
    "fmt"

    _ "github.com/go-sql-driver/mysql"
    "github.com/rushteam/gosql"
)

type UserModel struct {
    ID   int    `db:"id"`
    Name string `db:"name"`
}

func (u *UserModel) TableName() string {
    return "my_user"
}

func main() {
    db := gosql.NewCluster(
        gosql.AddDb("mysql", "user:password@tcp(127.0.0.1:3306)/test?parseTime=true&readTimeout=3s&writeTimeout=3s&timeout=3s"),
    )
    user := &UserModel{}
    err := db.Fetch(user, 
    	gosql.Where("id", 1), 
	gosql.Where("[like]name", "j%"),
    )
    if err != nil {
        fmt.Println(err)
    }
    fmt.Println(user)
}

Doc

Debug Mode

golangCopy
//this code will be start at debug mode and the sql will be print
gosql.Debug = true

Struct Model

To define a Model struct, use the struct and tag syntax.

Simple define a model

golangCopy
type User struct {
    ID int64
    Age int
    Name string
    CreatedAt time.Time
}

Usually define a Struct can be used as a model, gosql will parse out the table name, field mapping relationship,etc.

table: user
columns: id,age,name,created_at

Using tag syntax

Use structure tags to customize field mapping

golangCopy
type User struct {
    ID int64 `db:"uid,pk"`
    Age int `db:"age"`
    Name string `db:"fisrt_name"`
    CreatedAt time.Time `db:"created_at"`
}

table: user
columns: uid,age,fisrt_name,created_at
pk: uid

Define table name

Implement "TableName" method to specify the table name

golangCopy
type User struct {}
func (u *User) TableName() string {
    return "my_user"
}

table: my_user

Exec

INSERT

db.Insert(dst interface{}, opts ...Option) (Result, error)

golangCopy
user := &UserModel{}
user.Name = "jack"
ret,err := db.Insert(&user)

batch insert

golangCopy
users := []UserModel{}
u1 := UserModel{Name:"jack"}
u2 := UserModel{Name:"Tom"}
users = append(users,u1)
users = append(users,u2)
ret,err := db.Insert(users)

REPLACE

db.Replace(dst interface{}, opts ...Option) (Result, error)

golangCopy
user := &UserModel{}
user.Name = "jack"
ret,err := db.Replace(&user,gosql.Where("id",1))

UPDATE

Update(dst interface{}, opts ...Option) (Result, error)

golangCopy
user := &UserModel{}
user.Name = "jack Ma"
ret,err := db.Update(&user,gosql.Where("id",1))

DELETE

db.Delete(dst interface{}, opts ...Option) (Result, error)

golangCopy
user := &UserModel{}
ret,err := db.Delete(&user,gosql.Where("id",1))
//sql: delete from my_user where id = 1

QUERY

Get a record: db.Fetch(dst interface{}, opts ...Option) error

golangCopy
user := &UserModel{}
err := db.Fetch(user,
    gosql.Columns("id","name"),
    gosql.Where("id", 1),
    gosql.Where("[like]name", "j%"),
    gosql.OrWhere(func(s *gosql.Clause) {
        s.Where("[>=]score", "90")
        s.Where("[<=]age", "100")
    }),
    gosql.GroupBy("type"),
    gosql.OrderBy("score DESC"),
)

Get multiple records: db.FetchAll(dst interface{}, opts ...Option) error

golangCopy
var userList []UserModel
err := db.FetchAll(&userList,
    gosql.Columns("id","name"),
    gosql.Where("id", 1),
    gosql.Where("[like]name", "j%"),
    gosql.OrWhere(func(s *gosql.Clause) {
        s.Where("[>]score", "90")
        s.Where("[<]score", "100")
    }),
    gosql.GroupBy("type"),
    gosql.OrderBy("score DESC"),
    gosql.Offset(0),
    gosql.Limit(10),
)

OPTION

WHERE

• gosql.Where("id",1)

golangCopy
gosql.Where("id",1)
//sql: id = 1

• gosql.Where("[>]age",18)

golangCopy
gosql.Where("[>]age",18)
//sql: age > 18

• gosql.Where("[in]id",[]int{1,2})

golangCopy
gosql.Where("[in]id",[]int{1,2})
//sql: id in (1,2)

• gosql.Where("[!in]id",[]int{1,2})

golangCopy
gosql.Where("[!in]id",[]int{1,2})
//sql: id not in (1,2)

• gosql.Where("[~]name","ja%")

golangCopy
gosql.Where("[~]name","ja%")
//sql: name like 'ja%'

• gosql.Where("[!~]name","ja%")

golangCopy
gosql.Where("[!~]name","ja%")
//sql: name not like 'ja%'

symbol [?]

• [=] equal

golangCopy
gosql.Where("[=]id",1)
//sql: id = 1

• [!=] not equal

golangCopy
gosql.Where("[!=]id",1)
//sql: id != 1

• [>] greater than

golangCopy
gosql.Where("[>]id",1)
//sql: id > 1

• [>=] greater or equal

golangCopy
gosql.Where("[>=]id",1)
//sql: id >= 1

• [<] less

golangCopy
gosql.Where("[<]id",1)
//sql: id < 1

• [<=] less or equal

golangCopy
gosql.Where("[<=]id",1)
//sql: id <= 1

• [in] in

golangCopy
gosql.Where("[in]id",[]int{1,2})
//sql: id in (1,2)

• [!in] not in

golangCopy
gosql.Where("[!in]id",[]int{1,2})
//sql: id not in (1,2)

• [is] is null

golangCopy
gosql.Where("[is]name",nil)
//sql: name is null

• [!is] not is null

golangCopy
gosql.Where("[!is]name",nil)
//sql: id is not null

• [exists] exists

golangCopy
gosql.Where("[exists]name","select 1")
//sql: name exists(select 1)

• [!exists] not exists

golangCopy
gosql.Where("[!exists]name","select 1")
//sql: name not exists(select 1)

• [#] sql

golangCopy
gosql.Where("[#]age=age-1")
//sql: age = age-1

Raw SQL: db.Query()

golangCopy
rows,err := db.Query("select * from my_user where id = ?",1)
//sql: select * from my_user where id = 1

select primary or replica

• db.Primary() change to primary db

golangCopy
ret,err := db.Primary().Fetch(...)

• db.Replica() change to replica

golangCopy
ret,err := db.Replica().Fetch(...)

Paging

Define a page function and return gosql.Option sturct

golangCopy
//Page  pn: per page num ,ps: page size
func Page(pn, ps int) gosql.Option {
	if pn < 1 {
		pn = 1
	}
	return func(s gosql.SQLSegments) gosql.SQLSegments {
		s.Limit(ps)
		s.Offset((pn - 1) * ps)
		return s
	}
}
func main() {
    user := &UserModel{}
    err := db.Fetch(user,
        Page(1,15),
    )
}

multi-database

golangCopy
gosql.NewCollect(
    gosql.NewCluster(
        gosql.AddDb("mysql", "user:password@tcp(127.0.0.1:3306)/test?parseTime=true&readTimeout=3s&writeTimeout=3s&timeout=3s"),
    ),
    "db1",
)
gosql.NewCollect(
    gosql.NewCluster(
        gosql.AddDb("mysql", "user:password@tcp(127.0.0.1:3306)/test?parseTime=true&readTimeout=3s&writeTimeout=3s&timeout=3s"),
    ),
    "db2",
)

db1 := gosql.Collect("db1")


db2 := gosql.Collect("db2")

builder of API

• builder.New() start a builder

golangCopy
s := builder.New()

• builder.Flag(f string) set a flag

golangCopy
s.Flag("test")

• builder.Field(fields string) Specified columns

default value *

golangCopy
s.Field("*")

• builder.Table(tbl string) Specified table name

golangCopy
s.Table("tbl.t1")

Where

builder.Where(key string, val inferface{})

• Eq

golangCopy
s.Where("t1.status", "0")
//sql: t1.status = 0

• Not Eq

golangCopy
s.Where("[!=]t1.status", "0")
//sql: t1.status != 0

• In

golangCopy
s.Where("[in]field", []string{"a", "b", "c"})
//sql: t1.field in (a,b,c)

• No In

golangCopy
s.Where("[!in]field", []string{"a", "b", "c"})
//sql: t1.status in (a,b,c)

Nested Where

• s.Where(func(s *builder.Clause){}

golangCopy
s.Where("[!]t1.a",1).Where(func(s *builder.Clause){
    s.Where("t1.b",1)
    s.OrWhere("t1.c",1)
})
//sql: t1.a != 1  and (t1.b = 1 or t1.c = 1)

Other statements

• Group By

golangCopy
s.GroupBy("class")
//sql: group by `class`

• Order By

golangCopy
s.OrderBy("id desc", "age asc")
//sql: order by `id` desc, `age` asc

• Limit

golangCopy
s.Limit(10)
//sql: limit 10

• Offset

golangCopy
s.Offset(10)
//sql: offset 10

Contributing

When everybody adds fuel, the flames rise high.

Let's build our self library.

You will be a member of rushteam which is An open source organization

Thanks for you, Good Lucy.