# Database Connection Pooling Example in Golang

Database connection pooling is a technique used to improve the performance of database-driven applications by reusing a set of pre-opened database connections.

In a typical database-driven application, the application opens a database connection, executes a query, and then closes the connection when it is done. This process is repeated for each query that the application needs to execute. This can be inefficient, especially if the application is executing a large number of queries, because it requires the overhead of constantly opening and closing connections to the database.

By using a database connection pool, the application can pre-open a set of database connections and manage them in a pool. When the application needs to execute a query, it simply acquires a connection from the pool and uses it to execute the query. When the query is done, the connection is returned to the pool for later use. This allows the application to quickly acquire and release connections as needed, without the overhead of constantly opening and closing connections.

In addition to improving performance, database connection pooling can also help prevent overloading the database server by limiting the number of connections that can be open at any given time. This helps ensure that the database server has sufficient resources to handle the load from the application.

In Go, a database connection pool is a group of pre-opened database connections that are managed by the Go runtime. This allows the application to quickly acquire and release database connections as needed, without the overhead of constantly opening and closing connections.

Here is an example of how you might use a database connection pool in Go:

```go
// import the necessary packages
import (
	"database/sql"
	_ "github.com/go-sql-driver/mysql" // import the MySQL driver
)

// create a global variable to hold the connection pool
var db *sql.DB

func main() {
	// initialize the connection pool
	var err error
	db, err = sql.Open("mysql", "user:password@tcp(host:port)/dbname")
	if err != nil {
		// handle error
	}

	// set the maximum number of connections in the pool
	db.SetMaxOpenConns(100)

	// use the connection pool to execute a query
	rows, err := db.Query("SELECT * FROM users")
	if err != nil {
		// handle error
	}
	defer rows.Close()

	// process the query results
	for rows.Next() {
		// scan the row into a variable
		var id int
		var name string
		err = rows.Scan(&id, &name)
		if err != nil {
			// handle error
		}

		// do something with the data
		fmt.Printf("id: %d, name: %s\n", id, name)
	}

	// check for errors from iterating over rows.Next()
	if err = rows.Err(); err != nil {
		// handle error
	}
}
```

In this example, we create a global `db` variable of type `*sql.DB`, which will hold our connection pool. We then use the `sql.Open()` function to initialize the connection pool, passing in the MySQL driver name and database connection string as arguments.

Next, we use the `db.SetMaxOpenConns()` method to set the maximum number of connections that can be open in the pool at any given time. This allows us to control the number of connections to the database and prevent overloading the server.

Finally, we use the connection pool to execute a query using the `db.Query()` method, and then iterate over the results using a `for` loop. Each row is scanned into variables using the `rows.Scan()` method, and then we process the data as needed.

By using a database connection pool, we can improve the performance of our Go application by reusing database connections and reducing the overhead of constantly opening and closing connections.

Here is an example of writing a custom database connection pool in Go:

```go
package main

import (
	"database/sql"
	"fmt"
	"log"
	"sync"

	_ "github.com/go-sql-driver/mysql" // import the MySQL driver
)

type ConnectionPool struct {
	// the underlying connection pool
	pool *sql.DB

	// the maximum number of connections in the pool
	maxConnections int

	// the current number of connections in the pool
	numConnections int

	// the mutex to synchronize access to the connection pool
	mutex *sync.Mutex
}

// NewConnectionPool creates a new ConnectionPool instance
func NewConnectionPool(dsn string, maxConnections int) (*ConnectionPool, error) {
	// create a new connection pool
	pool, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}

	// set the maximum number of connections in the pool
	pool.SetMaxOpenConns(maxConnections)

	// create a new ConnectionPool instance
	p := &ConnectionPool{
		pool:           pool,
		maxConnections: maxConnections,
		numConnections: 0,
		mutex:          &sync.Mutex{},
	}

	return p, nil
}

// GetConnection acquires a connection from the pool
func (p *ConnectionPool) GetConnection() (*sql.DB, error) {
	// acquire the mutex lock
	p.mutex.Lock()
	defer p.mutex.Unlock()

	// check if the pool is full
	if p.numConnections == p.maxConnections {
		return nil, fmt.Errorf("connection pool is full")
	}

	// increment the number of connections in the pool
	p.numConnections++

	// return a connection from the underlying pool
	return p.pool, nil
}

// ReleaseConnection releases a connection back to the pool
func (p *ConnectionPool) ReleaseConnection(conn *sql.DB) {
	// acquire the mutex lock
	p.mutex.Lock()
	defer p.mutex.Unlock()

	// decrement the number of connections in the pool
	p.numConnections--
}

func main() {
	// create a new connection pool
	pool, err := NewConnectionPool("user:password@tcp(host:port)/dbname", 100)
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()

	// acquire a connection from the pool
	conn, err := pool.GetConnection()
	if err != nil {
		log.Fatal(err)
    }
   // use the connection as before.
}
```

Hope you liked it!