I have a database per user in my use case (I know it is not the best decision but is a project requirement). I would like to open one connection and change the database name for every query.
I could use db.Exec("use clientdatabase;"); to change the database before execute every query but if at the same time another query arrives or is executing could be problems because all the app are using the same db connection.
May be, I could use a map of connections per client/database with a map number of elements in the max and deleting old connections.
Even I could create a connection for every query, but it could be a waste of time if one client have several queries.
I have found the way to reuse the same connection with different database names. My solution is to use transactions and set the database name when the transaction has begun. The following code with an output shows an example:
//create transaction1 in nameDB = default
tx1 := DB.Begin()
var users1 []User
tx1.Find(&users1)
fmt.Println(users1[0].CreatedAt)
//create new transaction and change nameDB to seconddb
tx2 := DB.Begin()
tx2.Exec("use seconddb;")
var users2 []User
tx2.Find(&users2)
fmt.Println(users2[0].CreatedAt)
//close tx2 so, it teakes effect on DB
tx2.Commit()
//same query with transaction1 to show that we are in default bd
var users3 []User
tx1.Find(&users3)
fmt.Println(users3[0].CreatedAt)
tx1.Commit()
After that the output:
2018-05-25 12:25:12 +0200 CEST
2018-05-23 12:43:51 +0200 CEST
2018-05-25 12:25:12 +0200 CEST
As you can see, the first and the third outputs corresponds to "default" database with date 2018-05-25 12:25:12 +0200 CEST in t1. And the second corresponds to "seconddb" database in t2.