How to handle postgres DB connection timeout/drop in gorm

Question

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?

zangw · Accepted Answer · 2022-10-13 05:12:11Z

3

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)

answered Oct 13, 2022 at 5:12

zangw

49.1k24 gold badges214 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nikita Petrov · Accepted Answer · 2022-10-13 05:24:57Z

0

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.

answered Oct 13, 2022 at 5:24

Nikita Petrov

593 bronze badges

Collectives™ on Stack Overflow

How to handle postgres DB connection timeout/drop in gorm

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related