Effortless Database Connection Pooling in Go |

The processes necessary in opening and shutting a DB connection are not costly enough to be a concern for small-scale operation. However, if your application grows larger, the ongoing opening and shutting of connections costs more and may start to affect how well your application runs. Instead of creating and closing a new connection for every activity, it often makes more sense to figure out a method to maintain existing connections and transfer them from one operation to another as needed.

What is database connection pooling?

Database connection pooling is a way to reduce the cost of opening and closing connections by maintaining a “pool” of open connections that can be passed from database operation to database operation as needed. In this manner, we avoid incurring the cost of needing to initiate and terminate a fresh connection for every query the database is requested to execute.

Database connection pooling isn’t a universally applicable solution, although it can help increase application performance. In certain cases, it might not even be a workable answer. Because keeping open connections and creating/closing connections both require resource expenditures.

Implementation

“Talk is cheap. Show me the code.” - Linus Torvalds

We will be writing a simple connection pooler in Go for TODO REST APIs.

First lets define our db table todos

CREATE TABLE todos (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    completed BOOLEAN NOT NULL DEFAULT FALSE
);

Then we will initialize our database connection and setting up the routes for our APIs.

package main

import (
    "database/sql"
    "encoding/json"
    "fmt"
    "log"
    "net/http"
    "strconv"

    _ "github.com/lib/pq"
    "github.com/gorilla/mux"
)

var db *sql.DB

// Todo represents a task in the to-do list
type Todo struct {
    ID        int    `json:"id"`
    Title     string `json:"title"`
    Completed bool   `json:"completed"`
}

func main() {
    // Database connection string
    connStr := "user=username password=password dbname=mydb sslmode=disable"

    // Open the connection
    var err error
    db, err = sql.Open("postgres", connStr)
    if err != nil {
        log.Fatalf("Error opening database: %q", err)
    }

    // Set the maximum number of concurrently open connections (in-use + idle)
    db.SetMaxOpenConns(25)

    // Set the maximum number of idle connections
    db.SetMaxIdleConns(25)

    // Set the maximum amount of time a connection may be reused
    db.SetConnMaxLifetime(5 * time.Minute)

    // Verify the connection to the database is still alive
    if err := db.Ping(); err != nil {
        log.Fatalf("Error pinging database: %q", err)
    }

    fmt.Println("Database connection established successfully")

    // Create a new router
    r := mux.NewRouter()

    // Register handler functions
    r.HandleFunc("/todos", getTodos).Methods("GET")
    r.HandleFunc("/todos/{id}", getTodo).Methods("GET")
    r.HandleFunc("/todos", createTodo).Methods("POST")
    r.HandleFunc("/todos/{id}", updateTodo).Methods("PUT")
    r.HandleFunc("/todos/{id}", deleteTodo).Methods("DELETE")

    // Start the server
    log.Fatal(http.ListenAndServe(":8080", r))
}

In the above the following part of the code that configures the database connection pool:

db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(5 * time.Minute)

db.SetMaxOpenConns(25) - This line sets the maximum number of open connections to the database.
db.SetMaxIdleConns(25) - This line sets the maximum number of idle connections in the pool.
db.SetConnMaxLifetime(5 * time.Minute) - This line sets the maximum amount of time a connection can be reused before it is closed and replaced by a new connection.

Next we will start creating the functions for our REST APIs


func getTodos(w http.ResponseWriter, r *http.Request) {
    rows, err := db.Query("SELECT id, title, completed FROM todos")
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }
    defer rows.Close()

    var todos []Todo
    for rows.Next() {
        var todo Todo
        if err := rows.Scan(&todo.ID, &todo.Title, &todo.Completed); err != nil {
            http.Error(w, err.Error(), http.StatusInternalServerError)
            return
        }
        todos = append(todos, todo)
    }

    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(todos)
}

func getTodo(w http.ResponseWriter, r *http.Request) {
    params := mux.Vars(r)
    id := params["id"]

    var todo Todo
    err := db.QueryRow("SELECT id, title, completed FROM todos WHERE id = $1", id).Scan(&todo.ID, &todo.Title, &todo.Completed)
    if err != nil {
        if err == sql.ErrNoRows {
            http.Error(w, "Todo not found", http.StatusNotFound)
        } else {
            http.Error(w, err.Error(), http.StatusInternalServerError)
        }
        return
    }

    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(todo)
}

func createTodo(w http.ResponseWriter, r *http.Request) {
    var todo Todo
    if err := json.NewDecoder(r.Body).Decode(&todo); err != nil {
        http.Error(w, err.Error(), http.StatusBadRequest)
        return
    }

    err := db.QueryRow("INSERT INTO todos (title, completed) VALUES ($1, $2) RETURNING id", todo.Title, todo.Completed).Scan(&todo.ID)
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }

    w.Header().Set("Content-Type", "application/json")
    w.WriteHeader(http.StatusCreated)
    json.NewEncoder(w).Encode(todo)
}

func updateTodo(w http.ResponseWriter, r *http.Request) {
    params := mux.Vars(r)
    id := params["id"]

    var todo Todo
    if err := json.NewDecoder(r.Body).Decode(&todo); err != nil {
        http.Error(w, err.Error(), http.StatusBadRequest)
        return
    }

    _, err := db.Exec("UPDATE todos SET title = $1, completed = $2 WHERE id = $3", todo.Title, todo.Completed, id)
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }

    w.WriteHeader(http.StatusNoContent)
}

func deleteTodo(w http.ResponseWriter, r *http.Request) {
    params := mux.Vars(r)
    id := params["id"]

    _, err := db.Exec("DELETE FROM todos WHERE id = $1", id)
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }

    w.WriteHeader(http.StatusNoContent)
}