Best practice for Database Open and Close connection

Getting Help
1

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

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

}

2

It’s hard for us to answer without seeing your code. You probably don’t need to do that unless you’re, for example, connecting to different databases each time.

The last paragraph of the sql.Open documentation says:

The returned DB is safe for concurrent use by multiple goroutines and maintains its own pool of idle connections. Thus, the Open function should be called just once. It is rarely necessary to close a DB.

Show us your code and we can give a more concrete answer.

3

No, just open and close db connection in the main function -main.go- (or in a package and then call that function in the main.go file) so all the functions will execute after you are connected to the db. Defer will close the db connection once main func ended executing everything else, also, dont forget to use db.Ping() just like this:

// Connect to the database
db, err := sql.Open("driver-name", "database=test1")

// Check errors 
if err != nil {
	log.Fatal(err)
}

// Close connection
defer.Close() 

// Ping verifies a connection to the database is still alive, establishing a connection if necessary
db.Ping()
if err != nil {
    log.fatal(err)
}

If you don’t know what defer does, check:
https://gobyexample.com/defer

2 Likes
4

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

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

}

5

Do not open and close a sql.DB every time you make a query to it. The sql.DB type maintains a connection pool so that subsequent queries made with a sql.DB go faster because they might be executed with an existing connection. If you’re opening and closing a sql.DB every function call, then you’re creating and destroying a connection pool every time you enter one of these functions. You should keep the connection pool so that subsequent calls to GetStudents and/or GetBooks, etc. can benefit from that connection pool.

I recommend creating a Repo (or similar) struct in repository.go that looks something like this:

type Repo struct {
    db *sql.DB
}

func NewRepo(driverName, connectionString string) (*Repo, error) {
    db, err := sql.Open(driverName, connectionString)
    if err != nil {
        return nil, err
    }
    return &Repo{db: db}, nil
}

Then change your GetStudents, GetBook, etc. functions into member functions of the Repo type:

func (r Repo) GetStudents(studentId int){
    row, err := r.db.query(“select * from student where studentId = :0”, studentId)
    if err != nil {
        log.panic(err)
    }
    defer row.close()
    //iterate through the row
}
2 Likes
6

The best resource I found about database connectivity and Go is:
Ultimate Guide to Building Database-Intensive Apps with Go.
However, I’m having trouble downloading it now.

You can get it here:

1 Like
7

Thanks for the solution. But just wanted to know where we should be closing the connection. As we have NewRepo func to create connection.

8

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.