Lewati ke konten
Gin Web Framework

Integrasi Database

Sebagian besar aplikasi Gin dunia nyata membutuhkan database. Panduan ini membahas cara menyusun akses database secara bersih, mengonfigurasi connection pooling, dan menerapkan pola yang menjaga handler Anda dapat diuji dan koneksi Anda sehat.

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