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