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

[samgavinio]

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?

[samgavinio]

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

[ghost]

@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!

[sgon00]

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.

[stretchkennedy]

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.

[QianYu92]

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?

[g10guang]

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

[stretchkennedy]

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

[g10guang]

@stretchkennedy Thanks.😄

[Overover1400]

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

[bjm88]

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.

[cyberience]

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

[YudiTjhia]

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.

[spinales]

could you show me an example of how to implement it

[febrianrendak]

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

the link is dead, can someone tell me the correct link or just paste here the solution/answer for this issue?

[stretchkennedy]

@febrianrendak It's issue #1053 in this repo.