example_test.go

Documentation: database/sql

     1  // Copyright 2013 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  	"fmt"
    11  	"log"
    12  	"strings"
    13  	"time"
    14  )
    15  
    16  var (
    17  	ctx context.Context
    18  	db  *sql.DB
    19  )
    20  
    21  func ExampleDB_QueryContext() {
    22  	age := 27
    23  	rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age=?", age)
    24  	if err != nil {
    25  		log.Fatal(err)
    26  	}
    27  	defer rows.Close()
    28  	names := make([]string, 0)
    29  
    30  	for rows.Next() {
    31  		var name string
    32  		if err := rows.Scan(&name); err != nil {
    33  			// Check for a scan error.
    34  			// Query rows will be closed with defer.
    35  			log.Fatal(err)
    36  		}
    37  		names = append(names, name)
    38  	}
    39  	// If the database is being written to ensure to check for Close
    40  	// errors that may be returned from the driver. The query may
    41  	// encounter an auto-commit error and be forced to rollback changes.
    42  	rerr := rows.Close()
    43  	if rerr != nil {
    44  		log.Fatal(rerr)
    45  	}
    46  
    47  	// Rows.Err will report the last error encountered by Rows.Scan.
    48  	if err := rows.Err(); err != nil {
    49  		log.Fatal(err)
    50  	}
    51  	fmt.Printf("%s are %d years old", strings.Join(names, ", "), age)
    52  }
    53  
    54  func ExampleDB_QueryRowContext() {
    55  	id := 123
    56  	var username string
    57  	var created time.Time
    58  	err := db.QueryRowContext(ctx, "SELECT username, created_at FROM users WHERE id=?", id).Scan(&username, &created)
    59  	switch {
    60  	case err == sql.ErrNoRows:
    61  		log.Printf("no user with id %d\n", id)
    62  	case err != nil:
    63  		log.Fatalf("query error: %v\n", err)
    64  	default:
    65  		log.Printf("username is %q, account created on %s\n", username, created)
    66  	}
    67  }
    68  
    69  func ExampleDB_ExecContext() {
    70  	id := 47
    71  	result, err := db.ExecContext(ctx, "UPDATE balances SET balance = balance + 10 WHERE user_id = ?", id)
    72  	if err != nil {
    73  		log.Fatal(err)
    74  	}
    75  	rows, err := result.RowsAffected()
    76  	if err != nil {
    77  		log.Fatal(err)
    78  	}
    79  	if rows != 1 {
    80  		log.Fatalf("expected to affect 1 row, affected %d", rows)
    81  	}
    82  }
    83  
    84  func ExampleDB_Query_multipleResultSets() {
    85  	age := 27
    86  	q := `
    87  create temp table uid (id bigint); -- Create temp table for queries.
    88  insert into uid
    89  select id from users where age < ?; -- Populate temp table.
    90  
    91  -- First result set.
    92  select
    93  	users.id, name
    94  from
    95  	users
    96  	join uid on users.id = uid.id
    97  ;
    98  
    99  -- Second result set.
   100  select 
   101  	ur.user, ur.role
   102  from
   103  	user_roles as ur
   104  	join uid on uid.id = ur.user
   105  ;
   106  	`
   107  	rows, err := db.Query(q, age)
   108  	if err != nil {
   109  		log.Fatal(err)
   110  	}
   111  	defer rows.Close()
   112  
   113  	for rows.Next() {
   114  		var (
   115  			id   int64
   116  			name string
   117  		)
   118  		if err := rows.Scan(&id, &name); err != nil {
   119  			log.Fatal(err)
   120  		}
   121  		log.Printf("id %d name is %s\n", id, name)
   122  	}
   123  	if !rows.NextResultSet() {
   124  		log.Fatalf("expected more result sets: %v", rows.Err())
   125  	}
   126  	var roleMap = map[int64]string{
   127  		1: "user",
   128  		2: "admin",
   129  		3: "gopher",
   130  	}
   131  	for rows.Next() {
   132  		var (
   133  			id   int64
   134  			role int64
   135  		)
   136  		if err := rows.Scan(&id, &role); err != nil {
   137  			log.Fatal(err)
   138  		}
   139  		log.Printf("id %d has role %s\n", id, roleMap[role])
   140  	}
   141  	if err := rows.Err(); err != nil {
   142  		log.Fatal(err)
   143  	}
   144  }
   145  
   146  func ExampleDB_PingContext() {
   147  	// Ping and PingContext may be used to determine if communication with
   148  	// the database server is still possible.
   149  	//
   150  	// When used in a command line application Ping may be used to establish
   151  	// that further queries are possible; that the provided DSN is valid.
   152  	//
   153  	// When used in long running service Ping may be part of the health
   154  	// checking system.
   155  
   156  	ctx, cancel := context.WithTimeout(ctx, 1*time.Second)
   157  	defer cancel()
   158  
   159  	status := "up"
   160  	if err := db.PingContext(ctx); err != nil {
   161  		status = "down"
   162  	}
   163  	log.Println(status)
   164  }
   165  
   166  func ExampleDB_Prepare() {
   167  	projects := []struct {
   168  		mascot  string
   169  		release int
   170  	}{
   171  		{"tux", 1991},
   172  		{"duke", 1996},
   173  		{"gopher", 2009},
   174  		{"moby dock", 2013},
   175  	}
   176  
   177  	stmt, err := db.Prepare("INSERT INTO projects(id, mascot, release, category) VALUES( ?, ?, ?, ? )")
   178  	if err != nil {
   179  		log.Fatal(err)
   180  	}
   181  	defer stmt.Close() // Prepared statements take up server resources and should be closed after use.
   182  
   183  	for id, project := range projects {
   184  		if _, err := stmt.Exec(id+1, project.mascot, project.release, "open source"); err != nil {
   185  			log.Fatal(err)
   186  		}
   187  	}
   188  }
   189  
   190  func ExampleTx_Prepare() {
   191  	projects := []struct {
   192  		mascot  string
   193  		release int
   194  	}{
   195  		{"tux", 1991},
   196  		{"duke", 1996},
   197  		{"gopher", 2009},
   198  		{"moby dock", 2013},
   199  	}
   200  
   201  	tx, err := db.Begin()
   202  	if err != nil {
   203  		log.Fatal(err)
   204  	}
   205  	defer tx.Rollback() // The rollback will be ignored if the tx has been committed later in the function.
   206  
   207  	stmt, err := tx.Prepare("INSERT INTO projects(id, mascot, release, category) VALUES( ?, ?, ?, ? )")
   208  	if err != nil {
   209  		log.Fatal(err)
   210  	}
   211  	defer stmt.Close() // Prepared statements take up server resources and should be closed after use.
   212  
   213  	for id, project := range projects {
   214  		if _, err := stmt.Exec(id+1, project.mascot, project.release, "open source"); err != nil {
   215  			log.Fatal(err)
   216  		}
   217  	}
   218  	if err := tx.Commit(); err != nil {
   219  		log.Fatal(err)
   220  	}
   221  }
   222  
   223  func ExampleDB_BeginTx() {
   224  	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
   225  	if err != nil {
   226  		log.Fatal(err)
   227  	}
   228  	id := 37
   229  	_, execErr := tx.Exec(`UPDATE users SET status = ? WHERE id = ?`, "paid", id)
   230  	if execErr != nil {
   231  		_ = tx.Rollback()
   232  		log.Fatal(execErr)
   233  	}
   234  	if err := tx.Commit(); err != nil {
   235  		log.Fatal(err)
   236  	}
   237  }
   238  
   239  func ExampleConn_ExecContext() {
   240  	// A *DB is a pool of connections. Call Conn to reserve a connection for
   241  	// exclusive use.
   242  	conn, err := db.Conn(ctx)
   243  	if err != nil {
   244  		log.Fatal(err)
   245  	}
   246  	defer conn.Close() // Return the connection to the pool.
   247  	id := 41
   248  	result, err := conn.ExecContext(ctx, `UPDATE balances SET balance = balance + 10 WHERE user_id = ?;`, id)
   249  	if err != nil {
   250  		log.Fatal(err)
   251  	}
   252  	rows, err := result.RowsAffected()
   253  	if err != nil {
   254  		log.Fatal(err)
   255  	}
   256  	if rows != 1 {
   257  		log.Fatalf("expected single row affected, got %d rows affected", rows)
   258  	}
   259  }
   260  
   261  func ExampleTx_ExecContext() {
   262  	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
   263  	if err != nil {
   264  		log.Fatal(err)
   265  	}
   266  	id := 37
   267  	_, execErr := tx.ExecContext(ctx, "UPDATE users SET status = ? WHERE id = ?", "paid", id)
   268  	if execErr != nil {
   269  		if rollbackErr := tx.Rollback(); rollbackErr != nil {
   270  			log.Fatalf("update failed: %v, unable to rollback: %v\n", execErr, rollbackErr)
   271  		}
   272  		log.Fatalf("update failed: %v", execErr)
   273  	}
   274  	if err := tx.Commit(); err != nil {
   275  		log.Fatal(err)
   276  	}
   277  }
   278  
   279  func ExampleTx_Rollback() {
   280  	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
   281  	if err != nil {
   282  		log.Fatal(err)
   283  	}
   284  	id := 53
   285  	_, err = tx.ExecContext(ctx, "UPDATE drivers SET status = ? WHERE id = ?;", "assigned", id)
   286  	if err != nil {
   287  		if rollbackErr := tx.Rollback(); rollbackErr != nil {
   288  			log.Fatalf("update drivers: unable to rollback: %v", rollbackErr)
   289  		}
   290  		log.Fatal(err)
   291  	}
   292  	_, err = tx.ExecContext(ctx, "UPDATE pickups SET driver_id = $1;", id)
   293  	if err != nil {
   294  		if rollbackErr := tx.Rollback(); rollbackErr != nil {
   295  			log.Fatalf("update failed: %v, unable to back: %v", err, rollbackErr)
   296  		}
   297  		log.Fatal(err)
   298  	}
   299  	if err := tx.Commit(); err != nil {
   300  		log.Fatal(err)
   301  	}
   302  }
   303  
   304  func ExampleStmt() {
   305  	// In normal use, create one Stmt when your process starts.
   306  	stmt, err := db.PrepareContext(ctx, "SELECT username FROM users WHERE id = ?")
   307  	if err != nil {
   308  		log.Fatal(err)
   309  	}
   310  	defer stmt.Close()
   311  
   312  	// Then reuse it each time you need to issue the query.
   313  	id := 43
   314  	var username string
   315  	err = stmt.QueryRowContext(ctx, id).Scan(&username)
   316  	switch {
   317  	case err == sql.ErrNoRows:
   318  		log.Fatalf("no user with id %d", id)
   319  	case err != nil:
   320  		log.Fatal(err)
   321  	default:
   322  		log.Printf("username is %s\n", username)
   323  	}
   324  }
   325  
   326  func ExampleStmt_QueryRowContext() {
   327  	// In normal use, create one Stmt when your process starts.
   328  	stmt, err := db.PrepareContext(ctx, "SELECT username FROM users WHERE id = ?")
   329  	if err != nil {
   330  		log.Fatal(err)
   331  	}
   332  	defer stmt.Close()
   333  
   334  	// Then reuse it each time you need to issue the query.
   335  	id := 43
   336  	var username string
   337  	err = stmt.QueryRowContext(ctx, id).Scan(&username)
   338  	switch {
   339  	case err == sql.ErrNoRows:
   340  		log.Fatalf("no user with id %d", id)
   341  	case err != nil:
   342  		log.Fatal(err)
   343  	default:
   344  		log.Printf("username is %s\n", username)
   345  	}
   346  }
   347  
   348  func ExampleRows() {
   349  	age := 27
   350  	rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age=?", age)
   351  	if err != nil {
   352  		log.Fatal(err)
   353  	}
   354  	defer rows.Close()
   355  
   356  	names := make([]string, 0)
   357  	for rows.Next() {
   358  		var name string
   359  		if err := rows.Scan(&name); err != nil {
   360  			log.Fatal(err)
   361  		}
   362  		names = append(names, name)
   363  	}
   364  	// Check for errors from iterating over rows.
   365  	if err := rows.Err(); err != nil {
   366  		log.Fatal(err)
   367  	}
   368  	log.Printf("%s are %d years old", strings.Join(names, ", "), age)
   369  }
   370
View as plain text