We’ll start with the most popular one: “github.com/go-sql-driver/mysql”.

First, let’s create a package to handle/maintain connection.

package db

import (
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
	"time"
)

var Conn *sql.DB

func init() {
	var err error
	Conn, err = sql.Open("mysql", "db")
	checkErr(err)

	Conn.SetMaxOpenConns(10)
	Conn.SetMaxIdleConns(10)
	Conn.SetConnMaxLifetime(time.Duration(1800) * time.Second)
}

func checkErr(err error) {
	if err != nil {
		panic(err)
	}
}

Now we can create a handler to query it:

type Row struct (
	ID `json:"id"`
	Name `json:"name"`
)

func Find(q string) {
	var rows []*Rows

	rows, err := db.Conn.Query(q)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		a := Row{}
		rows.Scan(&a.ID, &a.Name)
		advices = append(advices, &a)
	}
	rows.Close()
}

However, after awhile with Benchmark, I realize the performanc wasn’t that great.

Let’s enter https://github.com/gocraft/dbr. DBR promise to give better perfomance.

Optionally, we can hook into its with a event logger for metric instrument

package db

import (
	"log"
)

type ELog struct {
}

// Event receives a simple notification when various events occur
func (n *ELog) Event(eventName string) {
	log.Println(eventName)
}

// EventKv receives a notification when various events occur along with
// optional key/value data
func (n *ELog) EventKv(eventName string, kvs map[string]string) {
	log.Println(eventName, kvs)
}

// EventErr receives a notification of an error if one occurs
func (n *ELog) EventErr(eventName string, err error) error {
	log.Println(eventName, err)
	return err
}

// EventErrKv receives a notification of an error if one occurs along with
// optional key/value data
func (n *ELog) EventErrKv(eventName string, err error, kvs map[string]string) error {
	return err
}

// Timing receives the time an event took to happen
func (n *ELog) Timing(eventName string, nanoseconds int64) {
	log.Println(eventName, " take ", nanoseconds)
}

// TimingKv receives the time an event took to happen along with optional key/value data
func (n *ELog) TimingKv(eventName string, nanoseconds int64, kvs map[string]string) {
	log.Println(eventName, " take ", nanoseconds, kvs)
}

This does nothing fancy but log the time so we can easily do benchmark in dev modes as a quick and dirty way.

Let’s get started with dbr. We almost keep the same thing. Just switch to dbr.

package db

import (
	//"database/sql"
	_ "github.com/go-sql-driver/mysql"
	"github.com/gocraft/dbr"
	"time"
)

var Conn *dbr.Connection
var elog ELog

func init() {
	var err error
	elog = ELog{}
	Conn, err = dbr.Open("mysql", "db", &elog)
	checkErr(err)

	Conn.SetMaxOpenConns(80)
	Conn.SetMaxIdleConns(80)
	Conn.SetConnMaxLifetime(time.Duration(1800) * time.Second)
}

func GetDbSession() *dbr.Session {
	return Conn.NewSession(nil)
}

func checkErr(err error) {
	if err != nil {
		panic(err)
	}
}

The important thing here is despite of switching to dbr, we still need to import the driver.

Now we can run any queries:

type Row Struct {
  Foo string
  Bar string
}

var rows []*Row
var sess = db.Conn.NewSession(nil)
sess.SelectBySql("SELECT foo, bar from table").LoadStructs(&rows)