MySQL and Golang

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

Introduction

Creating our project

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

The database driver

To get the driver just run the following command:

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

Initial code

main.go

package mainimport (
"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

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 mainimport (
"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

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 mainimport (
"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

type Post struct {
ID int
Title string
}

Your file should now look like this:

package mainimport (
"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!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store