Using Go with MariaDB_MySQL

MariaDB

I have been using Google’s Go programming language for a couple of years and basically fell in love with it. About a year ago I started using it for almost all of my MariaDB and MySQL development, and pretty much everything I do unless it involves the python pandas library (a subject for a separate blog, perhaps).

While I could gush endlessly about Go (golang), the documentation sums things up quite nicely: “Go is expressive, concise, clean, and efficient. Its concurrency mechanisms make it easy to write programs that get the most out of multicore and networked machines, while its novel type system enables flexible and modular program construction. Go compiles quickly to machine code yet has the convenience of garbage collection and the power of run-time reflection. It's a fast, statically typed, compiled language that feels like a dynamically typed, interpreted language.”

I won’t delve deeply into the language. Do pleaseread more about it here.

For MariaDB and MySQL development, I currently use theGo-MySQL-Driver found here.

If you already have Go installed, you can easily fetch that library with “go get”:

<code>go get github.com/go-sql-driver/mysql</code>

Let’s look at a (very) simple program demonstrating usage, and find out whether the driver supports MariaDB 10. Pardon me for stripping out the error handling:

<code>skysql:dellis$ cat gomaria.gopackage mainimport (	"fmt"	"database/sql"	_ "github.com/go-sql-driver/mysql")func main() {	// Create the database handle, confirm driver is present	db, _ := sql.Open("mysql", "dellis:@/shud")	defer db.Close()	// Connect and check the server version	var version string	db.QueryRow("SELECT VERSION()").Scan(&version)	fmt.Println("Connected to:", version)}skysql:dellis$ go build gomaria.goskysql:dellis$ ./gomariaConnected to: 10.0.11-MariaDB</code>

Success!

One thing of interest is that the variable “db” references a database handle, rather than an actual connection to the database server. In the example above, a connection to the database isn’t actually created until we call db.QueryRow().

I have not explicitly opened or closed an individual connection in this example, because Go’s SQL package automatically handles connection pooling. Let’s give that a (very silly) test, again stripping out the error handling:

<code>skysql:dellis$ cat gomaria.gopackage mainimport (	"sync"	"database/sql"	_ "github.com/go-sql-driver/mysql")func main() {	// Create the database handle, confirm driver is present	db, _ := sql.Open("mysql", "dellis:@/shud")	defer db.Close()	// Test several connections	var wg sync.WaitGroup	for i := 0; i </code>

In theory, then, this will create ten goroutines (like and unlike threads, or, again quoting the documentation, “a function executing concurrently with other goroutines in the same address space”, which are multiplexed onto multiple system threads), each of which will try to connect to MariaDB and execute SELECT SLEEP(10). The main() function will wait until each of these has completed (via sync.WaitGroup), giving me time to check the processlist to see what happened.

Notice that I pass the database handle to my function but once again do not explicitly open or close connections.

Let’s try it:

<code>skysql:dellis$ ./gomariaMariaDB [shud]> SHOW PROCESSLIST;+----+--------+-----------------+------+---------+------+------------+------------------+----------+| Id | User | Host| db | Command | Time | State| Info | Progress |+----+--------+-----------------+------+---------+------+------------+------------------+----------+|3 | dellis | localhost:57547 | shud | Query |3 | User sleep | SELECT SLEEP(10) |0.000 ||4 | dellis | localhost:57548 | shud | Query |3 | User sleep | SELECT SLEEP(10) |0.000 ||5 | dellis | localhost:57549 | shud | Query |3 | User sleep | SELECT SLEEP(10) |0.000 ||6 | dellis | localhost:57550 | shud | Query |3 | User sleep | SELECT SLEEP(10) |0.000 ||7 | dellis | localhost:57551 | shud | Query |3 | User sleep | SELECT SLEEP(10) |0.000 ||8 | dellis | localhost:57552 | shud | Query |3 | User sleep | SELECT SLEEP(10) |0.000 ||9 | dellis | localhost:57553 | shud | Query |3 | User sleep | SELECT SLEEP(10) |0.000 || 10 | dellis | localhost:57554 | shud | Query |3 | User sleep | SELECT SLEEP(10) |0.000 || 11 | dellis | localhost:57555 | shud | Query |3 | User sleep | SELECT SLEEP(10) |0.000 || 12 | dellis | localhost:57556 | shud | Query |3 | User sleep | SELECT SLEEP(10) |0.000 || 13 | dellis | localhost | shud | Query |0 | init | SHOW PROCESSLIST |0.000 |+----+--------+-----------------+------+---------+------+------------+------------------+----------+</code>

And again: Success! We opened ten connections, and each of those issued their queries, with the main() function awaiting completion.

If you were to “sleep” this program with time.Sleep() after the goroutines complete, you might see some idle connections to the database remaining, held open by the connection pool. That can be controlled via db.SetMaxIdleConns() and db.SetMaxOpenConns().

There is a lot more to the topic of using MariaDB and MySQL with Go, and if you are interested I recommend visitingVividCortex’s tutorial available here. The site covers a lot of ground, including some of the limitations you may encounter. It is a great place to start.

With a language so very well designed for concurrency, it may be interesting to see whether the Go MySQL driver could take advantage of MariaDB’s non-blocking API as we see in the mariasql binding for Node.js, but that goes well beyond my subject today.

Thanks for reading.