gorm multiple databases connection management

Question

I have a requirement where my application talks to different databases . How do i manage connections in the gorm. Is there any way gorm supports connection management for multiple database. or i need to create map which holds all database connections.

if val, ok := selector.issure_db[issuer]; ok {
    return val , nil;

} else {

    var dbo  *db.DB;

    selector.mu.Lock()

    dbo, err := db.NewDb(Config)

    if err != nil {
        boot.Logger(ctx).Fatal(err.Error())
    }

    selector.issure_db[issuer] = dbo;

    selector.mu.Unlock()

    return repo ,nil;
}

Is there is a better way to do this?

You should use a factory function to get DB, it'll have mutex and other stuff. — sonus21, Nov 05 '20 at 14:49
Not something I'm aware of you can build one as you have done with selector map. — sonus21, Nov 05 '20 at 15:20

Abdul Rehman K · Answer 1 · 2021-10-22T18:39:21.463

You can create a package called database and write an init function in the init.go file which can create a DB object to connect with database for each database you have. And you can use this db object everywhere in the application which would enable connection pooling as well.

init.go

var db *gorm.DB

func init() {
    var err error
    dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8&parseTime=True&loc=Local", dbUser, dbPassword, dbHost, dbPort, dbName)
    db, err = gorm.Open("mysql", dataSourceName)
    db.DB().SetConnMaxLifetime(10 * time.Second)
    db.DB().SetMaxIdleConns(10)

    //initialise other db objects here
}

users.go

func getFirstUser() (user User) {
    db.First(&user)
    return
}

PS> This solution would be efficient if you have to connect to 1 or 2 database. If you need to connect to multiple databases at the same time, you should be using dbresolver plugin.

Old Answer

You can write a separate function which returns current database connection object every time you call the function.

func getDBConnection(dbUser, dbPassword, dbHost, dbName string) (db *gorm.DB, err error) {
    dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8&parseTime=True&loc=Local", dbUser, dbPassword, dbHost, dbPort, dbName)
    db, err = gorm.Open("mysql", dataSourceName)
    db.DB().SetConnMaxLifetime(10 * time.Second)
    return
}

And call defer db.Close() everytime after you call the getDBConnection function.

func getFirstUser() (user User) {
    db, _ := getDBConnection()
    defer db.Close()
    db.First(&user)
    return
}

This way your connections will be closed every time after you have executed the query.

Are you sure this would work performance wise? Imagine trying to handle e.g 1000 simultaneous users and you open and close 1000 times the database connection. — Stefanos Chrs, Oct 05 '21 at 04:05
Yes, here `gorm.Open` doesn't open a new connection every time you call getDBConnection. In fact, `gorm.Open` internally uses database/sql package which handles connection pooling for you. — Abdul Rehman K, Oct 18 '21 at 16:28
From the documentation, in order for gorm to reuse the connection you need to pass an existing db connection https://gorm.io/docs/connecting_to_the_database.html#Existing-database-connection and not a DSN. How would it know in this case that it needs to keep it as a connection pool and not a simple open / close? — Stefanos Chrs, Oct 19 '21 at 04:26
Thanks for correcting. I have updated my answer which would just create one DB object and reuse the same DB object in the entire application and also enable connection pooling. — Abdul Rehman K, Oct 22 '21 at 18:45
Your code still doesn't make much sense mate, you have a globally defined db variable, everytime you would call init with the new connection string (missing in the function definition) if would overwrite the other connection getting you back to square 1. You could solve this with a map of db objects — Stefanos Chrs, Oct 28 '21 at 06:56

score 1 · Answer 2 · answered Sep 10 '21 at 16:57

You can use the dbresolver plugin for GORM. It manages multiple sources and replicas and maintains an underlying connection pool for the group. You can even map models in your app to the correct database using the config.

Example from the docs:

import (
  "gorm.io/gorm"
  "gorm.io/plugin/dbresolver"
  "gorm.io/driver/mysql"
)

db, err := gorm.Open(mysql.Open("db1_dsn"), &gorm.Config{})

db.Use(dbresolver.Register(dbresolver.Config{
  // use `db2` as sources, `db3`, `db4` as replicas
  Sources:  []gorm.Dialector{mysql.Open("db2_dsn")},
  Replicas: []gorm.Dialector{mysql.Open("db3_dsn"), mysql.Open("db4_dsn")},
  // sources/replicas load balancing policy
  Policy: dbresolver.RandomPolicy{},
}).Register(dbresolver.Config{
  // use `db1` as sources (DB's default connection), `db5` as replicas for `User`, `Address`
  Replicas: []gorm.Dialector{mysql.Open("db5_dsn")},
}, &User{}, &Address{}).Register(dbresolver.Config{
  // use `db6`, `db7` as sources, `db8` as replicas for `orders`, `Product`
  Sources:  []gorm.Dialector{mysql.Open("db6_dsn"), mysql.Open("db7_dsn")},
  Replicas: []gorm.Dialector{mysql.Open("db8_dsn")},
}, "orders", &Product{}, "secondary"))

gorm multiple databases connection management

2 Answers2