Brent Austin | ba3052e | 2015-04-21 16:08:23 -0700 | [diff] [blame] | 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 ( |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 8 | "context" |
Brent Austin | ba3052e | 2015-04-21 16:08:23 -0700 | [diff] [blame] | 9 | "database/sql" |
| 10 | "fmt" |
| 11 | "log" |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 12 | "strings" |
| 13 | "time" |
Brent Austin | ba3052e | 2015-04-21 16:08:23 -0700 | [diff] [blame] | 14 | ) |
| 15 | |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 16 | var ( |
| 17 | ctx context.Context |
| 18 | db *sql.DB |
| 19 | ) |
Brent Austin | ba3052e | 2015-04-21 16:08:23 -0700 | [diff] [blame] | 20 | |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 21 | func ExampleDB_QueryContext() { |
Brent Austin | ba3052e | 2015-04-21 16:08:23 -0700 | [diff] [blame] | 22 | age := 27 |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 23 | rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age=?", age) |
Brent Austin | ba3052e | 2015-04-21 16:08:23 -0700 | [diff] [blame] | 24 | if err != nil { |
| 25 | log.Fatal(err) |
| 26 | } |
| 27 | defer rows.Close() |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 28 | names := make([]string, 0) |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 29 | |
Brent Austin | ba3052e | 2015-04-21 16:08:23 -0700 | [diff] [blame] | 30 | for rows.Next() { |
| 31 | var name string |
| 32 | if err := rows.Scan(&name); err != nil { |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 33 | // Check for a scan error. |
| 34 | // Query rows will be closed with defer. |
Brent Austin | ba3052e | 2015-04-21 16:08:23 -0700 | [diff] [blame] | 35 | log.Fatal(err) |
| 36 | } |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 37 | names = append(names, name) |
Brent Austin | ba3052e | 2015-04-21 16:08:23 -0700 | [diff] [blame] | 38 | } |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 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(err) |
| 45 | } |
| 46 | |
| 47 | // Rows.Err will report the last error encountered by Rows.Scan. |
Brent Austin | ba3052e | 2015-04-21 16:08:23 -0700 | [diff] [blame] | 48 | if err := rows.Err(); err != nil { |
| 49 | log.Fatal(err) |
| 50 | } |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 51 | fmt.Printf("%s are %d years old", strings.Join(names, ", "), age) |
Brent Austin | ba3052e | 2015-04-21 16:08:23 -0700 | [diff] [blame] | 52 | } |
| 53 | |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 54 | func ExampleDB_QueryRowContext() { |
Brent Austin | ba3052e | 2015-04-21 16:08:23 -0700 | [diff] [blame] | 55 | id := 123 |
| 56 | var username string |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 57 | var created time.Time |
| 58 | err := db.QueryRowContext(ctx, "SELECT username, created_at FROM users WHERE id=?", id).Scan(&username, &created) |
Brent Austin | ba3052e | 2015-04-21 16:08:23 -0700 | [diff] [blame] | 59 | switch { |
| 60 | case err == sql.ErrNoRows: |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 61 | log.Printf("no user with id %d\n", id) |
Brent Austin | ba3052e | 2015-04-21 16:08:23 -0700 | [diff] [blame] | 62 | case err != nil: |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 63 | log.Fatalf("query error: %v\n", err) |
Brent Austin | ba3052e | 2015-04-21 16:08:23 -0700 | [diff] [blame] | 64 | default: |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 65 | log.Printf("username is %q, account created on %s\n", username, created) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 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 { |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 80 | log.Fatalf("expected to affect 1 row, affected %d", rows) |
Brent Austin | ba3052e | 2015-04-21 16:08:23 -0700 | [diff] [blame] | 81 | } |
| 82 | } |
Dan Willemsen | ebae302 | 2017-01-13 23:01:08 -0800 | [diff] [blame] | 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 | } |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 121 | log.Printf("id %d name is %s\n", id, name) |
Dan Willemsen | ebae302 | 2017-01-13 23:01:08 -0800 | [diff] [blame] | 122 | } |
| 123 | if !rows.NextResultSet() { |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 124 | log.Fatalf("expected more result sets: %v", rows.Err()) |
Dan Willemsen | ebae302 | 2017-01-13 23:01:08 -0800 | [diff] [blame] | 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 | } |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 139 | log.Printf("id %d has role %s\n", id, roleMap[role]) |
Dan Willemsen | ebae302 | 2017-01-13 23:01:08 -0800 | [diff] [blame] | 140 | } |
| 141 | if err := rows.Err(); err != nil { |
| 142 | log.Fatal(err) |
| 143 | } |
| 144 | } |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 145 | |
| 146 | func ExampleDB_PingContext() { |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 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 | |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 156 | ctx, cancel := context.WithTimeout(ctx, 1*time.Second) |
| 157 | defer cancel() |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 158 | |
| 159 | status := "up" |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 160 | if err := db.PingContext(ctx); err != nil { |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 161 | status = "down" |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 162 | } |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 163 | log.Println(status) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 164 | } |
| 165 | |
| 166 | func ExampleConn_BeginTx() { |
| 167 | tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable}) |
| 168 | if err != nil { |
| 169 | log.Fatal(err) |
| 170 | } |
| 171 | id := 37 |
| 172 | _, execErr := tx.Exec(`UPDATE users SET status = ? WHERE id = ?`, "paid", id) |
| 173 | if execErr != nil { |
| 174 | _ = tx.Rollback() |
| 175 | log.Fatal(execErr) |
| 176 | } |
| 177 | if err := tx.Commit(); err != nil { |
| 178 | log.Fatal(err) |
| 179 | } |
| 180 | } |
| 181 | |
| 182 | func ExampleConn_ExecContext() { |
| 183 | // A *DB is a pool of connections. Call Conn to reserve a connection for |
| 184 | // exclusive use. |
| 185 | conn, err := db.Conn(ctx) |
| 186 | if err != nil { |
| 187 | log.Fatal(err) |
| 188 | } |
| 189 | defer conn.Close() // Return the connection to the pool. |
| 190 | id := 41 |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 191 | result, err := conn.ExecContext(ctx, `UPDATE balances SET balance = balance + 10 WHERE user_id = ?;`, id) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 192 | if err != nil { |
| 193 | log.Fatal(err) |
| 194 | } |
| 195 | rows, err := result.RowsAffected() |
| 196 | if err != nil { |
| 197 | log.Fatal(err) |
| 198 | } |
| 199 | if rows != 1 { |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 200 | log.Fatalf("expected single row affected, got %d rows affected", rows) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 201 | } |
| 202 | } |
| 203 | |
| 204 | func ExampleTx_ExecContext() { |
| 205 | tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable}) |
| 206 | if err != nil { |
| 207 | log.Fatal(err) |
| 208 | } |
| 209 | id := 37 |
| 210 | _, execErr := tx.ExecContext(ctx, "UPDATE users SET status = ? WHERE id = ?", "paid", id) |
| 211 | if execErr != nil { |
| 212 | if rollbackErr := tx.Rollback(); rollbackErr != nil { |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 213 | log.Fatalf("update failed: %v, unable to rollback: %v\n", execErr, rollbackErr) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 214 | } |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 215 | log.Fatalf("update failed: %v", execErr) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 216 | } |
| 217 | if err := tx.Commit(); err != nil { |
| 218 | log.Fatal(err) |
| 219 | } |
| 220 | } |
| 221 | |
| 222 | func ExampleTx_Rollback() { |
| 223 | tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable}) |
| 224 | if err != nil { |
| 225 | log.Fatal(err) |
| 226 | } |
| 227 | id := 53 |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 228 | _, err = tx.ExecContext(ctx, "UPDATE drivers SET status = ? WHERE id = ?;", "assigned", id) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 229 | if err != nil { |
| 230 | if rollbackErr := tx.Rollback(); rollbackErr != nil { |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 231 | log.Fatalf("update drivers: unable to rollback: %v", rollbackErr) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 232 | } |
| 233 | log.Fatal(err) |
| 234 | } |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 235 | _, err = tx.ExecContext(ctx, "UPDATE pickups SET driver_id = $1;", id) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 236 | if err != nil { |
| 237 | if rollbackErr := tx.Rollback(); rollbackErr != nil { |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 238 | log.Fatalf("update failed: %v, unable to back: %v", err, rollbackErr) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 239 | } |
| 240 | log.Fatal(err) |
| 241 | } |
| 242 | if err := tx.Commit(); err != nil { |
| 243 | log.Fatal(err) |
| 244 | } |
| 245 | } |
| 246 | |
| 247 | func ExampleStmt() { |
| 248 | // In normal use, create one Stmt when your process starts. |
| 249 | stmt, err := db.PrepareContext(ctx, "SELECT username FROM users WHERE id = ?") |
| 250 | if err != nil { |
| 251 | log.Fatal(err) |
| 252 | } |
| 253 | defer stmt.Close() |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 254 | |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 255 | // Then reuse it each time you need to issue the query. |
| 256 | id := 43 |
| 257 | var username string |
| 258 | err = stmt.QueryRowContext(ctx, id).Scan(&username) |
| 259 | switch { |
| 260 | case err == sql.ErrNoRows: |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 261 | log.Fatalf("no user with id %d", id) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 262 | case err != nil: |
| 263 | log.Fatal(err) |
| 264 | default: |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 265 | log.Printf("username is %s\n", username) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 266 | } |
| 267 | } |
| 268 | |
| 269 | func ExampleStmt_QueryRowContext() { |
| 270 | // In normal use, create one Stmt when your process starts. |
| 271 | stmt, err := db.PrepareContext(ctx, "SELECT username FROM users WHERE id = ?") |
| 272 | if err != nil { |
| 273 | log.Fatal(err) |
| 274 | } |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 275 | defer stmt.Close() |
| 276 | |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 277 | // Then reuse it each time you need to issue the query. |
| 278 | id := 43 |
| 279 | var username string |
| 280 | err = stmt.QueryRowContext(ctx, id).Scan(&username) |
| 281 | switch { |
| 282 | case err == sql.ErrNoRows: |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 283 | log.Fatalf("no user with id %d", id) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 284 | case err != nil: |
| 285 | log.Fatal(err) |
| 286 | default: |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 287 | log.Printf("username is %s\n", username) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 288 | } |
| 289 | } |
| 290 | |
| 291 | func ExampleRows() { |
| 292 | age := 27 |
| 293 | rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age=?", age) |
| 294 | if err != nil { |
| 295 | log.Fatal(err) |
| 296 | } |
| 297 | defer rows.Close() |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 298 | |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 299 | names := make([]string, 0) |
| 300 | for rows.Next() { |
| 301 | var name string |
| 302 | if err := rows.Scan(&name); err != nil { |
| 303 | log.Fatal(err) |
| 304 | } |
| 305 | names = append(names, name) |
| 306 | } |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 307 | // Check for errors from iterating over rows. |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 308 | if err := rows.Err(); err != nil { |
| 309 | log.Fatal(err) |
| 310 | } |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame^] | 311 | log.Printf("%s are %d years old", strings.Join(names, ", "), age) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 312 | } |