Go MySQL Tutorial [Part 3: Data Transfer]

Photo by Tobias Fischer on Unsplash

Reminder:

This little tutorial guides you through setting up a MySQL/MariaDB server and simple programming in Go. The idea is to present the minimum needed to have a working application. It is part of a small series of articles I wanted to write about databases.

Table of Contents

• Install your MySQL server, click here.
• Open a connection to your MySQL server, click here.
• MySQL data transfer with Go, click here.
• MySQL transactions with Go, coming soon

MySQL Data Transfer with Go

Now we can execute queries to enumerate entries in the table:

       rows, err := db.Query(` SELECT          id, data, creation
                                                        FROM            ` + tableName)
        if err != nil {
                log.Fatalf("SELECT failed: %v\n", err)
        }
        defer rows.Close()

Simple, right? But how do we retrieve the data? For this, we use the value returned by the db.Query() function:

       for rows.Next() {
                var (
                        id       uint64
                        data     string
                        creation time.Time
                )
                err = rows.Scan(&id, &data, &creation)
                if err != nil {
                        log.Fatalf("rows.Scan failed: %v\n", err)
                }

                fmt.Printf("%-5d | %-32s | %v\n", id, data, creation)
    }

As you can see, we iterate through each row and thanks to the rows.Scan() function we retrieve the variables. This function handles the conversion, taking into account the type of the variable passed as a parameter. Cool, right?

That said, this piece of code, while simple, doesn't show a small difficulty: what happens if a column is null? Well, we get the following error:

rows.Scan failed: sql: Scan error on column index 1, name "data": unsupported Scan, storing driver.Value type <nil> int
o type *string

This error message is quite explicit, don't you think? To handle this kind of case, there are two solutions: either create a table where the concerned columns cannot be null, or modify our code as follows:

       for rows.Next() {
                var (
                        id          uint64
                        dataPtr     *string
                        creationPtr *time.Time
                )
                err = rows.Scan(&id, &dataPtr, &creationPtr)
                if err != nil {
                        log.Fatalf("rows.Scan failed: %v\n", err)
                }

                data := "<nil>"
                if dataPtr != nil {
                        data = *dataPtr
                }
                fmt.Printf("%-5d | %-32s | %v\n", id, data, creationPtr)
    }

By making dataPtr a pointer to a string, we allow the corresponding column to return a NULL (or nil in Go).

Now, we just need to insert data into the table:

       res, err := db.Exec(`INSERT INTO `+tableName+` SET data=?, creation=NOW()`, "Hello World")
        if err != nil {
                log.Fatalf("db.Exec(INSERT) failed: %v", err)
        }

Did you notice the question mark in the query? It's a "placeholder". We can pass parameters to the db.Exec() function (as well as to db.Query()). It's convenient because the driver handles the escaping of the value. The issue is that these parameters depend on the order in which the question marks appear. So a simple change in the query can lead to errors that are hard to identify.

What would be really nice is to be able to use named parameters like this:

       res, err := db.Exec(`INSERT INTO `+tableName+` SET data=@data, creation=NOW()`,
                sql.Named("data", "Hello World"))
        if err != nil {
                log.Fatalf("db.Exec(INSERT) failed: %v", err)
        }

Well, the database/sql package supports it. The problem is that it gives the following error:

mysql: driver does not support the use of Named Parameters

Not cool. I haven't found another driver that handles this. Too bad. It seems to work with the pq driver for PostgreSQL. I was already wondering about the database choice for some of my projects...

We still need to know which ID was created for this new entry we just added:

       lastID, err := res.LastInsertId()
        if err != nil {
                log.Fatalf("res.LastInsertId failed: %v\n", err)
        }

        fmt.Printf("inserted %d\n", lastID)

Finally, it's possible to know how many rows were affected by a SQL query:

       rowCount, err := res.RowsAffected()
        if err != nil {
                log.Fatalf("res.RowsAffected failed: %v", err)
        }

In the next article, we'll see how to perform transactional queries.