Golang and Gin framework: best practices to connect to Postgresql

I come from this question.

Project

A very simple project with Gin framework with an endpoint to return a list of users based on their score.

Structure

liveops-tool -- user ---- model ------ user.go ---- service ------ userService.go ---- dao ------ directory.go (interface) ------ userDirectory.go (mock the db) ------ userPostgresDirectory.go -- main.go 

main.go

r.GET("/competition/users", func(c *gin.Context) {     numUsers, err := strconv.Atoi(c.DefaultQuery("numusers", "6"))     minScore, err := strconv.Atoi(c.DefaultQuery("minscore", "0"))     maxScore, err := strconv.Atoi(c.DefaultQuery("maxscore", "2000"))     fmt.Printf("numUsers: %d , minScore: %d , maxScore: %d \n", numUsers, minScore, maxScore)      if err == nil {         var userDirectoryService = userService.NewService(userDirectory.UserPostgrestDirectory{})         var res = userDirectoryService.GenerateUserListByScore(numUsers, minScore, maxScore)         c.JSON(http.StatusOK, gin.H{"users": res})     } else {         c.JSON(http.StatusBadRequest, gin.H{"users": "no valid"})     }  }) 

userService.go

package user  import (     dao "liveops-tool/user/dao"     models "liveops-tool/user/models" )  type UserService struct {     dir dao.Directory }  func NewService(dir dao.Directory) *UserService {     var service = &UserService{         dir: dir,     }     return service }  // GenerateUserListByScore returns a list of users for a tournament func (u UserService) GenerateUserListByScore(numUsers, minScore, maxScore int) []models.User {     return u.dir.SearchUsers(numUsers, minScore, maxScore) } 

userPostgresDirectory.go

package user  import (     "database/sql"     "fmt"     models "liveops-tool/user/models"     "strings"      _ "github.com/lib/pq" )  const (     host     = "localhost"     port     = 5432     user     = "postgres"     password = "XXXX"     dbname   = "XXXX" )  type UserPostgrestDirectory struct { }  func (u UserPostgrestDirectory) getConnection() *sql.DB {     psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+"password=%s dbname=%s sslmode=disable", host, port, user, password, dbname)     db, err := sql.Open("postgres", psqlInfo)     checkErr(err)      return db }   func (u UserPostgrestDirectory) SearchUsers(numUsers, minScore, maxScore int) []models.User {     db := u.getConnection()     defer db.Close()     err := db.Ping()     checkErr(err)      fmt.Println("Successfully connected!")      rows, err := db.Query("SELECT * FROM users WHERE total_score >= $  1 AND total_score <= $  2 LIMIT $  3", minScore, maxScore, numUsers)     if err != nil {         panic(err)     }      var users = []models.User{}     for rows.Next() {         var id int         var name string         var country string         var total_score int         err = rows.Scan(&name, &id, &country, &total_score)         checkErr(err)         fmt.Println("id | name | country | total_score ")         fmt.Printf("%3v | %8v | %6v | %6v\n", id, name, country, total_score)         user := models.User{             ID:         id,             Name:       strings.TrimSpace(name),             Country:    strings.TrimSpace(country),             TotalScore: total_score,         }          users = append(users, user)     }     return users }  func checkErr(err error) {     if err != nil {         panic(err)     } } 

Questions

  • Is it possible to retrieve the connection directly open or apply some singleton pattern to avoid creating a connection every time that the method SearchUsers is called? I even tried to pass the connection on the UserPostgrestDirectory constructor from main.go but I always get the error “database is closed”.

Any other feedback about the code is well received.

Golang and Gin framework: best practices to connect to Postgresql

I come from this question.

Project

A very simple project with Gin framework with an endpoint to return a list of users based on their score.

Structure

liveops-tool -- user ---- model ------ user.go ---- service ------ userService.go ---- dao ------ directory.go (interface) ------ userDirectory.go (mock the db) ------ userPostgresDirectory.go -- main.go 

main.go

r.GET("/competition/users", func(c *gin.Context) {     numUsers, err := strconv.Atoi(c.DefaultQuery("numusers", "6"))     minScore, err := strconv.Atoi(c.DefaultQuery("minscore", "0"))     maxScore, err := strconv.Atoi(c.DefaultQuery("maxscore", "2000"))     fmt.Printf("numUsers: %d , minScore: %d , maxScore: %d \n", numUsers, minScore, maxScore)      if err == nil {         var userDirectoryService = userService.NewService(userDirectory.UserPostgrestDirectory{})         var res = userDirectoryService.GenerateUserListByScore(numUsers, minScore, maxScore)         c.JSON(http.StatusOK, gin.H{"users": res})     } else {         c.JSON(http.StatusBadRequest, gin.H{"users": "no valid"})     }  }) 

userService.go

package user  import (     dao "liveops-tool/user/dao"     models "liveops-tool/user/models" )  type UserService struct {     dir dao.Directory }  func NewService(dir dao.Directory) *UserService {     var service = &UserService{         dir: dir,     }     return service }  // GenerateUserListByScore returns a list of users for a tournament func (u UserService) GenerateUserListByScore(numUsers, minScore, maxScore int) []models.User {     return u.dir.SearchUsers(numUsers, minScore, maxScore) } 

userPostgresDirectory.go

package user  import (     "database/sql"     "fmt"     models "liveops-tool/user/models"     "strings"      _ "github.com/lib/pq" )  const (     host     = "localhost"     port     = 5432     user     = "postgres"     password = "XXXX"     dbname   = "XXXX" )  type UserPostgrestDirectory struct { }  func (u UserPostgrestDirectory) getConnection() *sql.DB {     psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+"password=%s dbname=%s sslmode=disable", host, port, user, password, dbname)     db, err := sql.Open("postgres", psqlInfo)     checkErr(err)      return db }   func (u UserPostgrestDirectory) SearchUsers(numUsers, minScore, maxScore int) []models.User {     db := u.getConnection()     defer db.Close()     err := db.Ping()     checkErr(err)      fmt.Println("Successfully connected!")      rows, err := db.Query("SELECT * FROM users WHERE total_score >= $  1 AND total_score <= $  2 LIMIT $  3", minScore, maxScore, numUsers)     if err != nil {         panic(err)     }      var users = []models.User{}     for rows.Next() {         var id int         var name string         var country string         var total_score int         err = rows.Scan(&name, &id, &country, &total_score)         checkErr(err)         fmt.Println("id | name | country | total_score ")         fmt.Printf("%3v | %8v | %6v | %6v\n", id, name, country, total_score)         user := models.User{             ID:         id,             Name:       strings.TrimSpace(name),             Country:    strings.TrimSpace(country),             TotalScore: total_score,         }          users = append(users, user)     }     return users }  func checkErr(err error) {     if err != nil {         panic(err)     } } 

Questions

  • Is it possible to retrieve the connection directly open or apply some singleton pattern to avoid creating a connection every time that the method SearchUsers is called? I even tried to pass the connection on the UserPostgrestDirectory constructor from main.go but I always get the error “database is closed”.

Any other feedback about the code is well received.