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
5package sql_test
6
7import (
8	"context"
9	"database/sql"
10	"encoding/json"
11	"fmt"
12	"io"
13	"log"
14	"net/http"
15	"time"
16)
17
18func 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
35type Service struct {
36	db *sql.DB
37}
38
39func (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, `
66select
67	p.name
68from
69	people as p
70	join organization as o on p.organization = o.id
71where
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