[Query] Best Performance from MyMysql

[olliemoves]

Hi,

Firstly thank you for writing such a good library. This is not a bug I am just seeking some advice on using the library. I have currently written my database layer using MyMysql for a big project and I am very happy with how everything is working but I wanted to check that I am using it correctly as I think I might be opening and closing connections every time I connect to the database and I was hoping to use connection pooling type functionality.

I have a getConnection class which is used by all methods and I then use defer to close the connection at the end of the method:

import (
    "github.com/ziutek/mymysql/mysql"
    "github.com/ziutek/mymysql/thrsafe"
    "strconv"
    "tribeguru/configuration"
)

func getConnection() (mysql.Conn, error) {
    db := thrsafe.New("tcp", "", configuration.Get("dbHost"), configuration.Get("dbUser"), configuration.Get("dbPass"), configuration.Get("dbName"))
    err := db.Connect()
    if err != nil {
        return nil, err
    }
    return db, nil
}

type Course struct {
    Id          int
    PersonId    int
    Name        string
    Description string
}

func getCourse(row mysql.Row) (*Course, error) {
    if row == nil {
        return nil, errors.New("No row passed")
    }
    course := &Course{
        Id:          row.Int(0),
        PersonId:    row.Int(1),
        Name:        row.Str(2),
        Description: row.Str(3),
    }
    return course, nil
}

func GetCourseById(id int) (*Course, error) {
    db, err := getConnection()
    if err != nil {
        return nil, err
    }
    defer db.Close()
    row, _, err := db.QueryFirst("SELECT Id, PersonId, Name, Description FROM Course WHERE Id=%d;", id)
    if err != nil {
        return nil, err
    }
    course, err := getCourse(row)
    return course, err
}

As you can see in the GetCourseById I defer db.Close() so it closes at the end of the method. Does the MyMysql library support connection pooling or should I use the auto reconnect library and never close the connection?

[ziutek]

For connection pooling use database/sql package and mymysql/godrv as driver. Furthermore, if you use database/sql, your application can work with any database system (Postgress, . SQLite, MongoDB, ...)

Use raw mymysql only if you have specific problem that is difficult to be resolved using database/sql. In this case mymysql/autorc is preferred. Use mymysql/mysql only if you need some functionality that autorc lacks or if you want implement over it your own higher level interface (eg: connection pooling: http://www.ryanday.net/2012/09/12/golang-using-channels-for-a-connection-pool/).

See also: https://github.com/ziutek/mymysql/issues/42

Don't create an issue for simple question! Issue tracker is for issues. You can send such questions to golang-nuts list where are many people that can help you.