Awesome Open Source
Awesome Open Source
Gosql
golang orm and sql builder
Stars
139
License
mit
Open Issues
2
Most Recent Commit
3 months ago
Related Projects
go (14,453)
golang (3,683)
awesome (1,319)
orm (359)
golang-library (135)
gin (83)
db (53)
gorm (49)
Repo

gosql

GoTest GoDoc codecov Go Report Card LICENSE

gosql is a easy ORM library for Golang.

Style:

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.

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

    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

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:[email protected](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

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

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

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

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

table: my_user

Exec

INSERT

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

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

batch insert

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)

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

UPDATE

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

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

DELETE

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

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

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

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)
gosql.Where("id",1)
//sql: id = 1
  • gosql.Where("[>]age",18)
gosql.Where("[>]age",18)
//sql: age > 18
  • gosql.Where("[in]id",[]int{1,2})
gosql.Where("[in]id",[]int{1,2})
//sql: id in (1,2)
  • gosql.Where("[!in]id",[]int{1,2})
gosql.Where("[!in]id",[]int{1,2})
//sql: id not in (1,2)
  • gosql.Where("[~]name","ja%")
gosql.Where("[~]name","ja%")
//sql: name like 'ja%'
  • gosql.Where("[!~]name","ja%")
gosql.Where("[!~]name","ja%")
//sql: name not like 'ja%'

symbol [?]

  • [=] equal
gosql.Where("[=]id",1)
//sql: id = 1
  • [!=] not equal
gosql.Where("[!=]id",1)
//sql: id != 1
  • [>] greater than
gosql.Where("[>]id",1)
//sql: id > 1
  • [>=] greater or equal
gosql.Where("[>=]id",1)
//sql: id >= 1
  • [<] less
gosql.Where("[<]id",1)
//sql: id < 1
  • [<=] less or equal
gosql.Where("[<=]id",1)
//sql: id <= 1
  • [in] in
gosql.Where("[in]id",[]int{1,2})
//sql: id in (1,2)
  • [!in] not in
gosql.Where("[!in]id",[]int{1,2})
//sql: id not in (1,2)
  • [is] is null
gosql.Where("[is]name",nil)
//sql: name is null
  • [!is] not is null
gosql.Where("[!is]name",nil)
//sql: id is not null
  • [exists] exists
gosql.Where("[exists]name","select 1")
//sql: name exists(select 1)
  • [!exists] not exists
gosql.Where("[!exists]name","select 1")
//sql: name not exists(select 1)
  • [#] sql
gosql.Where("[#]age=age-1")
//sql: age = age-1

Raw SQL: db.Query()

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
ret,err := db.Primary().Fetch(...)
  • db.Replica() change to replica
ret,err := db.Replica().Fetch(...)

Paging

Define a page function and return gosql.Option sturct

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

gosql.NewCollect(
    gosql.NewCluster(
        gosql.AddDb("mysql", "user:[email protected](127.0.0.1:3306)/test?parseTime=true&readTimeout=3s&writeTimeout=3s&timeout=3s"),
    ),
    "db1",
)
gosql.NewCollect(
    gosql.NewCluster(
        gosql.AddDb("mysql", "user:[email protected](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
s := builder.New()
  • builder.Flag(f string) set a flag
s.Flag("test")
  • builder.Field(fields string) Specified columns

default value *

s.Field("*")
  • builder.Table(tbl string) Specified table name
s.Table("tbl.t1")

Where

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

  • Eq
s.Where("t1.status", "0")
//sql: t1.status = 0
  • Not Eq
s.Where("[!=]t1.status", "0")
//sql: t1.status != 0
  • In
s.Where("[in]field", []string{"a", "b", "c"})
//sql: t1.field in (a,b,c)
  • No In
s.Where("[!in]field", []string{"a", "b", "c"})
//sql: t1.status in (a,b,c)

Nested Where

  • s.Where(func(s *builder.Clause){}
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
s.GroupBy("class")
//sql: group by `class`
  • Order By
s.OrderBy("id desc", "age asc")
//sql: order by `id` desc, `age` asc
  • Limit
s.Limit(10)
//sql: limit 10
  • Offset
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.


Get A Weekly Email With Trending Projects For These Topics
No Spam. Unsubscribe easily at any time.
go (14,453) 
golang (3,683) 
awesome (1,319) 
orm (359) 
golang-library (135) 
gin (83) 
db (53) 
gorm (49) 

Find Open Source By Browsing 7,000 Topics Across 59 Categories

Advertising 📦 10
All Projects
Application Programming Interfaces 📦 124
Applications 📦 192
Artificial Intelligence 📦 78
Blockchain 📦 73
Build Tools 📦 113
Cloud Computing 📦 80
Code Quality 📦 28
Collaboration 📦 32
Command Line Interface 📦 49
Community 📦 83
Companies 📦 60
Compilers 📦 63
Computer Science 📦 80
Configuration Management 📦 42
Content Management 📦 175
Control Flow 📦 213
Data Formats 📦 78
Data Processing 📦 276
Data Storage 📦 135
Economics 📦 64
Frameworks 📦 215
Games 📦 129
Graphics 📦 110
Hardware 📦 152
Integrated Development Environments 📦 49
Learning Resources 📦 166
Legal 📦 29
Libraries 📦 129
Lists Of Projects 📦 22
Machine Learning 📦 347
Mapping 📦 64
Marketing 📦 15
Mathematics 📦 55
Media 📦 239
Messaging 📦 98
Networking 📦 315
Operating Systems 📦 89
Operations 📦 121
Package Managers 📦 55
Programming Languages 📦 245
Runtime Environments 📦 100
Science 📦 42
Security 📦 396
Social Media 📦 27
Software Architecture 📦 72
Software Development 📦 72
Software Performance 📦 58
Software Quality 📦 133
Text Editors 📦 49
Text Processing 📦 136
User Interface 📦 330
User Interface Components 📦 514
Version Control 📦 30
Virtualization 📦 71
Web Browsers 📦 42
Web Servers 📦 26
Web User Interface 📦 210
"Gosql" and other potentially trademarked words, copyrighted images and copyrighted readme contents likely belong to the legal entity who owns the "Rushteam" organization. Awesome Open Source is not affiliated with the legal entity who owns the "Rushteam" organization.