How to handle postgres DB connection timeout/drop in gorm-CodePudding

My DB connection and its getter is as follow:

func connectDB() (*gorm.DB, error) {
    db, err := gorm.Open(postgres.Open(dbURL), &gorm.Config{})
    if err != nil {
        return nil, err
    }
        
    return db, nil
 }

func GetDB() (*gorm.DB, error) {
    if db == nil {
        return connectDB()
    } else {
        return db, nil
    }
}

I use GetDB() in my code to do operations on the database. My app runs for about 15 minutes. How can I make sure the connection db *gorm.DB will not timeout during all that time? Even if it does not timeout within 15 minutes, how to reconnect gracefully if the connection happens to drop due to network error, etc?

CodePudding user response：

GORM using database/sql to maintain connection pool. The connection pool could handle the connection timeout and error. The connection pool could be configured as below

sqlDB, err := db.DB()

// SetMaxIdleConns sets the maximum number of connections in the idle connection pool.
sqlDB.SetMaxIdleConns(10)

// SetMaxOpenConns sets the maximum number of open connections to the database.
sqlDB.SetMaxOpenConns(100)

// SetConnMaxLifetime sets the maximum amount of time a connection may be reused.
sqlDB.SetConnMaxLifetime(time.Hour)

CodePudding user response：

I suggest you to use a generic database interface *sql.DB ping() function https://gorm.io/docs/generic_interface.html

Ping verifies a connection to the database is still alive, establishing a connection if necessary.

So whenever you do a new request to your database (or just for the requests you know would be executed after a long period of time) you can ping the db first and make sure it is still active (in other case the ping reconnects to the db automatically), and then do your request.