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

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",


So the host address is 172.17.0.3.

Now let’s connect to the database directly.

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.

}

# The code

Let’s write the drafts for our test code.

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.

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.

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.

And this goes to testQueryRoutine.

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.

## 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.

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.

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


## 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.

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.

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.

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.