sqlite in-memory databases do not seem to work with connection pools

[lovasoa]

Hello,
I am working on SQLPage, that uses sqlx, and where users can configure their own database connections.

I noticed that when using a connection pool and an in-memory sqlite database, migrations are only applied on whatever connection is made initially, and new connections returned by the connection pool are to new different empty databases.
This is a nasty bug, because when you test the system initially, everything works, but only when the initial connection exceeds its idle_timeout, a new connection is successfully returned, which is also a valid connection, but to a completely different database.

A temporary fix is to use idle_timeout(None), but when I do that, I notice a leak of database connections under high load, and at some point all connections are exhausted and the system stays forever in a state where any try to acquire a new connection results in a timeout, even after the peak load has passed.

[mdegel]

I have experienced the same / a very similar issue (sqlx 0.7.2).

I have some code similar to this:

sqlx::any::install_default_drivers();
let pool = AnyPoolOptions::new()
    .max_connections(2)
    .connect("sqlite::memory:")
    .await?;
migrate!("db/migrations").run(&pool).await?;

This code runs without issues (no errors). Attempting to access the DB later yields errors like these for tables that should have been created during migration:

SqliteError { code: 1, message: "no such table: artifacts" }

For me, setting idle_timeout(None) on the pool (as described above) didn't have any impact, meaning the error stayed the same. What prevents the error for me, is explicitly disabling parallel connections, via .max_connections(1).

[mdegel]

Looking into it a bit further from earlier issues this seems to be an SQLite limitation: #362 (comment)

[kyrias]

That comment should not be relevant anymore because shared mode is supposed to be used by default, but there appears to be something strange going on when using AnyPool where even explicitly enabling the shared cache with sqlite::memory:?cache=shared doesn't work. Using a named in-memory database does appear to work though: sqlite:file:foo?mode=memory&cache=shared.

[hoxxep]

A potential workaround could be to set min_connections to a larger number, which shouldn't leak connections in the same way as idle_timeout. In my limited testing of this workaround, 3 appears to be enough connections.

// define in memory DB connection options
let sqlite_opts = SqliteConnectOptions::from_str(":memory:").unwrap();

// min_connections = 3 to prevent the DB from being wiped randomly
let pool = SqlitePoolOptions::new()
    .min_connections(3)
    .max_connections(10)  // default is 10
    .connect_with(sqlite_opts)
    .await?;

I don't know if this will fix all cases. I thought the pool could be dipping below min_connections, which may drop the in memory DB when it reaches 0 connections — I haven't looked at the code to verify any of this though.

Edit: Sadly it's not a complete fix and the issue still reoccurs, but slightly less frequently.

[filtsin]

I tried

    let pool = SqlitePoolOptions::new()
        .min_connections(1)
        .max_connections(1)
        .idle_timeout(None)
        .max_lifetime(None) // <-

Probably works, connection still alive after some time and tables are not disappeared in memory db.

[LecrisUT]

Could you try #3289? It addresses a very specific issue though, i.e. if you are linking to a pre-built sqlite package.