在子例程中处理数据库的打开和关闭

I'm implementing a code where I need to perform few actions at fixed intervals.

Few of them are related to fetching data from mysql database.

To schedule these actions at fixed interval I'm using gocron. It is working quite well.

For the database, as of now, I'm creating an instance at the start of main program and passing it to subroutines. I'm using https://github.com/jmoiron/sqlx to work with DB.

The flow of code is:

i- initialise resources. For eg db = sql.Open;put the DB in common struct to pass to all subroutine

ii- scheduleActions using gocron (pass resources as needed)

iii- actions are specific subroutine that perform task as needed using given resource (for eg DB)

I got few cases where the mysql service needs to be restarted.

Then as expected I get error stating invalid connection. some thing like

[mysql] packets.go:33: unexpected EOF
[mysql] packets.go:130: write tcp 127.0.0.1:36191->127.0.0.1:3306: write: broken pipe
[mysql] connection.go:312: invalid connection

To get around this, I did an implementation to acquire the DB connection with in the subroutine and close with defer db.close(). With this I'm getting error related to too many open connections. I have checked for proper close of rows, as well usage of scan. And see the recommendations are being followed.

I would like to understand how to go about DB open and close handling in my case.

You can use sync.Once to prevent this:

var conn *sql.DB // Set package-wide, but not exported
var once sync.Once

func GetConnection() *sql.DB {
    once.Do(func() {
        var err error
        if conn, err = sql.Open("postgres", "<credentials>"); err != nil {
            log.Panic(err)
        }
        conn.SetMaxOpenConns(20) // Sane default
        conn.SetMaxIdleConns(0)
        conn.SetConnMaxLifetime(time.Nanosecond)
    })
    return conn
}

Read this: https://aaronoellis.com/articles/preventing-max-connection-errors-in-go