Gorm: Question on pooling/concurrency regarding recent documentation change, suggest clarification

[benguild]

Myself and others were sort of unclear on how to correctly handle database connection pooling following more recent documentation changes. On older versions of the documentation, this phrasing was included:

Gorm is goroutines friendly, so you can create a global variable to keep the connection and use it everywhere in your project.

GORM has since been updated, and this phrasing has been removed from the documentation, as has examples of using a global variable to store a single *gorm.DB object for the entire project at runtime.

I personally opted to open a *gorm.DB object for each HTTP request, and hope that these connections were recycled from the connection pool in the SQL driver. From examining the code, this seems like the correct approach, but I wasn't sure if GORM handled this for us. It looks like by using a global variable like before, GORM would share a single database connection for every single request to the application. Maybe there's something I'm missing, but can someone clarify?

Related: #246

[benguild]

@pjebs ...you might know the answer to this.

[benguild]

What I don't understand specifically is that, using the global variable model, HTTP requests aren't passed into the DB wrapper. So, for database transactions, it seems like the requests could hit via multiple connections? Or does a transaction object save up all of the transactions and somehow fire them on a single connection?

[benguild]

What's confusing in the comments in the documentation for GORM is:

Open initialize a new db connection, need to import driver first...

... It sounds like calling GORM DB's .Open method creates just 1 connection.

Then, the SQL driver 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.

So, do we call it once per request, once per project (if a single global DB variable hasn't been initialized yet)... or what?

[pjebs]

GORM really has nothing to do with the underlying sql.DB object. In fact I create it independently of GORM and pass it around _including_ to GORM when I want to use GORM for some stuff. NB: GORM's Open(...) can directly accept a sql.*DB object.

The advantage of that approach is that I can use the sql.*DB object elsewhere without GORM.
If you get GORM to create it for you (which the Open(...) command can also do), you have to access it using Gorm.DB.DB(). In this approach GORM always keeps a reference to the underlying sql.DB object - even if you no longer want to use GORM. In the other approach, the Garbage-Collector can clean out GORM when you stop using it but still use the sql.DB object independently.

Eitherway, there are no concurrency issues. Multiple goroutines can use it safely.

Now regarding best way to use it in a webserver app: https://github.com/go-sql-driver/mysql/issues/461

[benguild]

OK, got it. This could definitely be clarified in the docs... so, I'll leave the issue open. I’m hoping to try and go back and add whatever I can to the docs to make it better based on the questions I’ve had to ask or research.

[soldiershen]

@benguild @pjebs so is it right and safe to usr GORM to set a global connection(or set maxXXX) for all requests,rather than open&close connection for every request?

[benguild]

@soldiershen That's what the documentation implies, but I haven't really done any heavy testing personally.

[soldiershen]

@benguild I will test it.Is there a easy way to query live DB connection count on linux? I am not familiar with linux.Thanks

[pjebs]

Mysql command:

SHOW STATUS WHEREvariable_name= 'Threads_connected'

[leenux]

@pjebs
Hi,
My English is very poor, it is difficult to understand your words.so sorry:(
Can I get a short answer?
For example,Is it that need re-open the conn? db, err := gorm.Open(...) and defer close() on each request?
thank.

[pjebs]

No. Connection Pooling means you don't call defer close() on the db object.
You call the Open command once somewhere and keep the db object live in a global variable and keep using it everywhere.

You will need to set the SetConnMaxLifetime, SetMaxIdleConns and SetMaxOpenConns to appropriate values: https://github.com/go-sql-driver/mysql/issues/461