What is dbx?
dbx = DB + Cache
It is a golang database library that supports transparent caching of all table data. When the memory is large enough, caching services such as Memcached and Redis are no longer needed.
Moreover, the speed of reading cache is quite fast, and the local test QPS reaches 3.5 million + / seconds, which can effectively simplify the application-side business logic code.
It supports MySQL/Sqlite3 and free nesting of structures.
Its implementation principle is to automatically scan the table structure, determine the primary key and self-adding column, and cache data according to the row by the primary key, manage the cache transparently according to the row, the upper layer only needs to operate according to the ordinary ORM style API.
After a simple test (with small data), Sqlite3 can be queried directly at a speed of 3w+/s, and 350 w+/s after opening the cache is much faster than Redis (because it has no network IO). It then supports caching (generally for small tables, ensuring that memory can be opened when it can be put down)
db.Bind("user", &User{}, true)
db.Bind("group", &Group{}, true)
db.EnableCache(true)Golang is a static language. Reflections are often used to implement more complex functions, but they slow down severely when they are not used properly. Practice has found that we should try our best to use digital index instead of string index, such as Field () performance is about 50 times that of FieldByName (). Most DB libraries do not support nesting because reflection is slow and complex, especially when there are too many nested layers. Fortunately, through hard work, DBX effectively implements the nesting support of unlimited layers, and the performance is good.
type Human struct {
Age int64 `db:"age"`
}
type User struct {
Human
Uid int64 `db:"uid"`
Gid int64 `db:"gid"`
Name string `db:"name"`
CreateDate time.Time `db:"createDate"`
}by golang's reflective feature, it can achieve the convenience close to script language level. As follows:
// Open database
db, err = dbx.Open("mysql", "root@tcp(localhost)/test?parseTime=true&charset=utf8")
// insert one
db.Table("user").Insert(u1)
// find one
db.Table("user").Where("uid=?", 1).One(&user)
// find one by primary key
db.Table("user").WherePK(1).One(u2)
// update one by primary key
db.Table("user").Update(u2)
// delete one by primary key
db.Table("user").WherePK(1).Delete()
// find multi rows
db.Table("user").Where("uid>?", 1).All(&userList)
// find multi rows by IN()
db.Table("user").Where("uid IN(?)", []int{1, 2, 3}).All(&userList)Free redirection of log data streams.
// output error information generated by DB to standard output (console)
db.Stderr = os.Stdout
// output the error information generated by DB to the specified file
db.Stderr = dbx.OpenFile("./db_error.log")
// default: redirect the output of DB (mainly SQL statements) to "black hole" (no information such as executed SQL statements is output)
db.Stdout = ioutil.Discard
// default: Output from DB (mainly SQL statements) to standard output (console)
db.Stdout = os.StdoutSometimes we need to call the native interface to achieve more complex purposes.
// customize complex SQL to get a single result (native)
var uid int64
err = db.QueryRow("SELECT uid FROM user WHERE uid=?", 2).Scan(&uid)
if err != nil {
panic(err)
}
fmt.Printf("uid: %v\n", uid)
db.Table("user").LoadCache() // Customization requires manual refresh of the cachedb, err = dbx.Open("cql", "root@tcp(192.168.0.129:9042)/btc")
dbx.Check(err)
defer db.Close()
more: example/test_cql/main.go
package main
import (
"github.com/xiuno/dbx"
"fmt"
"os"
"time"
)
type User struct {
Uid int64 `db:"uid"`
Gid int64 `db:"gid"`
Name string `db:"name"`
CreateDate time.Time `db:"createDate"`
}
func main() {
var err error
var db *dbx.DB
// db, err = dbx.Open("sqlite3", "./db1.db?cache=shared&mode=rwc&parseTime=true&charset=utf8") // sqlite3
db, err = dbx.Open("mysql", "root@tcp(localhost)/test?parseTime=true&charset=utf8") // mysql
dbx.Check(err)
defer db.Close()
// output to
db.Stdout = os.Stdout // output sql to os.Stdout
db.Stderr = dbx.OpenFile("./db_error.log") // output error to specified file
// db.Stdout = ioutil.Discard // default: output sql to black hole
// argument setting:
db.SetMaxIdleConns(10)
db.SetMaxOpenConns(10)
// db.SetConnMaxLifetime(time.Second * 5)
// create table
_, err = db.Exec(`DROP TABLE IF EXISTS user;`)
_, err = db.Exec(`CREATE TABLE user(
uid INT(11) PRIMARY KEY AUTO_INCREMENT,
gid INT(11) NOT NULL DEFAULT '0',
name VARCHAR(255) DEFAULT '',
createDate DATETIME DEFAULT CURRENT_TIMESTAMP
);
`)
dbx.Check(err)
// enable cache, optional, usually only for small tables to enable the cache, more than 10W rows, not recommended to open!
db.Bind("user", &User{}, true)
db.EnableCache(true)
// insert one
u1 := &User{1, 1, "jack", time.Now()}
_, err = db.Table("user").Insert(u1)
dbx.Check(err)
// read one
u2 := &User{}
err = db.Table("user").WherePK(1).One(u2)
dbx.Check(err)
fmt.Printf("%+v\n", u2)
// read one, check exists
err = db.Table("user").WherePK(1).One(u2)
dbx.Check(err)
if dbx.NoRows(err) {
panic("not found.")
}
fmt.Printf("%+v\n", u2)
// update one
u2.Name = "jack.ma"
_, err = db.Table("user").Update(u2)
dbx.Check(err)
// delete one
_, err = db.Table("user").WherePK(1).Delete()
dbx.Check(err)
// Where condition + update
_, err = db.Table("user").WhereM(dbx.M{{"uid", 1}, {"gid", 1}}).UpdateM(dbx.M{{"Name", "jet.li"}})
dbx.Check(err)
// insert multi
for i := int64(0); i < 5; i++ {
u := &User{
Uid: i,
Gid: i,
Name: fmt.Sprintf("name-%v", i),
CreateDate: time.Now(),
}
_, err := db.Table("user").Insert(u)
dbx.Check(err)
}
// fetch multi
userList := []*User{}
err = db.Table("user").Where("uid>?", 1).All(&userList)
dbx.Check(err)
for _, u := range userList {
fmt.Printf("%+v\n", u)
}
// multi update
_, err = db.Table("user").Where("uid>?", 3).UpdateM(dbx.M{{"gid", 10}})
dbx.Check(err)
// multi delete
_, err = db.Table("user").Where("uid>?", 3).Delete()
dbx.Check(err)
// count()
n, err := db.Table("user").Where("uid>?", -1).Count()
dbx.Check(err)
fmt.Printf("count: %v\n", n)
// sum()
n, err = db.Table("user").Where("uid>?", -1).Sum("uid")
dbx.Check(err)
fmt.Printf("sum(uid): %v\n", n)
// max()
n, err = db.Table("user").Where("uid>?", -1).Max("uid")
dbx.Check(err)
fmt.Printf("max(uid): %v\n", n)
// min()
n, err = db.Table("user").Where("uid>?", -1).Min("uid")
dbx.Check(err)
fmt.Printf("min(uid): %v\n", n)
// Customize complex SQL to get a single result (native)
var uid int64
err = db.QueryRow("SELECT uid FROM user WHERE uid=?", 2).Scan(&uid)
dbx.Check(err)
fmt.Printf("uid: %v\n", uid)
// db.Table("user").LoadCache() // Customization requires manual refresh of the cache
// Customize complex SQL to get multiple (native)
var name string
rows, err := db.Query("SELECT `uid`, `name` FROM `user` WHERE 1 ORDER BY uid DESC")
dbx.Check(err)
defer rows.Close()
for rows.Next() {
rows.Scan(&uid, &name)
fmt.Printf("uid: %v, name: %v\n", uid, name)
}
db.Table("user").LoadCache() // Customization requires manual refresh of the cache
return
}