go-gorm / go-gorm/dbresolver

[Feature Request] Support table creation/alteration operations in resolver configuration

Open
#151 0 comments 1 reaction 1 assignee Claimed by @jinzhu View on GitHub
Dominant language
Go
Stars
538
Forks
69
PR merge metrics
No merged PRs in 30d

Description

**Description**
Currently, the table name extraction in dbresolver only matches SELECT, UPDATE, MERGE INTO, and INSERT operations to determine which database to use. However, in some scenarios, we also need to route DDL operations (CREATE TABLE, ALTER TABLE) to specific databases based on the resolver configuration.

**Current Behavior**
The current regex pattern in `utils.go` only matches:
- SELECT FROM
- UPDATE
- MERGE INTO
- INSERT INTO

```go
var fromTableRegexp = regexp.MustCompile("(?i)(?:FROM|UPDATE|MERGE INTO|INSERT [a-z ]*INTO) ['`\"]?([a-zA-Z0-9_]+)([ '`\",)]|$)")
```
These operations will be routed to the configured database based on the table name. However, DDL operations like CREATE TABLE and ALTER TABLE are not included in this pattern, which means they will always be executed on the primary database.

Expected Behavior The resolver should also support routing DDL operations to specific databases based on the configuration. This would be useful in scenarios such as:

- Creating tables in specific databases during initialization
- Performing schema migrations on specific databases
- Managing table structures in a sharded database setup
Proposed Solution Extend the regex pattern to include DDL operations:

```
var fromTableRegexp = regexp.MustCompile("(?i)(?:FROM|UPDATE|MERGE INTO|INSERT [a-z ]*INTO|CREATE TABLE|ALTER TABLE) ['`\"]?([a-zA-Z0-9_]+)([ '`\",)]|$)")
```

Use Case Example
```
dbresolver.Register(dbresolver.Config{
Replicas: []gorm.Dialector{replica1},
}, "users_*")

// This DDL should be executed on the configured database, not the primary
db.Exec("CREATE TABLE users_202401 (id int, name varchar(255))")
```

It is best to support configuration of `getTableFromRawSQL` function, which can provide more flexibility, or support a configuration callback function inside `getResolver` function

Additional Context This feature would be particularly useful in scenarios involving:

- Database sharding
- Table partitioning
- Multi-tenant applications
- Database migration tools
Let me know if you need any clarification or have questions about this feature request.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.