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?

Have you tried [`SetConnMaxLifetime`](https://golang.org/pkg/database/sql/#DB.SetConnMaxLifetime)? — Ainar-G, Nov 10 '16 at 12:46
@FranckJeannin postgresl, but actually I'm experiencing the same issue with TCP connection to rsyslog — Peter Krejci, Nov 10 '16 at 13:00
The problem here is that `sql.DB` is an abstraction. SQLite databases for example don't use TCP at all because they're file-based. So the actual TCP connections are behind several levels of abstraction that can't be broken without the use of `unsafe`. I'd suggest opening an issue on the Go issue tracker describing your situation. — Ainar-G, Nov 10 '16 at 13:10
You have to use the database driver package directly to specify how the TCP connections are made. Depending on the package, the driver may have a default Dialer which can be changed to return a modified connection, or you may need to register a custom sql.Driver to wrap the Dialer you want. — JimB, Nov 10 '16 at 14:38
The `dataSourceName` accepts a `connect_timeout` parameter, maybe that does what you need? Look at the pq documentation. — Alex Guerra, Nov 10 '16 at 17:04

Ilia Choly · Answer 1 · 2018-10-19T15:24:05.657

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 ...

score 6 · Answer 2 · edited Jun 14 '19 at 18:39

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().

Setting TCP timeout for SQL connection in Go

2 Answers2

Linked