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:
We want the DSN to be configurable at runtime, so we will pass it to the application using a command-line flag rather than hard-coding it. For simplicity during development, we’ll use the DSN above as the default value for the flag.
In our
cmd/api/main.gofile we’ll create a newopenDB()helper function. In this helper we’ll use thesql.Open()function to establish a newsql.DBconnection pool, then — because connections to the database are established lazily as and when needed for the first time — we also need to use thedb.PingContext()method to actually create a connection and verify that everything is set up correctly.
Let’s head back to our cmd/api/main.go file and update it like so:
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:
... 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:
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=>