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
$GOPATHis. It's usually~/gounless 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 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.
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"
"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.
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 . This maps the db:"favorites"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():
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 <h1> 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.
You can find the full source code in the GitHub repo digitalocean/databases.
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-inrangefunction 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
Contactstruct should stay unmodified. - List the contacts in the order that they were last updated, most recent first.
Top comments (0)