Skip to content
Gin Web Framework

Database Integration

Most real-world Gin applications need a database. This guide covers how to structure database access cleanly, configure connection pooling, and apply patterns that keep your handlers testable and your connections healthy.

Using database/sql with Gin

The Go standard library database/sql package works well with Gin. Open the connection once in main and pass it to your handlers.

package main


import (
  "database/sql"
  "log"
  "net/http"


  "github.com/gin-gonic/gin"
  _ "github.com/lib/pq"
)


func main() {
  db, err := sql.Open("postgres", "host=localhost port=5432 user=app dbname=mydb sslmode=disable")
  if err != nil {
    log.Fatal(err)
  }
  defer db.Close()


  // Verify the connection is alive
  if err := db.Ping(); err != nil {
    log.Fatal(err)
  }


  r := gin.Default()


  r.GET("/users/:id", func(c *gin.Context) {
    var name string
    err := db.QueryRowContext(c.Request.Context(), "SELECT name FROM users WHERE id = $1", c.Param("id")).Scan(&name)
    if err == sql.ErrNoRows {
      c.JSON(http.StatusNotFound, gin.H{"error": "user not found"})
      return
    }
    if err != nil {
      c.JSON(http.StatusInternalServerError, gin.H{"error": "database error"})
      return
    }
    c.JSON(http.StatusOK, gin.H{"name": name})
  })


  r.Run(":8080")
}

Always pass c.Request.Context() to database calls so that queries are cancelled automatically when the client disconnects.

Connection pooling configuration

The database/sql package maintains a pool of connections internally. For production workloads, configure the pool to match your database and traffic profile.

db, err := sql.Open("postgres", dsn)
if err != nil {
  log.Fatal(err)
}


// Maximum number of open connections to the database.
db.SetMaxOpenConns(25)


// Maximum number of idle connections retained in the pool.
db.SetMaxIdleConns(10)


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


// Maximum amount of time a connection may sit idle before being closed.
db.SetConnMaxIdleTime(1 * time.Minute)
  • SetMaxOpenConns prevents your application from overwhelming the database server under high load.
  • SetMaxIdleConns keeps warm connections ready so new requests avoid the cost of dialling.
  • SetConnMaxLifetime rotates connections so your app picks up DNS changes and does not hold stale server-side sessions.
  • SetConnMaxIdleTime closes connections that have been idle too long, freeing resources on both sides.

Dependency injection patterns

Closure

The simplest approach is to close over the *sql.DB in your handler functions.

package main


import (
  "database/sql"
  "net/http"


  "github.com/gin-gonic/gin"
)


func listUsers(db *sql.DB) gin.HandlerFunc {
  return func(c *gin.Context) {
    rows, err := db.QueryContext(c.Request.Context(), "SELECT id, name FROM users")
    if err != nil {
      c.JSON(http.StatusInternalServerError, gin.H{"error": "query failed"})
      return
    }
    defer rows.Close()


    type User struct {
      ID   int    `json:"id"`
      Name string `json:"name"`
    }
    var users []User
    for rows.Next() {
      var u User
      if err := rows.Scan(&u.ID, &u.Name); err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "scan failed"})
        return
      }
      users = append(users, u)
    }
    c.JSON(http.StatusOK, users)
  }
}


func main() {
  db, _ := sql.Open("postgres", "your-dsn")
  defer db.Close()


  r := gin.Default()
  r.GET("/users", listUsers(db))
  r.Run(":8080")
}

Middleware

Store the connection in the Gin context so any handler can retrieve it.

func DatabaseMiddleware(db *sql.DB) gin.HandlerFunc {
  return func(c *gin.Context) {
    c.Set("db", db)
    c.Next()
  }
}


func main() {
  db, _ := sql.Open("postgres", "your-dsn")
  defer db.Close()


  r := gin.Default()
  r.Use(DatabaseMiddleware(db))


  r.GET("/users/:id", func(c *gin.Context) {
    db := c.MustGet("db").(*sql.DB)
    // use db...
    _ = db
  })


  r.Run(":8080")
}

Struct with methods

Group related handlers into a struct that holds the database handle. This approach scales well when you have many handlers that share the same dependencies.

type UserHandler struct {
  DB *sql.DB
}


func (h *UserHandler) Get(c *gin.Context) {
  var name string
  err := h.DB.QueryRowContext(c.Request.Context(), "SELECT name FROM users WHERE id = $1", c.Param("id")).Scan(&name)
  if err != nil {
    c.JSON(http.StatusInternalServerError, gin.H{"error": "query failed"})
    return
  }
  c.JSON(http.StatusOK, gin.H{"name": name})
}


func main() {
  db, _ := sql.Open("postgres", "your-dsn")
  defer db.Close()


  uh := &UserHandler{DB: db}


  r := gin.Default()
  r.GET("/users/:id", uh.Get)
  r.Run(":8080")
}

The closure and struct patterns are generally preferred over middleware because they provide compile-time type safety and avoid type assertions at runtime.

Using GORM with Gin

GORM is a popular Go ORM. It wraps database/sql and adds migrations, associations, and a query builder.

package main


import (
  "net/http"


  "github.com/gin-gonic/gin"
  "gorm.io/driver/postgres"
  "gorm.io/gorm"
)


type Product struct {
  gorm.Model
  Name  string  `json:"name"`
  Price float64 `json:"price"`
}


func main() {
  dsn := "host=localhost user=app dbname=mydb port=5432 sslmode=disable"
  db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
  if err != nil {
    panic("failed to connect to database")
  }


  // Auto-migrate the schema
  db.AutoMigrate(&Product{})


  r := gin.Default()


  r.POST("/products", func(c *gin.Context) {
    var p Product
    if err := c.ShouldBindJSON(&p); err != nil {
      c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
      return
    }
    if err := db.Create(&p).Error; err != nil {
      c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to create product"})
      return
    }
    c.JSON(http.StatusCreated, p)
  })


  r.GET("/products/:id", func(c *gin.Context) {
    var p Product
    if err := db.First(&p, c.Param("id")).Error; err != nil {
      c.JSON(http.StatusNotFound, gin.H{"error": "product not found"})
      return
    }
    c.JSON(http.StatusOK, p)
  })


  r.Run(":8080")
}

Transaction handling in request handlers

When a request needs to perform multiple writes that must succeed or fail together, use a database transaction.

With database/sql

r.POST("/transfer", func(c *gin.Context) {
  tx, err := db.BeginTx(c.Request.Context(), nil)
  if err != nil {
    c.JSON(http.StatusInternalServerError, gin.H{"error": "could not begin transaction"})
    return
  }
  // Ensure rollback runs if we return early due to an error.
  defer tx.Rollback()


  _, err = tx.ExecContext(c.Request.Context(),
    "UPDATE accounts SET balance = balance - $1 WHERE id = $2", 100, 1)
  if err != nil {
    c.JSON(http.StatusInternalServerError, gin.H{"error": "debit failed"})
    return
  }


  _, err = tx.ExecContext(c.Request.Context(),
    "UPDATE accounts SET balance = balance + $1 WHERE id = $2", 100, 2)
  if err != nil {
    c.JSON(http.StatusInternalServerError, gin.H{"error": "credit failed"})
    return
  }


  if err := tx.Commit(); err != nil {
    c.JSON(http.StatusInternalServerError, gin.H{"error": "commit failed"})
    return
  }


  c.JSON(http.StatusOK, gin.H{"status": "transfer complete"})
})

With GORM

r.POST("/transfer", func(c *gin.Context) {
  err := db.Transaction(func(tx *gorm.DB) error {
    if err := tx.Model(&Account{}).Where("id = ?", 1).
      Update("balance", gorm.Expr("balance - ?", 100)).Error; err != nil {
      return err
    }
    if err := tx.Model(&Account{}).Where("id = ?", 2).
      Update("balance", gorm.Expr("balance + ?", 100)).Error; err != nil {
      return err
    }
    return nil
  })


  if err != nil {
    c.JSON(http.StatusInternalServerError, gin.H{"error": "transfer failed"})
    return
  }
  c.JSON(http.StatusOK, gin.H{"status": "transfer complete"})
})

GORM’s Transaction method handles Begin, Commit, and Rollback automatically based on the returned error.

Best practices

  • Initialize the database connection in main and share it via closures, a struct, or middleware. Never open a new connection per request.
  • Always use parameterized queries. Pass user input as arguments ($1, ?) rather than concatenating strings. This prevents SQL injection.
  • Configure the connection pool for production. Set MaxOpenConns, MaxIdleConns, and ConnMaxLifetime to values that match your database server limits and expected traffic.
  • Handle connection errors gracefully. Call db.Ping() at startup to fail fast. In handlers, return meaningful HTTP status codes and avoid leaking internal error details to clients.
  • Pass the request context to queries. Use c.Request.Context() so that long-running queries are cancelled when the client disconnects or a timeout fires.
  • Close *sql.Rows with defer. Failing to close rows leaks connections back to the pool.

See also