Creating a Simple Contacts List with Go and PostgreSQL

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

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.

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.

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.

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:

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.

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

// ContactFavorites is a field that contains a contact's favoritestypeContactFavoritesstruct{Colors[]string`json:"colors"`}// Contact represents a Contact model in the database typeContactstruct{IDintName,Address,PhonestringFavoritesJSONtypes.JSONText`db:"favorites"`Favorites*ContactFavorites`db:"-"`CreatedAtstring`db:"created_at"`UpdatedAtstring`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:

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)iferr!=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.

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 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():

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:

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.