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