Accessing Relational Databases Using Go

Published

Have you considered using Go to access your relational databases? In this Write Stuff article, Gigi Sayfan shows you how to access PostgreSQL two ways using Go's standard library and sqlx.

Go (a.k.a Golang) is an open source programming language that focuses on simplicity and has top-notch support for concurrent programming. It was designed at Google in 2007 and has become very popular for building secure, scalable and robust applications.

In this article, I'll demonstrate two ways to access a PostgreSQL relational database in Go - the first using the standard Go library, and the second using sqlx.

Overview

As a sample micro-dataset I found a sensational and frankly quite nonsensical list of European historical (15th - 19th century) geniuses and their IQ. There are 13 geniuses on the list:

Name                           IQ  Nationality  
-----------------------------------------------
Charles Dickens                165 English  
Rafael                         170 Italian  
Michael Faraday                175 English  
Baruch Spinoza                 175 Dutch  
Michaelangelo                  177 Italian  
Desiderius Erasmus             177 Dutch  
Rene Descartes                 177 French  
Galileo Galilei                182 Italian  
John Stuart Mill               182 English  
Gottfried Wilhelm Leibnitz     191 German  
Isaac Newton                   192 English  
Leonardo Da Vinci              200 Italian  
Johann Wolfgang von Goethe     220 German  

As a general background on IQ (intelligent quotient), the standard Stanford-Bine intelligence test can only measure scores in the range 40 - 160. The scores fall on a bell curve (normal distribution) where the mean is 100. Scores become less reliable as you deviate from the mean. The tests are unable to distinguish between people with scores higher than 160 (or lower than 40). Please don't take these scores seriously or try to draw any conclusions whatsoever. This is strictly for fun. But, you may want to read up on the great people in this list.

Back to Go and databases... I'll create a table in a PostgreSQL database and populate it with this list of geniuses and then I'll show you how to perform typical data access, data manipulation and queries all through a Go program.

The database/sql Package

The Go standard library comes with a package called "database/sql" that defines various interfaces and data types for working with relational databases. The library is very low-level and doesn't provide any cross-implementation abstraction. You will need to construct the connection string for your specific DBMS and the specific SQL flavor. The sql.DB is a DB handle that represents a pool of zero or more connections.

Connecting to a Postgres DB

Here the connect() function connects to a local PostgreSQL database and returns the DB handle:

const (  
    host     = "localhost"
    port     = 5432
    user     = "postgres"
    password = "postgres"
    dbname   = "postgres"
    sslmode = "disable"
)

func connect() *sql.DB {  
    t := "host=%s port=%d user=%s password=%s dbname=%s sslmode=disable"
    connectionString := fmt.Sprintf(t, host, port, user, password, dbname, sslmode)
    db, err := sql.Open("postgres", connectionString)
    if err != nil {
        log.Fatal(err)
    }

    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }

    return db
}

The sql.Open() function that returns the sql.DB object is actually NOT connecting to the database. That's why the db.Ping() call is there to actually establish a connection and verify the database can be reached.

In the interest of conciseness, I treat any error as a fatal error, which will cause the program to exit immediately. Normally, I would perform some retry logic and/or report these errors to the caller to deal with the situation.

Creating Tables

The sql.DB interface provides the Exec() method that can send commands to the database. It can be used to execute any command against it including DDL commands. The createSchema() function creates a genius table with name, IQ and nationality columns:

func createSchema(db *sql.DB) {  
    schema := `
        CREATE TABLE IF NOT EXISTS genius (
          id SERIAL PRIMARY KEY,
          name TEXT UNIQUE,
          iq INTEGER,
          nationality TEXT
        );
    `
    _, err := db.Exec(schema)
    if err != nil {
        log.Fatal(err)
    }
}

The db.Exec() method returns a Results object and an Error object. In this case, I don't care about the result, so I assign it to the _ placeholder, which Go doesn't complain about if I don't use later. Unused named variables will cause a compilation error.

Populating the table

You can insert data using the Exec() method too. It's interesting that Go automatically uses upsert, which means inserting existing data will overwrite the previous one. The genius name is defined in the schema as unique. If you insert another genius with the same name, the second genius data (IQ and nationality) will prevail with no error. I also defined a little helper function called exec() that does the error checking and panics if something goes wrong. As a bonus, I also threw in a cleanDB() function that deletes all data from the genius table. The main event is the populateDB() function that iterates over our 13 geniuses and inserts them into the database.

func exec(db *sql.DB, command string) {  
    _, err := db.Exec(command)
    if err != nil {
        log.Fatal(err)
    }
}

func cleanDB(db *sql.DB) {  
    exec(db, "DELETE FROM genius")
}


func populateDB(db *sql.DB) {  
    data := []Genius{
        {"Charles Dickens", 165, "English"},
        {"Rafael", 170, "Italian"},
        {"Michael Faraday", 175, "English"},
        {"Baruch Spinoza", 175, "Dutch"},
        {"Michaelangelo", 177, "Italian"},
        {"Desiderius Erasmus", 177, "Dutch"},
        {"Rene Descartes", 177, "French"},
        {"Galileo Galilei", 182, "Italian"},
        {"John Stuart Mill", 182, "English"},
        {"Gottfried Wilhelm Leibnitz", 191, "German"},
        {"Isaac Newton", 192, "English"},
        {"Leonardo Da Vinci", 200, "Italian"},
        {"Johann Wolfgang von Goethe", 220, "German"},
    }

    for _, g := range data {
        t := "INSERT INTO genius (name, iq, nationality) VALUES ('%s', %d, '%s')"
        command := fmt.Sprintf(t, g.Name, g.IQ, g.Nationality)
        exec(db, command)
    }
}

Querying Data

Querying data is a little awkward. The Query() method accepts a SQL select query string returning a Rows object that you can iterate on with .Next(), but to get to the current row you have to scan it using the rows' Scan() method into pre-defined variables for each column. It's not the slickest interface, but it gets the job done.

func getEnglishGeniuses(db *sql.DB) {  
    rows, err := db.Query("SELECT name, iq FROM genius WHERE nationality='English'")
    if err != nil {
        log.Fatal(err)
    }

    var name string
    var iq int
    for rows.Next() {
        err = rows.Scan(&name, &iq)
        if err != nil {
            log.Fatal(err)
        }

        fmt.Println("name:", name, "IQ:", iq)
    }
}

Output:

name: Charles Dickens IQ: 165  
name: Michael Faraday IQ: 175  
name: John Stuart Mill IQ: 182  
name: Isaac Newton IQ: 192  

Using sqlx for Fun and Profit (or just fun)

The sqlx package extends the standard "database/sql" package. It has objects that are a superset of the standard objects (DB, Rows, Tx and Stmt). This concept makes it very easy to transition to sqlx, since all your existing DB code still works. Here are the main features of sqlx:

Let's see how sqlx makes life easier.

Connecting to the DB with sqlx

The requirement to ping the database after open is both non-intuitive and annoying. With sqlx you can use Connect() or even MustConnect(), which will panic if it can't open or ping the database. Here's how it's done with sqlx:

func connectx() *sqlx.DB {  
    t := "host=%s port=%d user=%s password=%s dbname=%s sslmode=disable"
    connectionString := fmt.Sprintf(t, host, port, user, password, dbname)
    db := sqlx.MustConnect("postgres", connectionString)
    return db
}

Creating Tables with sqlx

The MustExec() method of sqlx is similar to the standard Exec except it panics if something goes wrong, which again provides a convenient way to sidestep explicit and verbose error handling.

func createSchemax(db *sqlx.DB) {  
    schema := `
        CREATE TABLE IF NOT EXISTS genius (
          id SERIAL PRIMARY KEY,
          name TEXT UNIQUE,
          iq INTEGER,
          nationality TEXT
        );
    `
    db.MustExec(schema)
}

Populating the table with sqlx

Again, MustExec() comes in handy. There is no need for the wrapper exec() method with sqlx:

func populateDBx(db *sqlx.DB) {  
    data := []Genius{
        {"Charles Dickens", 165, "English"},
        {"Rafael", 170, "Italian"},
        {"Michael Faraday", 175, "English"},
        {"Baruch Spinoza", 175, "Dutch"},
        {"Michaelangelo", 177, "Italian"},
        {"Desiderius Erasmus", 177, "Dutch"},
        {"Rene Descartes", 177, "French"},
        {"Galileo Galilei", 182, "Italian"},
        {"John Stuart Mill", 182, "English"},
        {"Gottfried Wilhelm Leibnitz", 191, "German"},
        {"Isaac Newton", 192, "English"},
        {"Leonardo Da Vinci", 200, "Italian"},
        {"Johann Wolfgang von Goethe", 220, "German"},
    }

    for _, g := range data {
        t := "INSERT INTO genius (name, iq, nationality) VALUES ('%s', %d, '%s')"
        command := fmt.Sprintf(t, g.Name, g.IQ, g.Nationality)
        db.MustExec(command)
    }
}

Querying Data with sqlx

This is where the real wins are. The struct mapping of sqlx lets you map the results of a query into a struct. Note that sqlx is taking advantage of Go's struct tags (e.g. db:"name") to add metadata to each field and determine the corresponding database column (e.g. column "iq" maps to structure field "IQ"). The sqlx package reads these tags using reflection.

type Genius struct {  
    Name        string `db:"name"`
    IQ          int    `db:"iq"`
    Nationality string `db:"nationality"`
}

func getEnglishGeniusesx(db *sqlx.DB) {  
    geniuses := []Genius{}
    db.Select(&geniuses, "SELECT name, iq FROM genius WHERE nationality='English'")

    for _, g := range geniuses {
        fmt.Println("name:", g.Name, "IQ:", g.IQ)
    }
}

Transactions

Transactions are one of the most important features of relational databases to support data integrity. A transaction may include an arbitrary number of operations on the database that must all succeed or fail. All partial modifications can be rolled back on any failure. Working with transactions is pretty straightforward - you create a transaction that performs operations on the transaction handle instead of the usual database handle, and eventually you either commit all the operations you performed or rollback all the operations. The changes to the database will be visible to other users only once you commit them. Note that transactions use a single connection from the pool, so you must scan or close all cursors (Rows) before issuing further commands or queries.

Let's bump the intelligence of all Dutch geniuses up by 10 points:

func increaseIntelligenceOfDutchGeniusesx(db *sqlx.DB) {  
    geniuses := []Genius{}
    db.Select(&geniuses, "SELECT name, iq FROM genius WHERE nationality='Dutch'")
    tx, err := db.Beginx()
    if err != nil {
        panic("Can't start transaction")
    }

    for _, g := range geniuses {
        t := "UPDATE genius SET iq = %d WHERE name = '%s'"
        command := fmt.Sprintf(t, g.IQ + 10, g.Name)
        _, err = tx.Exec(command)
        if err != nil {
            fmt.Println("Rolling back transaction")
            tx.Rollback()
            return
        }
    }
    tx.Commit()
}

If any update fails, all updates are rolled back.

Using a Compose PostgreSQL Deployment

Switching to use Compose is as simple as changing the constants that create the connection string. The SSL mode must be changed from "disabled" to "require":

const (  
    host     = "aws-us-east-1-portal.6.dblayer.com"
    port     = 20180
    user     = "admin"
    password = "*********"
    dbname   = "compose"
    sslmode = "require"
)

Here is a screenshot of the genius table from the Compose console:

Conclusion

Go has a pretty good, non-opinionated SQL package to work with relational databases. It is pretty low-level and stays out of your way for the most part. The sqlx package extends the built-in "database/sql" package with tag-based automatic struct mapping and a few convenience functions. It is very compatible and easy to switch back and forth if needed. If you want to have full control on your database I highly recommend it.


Do you want to shed light on a favorite feature in your preferred database? Why not write about it for Write Stuff?

Gigi Sayfan is the chief platform architect of VRVIU, a start-up developing cutting-edge hardware and software technology in the virtual reality space. Gigi has been developing software professionally for 21 years in domains as diverse as instant messaging, morphing, chip fabrication process control, embedded multi-media application for game consoles, brain-inspired machine learning, custom browser development, web services for 3D distributed game platform, IoT/sensors and most recently virtual reality.

attribution John Towner

This article is licensed with CC-BY-NC-SA 4.0 by Compose.

Conquer the Data Layer

Spend your time developing apps, not managing databases.