Popularity
4.4
Declining
Activity
4.1
-
102
11
15

Programming language: Go
License: MIT License
Tags: Utilities    
Latest version: v1.1.1

mssqlx alternatives and similar packages

Based on the "Utilities" category.
Alternatively, view mssqlx alternatives based on common mentions on social networks and blogs.

Do you think we are missing an alternative of mssqlx or a related project?

Add another 'Utilities' Package

README

mssqlx

Build Status Go Report Card Coverage Status godoc license

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.


*Note that all licence references and agreements mentioned in the mssqlx README section above are relevant to that project's source code only.