Using an SQL database in Golang

The principle of processes within the Twelve-factor app methodology asserts that an application executes as stateless processes. This means that data that needs persisting should be stored in an external backing service such as a database.

When applying this principle to your Golang written applications, you might need to use an SQL database to persist data.

Within this tutorial, we’ll learn how to build a console-based reminder application written in Golang that uses an SQL database.

We will focus on the Microsoft SQL Server and connect the Golang application to a database within an SQL Server to store a user’s reminders. We are choosing Microsoft SQL Server over MySQL not because one is better than the other, but because one has less tutorials than the other. Besides that, MSSQL syntax is easier than MySQL.

Here’s what we’ll cover:

• Golang and SQL prerequisites
• Using the Microsoft SQL Server
  • Installing the Microsoft SQL Server
• Creating an SQL Server database
  • Creating the Reminders table
• Building a Golang console application
  • Creating the Go application
• Building the database package
  • Building the main function
  • Inserting a reminder record
  • Retrieving a reminder record
  • Deleting a reminder record
• Testing the Golang console application
• Further considerations
  • Using a validation library
  • Using a CLI library
  • Handling query timeouts

Golang and SQL prerequisites

To follow along with building a console application, it is recommended that you have the following:

• A basic understanding of the Go programming language
• Golang installed on your local computer
• Docker installed on your local computer

Using the Microsoft SQL Server

The Microsoft SQL Server is a relational database management system (RDBMS) that contains several components. One of these components is the database engine that manages the data stored within the SQL Server.

For this tutorial we will use the database engine component of the SQL Server. To establish a connection to the database engine, we need the database package from Golang’s standard library and the go-mssqldb package.

To get started, let’s install the SQL Server instance as a Docker image on a local computer.

Installing the Microsoft SQL Server

We can now install the Microsoft SQL Server instance through a Docker image from the computer’s terminal.

Execute the Docker pull command below from your terminal to pull a container image for the 2019 Microsoft SQL server instance from DockerHub:

docker pull mcr.microsoft.com/mssql/server:2019-latest

Next, execute the Docker run command below to run the pulled container image using Docker. The following command contains arguments that pass into the container image as environment variables to configure the container.

Make sure to change the INSTANCE_PASSWORD placeholder in the command to a more secure password and remember the password, because we’ll use it later:

sudo docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=INSTANCE_PASSWORD" \
   -p 1433:1433 --name sql1 -h sql1 \
   -d mcr.microsoft.com/mssql/server:2019-latest

You can further execute the docker ps command to see the MSSQL container running in the background.

Creating an SQL Server database

In the previous section, we pulled the Microsoft SQL Server image to run the SQL server instance. Now, let’s proceed to create a database for our Golang application within the SQL Server instance.

To create a database, you can either use a database GUI tool, such as the SQL Server Management Studio (SSMS), or through the sqlcmd CLI utility.

We’ll continue by connecting to the Docker image running the SQL server instance to create a database from the terminal.

First, execute the Docker exec command below to start a Bash shell within the Docker container running the SQL Server instance. This step enables us access the sqlcmd tool within the container:

sudo docker exec -it sql1 "bash"

Next, connect with sqlcmd by executing the command below inside the interactive Bash shell started above.

Again, note that you must change the INSTANCE_PASSWORD placeholder below with the password you used when running the Docker image:

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "INSTANCE_PASSWORD"

To begin creating the database, type in the SQL query below to create a database named goConsole:

CREATE DATABASE goConsole

Next, run the batch separator command below to execute the SQL query you typed above:

GO 

To further confirm the database was created, we can type the SELECT statement below to retrieve all available databases by their name:

SELECT Name from sys.Databases

Then, run the command below to execute the SELECT statement above:

GO

Five databases created within your new SQL Server instance will then print out:

Creating the Reminders table

To create a Reminders table within the goConsole database for our reminder application, type in the two SQL queries below, first with the USE statement to set the current database from master to goConsole:

USE goConsole

Next, type in the CREATE TABLE statement below to create a table within the goConsole database named Reminders:

CREATE TABLE Reminders ( ID int IDENTITY(1, 1), title varchar(75), description varchar(175), alias varchar(70)) 

The Reminders table will create when the SQL query above executes and will contain the ID, title, description, and alias columns.

Using the IDENTITY function with the ID column ensures the ID column value automatically increments by 1 each time a new record inserts to the Reminders table.

Continue to run the Go command below to execute the two SQL queries we typed above:

GO  

Now that the database is created, let’s proceed to to the Golang aspects of this tutorial.

Building a Golang console application

Within this section, we will build a simplified console application to use the goConsole database that we just created. However, before we move forward, it is important that we understand Golangs sql package and methods.

Golang’s sql package comes from Golang’s standard library, and it provides an interface around SQL databases. To use an SQL database, the we must use the sql package with the driver for the SQL database.

We’ll see how this is done when we use the go-mssqldb database driver with the sql package.

Golang methods are similar to functions, however, a method has a receiver argument attached to it.

Within this tutorial, we’ll create a struct type to contain a field that stores a point to the database handler from the sql package and create methods that have a receiver of the struct type.

Also note that we will only implement the CREATE, RETRIEVE, and DELETE operations of a reminder data without alerts.

Creating the Go application

To begin creating the Go application, Execute the two commands below to create a new directory for storing the Go project and move into the mssql-go-cli directory:

# create a new directory
mkdir mssql-go-cli

# move into new directory
cd mssql-go-cli

Next, execute the Go command below to bootstrap a Go project with a go.mod file to manage the dependencies within this project:

go mod init mssql-go-cli

Execute the go get command below to install the go-mssqldb package to connect your Microsoft SQL database from this Go application:

go get github.com/denisenkom/go-mssqldb github.com/joho/godotenv/cmd/godotenv

Finally, create a main.go file and add the content of the code block below into the file. The code within the main function serves as a boilerplate for the application while we implement the real logic in the next section:

// main.go
package main
import "fmt"

func main() {
  fmt.Println("-> Welcome to the Reminders Console App built using Golang and Microsoft SQL Server")  
}

When the application runs through the go run main.go command, a string prints to the console, as shown below:

At this point, we have the Go application working without being connected to the SQL Server. So, let’s create a custom database package next that contains the code for establishing a connection to the SQL Server instance.

Building the database package

A Go package is a collection of file(s) containing Go code within a directory. Hence, to create the database package for this application, we must create a new directory within the mssql-go-cli project directory.

To do this, create a directory named database and create a file within the new directory named database.go.

Next, add the content of the code block below into the database.go file to create an exported Database struct with an exported SqlDb field. The field’s data type points to the DB struct:

// ./database/database.go
package database

import (
   "context"
   "database/sql"
)

type Database struct {
   SqlDb *sql.DB
}

var dbContext = context.Background()

The struct exported from the database package then initializes in the main function in the next step.

Building the main function

Let’s proceed to refactor the main.go file to prompt a user for an action and accept input from the console.

Add the content of the code block below into the main.go file:

// ./main.go
package main

import (
   "bufio"
   "database/sql"
   "fmt"
   _ "github.com/denisenkom/go-mssqldb"
   "github.com/joho/godotenv"
   "mssql-go-cli/database"
   "os"
)

func main() {
    envErr := godotenv.Load(); if envErr != nil {
       fmt.Printf("Error loading credentials: %v", envErr)
    }

var (
   password = os.Getenv("MSSQL_DB_PASSWORD")
   user = os.Getenv("MSSQL_DB_USER")
   port = os.Getenv("MSSQL_DB_PORT")
   database = os.Getenv("MSSQL_DB_DATABASE")
)

connectionString := fmt.Sprintf("user id=%s;password=%s;port=%s;database=%s", user, password, port, database)

   sqlObj, connectionError := sql.Open("mssql", database.ConnectionString); if connectionError != nil {
      fmt.Println(fmt.Errorf("error opening database: %v", connectionError))
   }

   data := database.Database{
      SqlDb: sqlObj,
   }

   fmt.Println("-> Welcome to Reminders Console App, built using Golang and Microsoft SQL Server")
   fmt.Println("-> Select a numeric option; \n [1] Create a new Reminder \n [2] Get a reminder \n [3] Delete a reminder")

   consoleReader := bufio.NewScanner(os.Stdin)
   consoleReader.Scan()
   userChoice := consoleReader.Text()

   switch userChoice {
   case "1":
      var (
         titleInput,
         descriptionInput,
         aliasInput string
      )
      fmt.Println("You are about to create a new reminder. Please provide the following details:")

      fmt.Println("-> What is the title of your reminder?")
      consoleReader.Scan()
      titleInput = consoleReader.Text()

      fmt.Println("-> What is the description of your reminder?")
      consoleReader.Scan()
      descriptionInput = consoleReader.Text()

      fmt.Println("-> What is an alias of your reminder? [ An alias will be used to retrieve your reminder ]")
      consoleReader.Scan()
      aliasInput = consoleReader.Text()

      data.CreateReminder(titleInput, descriptionInput, aliasInput)

   case "2":
      fmt.Println("-> Please provide an alias for your reminder:")
      consoleReader.Scan()
      aliasInput := consoleReader.Text()

      data.RetrieveReminder(aliasInput)

   case "3":
      fmt.Println("-> Please provide the alias for the reminder you want to delete:")
      consoleReader.Scan()
      deleteAlias := consoleReader.Text()

      data.DeleteReminder(deleteAlias)

   default:
      fmt.Printf("-> Option: %v is not a valid numeric option. Try 1 , 2 , 3", userChoice)
   }
}

At a quick glance, when the above main function executes, text prints to the console to inform users of the available actions to either create, retrieve, or delete a reminder. Then, the user’s input will match one of the cases within the switch expression.

To better understand the code, let’s break the file down and go through the major parts gradually.

Understanding the main function’s code

To begin, the environmental variables within the .env file load into the application through the Load function from the dotenv package. We an then open the database by calling sql.Open() and storing the result in the SqlDb field within the Database struct.

From line 36 of the code block, a message prints to the console to inform the user of the actions to either create, retrieve, or delete a reminder. The scanner from the bufio package then reads an input from the console and stores the text value in the userChoice variable.

We can then use the userChoice variable as a condition expression for the switch statement contained within the code.

Whenever any of the cases within the switch statement match, a user is further prompted to provide more details through the console and these details pass as an argument to a helper function that executes an SQL query against the database.

For example, the first case matches a 1 console input to create a reminder. When matched, a user is further prompted for a title, description, and alias details for the reminder about to be created.

The variables declared above store the provided details and pass as arguments into a createReminder function to perform a CREATE operation on the connected Microsoft SQL database.

At this juncture, we now have an application that accepts user inputs through the console. However, these inputs are not yet stored in the goConsole database because the methods in the switch cases have not been created.

Let’s proceed to create the createReminder, retrieveReminder, and deleteReminder methods in a separate file within the database package.

Inserting a reminder record

To begin building the first function for inserting a reminder record, create a file within the database directory named operations.go and add the code below into the operations.go file:

// ./database/operations.go
package database
import (
   "database/sql"
   "fmt"
)

func (db Database) CreateReminder(titleInput, aliasInput, descriptionInput string) (int64,  error) {
   var err error

   err = db.SqlDb.PingContext(dbContext); if err != nil {
      return -1, err
   }

   queryStatement :=  `
    INSERT INTO reminders(title, description, alias ) VALUES (@Title, @Description, @Alias);
    select isNull(SCOPE_IDENTITY(), -1);
   `

   query, err := db.SqlDb.Prepare(queryStatement); if err != nil {
      return -1, err
   }

   defer query.Close()

   newRecord := query.QueryRowContext(dbContext,
      sql.Named("Title", titleInput),
      sql.Named("Description", descriptionInput),
      sql.Named("Alias", aliasInput),
   )

   var newID int64
   err = newRecord.Scan(&newID); if err != nil {
      return -1, err
   }

   return newID, nil
}

The exported CreateReminder method within the code block above accepts the string details of a reminder. The first thing the function does is call the PingContext() method to verify that a connection is active.

Next, an SQL statement, prepared using the Prepare() method, is stored in the queryStatement variable. The SQL statement then executes by passing the dbContext and query arguments into the QueryRowContext method.

Take note of how the method’s parameters add to the SQL statement using arguments through the NamedArg() method rather than directly formatting the parameters into the SQL statement.

This process reduces the risk of an SQL injection attack. However, you can adopt a data validation library later on.

Retrieving a reminder record

To retrieve a reminder record, first add the exported RetrieveReminder method within the code block below the existing methods in the operations.go file.

The RetrieveReminder method first checks whether the database connection is alive or not, and then executes an SQL query to retrieve a reminder record from the database and print out the data to the console:

// ./database/operations.go

func (db Database) RetrieveReminder() error {
err := db.SqlDb.PingContext(dbContext); if err != nil {
   return err
}

   sqlStatement := fmt.Sprintf("SELECT title, description, alias FROM REMINDERS;")

   data, queryErr := db.SqlDb.QueryContext(dbContext, sqlStatement); if queryErr != nil {
      return queryErr
   }

   for data.Next() {
      var title, description, alias string

      nErr := data.Scan(&title, &description, &alias); if nErr != nil {
         return nErr
      }

      fmt.Printf("--> Your Reminder: \n \t Title: %v \n \t Description: %v \n \t Alias: %v \n",
      title, description, alias,
         )

      return nil
   }

   return nil
}

Above, using the QueryContext() method, a SELECT SQL statement executes to read the title, description, and alias values from the reminder table.

The QueryContext() method then returns the selected table rows in a result set that we can further iterate upon in the for loop.

The Scan() method within the for loop further copies the value of the column iterated upon into the title, description, and alias variables; after this, they format into a string and print to the console.

Deleting a reminder record

To delete a reminder record, add the deleteReminder method below to the existing CreateReminder and RetrieveReminder methods in the operations.go file:

// ./database/operations.go

func (db Database) DeleteReminder(alias string) error {
var err error

err = db.SqlDb.PingContext(dbContext); if err != nil {
   fmt.Printf("Error checking db connection: %v", err)
}

queryStatement := `DELETE FROM reminders WHERE alias = @alias;`

_, err = db.SqlDb.ExecContext(dbContext, queryStatement, sql.Named("alias", alias))
if err != nil {
   return err
}

fmt.Printf("Reminder with %v alias deleted", alias)

return nil
}

The DeleteReminder method above accepts an string alias value in its signature. The DELETE SQL query statement uses a WHERE clause to specify which reminder to delete.

The query statement then prepares and the function’s alias parameter is used as the condition of the WHERE clause in the SQL statement.

For this scenario, the ExecContext() method executes the DELETE SQL statement because we do not execute any row to return from the query.

Testing the Golang console application

From the last three sections, we implemented three methods to CREATE, READ, and DELETE records from the connected goConsole database. Let’s now proceed to test the entire console application.

The following steps walk us through the testing process using the available options of the console application.

To begin, execute the command below to run the application; type 1 into the console to select the first item within the message printed to the console for creating a reminder.

The application further prompts us for a title, description, and alias value for the reminder about to be created:

go run ./main.go

Again, execute the command below to run the application. This time, type 2 into the console to select the second option for retrieving saved reminders:

go run ./main.go

Execute the command below to run the application for the final test. This time, type 3 into the console to select the last option to delete the saved reminder. The application further prompts us to specify the alias of the reminder we want to delete:

go run ./main.go

At this point, we can conclude that we built a simplified console application that uses an SQL Server database to handle data.

Further considerations

Within this article we focused on the aspects of using a SQL Server database. If you want to leverage the code written here for building a more complex application, consider the following.

Using a validation library

Ensure you use a data validation library to validate user input before using it in your SQL query. This reduces the risk of an SQL injection attack against the connected database.

Examples of data validation libraries for Golang include go-ozzo and govalidator.

Using a CLI library

Secondly, use a library to create CLI applications. To avoid complicating the tutorial, we relied on a switch statement to handle the application flow. However, you can improve the flow by using a CLI library for Golang such as Cobra.

Handling query timeouts

Golang has been able to provide support for handling timeouts, which are used to execute queries with timeout and cancellation. What this entails is that we can cancel a database query if the client cancels the request or also set a timeout to cancel a query. These can be achieved using the Golang’s context.Context​ instance:
​​

​​func retrieveReminder(w http.ResponseWriter, r *http.Request) {
​​        ctx, cancel := context.WithTimeout(dbContext, 3*time.Second)
​​        defer cancel()
​​
​​        _, err := db.QueryContext(ctx, "SELECT sleep(15)")

​​​With that in place, we can do this to prevent our server from performing unwanted tasks or even taking longer to provide resources, in this case our reminders.

Conclusion

Within this tutorial we built a console application that uses Golang’s native database package and the go-mssqldb package to connect and perform CRUD operations on a SQL Server database.

The code for the console application built within the tutorial is available within this public GitHub repository. Feel free to clone the repository and use the application as a starting point for your own Golang application.