SQL package

You already know that a database is a collection of structured data that is usually controlled by a database management system (DBMS) that allows users to retrieve, update, and manage data.

You've also been acquainted with some of the most common database management systems used to make and modify databases, such as SQLite, MySQL, etc.

Now it's time to learn how to use Go to interact with databases. In this topic, you'll learn about the database/sql package; it provides a lightweight interface around SQL (or SQL-like) databases.

Database drivers

Before you can start working with databases, you'll need to install and import a database driver. In simple terms, a database driver implements a generic database API for a variety of SQL or SQL-like databases.

Within the example-db project files, let's take a look at the contents of the main.go file, and the import statement in particular:

import (
    "database/sql"
    "log"

    _ "github.com/mattn/go-sqlite3"
)

The purpose of the blank identifier _ before the go-sqlite3 package import is to trigger the init() function within the sqlite3.go file of the go-sqlite3 package. It makes the sqlite3 database driver register itself as being available to the database/sql package.

Also, If you're working in Windows OS, you'll need to install the gcc compiler for your program to work with thego-sqlite3 package. To do so, you can visit the tdm-gcc compiler website and download the 64+32-bit MinGW-64 edition installer.

Next up, we'll need to initialize Go modules for our project and also use the go get command to install the go-sqlite3 package. For this, let's execute the following commands within the example-db project directory:

$ go mod init example-db
$ go get github.com/mattn/go-sqlite3
$ go mod tidy 

Now we're ready to access the sqlite3 database! However, remember that there are many other database drivers that we can use to connect to other databases, such as MySQL, Oracle, MS SQL Server, Postgres, etc.

Opening and closing database connections

Now that we've loaded the sqlite3 database driver, it's time to access the chinook database using the sql.Open() function:

package main

import (
    "database/sql"
    "log"

    _ "github.com/mattn/go-sqlite3"
)

const dbFile = "chinook.db"

func main() {
    db, err := sql.Open("sqlite3", dbFile) // Creating a DB connector
    if err != nil {
        log.Fatal()
    }
    defer db.Close() // Trying to close DB connectors

    ... // Doing something useful with the DB...
}

The sql.Open() function takes two string type arguments:

• driverName — the string that the driver used to register itself with the database/sql package; in this case, "sqlite3";
• dataSourceName — a string with a driver-specific syntax that tells the database driver how to access the underlying data source; in this case, we're accessing a local database file "chinook.db" contained within the constant dbFile.

Finally, the sql.Open() function returns a sql.DB type object within the db variable and an error err, if any.

The sql.DB type

The sql.DB type represents a database itself as an object that we can manipulate. It is a special struct type that we can use to create statements, execute queries, and fetch results.

Take notice that the sql.DB type is not directly a DB connection, it is a database handle representing a pool of zero or more underlying connections. It is also responsible for performing additional tasks, such as:

• Opening and closing connections to a database using a database driver;
• Managing a pool of database connections depending on the needs of the project.

Raw SQL queries

Now it is finally time to retrieve data from the chinook database. We can do this with the db.Query() function using a raw SQL query:

...

func main() {
    ... // Creating a DB object of 'chinook.db' with the sql.Open() function

    // Creating the SELECT query to fetch all rows from the 'genres' table
    rows, err := db.Query("SELECT genreid, name FROM genres")
    if err != nil {
        log.Fatal()
    }
    defer rows.Close() // Closing the queried 'rows'

    ... // Additional operations such as iterating over rows go here...
}

db.Query() executes a string type SQL statement that returns rows, typically a SELECT statement. In the above example, we pass as an argument the raw SQL query: "SELECT genreid, name FROM genres" to fetch all the rows contained within the genres table.

Finally, db.Query() returns the selected rows in case the query is successful, and an error err if the query fails to be executed.

Iterating over the fetched rows

After successfully fetching the data from the genres table of the chinook database, we can use the Next() function to iterate over the queried rows:

...

func main() {
    ... // Creating a DB object of 'chinook.db' with the sql.Open() function

    ... // Creating the SELECT query to fetch all rows from 'genres'

    // Creating variables to assign the query results to
    var genreId int
    var name string

    // Iterating over the rows
    for rows.Next() {
        // Reading the columns in each row into the variables
        err := rows.Scan(&genreId, &name)
        if err != nil {
            log.Fatal(err)
        }
        fmt.Println(genreId, name)
    }

    // Checking for errors after iterating over the rows
    if err = rows.Err(); err != nil {
        log.Fatal(err)
    }
}

First, we need to create two variables to assign the query results to: genreId and name. We only create these two variables because the genres table only has those two columns, respectively. The next step is to call the db.Query() function.

Then, to iterate over all the queried rows, we'll need to use the for rows.Next() loop. Within it, we call the rows.Scan() function to read the columns in every row into the genreId and name variables, and finally output one by one all the rows contained within the genres table:

// Output:
// 1 Rock
// 2 Jazz
// 3 Metal
// ...

Summary

In this topic, we've learned how to use the database/sql package to interact with databases in Go. Specifically, we've covered the following theory:

• What a database driver is and how to install one to our Go project;
• How to open and close database connections using the Open() and Close() functions, respectively;
• What the sql.DB type is and what it is used for when interacting with a database;
• How to perform Raw SQL queries using the Query() function.

We've also learned how to iterate over the rows of the queried data using the Next() function, and how to read the queried data into variables using the Scan() function.

Good going! Now let's move ahead and test our newly acquired knowledge by solving some theory and coding tasks!