模式交换支持Go GORM

Question

I'm writing a REST API in which I need to dynamically deploy connections to multiple schemas.

Example: I have two schemas in which I need to change depending on which user is trying to consume the data.

The idea is to have many other schemes, one for each user.

I saw this issue but the example used is static needed to find a way to dynamically change. I'll put the user schema in the JWT token.

My project: Panda-API

Any suggestion?

Answer 1

OPTION 1

You can change the database.GetConnection() method to receive the username and connect directly to the database without having to change all services and models. You're storing the user in the gin.Context object on security_middleware.go, so you can get it from there on the controllers and pass it to the services so they get the corresponding DB connection.

But for this, you have to remove the Singleton pattern you have to store the DB object and create a pool of DB objects, maybe in a map[string]*DB and instead of caching the DB object in the services package, you cache all user-DB objects in the database package.

Your database/database.go file will look something like:

// Add sync import to handle concurrent access to the cache
import "sync"

// ... existent code

// DB objects cache
type DBs struct {
    Cache map[string]*gorm.DB
    sync.RWMutex
}

var dbs *DBs

// Init cache
func init() {
    dbs = DBs{
        Cache: make(map[string]*gorm.DB)
    }
}

func GetConnection(username string) *gorm.DB {  
    // Try to get connection from the cache
    dbs.RLock()
    if db, ok := dbs.Cache[username]; ok {
        dbs.RUnlock()
        return db
    }

    // Figure out DB_NAME dynamically here, based on username...
    //...
    dbName := figuredOutDB_NAME    

    db, err := gorm.Open(DB_DATABASE, "host=" + DB_HOST + " user=" + DB_USER + " dbname=" + dbName + " sslmode=" + DB_SSL_MODE + " password=" + DB_PASSWORD)

    if err != nil {
        panic(err)
    }

    //Ativa log de todas as saidas da conexão (SQL)
    db.LogMode(GetENVLogMode())
    //Seta o maximo de conexões
    db.DB().SetMaxIdleConns(DB_MAX_CONNECTION)
    db.DB().SetMaxOpenConns(DB_MAX_CONNECTION)

    DropTablesIfExists(db)
    AutoMigrate(db)
    AutoPopulate(db)
    AddForeignKeys(db)

    // Save connection to cache
    dbs.Lock()
    dbs.Cache[username] = db
    dbs.Unlock()

    return db
}

// ... and so on

Then remove the services/services.go file as it would be useless. And change your services methods to receive the username as a param and instead of using the Con variable, call Con := database.GetConnection(username) every time.

I hope that gives you an idea of a possible solution. Of course there may be other options, but that's what i can think on right now.

The problem i see with this method is that you'll have one connection open (and a gorm.DB object) for each user in the system, not sure how many users you're expecting, but it can be a problem.

OPTION 2

Another solution is to follow the same changes on the services so they receive the user as a param on all methods, but instead of getting a new connection, set the username/db name to a custom model property that you can use to implement your own Model.TableName() method that uses that property to return the schema.table format.

So you change your models to have a private property with a setter, like:

type Person struct {
   schemaName string

   // ... existent properties.
}

func (p *Person) SetUser(u string) string {
    // Figure out the schema name from the username
    //...

    p.schemaName = schema
}

func (p *Person) TableName() string {
    return p.schemaName + ".persons"
}

Then, on your services you set the user every time you create a new model instance:

func GetPeople(pag helpers.Pagination, q url.Values, username string) models.People {

    var people models.People
    (&people).SetUser(username)

    db := Con

    // ... and so on

These are 2 possible solutions i can think on now. There may be more and better, but hope that helps.