Database client library, proxy for any master slave, master master structures. Lightweight, performant and auto balancing in mind.
Embeddable, high availability, performance and lightweight database client library. Support go 1.9 or newer.
Features and concepts are:
select/get/query
queries when detected bad connection causing by DBMS's timeout policy which auto-closes non interactive/timeout connection.For more detail of api, please refer to godoc
go get -u github.com/linxGnu/mssqlx
mssqlx is compatible to all kind of databases which database/sql
supports. Below code is mysql
usage:
import (
_ "github.com/go-sql-driver/mysql"
"github.com/linxGnu/mssqlx"
)
dsn := "root:123@(%s:3306)/test?charset=utf8&collation=utf8_general_ci&parseTime=true"
masterDSNs := []string{
fmt.Sprintf(dsn, "172.31.25.233"), // address of master 1
fmt.Sprintf(dsn, "172.31.24.233"), // address of master 2 if have
fmt.Sprintf(dsn, "172.31.23.233"), // address of master 3 if have
}
slaveDSNs := []string{
fmt.Sprintf(dsn, "172.31.25.234"), // address of slave 1
fmt.Sprintf(dsn, "172.31.25.235"), // address of slave 2
fmt.Sprintf(dsn, "172.31.25.236"), // address of slave 3
}
db, _ := mssqlx.ConnectMasterSlaves("mysql", masterDSNs, slaveDSNs)
Recommended to set flag as following:
db, _ := mssqlx.ConnectMasterSlaves("mysql", masterDSNs, slaveDSNs, mssqlx.WithWsrep())
Read-queries will be distributed among both masters and slaves:
db, _ := mssqlx.ConnectMasterSlaves("mysql", masterDSNs, slaveDSNs, mssqlx.WithReadQuerySource(mssqlx.ReadQuerySourceAll))
It's highly recommended to setup configuration before querying.
db.SetMaxIdleConns(20) // set max idle connections to all nodes
// db.SetMasterMaxIdleConns(20) // set max idle connections to master nodes
// db.SetSlaveMaxIdleConns(20) // set max idle connections to slave nodes
db.SetMaxOpenConns(50) // set max open connections to all nodes
// db.SetMasterMaxOpenConns(50)
// db.SetSlaveMaxOpenConns(50)
// if nodes fail, checking healthy in a period (in milliseconds) for auto reconnect. Default is 500.
db.SetHealthCheckPeriod(1000)
// db.SetMasterHealthCheckPeriod(1000)
// db.SetSlaveHealthCheckPeriod(1000)
type Person struct {
FirstName string `db:"first_name"`
LastName string `db:"last_name"`
Email string
Data []byte
}
var people []Person
db.Select(&people, "SELECT * FROM person WHERE id > ? and id < ? ORDER BY first_name ASC", 1, 1000)
var person Person
db.Get(&person, "SELECT * FROM person WHERE id = ?", 1)
// Loop through rows using only one struct
var person Person
rows, err := db.Queryx("SELECT * FROM person") // or db.QueryxOnMaster(...)
for rows.Next() {
if err := rows.StructScan(&person); err != nil {
log.Fatalln(err)
}
fmt.Printf("%#v\n", person)
}
// Loop through rows using only one struct
var person Person
rows, err := db.NamedQuery(`SELECT * FROM person WHERE first_name = :fn`, map[string]interface{}{"fn": "Bin"}) // or db.NamedQueryOnMaster(...)
for rows.Next() {
if err := rows.StructScan(&person); err != nil {
log.Fatalln(err)
}
fmt.Printf("%#v\n", person)
}
result, err := db.Exec("DELETE FROM person WHERE id < ?", 100)
// Recommended write transaction this way
tx, e := db.Begin()
if e != nil {
return e
}
shouldAutoRollBack := true
defer func() {
if e := recover(); e != nil {
err = fmt.Errorf("%v", e)
tx.Rollback()
} else if err != nil && shouldAutoRollBack {
tx.Rollback()
}
}()
if _, err = tx.Exec("INSERT INTO person(first_name, last_name, email, data) VALUES (?,?,?,?)", "Jon", "Dow", "jon@gmail", []byte{1, 2}); err != nil {
return
}
if _, err = tx.Exec("INSERT INTO person(first_name, last_name, email, data) VALUES (?,?,?,?)", "Jon", "Snow", "snow@gmail", []byte{1}); err != nil {
return
}
if err = tx.Commit(); err != nil {
shouldAutoRollBack = false
}
OnMaster
, querying on slaves-only has suffix OnSlave
.select/show queries
are balanced on slaves.