mysql database gets error in golang 【sql: database is closed】

Question

I try to set up mysql database in golang.
I created db.go for mysql setting up and import it to main.go.
But when I run main.go, error occurs because of db.go.
I want to resolve this error.

There is no compile error.
But when running go run main.go, error occurs.

main.go

package main

// import

func main() {
    err := godotenv.Load()
    if err != nil {
    }
    db := db.NewDatabase(os.Getenv("MYSQL_USER"), os.Getenv("MYSQL_PASSWORD"), os.Getenv("MYSQL_HOST"))
    s3 := s3.NewS3(os.Getenv("AWS_ACCESS_KEY_ID"), os.Getenv("AWS_SECRET_ACCESS_KEY"))
    dao := dao.NewDao(db.DATABASE, s3)
    service := service.NewService(dao)
    cntlr := controller.NewController(service)

    router := gin.Default()

    router.Use(cors.New(cors.Config{
        AllowOrigins:     []string{"*"},
        AllowMethods:     []string{"GET", "POST", "OPTIONS"},
        AllowHeaders:     []string{"Content-Type", "Content-Length", "Accept-Encoding", "X-CSRF-Token", "Authorization", "accept", "origin", "Cache-Control", "X-Requested-With"},
        ExposeHeaders:    []string{"Content-Length"},
        AllowCredentials: true,
        AllowOriginFunc: func(origin string) bool {
            return true
        },
        MaxAge: 15 * time.Second,
    }))

    api := router.Group("/api")
    {
        api.GET("/articles", func(c *gin.Context) {
            cntlr.GetArticleController(c)
        })
        api.GET("/article/:id", func(c *gin.Context) {
            cntlr.GetSingleArticleController(c)
        })
        api.GET("/delete/:id", func(c *gin.Context) {
            cntlr.DeleteArticleController(c)
        })
        api.POST("/post", func(c *gin.Context) {
            cntlr.PostController(c)
        })
        api.POST("/post/image", func(c *gin.Context) {
            cntlr.PostImageController(c)
        })
        api.POST("/post/image/db", func(c *gin.Context) {
            cntlr.PostImageToDBController(c)
        })
    }

    router.Run(":2345")
}

db.go

package db

import "database/sql"

type Database struct {
    MYSQL_USER     string
    MYSQL_PASSWORD string
    MYSQL_HOST     string
    DATABASE       *sql.DB
}

func NewDatabase(user, password, host string) *Database {
    db, err := sql.Open("mysql", user+":"+password+"@tcp("+host+":3306)/article")
    if err != nil {
        panic(err.Error())
    }

    defer db.Close()

    err = db.Ping()
    if err != nil {
        panic(err.Error())
    }

    _, err = db.Exec("CREATE DATABASE IF NOT EXISTS article;")
    if err != nil {
        panic(err)
    }

    _, err = db.Exec("use article;")
    if err != nil {
        panic(err)
    }

    _, err = db.Exec("CREATE TABLE IF NOT EXISTS `articles` (`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,uuid varchar(36), `title` VARCHAR(100) NOT NULL,`content` TEXT NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;")
    if err != nil {
        panic(err)
    }

    _, err = db.Exec("create table IF NOT EXISTS images (id int AUTO_INCREMENT NOT NULL PRIMARY KEY, article_uuid varchar(36), image_name varchar(50)); ")
    if err != nil {
        panic(err)
    }
    Database := new(Database)
    Database.DATABASE = db
    return Database
}

Here is full source code(branch: refactor-db):
https://github.com/jpskgc/article/tree/refactor-db

I expect there is no error when running go run main.go.
But the actual is not.
There will be some problem around db.go.

Here is the error message.

GET /api/articles HTTP/1.1
Host: localhost:2345
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3
Accept-Encoding: gzip, deflate, br
Accept-Language: en-US,en;q=0.9
Cache-Control: max-age=0
Connection: keep-alive
Sec-Fetch-Mode: navigate
Sec-Fetch-Site: none
Sec-Fetch-User: ?1
Upgrade-Insecure-Requests: 1
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/76.0.3809.132 Safari/537.36


sql: database is closed
/Users/jpskgc/article/api/dao/dao.go:36 (0x1553240)
        (*Dao).GetArticleDao: panic(err.Error())
/Users/jpskgc/article/api/service/service.go:34 (0x17a9abb)
        Service.GetArticleService: results := s.dao.GetArticleDao()
/Users/jpskgc/article/api/controller/controller.go:19 (0x17cd664)
        Controller.GetArticleController: articles := controller.service.GetArticleService()
/Users/jpskgc/article/api/main.go:79 (0x17cec1f)
        main.func2: cntlr.GetArticleController(c)
/Users/jpskgc/go/pkg/mod/github.com/gin-gonic/[email protected]/context.go:124 (0x1791e89)
        (*Context).Next: c.handlers[c.index](c)
/Users/jpskgc/go/pkg/mod/github.com/gin-gonic/[email protected]/recovery.go:83 (0x17a5159)
        RecoveryWithWriter.func1: c.Next()
/Users/jpskgc/go/pkg/mod/github.com/gin-gonic/[email protected]/context.go:124 (0x1791e89)
        (*Context).Next: c.handlers[c.index](c)
/Users/jpskgc/go/pkg/mod/github.com/gin-gonic/[email protected]/logger.go:240 (0x17a4200)
        LoggerWithConfig.func1: c.Next()
/Users/jpskgc/go/pkg/mod/github.com/gin-gonic/[email protected]/context.go:124 (0x1791e89)
        (*Context).Next: c.handlers[c.index](c)
/Users/jpskgc/go/pkg/mod/github.com/gin-gonic/[email protected]/gin.go:389 (0x179b6a1)
        (*Engine).handleHTTPRequest: c.Next()
/Users/jpskgc/go/pkg/mod/github.com/gin-gonic/[email protected]/gin.go:351 (0x179aed3)
        (*Engine).ServeHTTP: engine.handleHTTPRequest(c)
/usr/local/go/src/net/http/server.go:2774 (0x12e2a07)
        serverHandler.ServeHTTP: handler.ServeHTTP(rw, req)
/usr/local/go/src/net/http/server.go:1878 (0x12de5f0)
        (*conn).serve: serverHandler{c.server}.ServeHTTP(w, w.req)
/usr/local/go/src/runtime/asm_amd64.s:1337 (0x1059ea0)
        goexit: BYTE    $0x90   // NOP

[GIN] 2019/09/10 - 16:58:46 | 500 |    5.990926ms |             ::1 | GET      /api/articles

Answer 1

Just remove defer db.Close() in db.go You need to close connection out of NewDatabase method