123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158 |
- // Copyright 2018 The Go Authors. All rights reserved.
- // Use of this source code is governed by a BSD-style
- // license that can be found in the LICENSE file.
- package sql_test
- import (
- "context"
- "database/sql"
- "encoding/json"
- "fmt"
- "io"
- "log"
- "net/http"
- "time"
- )
- func Example_openDBService() {
- // Opening a driver typically will not attempt to connect to the database.
- db, err := sql.Open("driver-name", "database=test1")
- if err != nil {
- // This will not be a connection error, but a DSN parse error or
- // another initialization error.
- log.Fatal(err)
- }
- db.SetConnMaxLifetime(0)
- db.SetMaxIdleConns(50)
- db.SetMaxOpenConns(50)
- s := &Service{db: db}
- http.ListenAndServe(":8080", s)
- }
- type Service struct {
- db *sql.DB
- }
- func (s *Service) ServeHTTP(w http.ResponseWriter, r *http.Request) {
- db := s.db
- switch r.URL.Path {
- default:
- http.Error(w, "not found", http.StatusNotFound)
- return
- case "/healthz":
- ctx, cancel := context.WithTimeout(r.Context(), 1*time.Second)
- defer cancel()
- err := s.db.PingContext(ctx)
- if err != nil {
- http.Error(w, fmt.Sprintf("db down: %v", err), http.StatusFailedDependency)
- return
- }
- w.WriteHeader(http.StatusOK)
- return
- case "/quick-action":
- // This is a short SELECT. Use the request context as the base of
- // the context timeout.
- ctx, cancel := context.WithTimeout(r.Context(), 3*time.Second)
- defer cancel()
- id := 5
- org := 10
- var name string
- err := db.QueryRowContext(ctx, `
- select
- p.name
- from
- people as p
- join organization as o on p.organization = o.id
- where
- p.id = :id
- and o.id = :org
- ;`,
- sql.Named("id", id),
- sql.Named("org", org),
- ).Scan(&name)
- if err != nil {
- if err == sql.ErrNoRows {
- http.Error(w, "not found", http.StatusNotFound)
- return
- }
- http.Error(w, err.Error(), http.StatusInternalServerError)
- return
- }
- io.WriteString(w, name)
- return
- case "/long-action":
- // This is a long SELECT. Use the request context as the base of
- // the context timeout, but give it some time to finish. If
- // the client cancels before the query is done the query will also
- // be canceled.
- ctx, cancel := context.WithTimeout(r.Context(), 60*time.Second)
- defer cancel()
- var names []string
- rows, err := db.QueryContext(ctx, "select p.name from people as p where p.active = true;")
- if err != nil {
- http.Error(w, err.Error(), http.StatusInternalServerError)
- return
- }
- for rows.Next() {
- var name string
- err = rows.Scan(&name)
- if err != nil {
- break
- }
- names = append(names, name)
- }
- // Check for errors during rows "Close".
- // This may be more important if multiple statements are executed
- // in a single batch and rows were written as well as read.
- if closeErr := rows.Close(); closeErr != nil {
- http.Error(w, closeErr.Error(), http.StatusInternalServerError)
- return
- }
- // Check for row scan error.
- if err != nil {
- http.Error(w, err.Error(), http.StatusInternalServerError)
- return
- }
- // Check for errors during row iteration.
- if err = rows.Err(); err != nil {
- http.Error(w, err.Error(), http.StatusInternalServerError)
- return
- }
- json.NewEncoder(w).Encode(names)
- return
- case "/async-action":
- // This action has side effects that we want to preserve
- // even if the client cancels the HTTP request part way through.
- // For this we do not use the http request context as a base for
- // the timeout.
- ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
- defer cancel()
- var orderRef = "ABC123"
- tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
- _, err = tx.ExecContext(ctx, "stored_proc_name", orderRef)
- if err != nil {
- tx.Rollback()
- http.Error(w, err.Error(), http.StatusInternalServerError)
- return
- }
- err = tx.Commit()
- if err != nil {
- http.Error(w, "action in unknown state, check state before attempting again", http.StatusInternalServerError)
- return
- }
- w.WriteHeader(http.StatusOK)
- return
- }
- }
|