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 | |
Colin Cross | 430342c | 2019-09-07 08:36:04 -0700 | [diff] [blame] | 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 | |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 223 | func ExampleConn_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 |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame] | 248 | 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] | 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 { |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame] | 257 | log.Fatalf("expected single row affected, got %d rows affected", rows) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 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 { |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame] | 270 | log.Fatalf("update failed: %v, unable to rollback: %v\n", execErr, rollbackErr) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 271 | } |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame] | 272 | log.Fatalf("update failed: %v", execErr) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 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 |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame] | 285 | _, err = tx.ExecContext(ctx, "UPDATE drivers SET status = ? WHERE id = ?;", "assigned", id) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 286 | if err != nil { |
| 287 | if rollbackErr := tx.Rollback(); rollbackErr != nil { |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame] | 288 | log.Fatalf("update drivers: unable to rollback: %v", rollbackErr) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 289 | } |
| 290 | log.Fatal(err) |
| 291 | } |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame] | 292 | _, err = tx.ExecContext(ctx, "UPDATE pickups SET driver_id = $1;", id) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 293 | if err != nil { |
| 294 | if rollbackErr := tx.Rollback(); rollbackErr != nil { |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame] | 295 | log.Fatalf("update failed: %v, unable to back: %v", err, rollbackErr) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 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() |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame] | 311 | |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 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: |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame] | 318 | log.Fatalf("no user with id %d", id) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 319 | case err != nil: |
| 320 | log.Fatal(err) |
| 321 | default: |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame] | 322 | log.Printf("username is %s\n", username) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 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 | } |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame] | 332 | defer stmt.Close() |
| 333 | |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 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: |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame] | 340 | log.Fatalf("no user with id %d", id) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 341 | case err != nil: |
| 342 | log.Fatal(err) |
| 343 | default: |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame] | 344 | log.Printf("username is %s\n", username) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 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() |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame] | 355 | |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 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 | } |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame] | 364 | // Check for errors from iterating over rows. |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 365 | if err := rows.Err(); err != nil { |
| 366 | log.Fatal(err) |
| 367 | } |
Colin Cross | d9c6b80 | 2019-03-19 21:10:31 -0700 | [diff] [blame] | 368 | log.Printf("%s are %d years old", strings.Join(names, ", "), age) |
Dan Willemsen | c741332 | 2018-08-27 23:21:26 -0700 | [diff] [blame] | 369 | } |