Multiple connection support

[lafriks]

It would be nice to have support to provide multiple connections with role write or read-only so that it would possible to use for example with PostgreSQL cluster that has single master and multiple read only nodes. Rel would then send write operations to master and select operations to one of slaves

[Fs02]

Rel would then send write operations to master and select operations to one of slaves

In addition to this, it'll be good if REL supports query option to allow reading from master

[Fs02]

my current idea for this is to create a new adapter that can wraps any of existing adapters. the advantage of using adapter is it should be easy to implement, and this idea can be expanded further in case we need to create some kind of db router (different table have specific db).

example api:

• primaryreplica.New(primary Adapter, replicas ...Adapter)

example usage:

adapter := primaryreplica.New(
    mysql.Open("root@(primary:3306)/rel_test"),
    mysql.Open("root@(replica1:3306)/rel_test"),
    mysql.Open("root@(replica2:3306)/rel_test"),
)
defer adapter.Close()

// initialize REL's repo.
repo := rel.New(adapter)
repo.Ping(context.TODO())

example future improvement with dbrouter:

// dbrouter.New(map[string]Adapter sources, map[string]string routes, defaultRoute string)
adapter := dbrouter.New(
  map[string]Adapter{
    "main": primaryreplica.New(....), // primary replica setup
    "products": mysq.Open(...), // regular db setup
  },
  map[string]string{"products": "products"},
  "main",
)

I'm still not sure with the package name, other option maybe masterreplica, sourcereplica, masterstandby (https://en.wikipedia.org/wiki/Master/slave_(technology)) 🤔

[lafriks]

But how would you specify that selects (except for update) go to one set of connection and inserts, updates, deletes and selects with for update to other?

[Fs02]

because the adapter wrapper also implements Adapter (https://github.com/go-rel/rel/blob/master/adapter.go), we can just add logics to select which original adapter to use?

• Ping - ping all adapters
• Aggregate - use replica by default
• Query - use replica by default
• Insert - use primary
• InsertAll - use primary
• Update - use primary
• Delete - use primary
• Exec - parse query to decide if not specified
• Begin - use primary (return primary adapter)
• Commit - use primary
• Rollback - use primary
• Apply - use primary

[Fs02]

do you have another idea?

[lafriks]

if Query/Aggregate is run in transaction or Query has rel.ForUpdate it must also use primary

[Fs02]

Right, should be easy to add that logics
And from what I know that query have no effect outside transaction anyway 🤔

[lafriks]

I mean if I start transaction and do insert/update etc any SELECTS that are done within that transaction should be done within same transaction using same connection

[Fs02]

yes, correct, that exactly what will happen since Begin method in Adapter is actually returns another adapter with transaction connection, so it should be covered 👍

[Fs02]

still wip but I've created poc for idea described above https://github.com/go-rel/primaryreplica

[lafriks]

Shouldn't it also check context in here to see if in transaction and return primary if it is

[Fs02]

nice observation 👍

we should not need to check context in adapter, because it's taken care by repository. the flow is roughly like this:

• when transaction is started, the adapter will return writer adapter from Begin function.
• returned transaction will be wrapped to a context that will be passed to transaction block.
• before running any operation, every method in repository struct will first call fetchContext method, which will return appropriate adapter. (example on Find)
• from there, everything that happens inside transaction block will use writer adapter only, and there's no communication to primary adapter.