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)

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

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

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
}