Colin Cross | 1371fe4 | 2019-03-19 21:08:48 -0700 | [diff] [blame] | 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 | } |