How To have multiple database connections

[ace3]

Hi, i have a question, whats the best way, to be able to have multiple database connection pools, so i can consume it from the controllers.

as from primary one, we can access it from &ctx.db

thanks

[jondot]

Hi @ace3 do you need multiple databases as in "one per tenant/customer" (thousands!) or multiple databases as in say "one analytics database, one app database" (a few, 2-3 different connected databases)?

[ace3]

hi @jondot , i need a multiple databases.

in my case, i'm connecting to primary database ( app database ) , and another connection to slave / 2nd database. ( for historical transaction )

thanks

[jondot]

@kaplanelad I think we can prepare an example for 2 connections. something like this:

1. Add connection URL in the new settings config section for the second database
2. Connect to the new database in after_routes, and add a Layer to expose the second connection
3. Because SeaORM requires a db connection every time -- we actually have it easy. The developer pulls the second connection from the router through extractor, and passes it as a &db parameter

I think we only need to verify that (2) a "connect to database" utility function is exposed for the developer to use when ever they want

@kaplanelad more thoughts, something i missed maybe?

[kaplanelad]

@jondot, Just to clarify, are you suggesting using extensions layer in the after_route ?

support for multiple connections, how should we seamlessly integrate it with our migration generator dangerously_truncate and more? Is there a need to extend support for it as well?

Or is it the case that the second connection won't be supporting these features?

[ace3]

for my case, actually, i don't mind if the dangerously_truncate feature is not available for the 2nd/3rd connection.

for the number 2, ( adding layer to after_routes ) , i think i can try it, since there is already an example for that case.

Still need some info on the extractor thing.
in order to do that, need to change the base loco code ? or can be done with already exist func ?

[jondot]

Yes I think @ace3 is correct, since this is "just" a second connection, we can use it only in "routing time", meaning routers get access to it which means Loco does not manage it (no migrations, reset, etc.), it just uses it as a live DB.

Lets start a story, thinking out loud

The solution should be something very similar to this:

1. Adding as an extension: https://github.com/loco-rs/loco/blob/master/examples/llm-candle-inference/src/app.rs#L41-L42 (instead of model, a pg pool). All the "preparation code" for getting a pg db URL, etc. can be done just before this step.
2. Consuming it in a router, like this: https://github.com/loco-rs/loco/blob/master/examples/llm-candle-inference/src/controllers/home.rs#L13 with Extension and the type that was encoded.

In terms of Loco, @kaplanelad it would be nice to add this as a PgPoolExtension of sorts, because it should be cheap for us to give users and we already use most of the types (so no new dependency). It will target specifically use cases of a "second DB":

1. admin db
2. analytics / reports db
3. master/slave
4. audit db
   etc.

What happens when there's a 3rd DB?

If this happens and is a user pain, we can always create a MultiPgPoolExtension where we encode multiple DBs under a user-readable key, something like

MultiPgPool{ 
  databases: { 
     [key] : PgPool
  }
}

of course all access needs to be atomic, etc.

Suggested solution

Create a MultiPgPool extension,

takes a configuration struct from YAML:

settings:   # this is the "custom user settings" in Loco
  multi_pgpool:  # this is a strongly typed Serde based configuration for MultiPgPool
     database1:
         url: ...
         <other poool params
     reports_db:
        url: ...

and then, in app hook

after_routes() {
    pgconfig = <get config, serde as MultiPgPoolConfig>
    extension = create_pgpool_extension(pgconfig) // connects, sets up, returns a "live" MultiPgPool object
   layer(... ) // add the layer
}

in controllers

fn foo(ExtractMultiPgPool(mgp) ){
  let db = mgp.get("db_name");
  users::find(&db, ...)
}