database/sql: add examples for opening and testing a DB pool

Show two larger application examples. One example that
could be used in a CLI, the other in a long running
service. These demonstarates different strategies for
handling DB.Ping errors in context.

Fixes #23738

Change-Id: Id01213caf1f47917239a7506b01d30e37db74d31
Reviewed-on: https://go-review.googlesource.com/c/101216
Reviewed-by: Brad Fitzpatrick <bradfitz@golang.org>

1 files changed, 86 insertions, 0 deletions

diff --git a/src/database/sql/example_cli_test.go b/src/database/sql/example_cli_test.go
new file mode 100644
index 0000000000..8c61d755bb
--- /dev/null
+++ b/src/database/sql/example_cli_test.go
@@ -0,0 +1,86 @@
+// Copyright 2018 The Go Authors. All rights reserved.
+// Use of this source code is governed by a BSD-style
+// license that can be found in the LICENSE file.
+
+package sql_test
+
+import (
+	"context"
+	"database/sql"
+	"flag"
+	"log"
+	"os"
+	"os/signal"
+	"time"
+)
+
+var pool *sql.DB // Database connection pool.
+
+func Example_openDBCLI() {
+	id := flag.Int64("id", 0, "person ID to find")
+	dsn := flag.String("dsn", os.Getenv("DSN"), "connection data source name")
+	flag.Parse()
+
+	if len(*dsn) == 0 {
+		log.Fatal("missing dsn flag")
+	}
+	if *id == 0 {
+		log.Fatal("missing person ID")
+	}
+	var err error
+
+	// Opening a driver typically will not attempt to connect to the database.
+	pool, err = sql.Open("driver-name", *dsn)
+	if err != nil {
+		// This will not be a connection error, but a DSN parse error or
+		// another initialization error.
+		log.Fatal("unable to use data source name", err)
+	}
+	defer pool.Close()
+
+	pool.SetConnMaxLifetime(0)
+	pool.SetMaxIdleConns(3)
+	pool.SetMaxOpenConns(3)
+
+	ctx, stop := context.WithCancel(context.Background())
+	defer stop()
+
+	appSignal := make(chan os.Signal, 3)
+	signal.Notify(appSignal, os.Interrupt)
+
+	go func() {
+		select {
+		case <-appSignal:
+			stop()
+		}
+	}()
+
+	Ping(ctx)
+
+	Query(ctx, *id)
+}
+
+// Ping the database to verify DSN provided by the user is valid and the
+// server accessible. If the ping fails exit the program with an error.
+func Ping(ctx context.Context) {
+	ctx, cancel := context.WithTimeout(ctx, 1*time.Second)
+	defer cancel()
+
+	if err := pool.PingContext(ctx); err != nil {
+		log.Fatalf("unable to connect to database: %v", err)
+	}
+}
+
+// Query the database for the information requested and prints the results.
+// If the query fails exit the program with an error.
+func Query(ctx context.Context, id int64) {
+	ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
+	defer cancel()
+
+	var name string
+	err := pool.QueryRowContext(ctx, "select p.name from people as p where p.id = :id;", sql.Named("id", id)).Scan(&name)
+	if err != nil {
+		log.Fatal("unable to execute search query", err)
+	}
+	log.Println("name=", name)
+}