Support for an async `Database` in `Wallet<D>`

[garydevenay]

Describe the enhancement
The current storage backends don't have support for async operations. After speaking with @afilini briefly on Discord about this, it's clear that BDK 1.0/bdk_core should give us better support for that.

Since we are a few months away from the bdk_core release, I'm proposing to create an async capable SqliteDatabase (PooledSqliteDatabase?) that will allow us to access a pool of connections across threads and serve as a bridge until the 1.0 release.

Use case

• Allow our Wallet to be used across threads safely.
• Serve as a temporary implementation avenue for async applications

[LLFourn]

Hey @garydevenay. There's a lot of simplifications coming up for v1.0 in how this is done. There are two things you need to be able to store:

1. Raw transaction data. This includes full transactions and list of outputs at a Txid. Essentially an append only list of TxNode.
2. A list of compact transactions in the form (Txid, confirmation_height) and "checkpoints" (height, blockhash). The primary key for the transactions would be the txid while for the checkpoints the height would be the primary key.

You don't need to provide any indexes for these things. Being able to iterate over the records and slurp them all into memory is all that is required.

If you wanted to get started now I'd say at a low level API you'd need in bdk v1.0 would be something like the API of ChainGraph:

https://github.com/LLFourn/bdk_core_staging/blob/17e06985cf2a7d5f2f5a5520f1385a64b448ef59/bdk_core/src/chain_graph.rs#L16

but instead of inserting things into memory it would insert into a database. It wouldn't need to do the apply_update thing though or to check the consistency of what you are being told to insert.

One good reason not to get started now is that we are in the middle of adding "associated data" to transactions so you can store things like the position in the block and/or timestamp or even some application data along with the txid. For sqlite you're going to probably going to have to have a trait you can offer to support this so you know how to store this additional data (which may be application defined).

[garydevenay]

Hey @LLFourn, thanks for those notes.

Agree the v1.0 release will make this a lot easier. As that seems to be months away, my approach for this will be to implement the existing Database trait that uses a pool of Sqlite connections to allow Wallet to be passed between threads.

I will be taking the existing migrations, tables, and queries from the SqliteDatabase implementation that is currently included.

I understand that there may be better future opportunities to start that, but it's currently a large blocker for me at the moment.

Example method:

async fn insert_script_pubkey(&self, keychain: String, child: u32, script: &[u8]) -> Result<i64, bdk::Error> {
    let mut conn = self.pool.acquire().await?;
    let id = sqlx::query!(
        "INSERT INTO script_pubkeys (keychain, child, script) VALUES (?1, ?2, ?3)",
        keychain,
        child,
        script,
    ).execute(&mut conn).await?;

    Ok(id)
}

Edit (10/11/22): I'm also open to writing an implementation for this in bdk_core afterward. But going to focus on having something work in the currently released versions first.