Let's Go Further Database setup and configuration › Connecting to PostgreSQL
Previous · Contents · Next
Chapter 5.2.

Connecting to PostgreSQL

OK, now that our new greenlight database is set up, let’s look at how to connect to it from our Go application.

As you probably remember from Let’s Go, to work with a SQL database we need to use a database driver to act as a ‘middleman’ between Go and the database itself. You can find a list of available drivers for PostgreSQL in the Go wiki, but for our project we’ll opt for the popular, reliable, and well-established pq package.

If you’re coding along with this book, then go ahead and use go get to download the latest v1.N.N release of pq like so:

$ go get github.com/lib/pq@v1
go: downloading github.com/lib/pq v1.10.9
go get: added github.com/lib/pq v1.10.9

To connect to the database we’ll also need a data source name (DSN), which is basically a string that contains the necessary connection parameters. The exact format of the DSN will depend on which database driver you’re using (and should be described in the driver documentation), but when using pq you should be able to connect to your local greenlight database as the greenlight user with the following DSN:

postgres://greenlight:pa55word@localhost/greenlight

Establishing a connection pool

The code that we’ll use for connecting to the greenlight database from our Go application is almost exactly the same as in the first Let’s Go book. So we won’t dwell on the details, and hopefully this will all feel very familiar.

At a high level:

Let’s head back to our cmd/api/main.go file and update it like so:

File: cmd/api/main.go
package main

import (
    "context"      // New import
    "database/sql" // New import
    "flag"
    "fmt"
    "log/slog"
    "net/http"
    "os"
    "time"

    // Import the pq driver so that it can register itself with the database/sql 
    // package. Note that we alias this import to the blank identifier, to stop the Go 
    // compiler complaining that the package isn't being used.
    _ "github.com/lib/pq"
)

const version = "1.0.0"

// Add a db struct field to hold the configuration settings for our database connection
// pool. For now this only holds the DSN, which we will read in from a command-line flag.
type config struct {
    port int
    env  string
    db   struct {
        dsn string
    }
}

type application struct {
    config config
    logger *slog.Logger
}

func main() {
    var cfg config

    flag.IntVar(&cfg.port, "port", 4000, "API server port")
    flag.StringVar(&cfg.env, "env", "development", "Environment (development|staging|production)")

    // Read the DSN value from the db-dsn command-line flag into the config struct. We
    // default to using our development DSN if no flag is provided.
    flag.StringVar(&cfg.db.dsn, "db-dsn", "postgres://greenlight:pa55word@localhost/greenlight", "PostgreSQL DSN")

    flag.Parse()

    logger := slog.New(slog.NewTextHandler(os.Stdout, nil))

    // Call the openDB() helper function (see below) to create the connection pool,
    // passing in the config struct as an argument. If this returns an error, we log 
    // it and exit the application immediately.
    db, err := openDB(cfg)
    if err != nil {
        logger.Error(err.Error())
        os.Exit(1)
    }

    // Defer a call to db.Close() so that the connection pool is closed before the
    // main() function exits.
    defer db.Close()

    // Also log a message to say that the connection pool has been successfully 
    // established.
    logger.Info("database connection pool established")

    app := &application{
        config: cfg,
        logger: logger,
    }

    srv := &http.Server{
        Addr:         fmt.Sprintf(":%d", cfg.port),
        Handler:      app.routes(),
        IdleTimeout:  time.Minute,
        ReadTimeout:  5 * time.Second,
        WriteTimeout: 10 * time.Second,
        ErrorLog:     slog.NewLogLogger(logger.Handler(), slog.LevelError),
    }

    logger.Info("starting server", "addr", srv.Addr, "env", cfg.env)
    
    // Because the err variable is now already declared in the code above, we need
    // to use the = operator here, instead of the := operator.
    err = srv.ListenAndServe()
    logger.Error(err.Error())
    os.Exit(1)
}

// The openDB() function returns a sql.DB connection pool.
func openDB(cfg config) (*sql.DB, error) {
    // Use sql.Open() to create an empty connection pool, using the DSN from the config
    // struct.
    db, err := sql.Open("postgres", cfg.db.dsn)
    if err != nil {
        return nil, err
    }

    // Create a context with a 5-second timeout deadline.
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()

    // Use PingContext() to establish a new connection to the database, passing in the
    // context we created above as a parameter. If the connection couldn't be
    // established successfully within the 5-second deadline, then this will return an
    // error. If we get this error, or any other, we close the connection pool and 
    // return the error.
    err = db.PingContext(ctx)
    if err != nil {
        db.Close()
        return nil, err
    }

    // Return the sql.DB connection pool.
    return db, nil
}

Once the cmd/api/main.go file is updated, go ahead and run the application again. You should now see a log message on startup confirming that the connection pool has been successfully established. Similar to this:

$ go run ./cmd/api
time=2023-09-10T10:59:13.722+02:00 level=INFO msg="database connection pool established"
time=2023-09-10T10:59:13.722+02:00 level=INFO msg="starting server" addr=:4000 env=development

Decoupling the DSN

At the moment the default command-line flag value for our DSN is explicitly included as a string in the cmd/api/main.go file.

Even though the username and password in the DSN are just for the development database on your local machine, it would be preferable to not have this information hard-coded into our project files (which could be shared or distributed in the future).

So let’s take some steps to decouple the DSN from our project code and instead store it as an environment variable on your local machine.

If you’re following along, create a new GREENLIGHT_DB_DSN environment variable by adding the following line to either your $HOME/.profile or $HOME/.bashrc files:

File: $HOME/.profile
...

export GREENLIGHT_DB_DSN='postgres://greenlight:pa55word@localhost/greenlight'

Once that’s done you’ll need to reboot your computer, or — if that’s not convenient right now — run the source command on the file that you’ve just edited to effect the change. For example:

$ source $HOME/.profile

Either way, once you’ve rebooted or run source you should be able to see the value for the GREENLIGHT_DB_DSN environment variable in your terminal by running the echo command. Like so:

$ echo $GREENLIGHT_DB_DSN
postgres://greenlight:pa55word@localhost/greenlight

Now let’s update our cmd/api/main.go file to access the environment variable using the os.Getenv() function, and set this as the default value for our DSN command-line flag.

It’s fairly straightforward in practice:

File: cmd/api/main.go
package main

...

func main() {
    var cfg config

    flag.IntVar(&cfg.port, "port", 4000, "API server port")
    flag.StringVar(&cfg.env, "env", "development", "Environment (development|staging|production)")

    // Use the value of the GREENLIGHT_DB_DSN environment variable as the default value
    // for our db-dsn command-line flag.
    flag.StringVar(&cfg.db.dsn, "db-dsn", os.Getenv("GREENLIGHT_DB_DSN"), "PostgreSQL DSN")

    flag.Parse()

    ...
}

If you restart the application again now, you should find that it compiles correctly and works just like before.

You can also try specifying the -help flag when running the application. This should output the descriptive text and default values for our three command-line flags, including the DSN value pulled through from the environment variable. Similar to this:

$ go run ./cmd/api -help
Usage of /tmp/go-build417842398/b001/exe/api:
  -db-dsn string
        PostgreSQL DSN (default "postgres://greenlight:pa55word@localhost/greenlight")
  -env string
        Environment (development|staging|production) (default "development")
  -port int
        API server port (default 4000)

Additional information

Using the DSN with psql

A nice side effect of storing the DSN in an environment variable is that you can use it to easily connect to the greenlight database as the greenlight user, rather than specifying all the connection options manually when running psql. Like so:

$ psql $GREENLIGHT_DB_DSN 
psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

greenlight=>