tsenart / nap

A Go sql.DB wrapper for master-slave topologies

Nap

Nap is a library that abstracts access to master-slave physical SQL servers topologies as a single logical database mimicking the standard sql.DB APIs.

Nap requires Go version 1.8 or greater.

Install

$ go get github.com/tsenart/nap

Usage

package main

import (
  "log"

  "github.com/tsenart/nap"
  _ "github.com/go-sql-driver/mysql" // Any sql.DB works
)

func main() {
  // The first DSN is assumed to be the master and all
  // other to be slaves
  dsns := "tcp://user:password@master/dbname;"
  dsns += "tcp://user:password@slave01/dbname;"
  dsns += "tcp://user:password@slave02/dbname"

  db, err := nap.Open("mysql", dsns)
  if err != nil {
    log.Fatal(err)
  }
  
  if err := db.Ping(); err != nil {
    log.Fatalf("Some physical database is unreachable: %s", err)
  }

  // Read queries are directed to slaves with Query and QueryRow.
  // Always use Query or QueryRow for SELECTS
  // Load distribution is round-robin only for now.
  var count int
  err = db.QueryRow("SELECT COUNT(*) FROM sometable").Scan(&count)
  if err != nil {
    log.Fatal(err)
  }

  // Write queries are directed to the master with Exec.
  // Always use Exec for INSERTS, UPDATES
  err = db.Exec("UPDATE sometable SET something = 1")
  if err != nil {
    log.Fatal(err)
  }

  // Prepared statements are aggregates. If any of the underlying
  // physical databases fails to prepare the statement, the call will
  // return an error. On success, if Exec is called, then the
  // master is used, if Query or QueryRow are called, then a slave
  // is used.
  stmt, err := db.Prepare("SELECT * FROM sometable WHERE something = ?")
  if err != nil {
    log.Fatal(err)
  }

  // Transactions always use the master
  tx, err := db.Begin()
  if err != nil {
    log.Fatal(err)
  }
  // Do something transactional ...
  if err = tx.Commit(); err != nil {
    log.Fatal(err)
  }

  // If needed, one can access the master or a slave explicitly.
  master, slave := db.Master(), db.Slave()
}

Todo

• Support other slave load balancing algorithms.

License

See LICENSE

nap's Issues

How to use with GORM?

I want to use nap with github.com/jinzhu/gorm.
https://github.com/jinzhu/gorm

I had tried below this code.

db, _ := nap.Open("mysql", napConnectionString)
conn, err = gorm.Open("mysql", db)

However, I got an error nvalid database source: {[0xc00019c480] 0} is not a valid type.

I think it causes that nap.Stmt.Prepare() method is different from go.SQLCommon.Prepare().

https://github.com/tsenart/nap/blob/master/db.go#L97
https://github.com/jinzhu/gorm/blob/master/interface.go#L8

How do I solve this?
Thanks.

Slave recovery

It looks like if a slave dies, some % of slave requests will simply fail. Feature request: support failure detection, and some kind of circuit-breaker recovery mechanism.

Port to gorm

Can I use this with the gorm(https://github.com/jinzhu/gorm).

If yes, can you give any example code(noob here).

db.pdbs[i].SetConnMaxLifetime undefined (type *sql.DB has no field or method SetConnMaxLifetime)

github.com/tsenart/nap

../src/github.com/tsenart/nap/db.go:130: db.pdbs[i].SetConnMaxLifetime undefined (type *sql.DB has no field or method SetConnMaxLifetime)

deleting func (db *DB) SetConnMaxLifetime(d time.Duration)

fixes the problem

assigning stmts from goroutine might result in data race

nap/db.go

Line 101 in a650a3f

stmts[i], err = db.pdbs[i].Prepare(query)

here might need some kind of locking over stmts variable.