Skip to content
/ mssqlx Public

Database client library, proxy for any master slave, master master structures. Lightweight, performant and auto balancing in mind.

License

Notifications You must be signed in to change notification settings

linxGnu/mssqlx

Repository files navigation

mssqlx

Go Report Card Coverage Status godoc

Embeddable, high availability, performance and lightweight database client library. Support go 1.9 or newer.

Features and concepts are:

  • Builtin layer/extension to sqlx.
  • Auto proxy for any master-slave, master-master databases. Compatible with Wsrep, Galera Cluster and others.
  • Auto and lightweight round-robin balancer for queries.
  • Builtin error handling for Wsrep, Galera and some database drivers. Auto retry select/get/query queries when detected bad connection causing by DBMS's timeout policy which auto-closes non interactive/timeout connection.
  • Auto health checking.

For more detail of api, please refer to godoc

Install

go get -u github.com/linxGnu/mssqlx

Connecting to Databases

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)

Connecting to Galera Cluster

Recommended to set flag as following:

db, _ := mssqlx.ConnectMasterSlaves("mysql", masterDSNs, slaveDSNs, mssqlx.WithWsrep())

Connecting to Databases with custom read-query source

Read-queries will be distributed among both masters and slaves:

db, _ := mssqlx.ConnectMasterSlaves("mysql", masterDSNs, slaveDSNs, mssqlx.WithReadQuerySource(mssqlx.ReadQuerySourceAll))

Configuration

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)

Select

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)

Get

var person Person
db.Get(&person, "SELECT * FROM person WHERE id = ?", 1)

Queryx

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

Named query

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

Exec (insert/update/delete/etc...)

result, err := db.Exec("DELETE FROM person WHERE id < ?", 100)

Transaction

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

Notices

  • APIs supports executing query on master-only or slave-only (or boths). Function name for querying on master-only has suffix OnMaster, querying on slaves-only has suffix OnSlave.
  • Default select/show queries are balanced on slaves.

About

Database client library, proxy for any master slave, master master structures. Lightweight, performant and auto balancing in mind.

Topics

Resources

License

Stars

Watchers

Forks

Packages

No packages published

Languages