example_service_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  	"encoding/json"
    11  	"fmt"
    12  	"io"
    13  	"log"
    14  	"net/http"
    15  	"time"
    16  )
    17  
    18  func Example_openDBService() {
    19  	// Opening a driver typically will not attempt to connect to the database.
    20  	db, err := sql.Open("driver-name", "database=test1")
    21  	if err != nil {
    22  		// This will not be a connection error, but a DSN parse error or
    23  		// another initialization error.
    24  		log.Fatal(err)
    25  	}
    26  	db.SetConnMaxLifetime(0)
    27  	db.SetMaxIdleConns(50)
    28  	db.SetMaxOpenConns(50)
    29  
    30  	s := &Service{db: db}
    31  
    32  	http.ListenAndServe(":8080", s)
    33  }
    34  
    35  type Service struct {
    36  	db *sql.DB
    37  }
    38  
    39  func (s *Service) ServeHTTP(w http.ResponseWriter, r *http.Request) {
    40  	db := s.db
    41  	switch r.URL.Path {
    42  	default:
    43  		http.Error(w, "not found", http.StatusNotFound)
    44  		return
    45  	case "/healthz":
    46  		ctx, cancel := context.WithTimeout(r.Context(), 1*time.Second)
    47  		defer cancel()
    48  
    49  		err := s.db.PingContext(ctx)
    50  		if err != nil {
    51  			http.Error(w, fmt.Sprintf("db down: %v", err), http.StatusFailedDependency)
    52  			return
    53  		}
    54  		w.WriteHeader(http.StatusOK)
    55  		return
    56  	case "/quick-action":
    57  		// This is a short SELECT. Use the request context as the base of
    58  		// the context timeout.
    59  		ctx, cancel := context.WithTimeout(r.Context(), 3*time.Second)
    60  		defer cancel()
    61  
    62  		id := 5
    63  		org := 10
    64  		var name string
    65  		err := db.QueryRowContext(ctx, `
    66  select
    67  	p.name
    68  from
    69  	people as p
    70  	join organization as o on p.organization = o.id
    71  where
    72  	p.id = :id
    73  	and o.id = :org
    74  ;`,
    75  			sql.Named("id", id),
    76  			sql.Named("org", org),
    77  		).Scan(&name)
    78  		if err != nil {
    79  			if err == sql.ErrNoRows {
    80  				http.Error(w, "not found", http.StatusNotFound)
    81  				return
    82  			}
    83  			http.Error(w, err.Error(), http.StatusInternalServerError)
    84  			return
    85  		}
    86  		io.WriteString(w, name)
    87  		return
    88  	case "/long-action":
    89  		// This is a long SELECT. Use the request context as the base of
    90  		// the context timeout, but give it some time to finish. If
    91  		// the client cancels before the query is done the query will also
    92  		// be canceled.
    93  		ctx, cancel := context.WithTimeout(r.Context(), 60*time.Second)
    94  		defer cancel()
    95  
    96  		var names []string
    97  		rows, err := db.QueryContext(ctx, "select p.name from people as p where p.active = true;")
    98  		if err != nil {
    99  			http.Error(w, err.Error(), http.StatusInternalServerError)
   100  			return
   101  		}
   102  
   103  		for rows.Next() {
   104  			var name string
   105  			err = rows.Scan(&name)
   106  			if err != nil {
   107  				break
   108  			}
   109  			names = append(names, name)
   110  		}
   111  		// Check for errors during rows "Close".
   112  		// This may be more important if multiple statements are executed
   113  		// in a single batch and rows were written as well as read.
   114  		if closeErr := rows.Close(); closeErr != nil {
   115  			http.Error(w, closeErr.Error(), http.StatusInternalServerError)
   116  			return
   117  		}
   118  
   119  		// Check for row scan error.
   120  		if err != nil {
   121  			http.Error(w, err.Error(), http.StatusInternalServerError)
   122  			return
   123  		}
   124  
   125  		// Check for errors during row iteration.
   126  		if err = rows.Err(); err != nil {
   127  			http.Error(w, err.Error(), http.StatusInternalServerError)
   128  			return
   129  		}
   130  
   131  		json.NewEncoder(w).Encode(names)
   132  		return
   133  	case "/async-action":
   134  		// This action has side effects that we want to preserve
   135  		// even if the client cancels the HTTP request part way through.
   136  		// For this we do not use the http request context as a base for
   137  		// the timeout.
   138  		ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
   139  		defer cancel()
   140  
   141  		var orderRef = "ABC123"
   142  		tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
   143  		if err != nil {
   144  			http.Error(w, err.Error(), http.StatusInternalServerError)
   145  			return
   146  		}
   147  		_, err = tx.ExecContext(ctx, "stored_proc_name", orderRef)
   148  
   149  		if err != nil {
   150  			tx.Rollback()
   151  			http.Error(w, err.Error(), http.StatusInternalServerError)
   152  			return
   153  		}
   154  		err = tx.Commit()
   155  		if err != nil {
   156  			http.Error(w, "action in unknown state, check state before attempting again", http.StatusInternalServerError)
   157  			return
   158  		}
   159  		w.WriteHeader(http.StatusOK)
   160  		return
   161  	}
   162  }
   163
View as plain text