Creating a Simple Contacts List with Go and PostgreSQL
Tags: go, postgres, sql, webdev
Published: 2019-05-15 17:00:33

In this post, we will build a simple web page containing a contacts list, with the contacts fetched from a PostgreSQL database. We will connect to the database in Go and use PostgreSQL's support for JSON columns. This is what the result will look like:

![](https://assets.digitalocean.com/ghost/2019/02/Kamal-1.png)

By following this post, you will learn how to connect to a PostgreSQL database in Go using the `sqlx` and `pgx` packages, render data dynamically using a template, and serve the resulting page on an HTTP server.

# Requirements

Before we get started:

1. Make sure you have Go installed. See [this post](https://www.callicoder.com/golang-installation-setup-gopath-workspace/) for instructions.
2. Make sure you know where your `$GOPATH` is. It's usually `~/go` unless set differently.

# Getting an HTTP Server Up

In a new empty directory inside your `$GOPATH`, create a file named `main.go`. You can name the directory anything you like: I went with `go-contacts`. We'll start with setting up the HTTP server using Go's built-in `net/http` package.

```go
package main

import (
    "flag"
    "log"
    "net/http"
"os"
)

var (
    listenAddr = flag.String("addr", getenvWithDefault("LISTENADDR", ":8080"), "HTTP address to listen on")
)

func getenvWithDefault(name, defaultValue string) string {
        val := os.Getenv(name)
        if val == "" {
                val = defaultValue
        }

        return val
}

func main() {
    flag.Parse()

    log.Printf("listening on %s\n", *listenAddr)
    http.ListenAndServe(*listenAddr, nil)
}
```

The server will want a host and a port to listen on, so we ask for that in a CLI flag named `addr`. We also want to offer the option to pass in the setting in an environment variable, so the default value for the flag will be taken from the `LISTENADDR` environment variable. This means that if the CLI flag isn't passed, the value of the environment variable will be used. If neither are set, we'll fall back to `:8080`.

If you save the file and run it now, you should be able to browse to [http://localhost:8080](http://localhost:8080).

```
go run main.go
```

and see—hold on, is that a "404 page not found" error?!

![](https://media.giphy.com/media/HUkOv6BNWc1HO/giphy.gif)

That's fine! It's because we haven't configured any routes or pages yet, so the server doesn't know how to respond to the request. Why don't we go ahead and do that now.

## Contacts List Page

Let's create the contacts list page and serve it on the root path, `/`. We'll use the `template/html` package so that we can easily pass in dynamic data (the contacts) to be rendered in the page later.

Create a directory named `templates` alongside `main.go` and within it a file named `index.html` with the following content:

```html
<!doctype html>
<html>
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">

        <title>Contacts</title>
        <link rel="stylesheet" href="https://unpkg.com/tachyons@4.10.0/css/tachyons.min.css"/>
    </head>
    <body>
        <div class="mw6 center pa3 sans-serif">
            <h1 class="mb4">Contacts</h1>
        </div>
    </body>
</html>
```

This is a page with basic styling that will serve as the base for our contacts list.

Now we need to read the index.html template in our program. Import `html/template` and add a global variable to hold the templates right after `listenAddr` at the top:

```go
import (
    "flag"
    "log"
    "html/template"
    "net/http"
)

var (
        listenAddr       = flag.String("addr", getenvWithDefault("LISTENADDR", ":8080"), "HTTP address to listen on")
        tmpl             = template.New("")
)
```

Inside `main()` , after the `flag.Parse()` line, add the following. For compatibility with all operating systems, import the `path/filepath` package as we will use to construct the path to the template files.

```go
var err error

_, err = tmpl.ParseGlob(filepath.Join(".", "templates", "*.html"))
if err != nil {
    log.Fatalf("Unable to parse templates: %v\n", err)
}
```

This will read every HTML file in the templates directory and prepare it for rendering. Now that we've done that, we want to configure the template to be rendered on `/`. Add a new function at the very bottom of the file to serve the page:

```go
func handler(w http.ResponseWriter, r *http.Request) {
    tmpl.ExecuteTemplate(w, "index.html", nil)
}
```

Finally, configure the server to use this handler function. Above the `log.Printf()` line in `main()`, add:

```go
http.HandleFunc("/", handler)
```

Now we're ready! The whole file should look like this:

```go
package main

import (
    "flag"
    "log"
    "html/template"
    "net/http"
    "os"
    "path/filepath"
)

var (
    listenAddr = flag.String("addr", getenvWithDefault("LISTENADDR", ":8080"), "HTTP address to listen on")
    tmpl       = template.New("")
)

func getenvWithDefault(name, defaultValue string) string {
        val := os.Getenv(name)
        if val == "" {
                val = defaultValue
        }

        return val
}

func main() {
    flag.Parse()

    var err error

    _, err = tmpl.ParseGlob(filepath.Join(".", "templates", "*.html"))
    if err != nil {
        log.Fatalf("Unable to parse templates: %v\n", err)
    }

    http.HandleFunc("/", handler)
    log.Printf("listening on %s\n", *listenAddr)
    http.ListenAndServe(*listenAddr, nil)
}

func handler(w http.ResponseWriter, r *http.Request) {
    tmpl.ExecuteTemplate(w, "index.html", nil)
}
```

Run `go run main.go` again and you should see the template we've configured.

![](https://assets.digitalocean.com/ghost/2019/03/Kamal-2.png)

#Contacts in a Database

Something is missing in the page—the actual contacts! Let's add them in.

We will use DigitalOcean Databases to quickly get a PostgreSQL cluster up. If you haven’t yet, create a new one—it only takes a few minutes: if you prefer a text post, see [the product documentation for Databases](https://www.digitalocean.com/docs/databases/how-to/clusters/create/). If you prefer a video, [click here](https://www.youtube.com/watch?v=jY5FhyiEdig).

![](https://assets.digitalocean.com/ghost/2019/02/Kamal-3.png)

Once you've created the cluster, copy its Connection String from the control panel. In the Connection Details section in the Overview page, choose "Connection string" from the list and copy it:

![](https://assets.digitalocean.com/ghost/2019/03/Kamal-4.png)

The connection string contains all the details necessary to connect to your database (including your password) so be sure to keep it safe.

## Initializing the Database

Our Go app will only handle displaying the contacts, so I have prepared an SQL export containing 10 randomly generated contacts that you can import into your database. You can find it [here](https://raw.githubusercontent.com/digitalocean/databases/master/examples/contacts.sql).

On macOS, I like to use TablePlus to work with my databases, but you can use any client you prefer or import it using the `psql` CLI command like so:

```
psql 'your connection string here' < contacts.sql
```

## Fetching the Contacts

Ok, so now we have a database with some contacts in it 🎉 Let's have our program connect to it and fetch the contacts. We'll build this functionality step by step.

There are many ways to connect to a PostgreSQL database in Go. In this case, we also need a convenient way to access JSONB fields since our contacts database uses them. I personally found the combination of [`github.com/jmoiron/sqlx`](http://github.com/jmoiron/sqlx) and [`github.com/jackc/pgx`](http://github.com/jackc/pgx) to work best.

Start by importing the packages:

```
go get -u -v github.com/jackc/pgx github.com/jmoiron/sqlx
```

And adding them at the top of `main.go`:

```go
import (
    ...

    _ "github.com/jackc/pgx/stdlib"
    "github.com/jmoiron/sqlx"
    "github.com/jmoiron/sqlx/types"
)
```

Now, there are a few things that we need to do. We need to define the Contact type based on the database's table structure and connect to our PostgreSQL database. When serving the contacts page, we will query the database for the contacts and pass them to the template for rendering.

### Contact Type

Add these types to `main.go`. They match the structure of [the contacts database export]((https://raw.githubusercontent.com/digitalocean/databases/master/examples/contacts.sql)) and prepare support for the JSONB field `favorites`:

```go
// ContactFavorites is a field that contains a contact's favorites
type ContactFavorites struct {
    Colors []string `json:"colors"`
}
    
// Contact represents a Contact model in the database 
type Contact struct {
    ID                   int
    Name, Address, Phone string
    
    FavoritesJSON types.JSONText    `db:"favorites"`
    Favorites     *ContactFavorites `db:"-"`
    
    CreatedAt string `db:"created_at"`
    UpdatedAt string `db:"updated_at"`
}
```

### Database Connection

Note that we haven't connected to the database yet 👀 Let's do that now. We'll pass in the PostgreSQL connection string as a CLI flag and add a global database variable. So again at the top of `main.go`:

```go
var (
    connectionString = flag.String("conn", getenvWithDefault("DATABASE_URL", ""), "PostgreSQL connection string")
    listenAddr       = flag.String("addr", ":8080", "HTTP address to listen on")
    db               *sqlx.DB
    tmpl             = template.New("")
)
```

Note that we use the function `getenvWithDefault` like with the listen address to allow the connection string to be passed using an environment variable (`DATABASE_URL`) in addition to the CLI flag (`-conn`).

After the templating logic in `main()` (right above `http.HandleFunc()`), add the following:

```go
if *connectionString == "" {
    log.Fatalln("Please pass the connection string using the -conn option")
}

db, err = sqlx.Connect("pgx", *connectionString)
if err != nil {
    log.Fatalf("Unable to establish connection: %v\n", err)
}
```

We're now connected to our PostgreSQL database!

![](https://media.giphy.com/media/3ohrygAnH3QLVxC3Pq/giphy.gif)

### Querying the Database for Contacts

Add a new function to the bottom of the file to fetch all contacts from the database. For clearer errors, we'll make use of another package: `github.com/pkg/errors`. Download it and import it at the top of `main.go` as usual.

```
go get -u -v github.com/pkg/errors
```

```go
import (
    ...
    "github.com/pkg/errors"
    ...
)

…

func fetchContacts() ([]*Contact, error) {
    contacts := []*Contact{}
    err := db.Select(&contacts, "select * from contacts")
    if err != nil {
        return nil, errors.Wrap(err, "Unable to fetch contacts")
    }

    return contacts, nil
}
```

One thing that's missing right now is the favorites column. If you look at the Contact type, we've defined this field: <code>FavoritesJSON types.JSONText \`db:"favorites"`</code>. This maps the `favorites` column in the database to the `FavoritesJSON` field in the Contact struct, making it available as a JSON object serialized as text.

This means that we need to manually parse and unmarshal the JSON objects into actual Go structs. We will use Go’s `encoding/json` package so make sure to import it at the top of `main.go`. Adding onto `fetchContacts()`:

```go
import (
    ...
    "encoding/json"
    ...
)
...
func fetchContacts() ([]*Contact, error) {
    ...

    for _, contact := range contacts {
        err := json.Unmarshal(contact.FavoritesJSON, &contact.Favorites)

        if err != nil {
            return nil, errors.Wrap(err, "Unable to parse JSON favorites")
        }
    }

    return contacts, nil
}
```

The resulting structs will be stored in the `Favorites` field in the Contact struct.

## Rendering the Contacts

Cool, we have data. Let's use it! Inside the `handler()` function, we'll use `fetchContacts()` to get the contacts and then pass them to the template:

```go
func handler(w http.ResponseWriter, r *http.Request) {
    contacts, err := fetchContacts()
    if err != nil {
        w.WriteHeader(http.StatusInternalServerError)
        w.Write([]byte(err.Error()))
        return
    }

    tmpl.ExecuteTemplate(w, "index.html", struct{ Contacts []*Contact }{contacts})
}
```

This will attempt to fetch the contacts, display an error on failure, and pass them to the template. Note that if an error occurs, the full error will be sent as the response. In a production environment you will want to log the error and send a generic error message instead.

Now we need to modify the template to do something with the contacts we are passing to it. To display favorite colors as a comma-separated list, we'll use the `strings.Join` function. Before we are able to use it inside the template, we need to define it as a template function, inside `main()` above the `tmpl.ParseGlob` line. Don’t forget to import the `strings` package at the top:

```go
import (
    ...
    "strings"
    ...
)

...

tmpl.Funcs(template.FuncMap{"StringsJoin": strings.Join})
_, err = tmpl.ParseGlob(filepath.Join(".", "templates", "*.html"))

...
```

Then, under the `<h1>` line in the HTML template, add the following:

```html
{{range .Contacts}}
<div class="pa2 mb3 striped--near-white">
    <header class="b mb2">{{.Name}}</header>
    <div class="pl2">
        <p class="mb2">{{.Phone }}</p>
        <p class="pre mb3">{{.Address}}</p>
        <p class="mb2"><span class="fw5">Favorite colors:</span> {{StringsJoin .Favorites.Colors ", "}}</p>
    </div>
</div>
{{end}}
```

That's all! The final `main.go` file should look like so:

```go
package main

import (
    "encoding/json"
    "flag"
    "log"
    "html/template"
    "net/http"
        "path/filepath"
    "strings"

    _ "github.com/jackc/pgx/stdlib"
    "github.com/jmoiron/sqlx"
    "github.com/jmoiron/sqlx/types"
    "github.com/pkg/errors"
)

// ContactFavorites is a field that contains a contact's favorites
type ContactFavorites struct {
    Colors []string `json:"colors"`
}

// Contact represents a Contact model in the database    
type Contact struct {
    ID                   int
    Name, Address, Phone string

    FavoritesJSON types.JSONText    `db:"favorites"`
    Favorites     *ContactFavorites `db:"-"`

    CreatedAt string `db:"created_at"`
    UpdatedAt string `db:"updated_at"`
}

var (
    connectionString = flag.String("conn", getenvWithDefault("DATABASE_URL", ""), "PostgreSQL connection string")
    listenAddr       = flag.String("addr", getenvWithDefault("LISTENADDR", ":8080"), "HTTP address to listen on")
    db               *sqlx.DB
    tmpl             = template.New("")
)

func getenvWithDefault(name, defaultValue string) string {
        val := os.Getenv(name)
        if val == "" {
                val = defaultValue
        }

        return val
}

func main() {
    flag.Parse()
    var err error

    // templating

    tmpl.Funcs(template.FuncMap{"StringsJoin": strings.Join})
    _, err = tmpl.ParseGlob(filepath.Join(".", "templates", "*.html"))
    if err != nil {
        log.Fatalf("Unable to parse templates: %v\n", err)
    }

    // postgres connection

    if *connectionString == "" {
        log.Fatalln("Please pass the connection string using the -conn option")
    }

    db, err = sqlx.Connect("pgx", *connectionString)
    if err != nil {
        log.Fatalf("Unable to establish connection: %v\n", err)
    }

    // http server

    http.HandleFunc("/", handler)

    log.Printf("listening on %s\n", *listenAddr)
    http.ListenAndServe(*listenAddr, nil)
}

func fetchContacts() ([]*Contact, error) {
    contacts := []*Contact{}
    err := db.Select(&contacts, "select * from contacts")
    if err != nil {
        return nil, errors.Wrap(err, "Unable to fetch contacts")
    }

    for _, contact := range contacts {
        err := json.Unmarshal(contact.FavoritesJSON, &contact.Favorites)

        if err != nil {
            return nil, errors.Wrap(err, "Unable to parse JSON favorites")
        }
    }

    return contacts, nil
}

func handler(w http.ResponseWriter, r *http.Request) {
    contacts, err := fetchContacts()
    if err != nil {
        w.WriteHeader(http.StatusInternalServerError)
        w.Write([]byte(err.Error()))
        return
    }

    tmpl.ExecuteTemplate(w, "index.html", struct{ Contacts []*Contact }{contacts})
}
```

Run the program again, passing in your database's connection string like so and you should see the contacts list:

```
go run main.go -conn "connection string here"
# alternatively:
DATABASE_URL="connection string here" go run main.go
```

![](https://assets.digitalocean.com/ghost/2019/03/Kamal-5.png)

# Conclusion

After following this post, you will have learned how to build a simple contacts list step-by-step, starting with an empty page served by an HTTP web-server and ending with one that renders a list of contacts fetched from a PostgreSQL database. Along the way, you will have become familiar with using `html/template` to render a web page with dynamic data, connecting to a PostgreSQL database, and interacting with JSONB objects stored in the database.

You can find the full source code in the GitHub repo [digitalocean/databases](https://github.com/digitalocean/databases/tree/master/examples/go-contacts).

## Next Steps

Here are some things you can do after following this post for further practice:

* Print favorite colors as a bullet point list with each color being a separate item. Use `html/template`'s built-in `range` function to loop over the favorite colors slice.
* Add a favorite shape (square, circle, etc.) to one or more contacts and edit the template to display it. The `Contact` struct should stay unmodified.
* List the contacts in the order that they were last updated, most recent first.