Fix SQLConnectionPoolExhausted: sql: database/sql: connection pool exhausted, all connections are in use in Go

This error occurs when all connections in Go's database/sql connection pool are in use and a new query cannot acquire one before the timeout. Common causes include leaked connections from unclosed rows, too-low MaxOpenConns, or long-running transactions. Fix it by ensuring all rows and statements are closed, tuning pool settings, and adding connection timeout context.

Reading the Stack Trace

Here's what each line means:

• database/sql.(*DB).conn(0x14000118300, {0x1029f0ea0, 0x14000196040}, 0x1): The connection pool's conn method is blocked waiting for a free connection. All MaxOpenConns connections are currently in use.
• main.ListOrders(0x14000226000) /app/handlers/order.go:18 +0x1c4: The ListOrders handler at line 18 triggers a query that cannot acquire a connection from the exhausted pool.
• database/sql.(*DB).QueryContext(0x14000118300, {0x1029f0ea0, 0x14000196040}, {0x1028f1e60, 0x15}, {0x0, 0x0, 0x0}): QueryContext blocks waiting for a connection, eventually timing out via the context deadline.

Common Causes

func ListOrders(c *gin.Context) {
	rows, err := db.Query("SELECT * FROM orders")
	if err != nil {
		c.JSON(500, gin.H{"error": err.Error()})
		return
	}
	// Missing: defer rows.Close()
	var orders []Order
	for rows.Next() {
		var o Order
		rows.Scan(&o.ID, &o.Total)
		orders = append(orders, o)
	}
	c.JSON(200, orders)
}

db.SetMaxOpenConns(2) // only 2 connections for high-traffic app

tx, _ := db.Begin()
// ... complex operations taking minutes ...
tx.Commit() // connection held for entire duration

The Fix

Always defer rows.Close() immediately after the query. Configure pool settings with SetMaxOpenConns, SetMaxIdleConns, and SetConnMaxLifetime. Use QueryContext with a timeout. Check rows.Err() after the loop for errors that occurred during iteration.

func ListOrders(c *gin.Context) {
	rows, err := db.Query("SELECT * FROM orders")
	if err != nil {
		c.JSON(500, gin.H{"error": err.Error()})
		return
	}
	var orders []Order
	for rows.Next() {
		var o Order
		rows.Scan(&o.ID, &o.Total)
		orders = append(orders, o)
	}
	c.JSON(200, orders)
}

func init() {
	db.SetMaxOpenConns(25)
	db.SetMaxIdleConns(5)
	db.SetConnMaxLifetime(5 * time.Minute)
	db.SetConnMaxIdleTime(1 * time.Minute)
}

func ListOrders(c *gin.Context) {
	ctx, cancel := context.WithTimeout(c.Request.Context(), 5*time.Second)
	defer cancel()

	rows, err := db.QueryContext(ctx, "SELECT id, total FROM orders LIMIT 100")
	if err != nil {
		if errors.Is(err, context.DeadlineExceeded) {
			c.JSON(http.StatusGatewayTimeout, gin.H{"error": "query timed out"})
			return
		}
		c.JSON(http.StatusInternalServerError, gin.H{"error": "database error"})
		return
	}
	defer rows.Close()

	var orders []Order
	for rows.Next() {
		var o Order
		if err := rows.Scan(&o.ID, &o.Total); err != nil {
			c.JSON(http.StatusInternalServerError, gin.H{"error": "scan error"})
			return
		}
		orders = append(orders, o)
	}
	if err := rows.Err(); err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "query error"})
		return
	}
	c.JSON(http.StatusOK, orders)
}

Testing the Fix

package handlers_test

import (
	"net/http"
	"net/http/httptest"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/gin-gonic/gin"
	"github.com/stretchr/testify/assert"
)

func TestListOrders_ClosesRows(t *testing.T) {
	db, mock, _ := sqlmock.New()
	defer db.Close()

	rows := sqlmock.NewRows([]string{"id", "total"}).AddRow(1, 99.99)
	mock.ExpectQuery("SELECT").WillReturnRows(rows)

	gin.SetMode(gin.TestMode)
	r := gin.New()
	r.GET("/api/orders", NewListOrders(db))

	req := httptest.NewRequest(http.MethodGet, "/api/orders", nil)
	w := httptest.NewRecorder()
	r.ServeHTTP(w, req)

	assert.Equal(t, http.StatusOK, w.Code)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestListOrders_DBTimeout(t *testing.T) {
	db, mock, _ := sqlmock.New()
	defer db.Close()

	mock.ExpectQuery("SELECT").WillReturnError(context.DeadlineExceeded)

	gin.SetMode(gin.TestMode)
	r := gin.New()
	r.GET("/api/orders", NewListOrders(db))

	req := httptest.NewRequest(http.MethodGet, "/api/orders", nil)
	w := httptest.NewRecorder()
	r.ServeHTTP(w, req)

	assert.Equal(t, http.StatusGatewayTimeout, w.Code)
}

Run your tests:

go test ./handlers/... -v

Pushing Through CI/CD

git checkout -b fix/go-sql-connection-pool,git add handlers/order.go handlers/order_test.go main.go,git commit -m "fix: close rows, tune pool settings, and add query timeouts",git push origin fix/go-sql-connection-pool

Your CI config should look something like this:

name: CI
on:
  pull_request:
    branches: [main]
jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-go@v5
        with:
          go-version: '1.22'
      - run: go mod download
      - run: go vet ./...
      - run: go test ./... -race -coverprofile=coverage.out
      - run: go build ./...

The Full Manual Process: 18 Steps

Here's every step you just went through to fix this one bug:

1. Notice the error alert or see it in your monitoring tool
2. Open the error dashboard and read the stack trace
3. Identify the file and line number from the stack trace
4. Open your IDE and navigate to the file
5. Read the surrounding code to understand context
6. Reproduce the error locally
7. Identify the root cause
8. Write the fix
9. Run the test suite locally
10. Fix any failing tests
11. Write new tests covering the edge case
12. Run the full test suite again
13. Create a new git branch
14. Commit and push your changes
15. Open a pull request
16. Wait for code review
17. Merge and deploy to production
18. Monitor production to confirm the error is resolved

Total time: 30-60 minutes. For one bug.

go get github.com/bugstack/sdk

import "github.com/bugstack/sdk"

func init() {
  bugstack.Init(os.Getenv("BUGSTACK_API_KEY"))
}

Deploying the Fix (Manual Path)

1. Run go test ./... locally to confirm queries work with pool limits.
2. Open a pull request with the connection pool changes.
3. Wait for CI checks to pass on the PR.
4. Have a teammate review and approve the PR.
5. Merge to main and monitor connection pool metrics in staging.

Frequently Asked Questions