MySQL and Golang

This is a short article meant to teach you the basics of using MySQL with Golang.

Introduction

I expect you to know the basics of working with databases, this includes setting up a local MySQL database instance as well as knowing the basics of the SQL language.

Creating our project

We start by simply creating a folder for our project and a file called “main.go” inside of it.

mkdir golang-mysql && cd golang-mysql && touch main.go

The database driver

We will use a MySQL driver in order to connect to our database. The reason we do this is because the driver handles a lot of the stuff necessary to communicate with our database.

To get the driver just run the following command:

go get -u github.com/go-sql-driver/mysql

Initial code

Now we will write some initial code as well as import the needed packages.

main.go

package main
import (
  "fmt"
  "database/sql"
  _"github.com/go-sql-driver/mysql"
)
func main() {
  
}

For simplicity reasons we will write all the code in our main function, it wouldn’t make sense to do it any other way for this tutorial.

Accessing our database

To access our database and create a new database object we use this line of code:

db, err := sql.Open("mysql", "<user>:<password>@tcp(127.0.0.1:3306)/<database-name>")

This code creates a new database object. The first argument in the function is our drivers name, in our case it’s “mysql”. The second argument is a driver-specific syntax that tells the driver how to access the underlying datastore. Also, the default port for MySQL is 3306 so that’s what we use.

You might think that this opens a connection to our database, but it does not. It just creates a database object and prepares is for later use, the first connection happens lazily, when we need it. For example when we make a query for data.

We write the following code in our file, just replace <user> with your user, <password> with the user’s password and <database-name> with your database name.

main.go

package main
import (
  "database/sql"
  _"github.com/go-sql-driver/mysql"
)
func main() {
  db, err := sql.Open("mysql", 
"<user>:<password>@tcp(127.0.0.1:3306)/<database-name>")
  if err != nil {
    panic(err.Error())
  }
  defer db.Close()
}

If your database doesn’t have a password replace the “sql.Open()” line with this:

db ,err := sql.Open("mysql", "root:@/<database-name>")

This will as I wrote before create a database object and prepare it for later use, right after we check for errors. Checking for errors is something you almost always want to do. In our case, we simply panic if any errors occour.

The next line where we “defer” “db.Close()” simply keeps the connection open until for the rest of the function.

You can now run the program to make sure you don’t have any errors:

go build && ./golang-mysql

Inserting data

In my database I’ve created a table called “posts” with two columns (id and title).

We are now going to write an insert statement to create a post. Normally you would now want to use “db.Query()” when no rows will be returned, examples such actions is INSERT, UPDATE and DELETE. You would want to use “db.Exec()” instead, I might explain this further in another article. But for the sake of simplicity we will use “db.Query()” in this tutorial.

insert, err := db.Query("INSERT INTO posts(title) VALUES('My post')")
if err != nil {
    panic(err.Error())
}
defer insert.Close()

Here we run the query, then we of course check for errors, if there is no errors we continue.

Your file should now look like this:

package main
import (
  "database/sql"
  _"github.com/go-sql-driver/mysql"
)
func main() {
  db, err := sql.Open("mysql", "<user>:<password>@tcp(127.0.0.1:3306)/<database-name>")
  if err != nil {
    panic(err.Error())
  }
  defer db.Close()
  insert, err := db.Query("INSERT INTO posts(title) VALUES('My post')")
  if err != nil {
    panic(err.Error())
  }
  defer insert.Close()
}

If you now run the program a post with the title “My post” is going to be created in the database.

Getting data

To get and use the data we first need to define a struct.

type Post struct {
  ID int
  Title string
}

Your file should now look like this:

package main
import (
  "fmt"
  "database/sql"
  _"github.com/go-sql-driver/mysql"
)
type Post struct {
  ID int
  Title string
}
func main() {
  db, err := sql.Open("mysql", "<user>:<password>@tcp(127.0.0.1:3306)/<database-name>")
  if err != nil {
    panic(err.Error())
  }
  defer db.Close()
}

You can see I’ve removed the code that we used to insert data because we obviously don’t want do that again. I also added “fmt” to our imports as we will print out the result.

Instead we write some code that will query all the posts, loop over the result and for every post create a new instance of “Post”.

posts, err := db.Query("SELECT id, title FROM posts")
if err != nil {
    panic(err.Error())
}
for posts.Next() {
  var post Post
  err := posts.Scan(&post.ID, &post.Title)
  if err != nil {
    panic(err.Error())
  }
  fmt.Println(post)
}

You can see how we used “.Scan()” to insert the values into the Post instance. If everything went good and we got not errors we simply print out the post. If we had multiple posts in our database the program would print out more posts but in our case it’s only one.

If you now run the program it should print out something like this:

{1 My post}

Thank you for reading!

I hope this helped you understand the basics of working with databases in Golang, I’m planning to write some more advanced articles on this subject, so if you liked this make sure to leave a review and share.