Should we open a new db connection for every new request

(AkezhanOb1) #1
here is an example with a connector to the database, here I do not close the database, should I close and open a database connection for every request?

(Yamil Bracho) #2

There is a basic connection pool in the database/sql package so it reuse database connections. So, you can open when you need and then close it. The close action just returns the connection to the pool.

(Ben Morrison) #3

You don’t need to close each connection every time you’re finished with a given transaction. That would add lots of unnecessary overhead. Like @Yamil_Bracho said, a connection pool is maintained for you, which you can see in the documentation for the sql.DB type in database/sql here:

DB is a database handle representing a pool of zero or more underlying connections. It’s safe for concurrent use by multiple goroutines.

The sql package creates and frees connections automatically; it also maintains a free pool of idle connections. If the database has a concept of per-connection state, such state can be reliably observed within a transaction (Tx) or connection (Conn). Once DB.Begin is called, the returned Tx is bound to a single connection. Once Commit or Rollback is called on the transaction, that transaction’s connection is returned to DB’s idle connection pool. The pool size can be controlled with SetMaxIdleConns.

It’d be simpler, and faster, to use sql.Tx for each transaction after you open the initial database connection. I illustrated this by adding the dbQuery function to your example. Granted, you’d obviously have to fill in the details, but you’ll get the idea of it here:

Also, it’s a good idea to *sqlDB.Ping() the database before each transaction.