英文:
Schema exchange support Go GORM
问题
我正在编写一个REST API，在其中我需要动态部署到多个模式的连接。
例如：我有两个模式，根据尝试使用数据的用户来进行更改。
想法是有许多其他的模式，每个用户一个。
我看到了这个问题，但是示例使用的是静态，我需要找到一种动态更改的方法。我将把用户模式放在JWT令牌中。
我的项目：Panda-API
有什么建议吗？
英文:
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?
答案1
得分: 1
选项1
你可以更改database.GetConnection()方法，使其接收用户名并直接连接到数据库，而无需更改所有的服务和模型。你将用户存储在security_middleware.go文件中的gin.Context对象中，因此你可以从那里获取它，并将其传递给服务，以便它们获取相应的数据库连接。
但是，为了实现这一点，你必须移除你存储DB对象的单例模式，并创建一个DB对象池，可以使用map[string]*DB来实现，而不是在services包中缓存DB对象，你可以在database包中缓存所有用户-DB对象。
你的database/database.go文件将如下所示：
// 添加sync导入以处理对缓存的并发访问
import "sync"
// ...现有的代码
// DB对象缓存
type DBs struct {
Cache map[string]*gorm.DB
sync.RWMutex
}
var dbs *DBs
// 初始化缓存
func init() {
dbs = DBs{
Cache: make(map[string]*gorm.DB),
}
}
func GetConnection(username string) *gorm.DB {
// 尝试从缓存中获取连接
dbs.RLock()
if db, ok := dbs.Cache[username]; ok {
dbs.RUnlock()
return db
}
// 在这里动态确定DB_NAME，基于用户名...
// ...
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)
}
// 打开连接的所有输出都记录日志（SQL）
db.LogMode(GetENVLogMode())
// 设置最大空闲连接数
db.DB().SetMaxIdleConns(DB_MAX_CONNECTION)
db.DB().SetMaxOpenConns(DB_MAX_CONNECTION)
DropTablesIfExists(db)
AutoMigrate(db)
AutoPopulate(db)
AddForeignKeys(db)
// 将连接保存到缓存中
dbs.Lock()
dbs.Cache[username] = db
dbs.Unlock()
return db
}
// ...以此类推
然后删除`services/services.go`文件，因为它将变得无用。并且将你的服务方法更改为接收用户名作为参数，并且每次调用时使用`Con := database.GetConnection(username)`。
希望这给你提供了一个可能的解决方案的思路。当然，可能还有其他选项，但这是我现在能想到的。
我认为这种方法的问题在于，你将为系统中的每个用户打开一个连接（和一个gorm.DB对象），不确定你期望有多少用户，但这可能是一个问题。
选项2
--------
另一种解决方案是对服务进行相同的更改，使其在所有方法中接收用户作为参数，但是不获取新的连接，而是将用户名/数据库名称设置为自定义模型属性，你可以使用该属性来实现自己的`Model.TableName()`方法，该方法使用该属性返回`schema.table`格式。
因此，你需要更改你的模型，添加一个带有setter的私有属性，例如：
```go
type Person struct {
schemaName string
// ...现有属性。
}
func (p *Person) SetUser(u string) string {
// 从用户名中确定模式名称
//...
p.schemaName = schema
}
func (p *Person) TableName() string {
return p.schemaName + ".persons"
}
然后，在你的服务中，每次创建新的模型实例时都设置用户：
```go
func GetPeople(pag helpers.Pagination, q url.Values, username string) models.People {
var people models.People
(&people).SetUser(username)
db := Con
// ...以此类推
这是我现在能想到的两种可能的解决方案。可能还有更多更好的解决方案，但希望这些能有所帮助。
<details>
<summary>英文:</summary>
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.
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论