Use mysql connection as middleware in other packages

[illud]

I want to use mysql connection just one time and use it in other functions or packages,
in this case export connection to be used in other functions or endpoints as midelware
Here you can see the code: https://github.com/illud/go-api-mysql

[kync (Ali Koyuncu)]

Can you please tell us with more detail what you are trying to do? in your example code when you call Conn somewhere, you won’t be able to use the connection via the line 26

[Christophe_Meessen]

The defer instruction at line 26 will close the database connection when the Conn function terminates. Another problem of this function is that the db variable is not returned. Since you need the db variable to perform an SQL query, you won’t be able to perform any operation outside of Conn.

The most simple and straightforward solution is to create a global variable db holding the db value returned by sql.Open.

In you package db implement the following code:

// Mdb is global variable to MySQL database
var Mdb = MustConnectDB()

// MustConnectDB returns a pointer to the MySQL database or panics.
func MustConnectDB() *sql.DB {
    db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/tasks")
    if err != nil {
        fmt.Println("ERROR:", err)
        panic(err)
    }
    return db
}

In the file taksRoute.go (which I assume should be named taskRoute.go), I simply added the import statement for the db package. I also removed the sql.Open instructions and changed the name of the variable on which you call the Query and Prepare methods. This is the resulting file content.

import (
    ...
    db "../db"
    ...
)

func NewTask(c *gin.Context) {
	currentTime := time.Now()

	//var task []models.Task
	var usersBody models.Task
	c.BindJSON(&usersBody)
	// perform a db.Query insert
	insert, err := db.Mdb.Prepare("INSERT INTO task(title, description, date) VALUES(?, ?, ?)")
	// if there is an error inserting, handle it
	if err != nil {
		panic(err.Error())
	} else {
		insert.Exec(usersBody.TITLE, usersBody.DESCRIPTION, currentTime.Format("2006-01-02 15:04:05"))
		c.JSON(200, gin.H{
			"response": "Inserted",
		})
	}
	// be careful deferring Queries if you are using transactions
	defer insert.Close()
}

func GetTasks(c *gin.Context) {
	// Execute the query
	results, err := db.Mdb.Query("SELECT * FROM task ORDER BY id DESC")
	if err != nil {
		panic(err.Error()) // proper error handling instead of panic in your app
	}
	var task models.Task
	var tasks []models.Task
	for results.Next() {

		// for each row, scan the result into our tag composite object
		err = results.Scan(&task.ID, &task.TITLE, &task.DESCRIPTION, &task.DATE)
		if err != nil {
			panic(err.Error()) // proper error handling instead of panic in your app
		}
		// and then print out the tag's Name attribute
		//log.Println(task.ID, task.NAME)
		tasks = append(tasks, models.Task{task.ID, task.TITLE, task.DESCRIPTION, task.DATE})
	}

	c.JSON(200, gin.H{
		"response": tasks,
	})
}

Note: I didn’t test this code. I assume it’s correct.

[illud]

Ill check it out tonight at home and ill tell you if it works, thanks

[illud]

Trying to use mysql connection from other functions, but i think i solve it thanks a lot.

[kync (Ali Koyuncu)]

another solution to db management in the app, which is I use in all my projects is a context wrapper function:

 withDBContext(f func(db *DB) error {
       db.open()
       defer db.Close() // don't recommend to close and open every time db connections.
       f(db)
    })
withDBContext(func(db *Db) error){
    //run your queries
})

[illud]

Ill check it out when i get home. thanks.

[illud]

got this error
syntax error: unexpected ( after top level declaration

[illud]

i fixed it was like this var Mdb MustConnectDB() and i add the equal like this var Mdb = MustConnectDB() and it work. if you can edit your answer will be great for other people.

[Christophe_Meessen]

Sorry about this mistake. I corrected the code. Thanks.

[illud]

Thanks to you men.