Realize DB connection pool with golang

at first

I ended up talking about connection pools using GCP. The error "Maximum number of connections has exceeded 100" occurs. It is a solution that I found by investigating why the number of connections increases.

environment

Cloud: GCP Web server Vue / CloudRun API server Golang / CloudRun DB server MySQL / CloudSQL

Cause

Every time I hit the API, a new connection is made between the API server and the DB server.

Turned out.

In other words, the connection could not be reused.

Countermeasures

Use the following methods in database.sql to set the connection pool. Also, it seems that ORM can also be used for connection pooling.

Implemented as follows.

DBconnection

func NewLocalDBConnection() error {
	/* ===== connect datebase ===== */
	// user
	user := os.Getenv("MYSQL_USER") 
	// password
	password := os.Getenv("MYSQL_PASSWORD") 
	// connection database
	database := os.Getenv("MYSQL_DATABASE") 
	// connection host
	host := "localhost" 
	// connection port
	port := "3306" 

	var err error
	DB, err = setupDB("mysql", fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?parseTime=true", user, password, host, port, database))
	if err != nil {
		return fmt.Errorf("sql.Open: %v", err)
	}

	return err
}

//this function is a function for connection pooling
func setupDB(Driver string, dsn string) (*sql.DB, error) {
	db, err := sql.Open(Driver, dsn)
	if err != nil {
		return nil, err
	}
	//Set the maximum number of connections in the connection pool.
	db.SetMaxIdleConns(100)
	//Set the maximum number of connections. Set n to a value of 0 or less, and the number of connections is unlimited.
	db.SetMaxOpenConns(100)
	//Set the time when the connection can be reused. By setting d to a value of 0 or less, it can be reused forever.
	db.SetConnMaxLifetime(100 * time.Second)

	return db, err
}