How to Use Any SQL Database in Go

With a simple database abstraction package, using SQL from Go is easier than you might think.

SQL databases store collections of data in rows and columns. You can retrieve and update the data in a relational database management system (RDBMS) using the SQL language. Of the many SQL databases available, the most popular are MySQL, PostgreSQL, Microsoft SQL Server, and SQLite.

Functionality for interacting with databases in Go is in the database/sql package, part of the standard library.

The database/sql package interacts with SQL databases using drivers. You can import an appropriate driver package for your RDBMS and use it to interact with the database.

Getting Started With SQL Databases in Go

The database/sql package is a generic interface to relational databases. To work with a specific database server, you’ll need to use one of the many drivers available.

Thankfully, you don’t have to worry about specific implementations beyond the driver. The database/sql package handles database operations independently of the server you’re connecting to.

Some of the most popular Go database drivers are:

• Go-SQL Driver (MySQL)
• PQ (PostgreSQL)
• Go-SQLite3 (SQLite)
• MSSQL DB (Microsoft SQL Server)

You can use the LibHunt drivers list to find equivalents for other database types. The list also shows the relative popularity of each database system:

Installing and Importing Go Database Drivers

Once you’ve created a Go workspace and initialized a Go modules file, install the driver that matches your database system. For example, run one of the following commands in your workspace directory to install the MySQL or SQLite driver:

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

After you’ve installing your driver, import it for side effects by prefixing an underscore before the package. For example, to import the MySQL driver alongside the database/sql package:

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

By importing the driver package for side effects, you can use it to connect to, and execute operations on, the database.

Connecting to an SQL Database With Go

After importing the database drivers, you can create a database connection using the Open method of the database/sql package. This method takes the driver’s name and path to the database (for SQLite) or a connection string (for MySQL). For example, use either of the following:

db, err := sql.Open("sqlite3", "models/testdb.db") // SQLite

db, err := sql.Open("mysql", "user:password@/dbname") // MySQL

Once you’ve attempted to open the connection, remember to check for an error:

if err != nil {
    log.Fatalln(err)
}

Depending on your database system, the Open method may return an error if the database doesn’t exist. Once you’ve connected to a database, you can execute queries and prepare statements using the database instance that Open returns.

Executing SQL Commands

You can execute SQL commands using the Prepare method of your database instance. The Prepare method takes in an SQL command and returns a prepared statement for execution alongside an error object. For example, if you want to create a new table:

command, err := db.Prepare("CREATE TABLE IF NOT EXISTS login(username TEXT, password TEXT)")

The statement above creates a table named login, if it doesn’t already exist. The new table has fields named username and password, each of type TEXT.

If you’re inserting values from your program into your queries, you can use question mark (?) notation to denote placeholders and then pass the parameters on executing the statement.

command, err := db.Prepare("INSERT INTO login(username, password) values(?,?)")

Once you’ve created a prepared statement, you can execute it using its Exec method. This method lets you pass parameter values from your program:

exec, err := command.Exec(value1, Value2)

if err != nil {
    return 
}

The first value that Exec() returns is the result of the SQL query on your database. Using this query result, you can check for the number of rows affected or the last inserted ID:

affected, err := exec.RowsAffected()

if err != nil {
    return
}

fmt.Println(affected)

id, err := exec.LastInsertId()

if err != nil {
    return
}

fmt.Println(id)

Fetching Query Results

The database/sql package lets you query database results using the Query method of a database instance:

rows, err := db.Query("SELECT * FROM User")

if err != nil {
    return
}

The Query method returns a Rows struct that you can use to work with your result set. For example, you can use the Next method of your rows instance to iterate over it and work with individual rows:

var username, password string

for rows.Next() {
    err := rows.Scan(&username, &password)

if err != nil {
        log.Fatalln(err)
    }

fmt.Println(username, password)
}

In the example above, two string variables—username and password—represent each column value. The Scan method decodes the current row into those corresponding variables.

SQL Databases Always Come in Handy

Using databases in Go is straightforward with the database/sql package. You can use it to query and execute SQL commands in Go with ease.

SQL databases are at the core of many applications, especially those dealing with large or complex datasets. You can use databases like the in-memory SQLite database for your simple projects like web scraping, and building bots.

Proper knowledge of SQL and database management systems is essential to use them in your programs efficiently. However, if you choose not to learn SQL, you can learn how to use ORMs to interact with SQL databases in Go.