Opening "sqlite::memory:" is broken for SqlitePool

[therocode]

With sqlx 0.3.5 It is not possible to do
sqlx::SqlitePool::new("sqlite::memory:")

The connection seems like it works, but when using queries with this pool, sqlx panics with "Error: bad parameter or other API misuse".

I have drilled down the issue to the hack in url.rs line 82 in sqlx_core. That is the following snippet:

    /// Undo URL percent-encoding and return [authority]path[query]
    ///
    /// Mostly a hack to fix special-character handling for SQLite as its connection string is a
    /// file path and not _really_ a URL
    pub fn path_decoded(&self) -> Cow<str> {
        // omit scheme (e.g. `sqlite://`, `mysql://`)
        let mut url_str = &self.0.as_str()[self.0.scheme().len()..]
            .trim_start_matches(':')
            .trim_start_matches("//");

This turns "sqlite::memory:" into "memory:" which is not a valid sqlite file to open. This path is not hit at all when using a direct connection instead of a pool, so that's why this error doesn't show up on the tests since they don't use pools.

I can fix it locally by doing

        //the above hack breaks using sqlite::memory: since it turns it into just memory: so this hack undos that
        if *url_str == "memory:" {
            url_str = &":memory:";
        }

afterwards but that's an as ugly of a hack and I'm not happy to make a PR out of that. I feel there's a better fix but I am not knowledgable enough to know what it would be.

Note that you cannot workaround this by opening just ":memory:" instead of "sqlite::memory:" since the URL parser will panic saying that it's a relative URL with no base

The following application reproduces the error:

#[tokio::main]
async fn main() -> anyhow::Result<()>{
    
    //gives error: "relative URL without a base"
    //let pool = sqlx::SqlitePool::new(":memory:").await?;
    //gives error: "Error: bad parameter or other API misuse"
    let pool = sqlx::SqlitePool::new("sqlite::memory:").await?;


    sqlx::query(
        "CREATE TABLE IF NOT EXISTS tracks(
            id INTEGER PRIMARY KEY,
            track_num INTEGER NOT NULL,
            title TEXT NOT NULL,
            url TEXT);"
    )
    .execute(&pool)
    .await?;

    sqlx::query(
        "INSERT INTO tracks (track_num, title, url)
         VALUES ($1, $2, $3);",
    )
    .bind(&1i32)
    .bind(&"asdf")
    .bind(&"asdf")
    .execute(&pool)
    .await?;

    Ok(())
}

[abonander]

@mehcode I think we need SqlitePool::memory() and SqliteConnection::memory() constructors, thoughts?

[mehcode]

On master we now have SqliteConnectOptions and a dedicated connection string parser for sqlite. This specific issue should be fixed.

All sqlite::memory:, sqlite://:memory:, sqlite:, and sqlite:// should work to open an in-memory database.

---

@therocode I do have a question what you're doing with a pool around :memory:. Each connection will be a different database. We should probably be blocking or at least warning on this situation.

[therocode]

@mehcode oh I was not aware that each connection will be a different database...

I'm using pool becuase that's what I want my application to use when being run for real with a database file, but for manual testing and automatic tests, I use :memory: since it's more convenient, and I did not want to change my code to accomodate both pool and connection. Maybe I'll have to rethink my approach...

[mehcode]

@therocode It's unfortunately a SQLite limitation. One connection with :memory: is completely separate from another and it's not possible to share that internal memory.

Here is a work-around if this is just for tests. This will create a pool that will only ever produce the same SQLite connection (and thus the same data).

SqlitePool::builder()
  .max_size(1)
  .idle_timeout(None)
  .max_lifetime(None)
  .build("sqlite://")

[mehcode]

This is fixed on master