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  		<-appSignal
    53  		stop()
    54  	}()
    55  
    56  	Ping(ctx)
    57  
    58  	Query(ctx, *id)
    59  }
    60  
    61  // Ping the database to verify DSN provided by the user is valid and the
    62  // server accessible. If the ping fails exit the program with an error.
    63  func Ping(ctx context.Context) {
    64  	ctx, cancel := context.WithTimeout(ctx, 1*time.Second)
    65  	defer cancel()
    66  
    67  	if err := pool.PingContext(ctx); err != nil {
    68  		log.Fatalf("unable to connect to database: %v", err)
    69  	}
    70  }
    71  
    72  // Query the database for the information requested and prints the results.
    73  // If the query fails exit the program with an error.
    74  func Query(ctx context.Context, id int64) {
    75  	ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
    76  	defer cancel()
    77  
    78  	var name string
    79  	err := pool.QueryRowContext(ctx, "select p.name from people as p where p.id = :id;", sql.Named("id", id)).Scan(&name)
    80  	if err != nil {
    81  		log.Fatal("unable to execute search query", err)
    82  	}
    83  	log.Println("name=", name)
    84  }
    85
View as plain text