Support for Multiple Connections and Built-in dbresolver & Sharding for ORM and SQL Modules

[mak-alex]

Description:
We’d like to request support for multiple database connections across both the ORM and SQL modules in Yokai. This feature is crucial for complex projects that rely on multiple database instances or require specific query routing to different connections. Built-in support for dbresolver and sharding in both modules would elevate Yokai, enabling it to efficiently handle high-load distributed systems and complex data architectures.

Requested Features:

1. Multiple Connections Support for ORM and SQL Modules:

• Enable configuration and usage of multiple database connections in both ORM and SQL modules.

• Allow seamless switching between connections within a single application context, so specific models or queries can utilize different databases as needed.

2. dbresolver Integration in Both Modules:

• Implement built-in dbresolver capabilities to manage automatic read-write splitting and load balancing among replicas.
• Ensure compatibility with both ORM and SQL modules to cover various use cases.

3. Sharding Support:

• Add sharding capabilities, allowing data to be distributed across multiple databases with both ORM and SQL modules.
• Provide flexible sharding logic, customizable for different project needs.

Why This is Needed:
Many complex projects, particularly those that are data-intensive, require multiple database connections and dynamic query routing. Built-in dbresolver and sharding functionality would make Yokai’s ORM and SQL modules more powerful, ensuring better scalability, reduced latency, and improved load handling.

Suggested Implementation:
Consider implementing these features as part of the core ORM and SQL modules or as an optional extension that seamlessly integrates into Yokai’s ecosystem.

[jimcru21]

When it will supported the multiple database config?

[ekkinox]

Hello, sorry for the delay.

We plan to support it soon, priority has been put recently on the MCP instrumentation.

Meanwhile, this can easily be achieved by registering a struct constructor (fx.Provide) that has a sql.DB as a field, and in this constructor you create a custom connection.

This way you can make cohabitate 2 (or more) connections (the sql.DB from the Yokai SQL module and the custom one wrapped in your struct), and you can inject them independently where needed.

Same applies for the ORM.

As soon as the module offers multiple connections out of the box, I'll ping you here 👍

[ekkinox]

Hello 👋

FYI, we just released the version 1.4 of the SQL module.

This allows devs to configure and access several database connections (primary and auxiliaries), so they can:

• configure master / replica connections and handle queries splitting directly from the code
• or even configure connection to completely different system (useful for cross system migrations for ex)

=> I think this will solve the bigger part of your needs @mak-alex, but you'll need to handle those explicitly in your code.

Also, no breaking changes, primary sql.DB is still accessible like before, so you can safely bump the module version and start to add auxiliary connections.

If you still require a DBResolver mechanism (for automatic traffic split / sharing like in GORM), I suggest we close this issue and open a new dedicated one for this.

Let me know.