Support for lazy database connections

[Plebshot]

Motivation

When starting a service, a database could be temporarily unavailable. Some services may choose to continue running and deal with that when a query is made instead. This makes the service overall more robust as it doesn't have to be repeatedly restarted until the database becomes available again. It also simplifies setups with docker compose.

Proposed Solutions

Connecting lazily to the database can already be done in sqlx via Pool::connect_lazy(). A new function Database::connect_lazy() could be added that just swaps out the underlying Pool::connect() call. Alternatively, this could be toggled via a boolean in ConnectOptions.

Current Workarounds

I don't think there is a way to achieve this behavior at the current moment unless there is a way to create a DatabaseConnection from an underlying sqlx::Pool with public functions.

[billy1624]

I imagine the Database::connect_lazy() method is a shorthand of min_connections set to zero.

• Create Database connection lazily #1485

[Plebshot]

Ah I apologize, I looked through the issues but not the discussions. If that's the case then that's a workaround. As mentioned in the discussion, it would be helpful if it's explicitly documented somewhere.

[Plebshot]

Hm. It seems like min_connections doesn't prevent the function from failing if the connection is not available at the current time.

This would make sense as sqlx::PoolOptions::connect_with() calls inner.acquire() either way. As the comment also states:

/// The total number of connections opened is <code>min(1, [min_connections][Self::min_connections])</code>.

[tyt2y3]

We can merge into #1523

[Plebshot]

Thanks for looking over this, but I'm not entirely sure how #1523 is supposed to relate to this issue. Instead of retrying until success, connecting lazily to the database allows one to initialize everything and go into a state of "normal operation" even when the database is unavailable. Connecting lazily can never fail as the first connection is only acquired with the first request, meaning initialization errors can be treated the same as any other errors during operation.

[tyt2y3]

It sounds like your scenario is better handled at the framework level. Something like a LazyCell or a more sophisticated resource manager.

[Plebshot]

I'm not sure about that. sqlx already has that feature after all. It would just be about wrapping it.
Something like LazyCell would require to add another level of error handling every time you try to interact with the database and add a lot of unnecessary boilerplate code.

This kind of behavior is also the default for other DB drivers like the MongoDB Rust driver, as you can create a Client objects without the database needing to be available.

[tyt2y3]

If wrapping sqlx's connect_lazy works for you, a PR would be welcomed.

[basro]

I also would like this feature but I don't feel confident writing the PR to implement the connect_lazy wrapper.

For anyone interested in a workaround you can achieve this today by creating a db connection with sqlx and then converting that to a sea-orm DatabaseConnection:

    let db_url = "your postgres database url here";

    // create a sqlx PgPool
    let pg_pool = PgPoolOptions::new()
        .acquire_timeout(Duration::from_secs(8))
        .max_connections(50)
        .min_connections(2)
        .max_lifetime(hours(2))
        .idle_timeout(minutes(10))
        .connect_lazy(db_url)?;

    // Convert that to a sea_orm DatabaseConnection with this line:
    let db_conn = sea_orm::SqlxPostgresConnector::from_sqlx_postgres_pool(pg_pool);