I am using this code to handle all my database CRUD.
Similar issue: https://github.com/go-sql-driver/mysql/issues/556
package funcs
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"log"
)
var DbConn *sql.DB
func DbOpenConn() (*sql.DB, error) {
connstr := GetDBConnString()
DbConn, err := sql.Open("mysql", connstr)
CheckErr(err)
DbConn.SetMaxIdleConns(40)
DbConn.SetMaxOpenConns(100)
return DbConn, err
}
func FixConn() (*sql.DB) {
checkping := DbConn.Ping()
var err error
if checkping != nil { //if error
log.Println("DbConn.Ping failed here. Creating a new connection.")
DbConn, err = DbOpenConn()
CheckErr(err)
return DbConn
}
return DbConn
}
func DbExecute(db *sql.DB, query string, params ...interface{}) (sql.Result, error) {
db = FixConn()
stmt, err := db.Prepare(query)
CheckErr(err)
res, err := stmt.Exec(params...)
CheckErr(err)
stmt.Close()
return res, err
}
func DbInsert(db *sql.DB, query string, params ...interface{}) (int64, error) {
db = FixConn()
res, err := DbExecute(db, query, params...)
CheckErr(err)
id, err := res.LastInsertId()
CheckErr(err)
return id, err
}
func DbUpdate(db *sql.DB, query string, params ...interface{}) (int64, error) {
db = FixConn()
res, err := DbExecute(db, query, params...)
CheckErr(err)
affect, err := res.RowsAffected()
CheckErr(err)
return affect, err
}
func DbDelete(db *sql.DB, query string, params ...interface{}) (int64, error) {
var affect int64
var err error
db = FixConn()
affect, err = DbUpdate(db, query, params...)
CheckErr(err)
return affect, err
}
func DbQueryGetRows(db *sql.DB, query string) (*sql.Rows, error) {
var rows *sql.Rows
var err error
db = FixConn()
rows, err = db.Query(query)
CheckErr(err)
return rows, err
}
func DbQueryGetRowsParams(db *sql.DB, query string, params ...interface{}) (*sql.Rows, error) {
db = FixConn()
stmt, err1 := db.Prepare(query)
CheckErr(err1)
rows, err2 := stmt.Query(params...)
CheckErr(err2)
stmt.Close()
return rows, err2
}
func DbQueryGetRowsParamsSingle(db *sql.DB, query string, params ...interface{}) (*sql.Rows, error) {
var err error
db = FixConn()
CheckErr(err)
stmt, err := db.Prepare(query)
CheckErr(err)
rows, err := stmt.Query(params...)
CheckErr(err)
stmt.Close()
return rows, err
}
func FetchRowsByID(db *sql.DB, query string, param string) (*sql.Rows, error) {
var rows *sql.Rows
var err error
db = FixConn()
CheckErr(err)
rows, err = db.Query(query, param)
CheckErr(err)
return rows, err
}
func DbCloseConn(db *sql.DB) {
db.Close()
}
func CountRows(rows *sql.Rows) int {
count := 0
for rows.Next() {
count++
}
return count
}
I created the above mentioned code and I don’t think that I am leaking anything in it.
Whenever I call these functions from other packages, I always use rows.Close() (doubled checked) after I am done using the returned rows. I also added a log message tracking if the connection is dying in FixConn() function (but it is not).
Another place where I am checking if DbConn is still alive is main function.
if funcs.DbConn == nil {
log.Println("Creating new Connection. DbConn is Dead")
var err error
funcs.DbConn, err = funcs.DbOpenConn()
funcs.CheckErr(err)
}
One more thing: I haven’t used db.Close() anywhere in my code. I am not sure whether it is a good idea to open & close the DB connection with each CRUD request.
Please correct me if I am doing something wrong.
Thanks