Creating a Simple Contacts List with Go and PostgreSQL

By Kamal Nasser

Published: March 18, 2019
20 min read

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:

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:

Make sure you have Go installed. See this post for instructions.
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.

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 is passed, the value of the environment variable will be used. If neither are set, we’ll fall back to port 8080.

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

go run main.go

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

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:

<!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:

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.

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:

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:

http.HandleFunc(“/”, handler)

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

package main  

import (  
    "flag"  
    "log"  
    "html/template"  
    "net/http"  
)  
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.

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. If you prefer a video, click here.

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:

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.

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` and `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:

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 and prepare support for the JSONB field favorites:

// 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:

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:

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!

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  
  
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: FavoritesJSON types.JSONText db:“favorites”. 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 un-marshal 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():

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:

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:

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

Then, under the

line in the HTML template, add the following:

{{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:

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

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.

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.

[Hungry for more tutorials? Try Kamal’s guides to “Creating a Simple Contacts List with Laravel and PostgreSQL” and “Deploying a Fully-automated Git-based Static Website in Under 5 Minutes”]

Kamal Nasser is a Developer Advocate at DigitalOcean. He is also a Computer Science student with a passion for software engineering and avocados. You can find him on Twitter @kamaln7.