Postgres in Go

Published in

Namely Labs

3 min readAug 4, 2015

SQL Drivers

Go’s standard library was not built to include any specific database drivers. Here is a list of available third party SQL drivers http://golang.org/s/sqldrivers .

Setup

First we will need to import the packages that our program will use.

import (
  “database/sql”
  _ “github.com/lib/pq”
)

Here, we import the “database/sql” library which provides a generic interface for working with SQL databases. The second import, _”github.com/lib/pq”, is the actual postgresql driver. The underscore before the library means that we import pq without side effects. Basically, it means Go will only import the library for its initialization. For pq, the initialization registers pq as a driver for the SQL interface.

Open

Next we will need to open the database. It is important to note that calling “Open” does not open a connection to the database. The return from “Open” is a DB type and an error. The DB type represents a pool of connections which the sql package manages for you.

db, err := sql.Open(“postgres”,”user=Arnold dbname=TotalRecall sslmode=disable”)

“Open” returns an error which validates the arguments of a database open

if err != nil {
  log.Fatal(“Error: The data source arguments are not valid”)
}

Ping

Since the error returned from “Open” does not check if the datasource is valid calling Ping on the database is required

err = db.Ping()if err != nil {
  log.Fatal(“Error: Could not establish a connection with the database”)
}

Prepare

Once the DB has been set up, we can start safely preparing query statements. “Prepare” does not execute the statement.

queryStmt, err := db.Prepare(“SELECT name FROM users WHERE id=$1”)if err != nil {
  log.Fatal(err)
}

QueryRow

We can now “QueryRow” off of the prepared statement and store the returned row’s first column into the “name string”. “QueryRow” only queries for one row.

var name stringerr = queryStmt.QueryRow(15).Scan(&name)

In addition, a common error check is for “No Rows”. Some programs handle “No Rows” differently from other scanning errors. Errors like this are specific to the library, not Go in general.

if err == sql.ErrNoRows {
  log.Fatal(“No Results Found”)
}if err != nil {
  log.Fatal(err)
}

You can also skip explicitly preparing your Query statements.

var lastName stringerr = db.QueryRow(“SELECT last_name FROM users WHERE id=$1”, 15).Scan(&lastName)if err == sql.ErrNoRows {
  log.Fatal(“No Results Found”)
}if err != nil {
  log.Fatal(err)
}

Query

We can also handle a Query that returns multiple rows and stores the result into a “names” slice. In the code below you will see “rows.Next”, which moves the cursor to the next result row. If there is no next row or error preparing the next row, a false will be returned.

var names []stringrows, err := queryStmt.Query(15)
defer rows.Close()for rows.Next() {
  var name string  if err := rows.Scan(&name); err != nil {
    log.Fatal(err)
  }
  names = append(names, name)
}

This next check is for any errors encountered during the iteration.

err = rows.Err()if err != nil {
  log.Fatal(err)
}

Conclusion

Golang’s standard sql package is extremely simple, yet powerful. This post covers the basics of the sql package. If you would like to learn more, visit the official docs at: http://golang.org/pkg/database/sql. Feel free to leave any comments or questions.

Links: