MySQL and Go… yeah…

At first, it looks complicated. Primarily because I had to write a huge boilerplate for every query I had. And after reading this article on common pitfalls in this affair (totally recommended, by the way, take a look) I was convinced that it is far from being as simple as in, say, Python.

In this article I make an attempt to abstract some of this boilerplate away. The approach given here is useful if your program has a relatively small (dozens, maybe several hundred) different SQL queries (with parameters. If you pass various parameters to WHERE, it is still done within the same query), because we will prepare these statements and store them for the entire runtime of your program so you wouldn’t have to recreate them each time. According to docs, we can reuse the same statement within several goroutines; I presume that the database manager can bind the statement to different connections automatically when needed.

Preparing test MySQL database in Docker

Launching database

I don’t feel like installing MySQL to my laptop, so I’ll use an expendable Docker container. Creating it via

docker run --rm --name test-mysql-go -v /dev/shm/mysql-test:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=12345 mysql:latest

That volume forwarding is not mandatory, you may omit it. I did it for two reasons: I don’t want the database to write all this test garbage I’m about to produce to my HDD (even though it will be deleted eventually together with the container); and I/O operations with ramdisk (and /dev/shm is exactly that) are blazingly fast (plus, I have quite a lot of RAM on my new laptop, time to put it to good use).

I’ll use the Docker’s bridge network address in my Go code. The port will be the default one - 3306. The host can be found by invoking docker network inspect bridge and looking for test-mysql-go. It should look something like this

"Name": "test-mysql-go",
"EndpointID": "875726a7061bcf01e65d54b898f9a1f5b137782fcdc198781527ce43ad28fd3a",
"MacAddress": "02:42:ac:11:00:03",
"IPv4Address": "172.17.0.3/16",
"IPv6Address": ""

So the host address is 172.17.0.3.

Now let’s connect to the database directly.

docker exec -it test-mysql-go mysql --user=root -p

Enter the password 12345, you have provided it in the environment variable upon container launch.

Creating database and table

Now let’s make a very simple table containing two integers. Going to be enough.

CREATE DATABASE mydb;
USE mydb;
CREATE TABLE pootis (arg1 INT, arg2 INT);

}

The code

Let’s write the drafts for our test code.

package main

import (
	"math/rand"

	"database/sql"

	_ "github.com/go-sql-driver/mysql" //don't forget about this import if you are using MySQL
)

//Goroutine
//Used for testing
func testQueryRoutine(done chan<- bool) {

	done <- true
}

func main() {

	initDatabase()

	nRoutines := 1

	ok := make(chan bool, nRoutines)

	for i := 0; i < nRoutines; i++ {
		go testQueryRoutine(ok)
	}

	for i := 0; i < nRoutines; i++ {
		<-ok
	}
}

We’re going to use a separate goroutine for our SQL operations. Right now we have only one, but in the future we can increase their amount to test concurrency.

Initializing database managers

We have a initDatabase() function call, but we haven’t defined the function yet. This call should be placed in the beginning of your program, it will initialize the database managers that conveniently handle connections and operations for you. Let’s define it.

const dbAddrress = "root:12345@(172.17.0.3:3306)"
const maxDBConnections = 100

//global manager of connections
var mainDB *sql.DB

func initDatabase() {

	//creates the manager that handles connections by itself. No need to close it
	lMainDB, err := sql.Open("mysql", dbAddrress+"/mydb")

	if err != nil {
		log.Fatalln("Could not create database manager:", err.Error())
	} else {
		mainDB = lMainDB
		//setting maximum number of database connections
		mainDB.SetMaxOpenConns(maxDBConnections)
		//needed to circumvent "unexpected EOF" and "invalid connection" errors as shown in https://github.com/go-sql-driver/mysql/issues/674
		//maybe it should be one second?
		//also, consider db.SetMaxIdleConns(0) if needed
		mainDB.SetConnMaxLifetime(time.Second * 60)
	}
}

We created a global pointer to the database manager, as well as parameters, such as connection credentials and the maximum number of connections for the manager.

At this point it is already a working code… that does nothing. Or at least, it does not perform any queries.

Reusable prepared statements

Before we begin writing query functions, remember me mentioning stored prepared statements? Let’s make a function that returns those if they are already created and creates (and stores) them if not.

var preparedStatements = map[string]*sql.Stmt{}
var preparedStatementsMutex = sync.RWMutex{}

//If a statement exists, takes it from storage.
//If not, creates and stores. The query string is the key.
func getCreateStatement(db *sql.DB, query string) (*sql.Stmt, error) {
	preparedStatementsMutex.RLock()

	var stmt *sql.Stmt
	var present bool
	if stmt, present = preparedStatements[query]; present {
		preparedStatementsMutex.RUnlock()
		return stmt, nil
	}

	//If not present, let's create.
	preparedStatementsMutex.RUnlock()
	//Locking for both reading and writing now.
	preparedStatementsMutex.Lock()
	defer preparedStatementsMutex.Unlock()

	//There is a tiny possibility that one goroutine creates a statement but another one gets here as well.
	//Then the latter will receive the prepared statement instead of recreating it.
	if stmt, present = preparedStatements[query]; present {
		return stmt, nil
	}

	stmt, err := db.Prepare(query)
	if err != nil {
		return nil, err
	}

	preparedStatements[query] = stmt
	return stmt, nil
}

The storage is just a map. Keys are your query strings, since it is always the same for the same statement (you use ? in it for parameter placeholders).

I had to use some slightly convoluted locking going on in there. The good thing about sync.RWMutex is that it doesn’t lock if all we do is reading. After some time, your program will have created all (or almost all) the statements, so there will be no writes, and reading will be unconstrained, which may improve performance a bit.

A simple query function

The simplest task is to perform a query that does not return any rows, like an INSERT or UPDATE. Let’s add this to our code.

//Query strings. Will be used in the following sections
const (
	dbQueryStringInserter = "INSERT INTO pootis VALUES (?,?)"
	dbQueryStringSelector = "SELECT arg1, arg2 from pootis where arg1=?"
	dbQueryStringUpdater  = "UPDATE pootis SET arg2=? WHERE arg1=42"
)

type dummyError struct{}

func (d dummyError) Error() string {
	return "Dummy"
}

func randomUint8() uint8 {
	return uint8(rand.Int() % 255)
}

func execDB(db *sql.DB, query string, params []interface{}) (affectedRows int64, err error) {
	err = dummyError{}
	var stmt *sql.Stmt

	for err != nil {
		stmt, err = getCreateStatement(db, query)
		if err != nil {
			fmt.Println("Could not build query statement", query, ":", err.Error())
			time.Sleep(time.Second * 1)
			continue
		}

		var result sql.Result
		result, err = stmt.Exec(params...)
		if err != nil {
			fmt.Println("Could not execute query", query, ":", err.Error())
			time.Sleep(time.Second * 1)
			continue
		}

		var erro error
		affectedRows, erro = result.RowsAffected()
		if erro != nil {
			//Not ever ydatabase supports this.
			//Not a problematic error. Will show a message but will not terminate execution.
			fmt.Println("Could not get amount of affected rows:", erro.Error())
		}
	}
	return
}

And this goes to testQueryRoutine.

////////
//INSERT
////////
params := make([]interface{}, 2)
params[0], params[1] = randomUint8(), randomUint8()
fmt.Println("Inserting")
rowsInserted, err := execDB(mainDB, dbQueryStringInserter, params)
if err == nil {
	fmt.Println("Successfully inserted", rowsInserted, "rows!")
} else {
	fmt.Println("Error on insertion!", err.Error())
}

fmt.Println()

So what’s going on here? Our task is to insert two random integers into the table. So we create a slice of 2 elements, assgned random values to it and passed it to execDB. This is our simple query function.

There is a loop that restarts the procedure if there are any errors. The “build query” block is likely to throw an error if you have mistakes in your SQL query (maybe it is better to make the program crash using Fatalln in this case, actually). The second one is important though, because it handles the execution errors, such as the dreaded Too many connections error, which is very familiar to you if your database is used uncontrollably by a number of different services.

We use Exec() method here, because we don’t need any rows from the database. It returns a result object that contains information about the number of affected rows, which we print out.

If you run your program now, it will insert one row into your test database. The output will be

Inserting
Successfully inserted 1 rows!

You can add a test update block to testQueryRoutine. At this point it is unlikely that it will update anything; you may run a INSERT INTO pootis (arg1, arg2) VALUES (42,0); query to make an updateable row.

////////
//UPDATE
////////
params = make([]interface{}, 1)
params[0] = randomUint8()
rowsInserted, err = execDB(mainDB, dbQueryStringUpdater, params)
if err == nil {
fmt.Println("Successfully updated", rowsInserted, "rows!")
} else {
fmt.Println("Error on insertion!", err.Error())
}

fmt.Println()
fmt.Println("////////////////////////////////////////////////////")
fmt.Println()

Selecting a single value

Alright, it’s all fine and dandy, but how do we get data from the database? That’s a bit trickier. Let’s write a block into testQueryRoutine.

///////////////
//SELECT single
///////////////
params = make([]interface{}, 1)
params[0] = 42
var result1, result2 uint8
fmt.Println("Selecting single") //debug
err = queryDBSingular(mainDB, dbQueryStringSelector, params, &result1, &result2)
if err != nil {
	fmt.Println("Error on selecting single!", err.Error())
} else {
	fmt.Println("Received results:", result1, result2)
}

We expect two results in the query above, so we created two variables to store them and passed their addresses to the function. It will not return them, but write directly into them instead, returning only an error, if present.

So, here is that function.

func queryDBSingular(db *sql.DB, query string, params []interface{}, storages ...interface{}) error {
	var err error = dummyError{}
	var stmt *sql.Stmt

	for err != nil {
		stmt, err = getCreateStatement(db, query)
		if err != nil {
			fmt.Println("Could not build query statement", query, ":", err.Error())
			time.Sleep(time.Second * 1)
			continue
		}

		row := stmt.QueryRow(params...)
		err = row.Scan(storages...)
		if err != nil {
			if err == sql.ErrNoRows {
				return sql.ErrNoRows
			}
			fmt.Println("Could not execute query!", err.Error())
			time.Sleep(time.Second * 1)
			continue
		}
	}

	return nil
}

Just like in the case with parameters, it takes interface{} type for storages, since we don’t know the types required and returned by the database in the given query preliminarily, it can be anything depending on a query, and we are writing universal solutions. So it is important to always pay attention to your parameter and result storage types!

The output of this run will be something like this.

Inserting
Successfully inserted 1 rows!

Successfully updated 1 rows!
Selecting single
Received results: 42 26

Selects with multiple resulting rows

Getting one result in a SELECT is useful for tables where each entity is represented by one row that is updated. But if you have other types of data, you will likely require to SELECT much more than just one row. And that’s even trickier.

Here’s the test code to add to testQueryRoutine.

/////////////////
//SELECT multiple
/////////////////

const bufferSize = 10
params = make([]interface{}, 1)
params[0] = 42
sresult1 := make([]interface{}, bufferSize)
sresult2 := make([]interface{}, bufferSize)
fmt.Println("Selecting multiple")
rowsAmount, _ := QueryDBMultiple(mainDB, dbQueryStringSelector, params, sresult1, sresult2)
sresult1 = sresult1[:rowsAmount]
sresult2 = sresult2[:rowsAmount]
fmt.Println(params, sresult1, sresult2)

Here, instead of singular variables for storing a result from every column, we have interface{} slices of a predefined size. That means, we will not be able to fetch more rows than that. If I remember correctly, I didn’t have to impose this limitation for the function we will look into in this section, but it became mandatory for the universal function we will see in the next one.

If we get fewer rows than bufferSize, they should be cut off as shown above.

Here’s the function.

func QueryDBMultiple(db *sql.DB, query string, params []interface{}, storages ...[]interface{}) (counter int, err error) {
	err = dummyError{}
	var stmt *sql.Stmt

	for err != nil {
		stmt, err = getCreateStatement(db, query)
		if err != nil {
			fmt.Println("Could not build query statement", query, ":", err.Error())
			time.Sleep(time.Second * 1)
			continue
		}

		rows, err := stmt.Query(params...)
		if err != nil {
			// query error
			fmt.Println("Could not execute query", query, ":", err.Error())
			time.Sleep(time.Second * 1)
			continue
		}

		//number of columns should be equal to the amount of porvided slices
		resultsAmount := len(storages)

		var lenStorages int
		if resultsAmount > 0 {
			//sizes of each storage, they should be the same
			lenStorages = len(storages[0])
		} else {
			//error here, no storages provided
			break
		}

		for rows.Next() {
			if counter >= lenStorages {
				//we hit the limit of storage size, quitting with what we have so far
				break
			}

			dest := make([]interface{}, resultsAmount)
			for i := 0; i < resultsAmount; i++ {
				dest[i] = &(storages[i][counter])
			}

			if err = rows.Scan(dest...); err != nil {
				fmt.Println("Could not read query results:", query, ":", err.Error())
				rows.Close()
				time.Sleep(time.Second * 1)
				continue
			}

			counter++
		}
		if err := rows.Err(); err != nil {
			fmt.Println("Could not scan rows fully:", query, ":", err.Error())
			rows.Close()
			time.Sleep(time.Second * 1)
			continue
		}
		rows.Close()
	}

	return counter, nil
}

The storages parameter is basically a slice of slices of interface{}. First, we determine that there are storages provided and then we iterate over the results and put them into storages. If we hit the limit, we exit. According to docs rows.Next() returns true if there are any results left and false when nothing is left or there is an error. In that case rows.Err() will return the error.

The universal function

Finally, let’s specify a universal function that takes any query and elects the function to execute it with by itself. I’m not going to test it or explain it too much in this article though. Here it is.

func QueryDB(db *sql.DB, query string, params []interface{}, storages ...interface{}) (numericResult int64, err error) {

	if strings.HasPrefix(strings.ToUpper(strings.Trim(query, " \n\r")), "SELECT") {
		storagesAmount := len(storages)
		if storagesAmount == 0 {
			return 0, errors.New("storage variables are required for SELECT statement")
		}

		// https://stackoverflow.com/q/40343471/2052138
		switch x := storages[0].(type) {
		case []interface{}:
			//The provided storages are slices.
			//We will try fetching several rows.
			firstStorageLen := len(x)

			lStorages := make([][]interface{}, storagesAmount)

			for i, storage := range storages {
				if ss, ok := storage.([]interface{}); ok {
					if len(ss) != firstStorageLen {
						return 0, errors.New("storages are not of the same size")
					}
					lStorages[i] = ss
				} else {
					return 0, errors.New("not all storages are slices, even though the first one is")
				}
			}

			// if sss, ok := storages.([]interface{}); ok {
			returnedRows, erro := QueryDBMultiple(db, query, params, lStorages...)
			numericResult, err = int64(returnedRows), erro
			// }

		case interface{}:
			//The provided storages are not slices.
			//Therefore we will get only one row.
			erro := queryDBSingular(db, query, params, storages...)
			if erro == nil {
				numericResult = 1
			} else if erro == sql.ErrNoRows {
				err = nil
				numericResult = 0
			} else {
				err = erro
			}

		default:
			err = fmt.Errorf("unsupported type: %T", x)
		}

	} else {
		numericResult, err = execDB(db, query, params)
	}

	return
}

Basically, it ensures that the storages are provided if the query is SELECT. Then it chooses the function based on whether we provide singular variables or slices as storages. If it is not a SELECT statement, it opts out for Exec(). In any case, in addition to error when it happens, it returns a numeric result representing the number of affected rows or returned results, depending on a query.

In conclusion

Phew! That was a braintwist. Well, at least this attempt on writing our own library can be used as a tool for simple purposes of interaction with MySQL. There are no transactions, multi-query statements are not supported, and I have doubts about stored procedures on this one. But anyway, better than nothing!

As always, pleasure to see you here again.