Golang: Database Access

Hey, it’s me again. Previously we discussed structs and funcs by creating little gophers and having them squeak. Today we are going to look at how to access a database through Go, and a brief look at Go’s concurrency feature.

Database Access

Setting up a Connection

Before we get into more details, there are a few packages that we are going to need to import into our project.

import (
"database/sql"
_"github.com/go-sql-driver/mysql"
"log"
"fmt"
)

The ones to take note of would be the top two, this will be our interface for sql databases and the driver that we will be using to connect. database/sql will act as our interface, importing this will specify that we are going to be working with a type of SQL databases, and it will contain certain functions that we are going to need to open and query the database. Our “github.com/go-sql-driver/mysql” will be our driver, which must be used alongside the interface.

For the purposes of this demonstration, we’ll be working with MySQL.

Simple Database Functions

Now that we’ve gotten most of the easy stuff out of the way, lets take a look at opening the database and writing some queries. The following code will be used to open up a handler for the database:

db, err := sql.Open(“mysql”,”root:password@tcp(127.0.0.1:3306)/gopherDB”)
if err != nil {
log.Fatal(err)
}
defer db.Close()

This little snippet is what we are going to be using to handle the database. As you can see we are calling the sql package’s Open function, which will require 2 parameters. The first of which is going to be the driver we are using, mySQL, and the second will be our datasource with a format of user:pass@tcp(ip)/dbname.

Now, the Open function wont do anything until we’ve actually gotten a query setup and running, and we’re going to make a connection in just a second. But first just to make things a little clearer, I should show you the structure of our database currently.

As you can (barely) see. We have a database with one table, comprised of a gopher number and a gopher name. The gopher number will auto increment whenever we do an insertion so the only thing that we need to be concerned with for now is the gopher name. So let’s go ahead and insert a gopher into our little database.

stmt, err := db.Prepare(“CALL add_gopher(?)”)
if err != nil {
log.Fatal(err)
}
defer stmt.Close()
stmt.Query(“robotaro”)

Just like always, we are going to check if an error has occurred and then we’re going to log it. Otherwise we’ll return our little SQL statement. This one in particular is a stored procedure that we can call in order to add a new gopher, and it will have a single parameter comprising of the Gopher name and we will execute the query using the SQL packages Query function, in this case, “robotaro” is going to be added into the database. Just to make sure that there are gophers sitting inside of the database, lets do a select statement that will show us the rows next, then print it out for us:

rows, err := db.Query(“select * from gophers”)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next(){
err := rows.Scan(&gopherNumber,&gopherName)
if err != nil {
log.Fatal(err)
}
fmt.Println(gopherNumber, gopherName);
}
err = rows.Err()
if err != nil {
log.Fatal(err)
}

This just a regular SQL statement and it will return every single row in our table. The output is going to look a little something like this.

1 gotaro
2 zombietaro
6 robotaro

Now I was messing around with this earlier, so a couple of numbers in the sequence are missing. But the addition seems to be working as intended. This was just a simple insertion and query, but Go makes database access simple enough, and it handles multiple different databases from drivers that you can pull straight off of Github.For a full list of drivers you can take a look at the github site below.

That’s all we have time for right now. And it may be our last set of blog posts. We’ve barely scratched the surface of Go so if I may direct your attention to other features of Go that you might find elsewhere if you choose to continue (Just make sure you’re typing Golang or Go Programming language since its really hard to get search results of of Go).

  1. Concurrency — If you attended my presentation I did a brief discussion of how concurrency is a replacement for threads, but to shorten it up, concurrency in Go is created by running routines. These routines will run code in parallel, and when it’s done it will send a signal to a channel informing that it has completed execution. This handles synchronization because the channel will block until it receives a notification that it is done. I would have liked to include it into this post but it would be too long otherwise.
  2. Google queries — Since Go was created by Google, it has access to the Google’s search engine. Therefore you can write a program to pull out searches and get the results directly from Google.
  3. Web updating — Go can process results and directly send it to the html page, this is all done automatically. It can be very useful when trying to make something akin to a chat server.

Resources used in this post:

https://golang.org/pkg/database/sql/

Like what you read? Give Chad Sanidad a round of applause.

From a quick cheer to a standing ovation, clap to show how much you enjoyed this story.