mysql – How to set defer .close correctly in golang with dependency injection-ThrowExceptions

Exception or error:

I want to set defer xxx.Close() to close connection in golang.
But if I set defer xxx.Close() after INSERT SQL method, its result is null.
If I remove defer xxx.Close() after INSERT SQL method, its result returns proper value.
I set defer xxx.Close() to avoid connection limit and crash. But now this is not avoided if I remove defer xxx.Close().

  • I set dependency injection in golang.

dao.go

package dao

import (
    "database/sql"
    "log"
)

type Dao struct {
    database *sql.DB
}

func NewDao(database *sql.DB) *Dao {
    objs := &Dao{database: database}
    return objs
}

type DaoInterface interface {
    GetEmployeeListDao() *sql.Rows
    RegisterEmployeeDao(employee model.RegisterEmployee, uu string, encrypted string)
}

func (d *Dao) GetEmployeeListDao() *sql.Rows {

    results, err := d.database.Query("SELECT id, name, email, isDisplay FROM employee")
    if err != nil {
        panic(err.Error())
    }
    //it returns nothing.
    //If removing defer results.Close(), results returns correct value
    defer results.Close()
    return results
}

func (d *Dao) RegisterEmployeeDao(employee model.RegisterEmployee, uu string, encrypted string) {
    ins, err := d.database.Prepare("INSERT INTO employee(name, email, password, isDisplay) VALUES(?,?,?,?)")
    if err != nil {
        log.Fatal(err)
    }
    //same problem will occure here.
    defer ins.Close()
    _, err = ins.Exec(uu, employee.NAME, employee.EMAIL, encrypted, employee.ISDISPLAY)
    if err != nil {
        log.Fatal(err)
    }
}

service.go

package service

import (
    "crypto/aes"
    "crypto/cipher"
    "crypto/rand"
    "encoding/base64"
    "io"

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

type Service struct {
    dao dao.DaoInterface
}

func NewService(dao dao.DaoInterface) *Service {
    return &Service{dao: dao}
}

type ServiceInterface interface {
    GetEmployeeListService() []model.Employee
    RegisterEmployeeService(c *gin.Context)
}

func (s Service) GetEmployeeListService() []model.Employee {
    var employee []model.Employee

    results := s.dao.GetEmployeeListDao()

    singleEmployee := model.Employee{}
    for results.Next() {
        err := results.Scan(&singleEmployee.ID, &singleEmployee.NAME, &singleEmployee.EMAIL, &singleEmployee.ISDISPLAY)
        if err != nil {
            panic(err.Error())
        } else {
            employee = append(employee, singleEmployee)
        }
    }

    defer results.Close()

    var displayEmployee []model.Employee

    for _, e := range employee {
        if e.ISDISPLAY == true {
            displayEmployee = append(displayEmployee, e)
        }
    }

    return displayEmployee
}

func (s Service) RegisterEmployeeService(c *gin.Context) {
    var employee model.RegisterEmployee
    c.BindJSON(&employee)

    block, err := aes.NewCipher([]byte("aes-secret-key-1"))
    if err != nil {
        panic(err)
    }
    b := base64.StdEncoding.EncodeToString([]byte(employee.PASSWORD))
    cipt := make([]byte, aes.BlockSize+len(b))
    iv := cipt[:aes.BlockSize]
    if _, err := io.ReadFull(rand.Reader, iv); err != nil {
        panic(err)
    }
    cfb := cipher.NewCFBEncrypter(block, iv)
    cfb.XORKeyStream(cipt[aes.BlockSize:], []byte(b))
    encrypted := base64.StdEncoding.EncodeToString(cipt)

    s.dao.RegisterEmployeeDao(employee, encrypted)
}

I set dependency injection style in api.
main.go

package main

import (
    "os"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    db := db.NewDatabase(os.Getenv("MYSQL_USER"), os.Getenv("MYSQL_PASSWORD"), os.Getenv("MYSQL_HOST"))
    dao := dao.NewDao(db.DATABASE)
    service := service.NewService(dao)
    cntlr := controller.NewController(service)
    router := handler.NewHandler(cntlr)

    router.Router.Run(":8080")
}

I expect defer xxx.Close() to has no problem and insert and select sql returns correct value.
But the problem is because of defer xxx.Close() insert and select sql does not return correct value.
I want to know how to avoid connection limit and crash without this problem.

How to solve:

I think what you’re expecting is for the result set to be returned immediately as an array or something like that. This doesn’t happen because the client is lazy – it doesn’t give you results until you explicitly try to read a row using Next(). This is with good reason – if a result set has millions of rows in it, it’d be very slow to transmit the data over a network connection, and could even potentially exhaust the memory of the machine running the query. To avoid this, the result set is actually more of a “handle” that allows you to fetch more rows from the DB when you need them, rather than the results themselves.

When you call Close() on a result set, it signals that you no longer need any more data.

There might also be some confusion about defer here. defer triggers at the end of the function, so it will trigger when you return:

defer results.Close()
return results  // defer is triggered here, which will close the result set

Then when you try to read from it, it is a closed result set and so you can’t actually read anything from it.

For reference, here are the docs for what exactly happens when you call Close() on a result set.

Leave a Reply

Your email address will not be published. Required fields are marked *