[Bug]: Jobs table gets dropped in when running in memory mode in a long running server

[IgnisDa]

What happened?

I have this code in my startup:

    let mut store = SharedSqliteStorage::new(":memory:");
    SqliteStorage::setup(store.pool()).await?;

After some time (around 30 hours) in my experience, I start getting this the above error (in the title). I was not able to screenshot it unfortunately.

Expected behavior

Jobs to run fine.

Steps to reproduce

Just the normal setup.

Minimal code example

Version

1.0.0-rc.x

Environment

• OS: Mac
• Rust version: 1.90
• Cargo version: 1.90

Relevant log output

Additional context

Please let me know what logs I can give to help you debug this issue.

[IgnisDa]

I had my local serving running on my laptop the entire night and just started getting this error: Failed to push task: error returned from database: (code: 1) no such table: Jobs. This is what I noticed on my production server too.

[IgnisDa]

RCA (LLM-assisted): this appears to be an in-memory SQLite lifecycle issue with pooled connections. :memory: databases exist only while at least one connection is open. SQLx PoolOptions::new() defaults to min_connections = 0, idle_timeout = 10m, max_lifetime = 30m. In SharedSqliteStorage::new, the pool is created via PoolOptions::new().connect_lazy(url) without overriding those defaults. Once the pool reaps all idle connections, the :memory: DB is destroyed; the next acquire opens a fresh empty DB, so Jobs no longer exists and push fails with no such table: Jobs.

Minimal repro (forces pool to drop all connections quickly):

use std::time::Duration;

use apalis::prelude::*;
use apalis_sqlite::SqliteStorage;
use sqlx::{pool::PoolOptions, Sqlite};

#[tokio::main]
async fn main() {
    let pool = PoolOptions::<Sqlite>::new()
        .idle_timeout(Duration::from_millis(200))
        .max_connections(1)
        .min_connections(0)
        .connect(":memory:")
        .await
        .unwrap();

    SqliteStorage::setup(&pool).await.unwrap();

    tokio::time::sleep(Duration::from_millis(600)).await;

    let mut backend = SqliteStorage::new(&pool);
    let res = backend.push(1u32).await;
    println!("push result: {res:?}");
}

Expected output:
push result: Err(PushError(Database(SqliteError { code: 1, message: "no such table: Jobs" })))

[geofmureithi]

We dealt with this issue before:
apalis-dev/apalis#163

I will add:

SharedSqliteStorage::new_with_pool(pool)

[IgnisDa]

Ah damn lol.

[geofmureithi]

By default it should no longer drop, also added a new method if you need more customization