What's the recommended way to connect to MySQL from Go?

Question

I am looking for a reliable solution to connect to a MySQL database from Go. I've seen some libraries around, but it is difficult to determine the different states of completeness and current maintenance. I don't have complex needs, but I'd like to know what people are relying on or the most standard solution to connect to MySQL.

Answer 1

A few drivers are available but you should only consider those that implement the database/sql API as

• it provides a clean and efficient syntax,
• it ensures you can later change the driver without changing your code, apart the import and connection.

Two fast and reliable drivers are available for MySQL :

• MyMySQL
• Go-MySQL-Driver

I've used both of them in production, programs are running for months with connection numbers in the millions without failure.

Other SQL database drivers are listed on go-wiki.

Import when using MyMySQL :

import (
    "database/sql"
    _ "github.com/ziutek/mymysql/godrv"
)

Import when using Go-MySQL-Driver :

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

Connecting and closing using MyMySQL :

con, err := sql.Open("mymysql", database+"/"+user+"/"+password)
defer con.Close()
// here you can use the connection, it will be closed when function returns

Connecting and closing using Go-MySQL-Driver :

con, err := sql.Open("mysql", store.user+":"+store.password+"@/"+store.database)
defer con.Close()

Select one row :

row := con.QueryRow("select mdpr, x, y, z from sometable where id=?", id)
cb := new(SomeThing)
err := row.Scan(&cb.Mdpr, &cb.X, &cb.Y, &cb.Z)

Select multiple rows and build an array with results :

rows, err := con.Query("select a, b from item where p1=? and p2=?", p1, p2)
if err != nil { /* error handling */}
items := make([]*SomeStruct, 0, 10)
var ida, idb uint
for rows.Next() {
    err = rows.Scan(&ida, &idb)
    if err != nil { /* error handling */}
    items = append(items, &SomeStruct{ida, idb})
}

Insert :

_, err = con.Exec("insert into tbl (id, mdpr, isok) values (?, ?, 1)", id, mdpr)

You'll see that working in Go with MySQL is a delightful experience : I never had a problem, my servers run for months without errors or leaks. The fact that most functions simply take a variable number of arguments lighten a task which is tedious in many languages.

Note that if, in the future, you need to use another MySQL driver, you'll just have to change two lines in one go file : the line doing the import and the line opening the connection.

Answer 2

few things to take note the select 1 row example :

row := con.QueryRow("select mdpr, x, y, z from sometable where id=?",id) 
cb := new(SomeThing) 
err := row.Scan(&cb.Mdpr, &cb.X, &cb.Y, &cb.Z)

there is a missing row.Next() in this example. it need to call the row.Next() to grab the first row returned.

also there is some inflexibility to the library which in some way try to promote data minimalism. if you try to select columns that is not Scan it will throw errors (not just warnings)

Answer 3

I see the Gorm as a perfect way to connect to Mysql or PostgreSQL.