SQLITE ATTACH db command fails with sqlite::memory: URI

[mikechambers]

Looking at the docs at:
https://docs.rs/sqlx/0.4.0-beta.1/sqlx/sqlite/struct.SqliteConnectOptions.html

The docs specify to use "sqlite::memory:" to create an in-memory database. When using this syntax, doing an ATTACH command silently fails.

    let mut db = match SqliteConnection::connect("sqlite::memory:").await {
        Ok(e) => e,
        Err(e) => panic!("{:?}", e),
    };

    let q = match sqlx::query("ATTACH DATABASE '/Users/mesh/tmp/manifest.sqlite3' as 'tmpDb'").execute(&mut db).await {
        Ok(e) => e,
        Err(e) => panic!("FAIL: {:?}", e),
    };

    let q = match sqlx::query("CREATE TABLE DestinyInventoryItemDefinition AS SELECT * FROM tmpDb.DestinyInventoryItemDefinition").execute(&mut db).await {
        Ok(e) => e,
        Err(e) => panic!("FAIL: {:?}", e),
    };

with the following error:

thread 'main' panicked at 'FAIL: Database(SqliteError { code: 1, message: "no such table: tmpDb.DestinyInventoryItemDefinition" })', src/main.rs:96:19

Looking at the sqlite docs for in-memory databases at:
https://www.sqlite.org/inmemorydb.html

It mentions lists "file::memory:" as a string to create an in-memory database. Using this string above, and the ATTACH command works. ("sqlite:file::memory:") also works.

Not sure if this is a doc bug, or some other issue with SQLX, but I wanted to share here in case it was a bug. For the memory section of the docs, it might be worth link to : https://www.sqlite.org/inmemorydb.html.

[abonander]

sqlite::memory is our own URI format that we treat specially when parsing: https://github.com/launchbadge/sqlx/blob/master/sqlx-core/src/sqlite/options/parse.rs#L28-L33

SQLite itself expects the file: protocol for URIs which we rewrite the connection string to use.

It's not a stretch to think that the former might work with ATTACH but it's definitely not intended to. I'm not sure where we should document this, if anywhere.

Making it "just work" would require rewriting the query on the fly which is something we don't want to do, but we could provide a helper method. Maybe something on SqliteOptions to have it automatically attach extra databases on open.

[abonander]

Hang on, I thought you were trying to do ATTACH ':sqlite::memory:' as '...' which shouldn't be expected to work. The code you posted should actually work though. That's weird.

[mikechambers]

Yeah, it only worked using file:memory.

[abonander]

To be clear, you start by opening an in-memory database and then try to ATTACH a database on the filesystem, correct?

[mikechambers]

Yes, here is what im doing:

1. Create an in memory database
2. Attach a file based database
3. Copy some tables from file to in memory
4. Detach the file based DB from the in memory