Best practice for Database Open and Close connection

[srikanth c]

what is the best practice when we are using database\sql package for sql.Open and db.close(). I have scenario where i have to multiple function and each function is responsible to hit the database and fetch the data. In this case do we need write sql.open() and db.close() in each of those functions.

Please find the three file and rough layout of my code. If I’m using this approach. what would be the best possible way to follow with best practices.

1. main.go
   func main() {
   router := mux.NewRouter()
   controller := controllers.Controller{}router.HandleFunc("/protectedEndpoint", controller.GetStudents).Methods(“GET”)
   router.HandleFunc("/signup", controller.GetBook).Methods(“GET”)
   router.HandleFunc("/login", controller.GetReports).Methods(“GET”)
   }
2. controller.go

func GetStudents(w http.ResponseWriter, r *http.request){
//fetch request param and call the repository method to fetch data from db
studentId := r.URL.Query().get("id)
repository.GetStudents(studentId)
}

func GetBook(w http.ResponseWriter, r *http.request){
//fetch request param and call the repository method to fetch data from db
bookId := r.URL.Query().get("id)
repository.GetBook(bookId)
}

func GetReports(w http.ResponseWriter, r *http.request){
//fetch request param and call the repository method to fetch data from db
studentId := r.URL.Query().get("id)
repository.GetReports(studentId)
}

3. repository.go

import database/sql

func GetStudents(studentId int){
db, err := sql.open( driverName, connectionString)
if err != nil {
log.panic(err)
}
defer db.close()
row, err := db.query(“select * from student where studentId = :0”, studentId)
if err != nil {
log.panic(err)
}
defer row.close()
//iterate through the row
}

func GetBook(bookId int){
db, err := sql.open( driverName, connectionString)
if err != nil {
log.panic(err)
}
defer db.close()
row, err := db.query(“select * from book where bookId = :0”, bookId)
if err != nil {
log.panic(err)
}
defer row.close()
//iterate through the row
}

func GetReports(studentId int){
db, err := sql.open( driverName, connectionString)
if err != nil {
log.panic(err)
}
defer db.close()
row, err := db.query(“select * from reports where studentId = :0”, studentId)
if err != nil {
log.panic(err)
}
defer row.close()
//iterate through the row

}

[Marcin Romaszewicz]

Behind the scenes, DB is actually a connection pool, not a single connection, which will open new connections when you need them.

In our services - which do a LOT of DB work, we only open a single sql.DB connection and share it among all our handlers. Whenever a handler does a db.Whatever, the db object will allocate a real DB connection when needed, so we only open and close the DB in the main function, and pass it down to all the users. You only really need multiple sql.DB's if you open the DB with different login arguments.

Generally, you want to do something like this wherever you open a sql.DB connection:

db, err := sql.Open()

if err != nil { do error stuff}

defer db.Close()

If you have a single DB configuration, I'd recommend only having a single global sql.DB that everything in your code shares.

--
You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/2c3ca567-0923-4f63-9b79-a1fd46df0d21%40googlegroups.com.