Dealing with Lost Connections

[steve962]

I've been working with SeaORM 1.x for a while in a project, and am about to rework that project under SeaORM 2. One of the things I wanted to look at while I was reworking the code was more robust handling of lost connections, and I'm unclear on how this is handled inside SeaORM.

The issue I'm concerned with is that the project is an application that runs more or less 24/7 and is requesting data from several different databases constantly. It's using a DatabaseConnection established at application launch for each of these databases, but sometimes networking issues or server reboots cause a period of lost connections. The application ought to be able to handle that - it simply loses access to the data stream from that particular server for a time. But sometimes I find I need to restart the application to recover the lost connection. Unfortunately, it's really unclear what conditions cause this.

As part of the rework, I'd like to revise my connection handling to better resolve this kind of situation and to also handle the case where one or more of those databases are unavailable at application launch time. I was thinking along the lines of wrapping the DatabaseConnection with a small structure that also kept an atomic known state of the connection, and then if it's in a failed state, after a period of time it would close and release the old DatabaseConnection and attempt to create a new DatabaseConnection. All database requests would check the atomic known state first before attempting to use the connection, and if they fail with a connection error, would put the connection into it's failed state.

But as I was researching whether this was a good approach, I realized I'm not even sure how the connection is handled inside SeaORM itself and was wondering if there's a better solution. Anyone have feedback on this? Is there a best practice for dealing with lost connections?

[ryux1]

A DatabaseConnection is a handle to a SQLx connection pool, not a single long-lived socket. I would keep one pool per database and avoid the global failed flag: one failed query does not establish that the whole pool is unusable, and the flag introduces races between the task that marks it failed and tasks using healthy/new connections.

For SeaORM 2, start with the pool's built-in checks:

use sea_orm::{ConnectOptions, Database};
use std::time::Duration;

let mut opt = ConnectOptions::new(url);
opt.connect_lazy(true)
    .connect_timeout(Duration::from_secs(5))
    .acquire_timeout(Duration::from_secs(10))
    .idle_timeout(Duration::from_secs(5 * 60))
    .max_lifetime(Duration::from_secs(30 * 60))
    .test_before_acquire(true);

let db = Database::connect(opt).await?;

connect_lazy(true) lets application startup succeed while that database is unavailable; the first operations will still return errors until it is reachable. test_before_acquire(true) is already the default in SeaORM 2: an idle connection is pinged before checkout. If the check fails, SQLx discards it and tries another connection, opening a new one when necessary.

If the extra round trip on every checkout matters, SeaORM 2 also has test_before_acquire_if_idle_for(Duration::from_secs(...)), which only pings connections that have been idle long enough to plausibly have been dropped by a server, proxy, or firewall. Use that instead of test_before_acquire(true); the helper disables the every-checkout ping itself.

You still need to handle the query that observes the outage. At the worker/request boundary, classify the DbErr, report that database as temporarily unavailable, and retry idempotent work with bounded exponential backoff plus jitter. Do not blindly retry writes or transactions: if the connection drops around commit, the outcome may be unknown and repeating the operation can duplicate it. Give each database its own retry/backoff state so one outage does not stop the others.

Normally you should not close and rebuild the pool for a server reboot or stale idle socket. SQLx is designed to replace failed pooled connections. If it still only recovers after a process restart with the default acquire check enabled, log the exact DbErr/source chain and database backend. In particular, check that the application has not called close() on the pool (a closed pool does not reopen), and distinguish DNS/TLS/authentication failures or application transaction state from an ordinary disconnected socket.