I come from this question.


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


-- user
---- model
------ user.go
---- service
------ userService.go
---- dao
------ directory.go (interface)
------ userDirectory.go (mock the db)
------ userPostgresDirectory.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"})



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)


package user

import (
    models "liveops-tool/user/models"

    _ "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)

    return db

func (u UserPostgrestDirectory) SearchUsers(numUsers, minScore, maxScore int) []models.User {
    db := u.getConnection()
    defer db.Close()
    err := db.Ping()

    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 {

    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)
        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 {


  • 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.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.