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:

// 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:

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!