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  		_, err = tx.ExecContext(ctx, "stored_proc_name", orderRef)
	 144  
	 145  		if err != nil {
	 146  			tx.Rollback()
	 147  			http.Error(w, err.Error(), http.StatusInternalServerError)
	 148  			return
	 149  		}
	 150  		err = tx.Commit()
	 151  		if err != nil {
	 152  			http.Error(w, "action in unknown state, check state before attempting again", http.StatusInternalServerError)
	 153  			return
	 154  		}
	 155  		w.WriteHeader(http.StatusOK)
	 156  		return
	 157  	}
	 158  }
	 159
View as plain text