Databases with database/sql

sql.Open is lazy

The database/sql package talks to databases through drivers. The program imports the driver for its side effects only, while all real calls go through the generic DB handle.

sql.Open only validates the arguments and returns a handle, so it never touches the network. Call Ping right after Open when the program must fail fast on a wrong address or bad credentials.

The driver import below is a placeholder. Swap example.com/driver with the driver package for the database in use and keep the blank identifier in front of it.

package main import ( "database/sql" "fmt" _ "example.com/driver" ) func main() { db, err := sql.Open("somedb", "user:pass@/dbname") if err != nil { fmt.Println(err) return } if err := db.Ping(); err != nil { fmt.Println(err) return } fmt.Println("ready") db.Close() }

ready

Query rows

Query runs a statement that returns rows and hands back a Rows cursor. The program walks the cursor with Next and copies every row into variables with Scan.

Three cleanups keep reads safe: defer Close on the cursor, check Scan errors inside the loop, and check Err after the loop for failures that surface while streaming.

package main import ( "database/sql" "fmt" _ "example.com/driver" ) func main() { db, err := sql.Open("somedb", "user:pass@/dbname") if err != nil { fmt.Println(err) return } rows, err := db.Query("SELECT id, name FROM users") if err != nil { fmt.Println(err) return } defer rows.Close() for rows.Next() { var id int var name string if err := rows.Scan(&id, &name); err != nil { fmt.Println(err) return } fmt.Println(id, name) } if err := rows.Err(); err != nil { fmt.Println(err) } }

1 Ann

2 Bob

Exec writes

Exec runs a statement that returns no rows, such as insert, update, or delete. Arguments travel separately from the text, so question marks in the statement fill with safe values and injection has no room to grow.

The returned Result reports what changed. LastInsertId gives the new key on databases that support it, and RowsAffected tells how many rows the statement touched.

package main import ( "database/sql" "fmt" _ "example.com/driver" ) func main() { db, err := sql.Open("somedb", "user:pass@/dbname") if err != nil { fmt.Println(err) return } res, err := db.Exec("INSERT INTO users(name) VALUES(?)", "Ann") if err != nil { fmt.Println(err) return } id, _ := res.LastInsertId() rows, _ := res.RowsAffected() fmt.Println(id, rows) }

3 1

Tune the pool

A DB handle owns a pool of connections that opens and closes links behind the scenes. Defaults work for experiments, but services should set explicit limits before traffic arrives.

SetMaxOpenConns caps parallel links, SetMaxIdleConns keeps warm links ready for reuse, and SetConnMaxLifetime retires old links so load balancers and restarts stay smooth.

package main import ( "database/sql" "fmt" "time" _ "example.com/driver" ) func main() { db, err := sql.Open("somedb", "user:pass@/dbname") if err != nil { fmt.Println(err) return } db.SetMaxOpenConns(10) db.SetMaxIdleConns(5) db.SetConnMaxLifetime(5 * time.Minute) if err := db.Ping(); err != nil { fmt.Println(err) return } fmt.Println("ready") db.Close() }

ready

Next: Benchmarks and profiling

Article author: Arthur Isaev