Setting TCP timeout for SQL connection in Go

Question

When I connect to database (using standard go sql library) using VPN and VPN interface goes down, there's a 75 seconds timeout when I try to do SQL query, no matter if the interface goes up meanwhile. I'd like to decrease this timeout to some reasonable time, so my application won't be frozen for 75 seconds in such case.

db, err := sql.Open(driverName, dataSourceName)

Is it possible to set it somehow via db variable?

Answer 1

The database/sql package doesn't provide a general way to timeout a call to database/sql.Open. However, individual drivers provide this functionality via the DSN (dataSourceName) connection strings.

https://github.com/lib/pq

sql.Open("postgres", "user=user dbname=dbname connect_timeout=5")

https://github.com/go-sql-driver/mysql

sql.Open("mysql", "user:password@/dbname?timeout=5s")

https://github.com/denisenkom/go-mssqldb

sql.Open("sqlserver", "sqlserver://username:password@host/instance?dial+timeout=5")

etc ...

Answer 2

Starting with Go 1.8, the sql.DB abstraction now accepts context.Context, which can be used to time out connections faster.

func (c *Client) DoLookup(ctx context.Context, id int) (string, error) {
  var name string
  // create a child context with a timeout
  newCtx, cancel := context.WithTimeout(ctx, time.Second)
  // release resources used in `newCtx` if
  // the DB operation finishes faster than the timeout
  defer cancel()

  row := c.db.QueryRowContext(newCtx, "SELECT name FROM items WHERE id = ?", id)

  err := row.Scan(&name)
  if err != nil {
    return "", err
  }

  return name, nil
}

If your DoLookup function doesn't yet take a context.Context (and it really should!) you can create a parent one by calling context.TODO().