Gorm: [Question] Do you need to close the db connection every request?

Created on 9 Apr 2017 · 15Comments · Source: go-gorm/gorm

Hi 👋, please label this github issue as a question.

Just recently got into labstack/echo with gorm as the ORM and I've noticed that the echo cookbooks out there generally show that you would clone a new connection and close it every request.

Looking at the gorm docs, there doesn't seem be anything specific about doing this and most of the stuff I've seen generally just open one persistent connection and pass it around.

Is there anything note worthy in doing either with gorm?

Source

samgavinio

👍2

Most helpful comment

For anyone who comes across this issue later on, the correct way to deal with database connections in a long-running application is something like

func main() {
  db, err := gorm.Open("sqlite3", "example.db")
  if err != nil {
    log.Fatal(err)
  }
  defer db.Close()

  // if your program has a main loop, start it here
  // i.e. for a http server: 
  // http.ListenAndServe(":3000", func (w http.ResponseWriter, r *http.Request) {
  //   db.Find(...)
  // })
}

Behind the scenes, gorm.Open() uses https://golang.org/pkg/database/sql/, which is threadsafe and handles connection pooling for you. Don't call gorm.Open() every request. Call it once when setting up your application, and make sure you use defer db.Close() so connections are cleanly ended when your program exits.

stretchkennedy on 27 Sep 2017

👍71 ❤29 🚀6

All 15 comments

Found a sufficiently good answer here: https://github.com/jinzhu/gorm/issues/1053

samgavinio on 9 Apr 2017

@samgavinio Can you quickly summarize? Is it okay to Open a connection and use it everywhere else in the app and defer db.Close() it? Thanks!

ghost on 11 Apr 2017

👍3

I had the same question and after reading 5 related issues, I am still confused right now and don't know which way is the best practice. The official gorm doc just shows defer db.Close() way which is opening and closing a connection in every request. I think opening and closing DB connection takes a lot of time and resources, but it seems gorm recommends this way.

@samgavinio I am wondering what your final decision is? thanks.

sgon00 on 18 May 2017

👍3

For anyone who comes across this issue later on, the correct way to deal with database connections in a long-running application is something like

func main() {
  db, err := gorm.Open("sqlite3", "example.db")
  if err != nil {
    log.Fatal(err)
  }
  defer db.Close()

  // if your program has a main loop, start it here
  // i.e. for a http server: 
  // http.ListenAndServe(":3000", func (w http.ResponseWriter, r *http.Request) {
  //   db.Find(...)
  // })
}

stretchkennedy on 27 Sep 2017

👍71 ❤29 🚀6

But here: http://go-database-sql.org/retrieving.html it says if you don't Close a Query connection it would keep as open so you should defer rows.Close() it. How should we do here?

QianYu92 on 9 May 2018

@stretchkennedy OS wil automatically clean any socket connection after process exit. So I think defer db.Close() is not needed.

g10guang on 27 Oct 2018

👍1

@g10guang db.Close() can do a lot more than just cleaning up socket connections. For example, the go-sqlite3 driver calls sqlite3_close_v2, which does a lot of things including running fsync and getting rid of temporary journal and wal files.

Even if running cleanup code is currently unnecessary, it's generally a bad idea to leave it out, because the next version of a library could change the implementation.

stretchkennedy on 28 Oct 2018

👍3

@stretchkennedy Thanks.😄

g10guang on 2 Nov 2018

what will happen if we don't use "db.close" or " defer db.close"?(it's will close auto?)

Overover1400 on 10 Nov 2018

👀4

you should call db.close for DB's sake as well, it is still maintaining a connection and will eventually time it out if no keep alive activity, but not a good practice to rely on that.

bjm88 on 10 Nov 2018

👍5

@stretchkennedy OS wil automatically clean any socket connection after process exit. So I think defer db.Close() is not needed.

Prevent data corruption if there is pending writes, or other cache based activities related to your query, this will flush and pending activity, including possible index updates.

cyberience on 16 Sep 2019

it looks like when you only use single open connection and the multi concurrent requests happen, the code with BeginTransaction will lock the single open connection, so the later requests will be locked waiting until former request get finished. correct me if wrong.