example_cli_test.go

Documentation: database/sql

		 1  // Copyright 2018 The Go Authors. All rights reserved.
		 2  // Use of this source code is governed by a BSD-style
		 3  // license that can be found in the LICENSE file.
		 4  
		 5  package sql_test
		 6  
		 7  import (
		 8  	"context"
		 9  	"database/sql"
		10  	"flag"
		11  	"log"
		12  	"os"
		13  	"os/signal"
		14  	"time"
		15  )
		16  
		17  var pool *sql.DB // Database connection pool.
		18  
		19  func Example_openDBCLI() {
		20  	id := flag.Int64("id", 0, "person ID to find")
		21  	dsn := flag.String("dsn", os.Getenv("DSN"), "connection data source name")
		22  	flag.Parse()
		23  
		24  	if len(*dsn) == 0 {
		25  		log.Fatal("missing dsn flag")
		26  	}
		27  	if *id == 0 {
		28  		log.Fatal("missing person ID")
		29  	}
		30  	var err error
		31  
		32  	// Opening a driver typically will not attempt to connect to the database.
		33  	pool, err = sql.Open("driver-name", *dsn)
		34  	if err != nil {
		35  		// This will not be a connection error, but a DSN parse error or
		36  		// another initialization error.
		37  		log.Fatal("unable to use data source name", err)
		38  	}
		39  	defer pool.Close()
		40  
		41  	pool.SetConnMaxLifetime(0)
		42  	pool.SetMaxIdleConns(3)
		43  	pool.SetMaxOpenConns(3)
		44  
		45  	ctx, stop := context.WithCancel(context.Background())
		46  	defer stop()
		47  
		48  	appSignal := make(chan os.Signal, 3)
		49  	signal.Notify(appSignal, os.Interrupt)
		50  
		51  	go func() {
		52  		select {
		53  		case <-appSignal:
		54  			stop()
		55  		}
		56  	}()
		57  
		58  	Ping(ctx)
		59  
		60  	Query(ctx, *id)
		61  }
		62  
		63  // Ping the database to verify DSN provided by the user is valid and the
		64  // server accessible. If the ping fails exit the program with an error.
		65  func Ping(ctx context.Context) {
		66  	ctx, cancel := context.WithTimeout(ctx, 1*time.Second)
		67  	defer cancel()
		68  
		69  	if err := pool.PingContext(ctx); err != nil {
		70  		log.Fatalf("unable to connect to database: %v", err)
		71  	}
		72  }
		73  
		74  // Query the database for the information requested and prints the results.
		75  // If the query fails exit the program with an error.
		76  func Query(ctx context.Context, id int64) {
		77  	ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
		78  	defer cancel()
		79  
		80  	var name string
		81  	err := pool.QueryRowContext(ctx, "select p.name from people as p where p.id = :id;", sql.Named("id", id)).Scan(&name)
		82  	if err != nil {
		83  		log.Fatal("unable to execute search query", err)
		84  	}
		85  	log.Println("name=", name)
		86  }
		87
View as plain text