Observable + bean-count

Two of my favorite tools lately are Observable (observablehq.com) (a long time favorite for quickly prototyping javascript and creating interactive visualizations) and bean-count (a more recent addition for doing “hobbyist double entry accounting” (a special form of “fun”)).

fava is a great open source web UI for bean-count that gives great default reports, and lets you submit custom queries. However, part of the reason bean-count is so cool is that you have this super rich data that I want to experiment rendering in all kinds of ways, and also combine with other data like my predicted future spending etc.

Naturally, I wanted to combine my two favorite tools.

Localhost & CORS

Fava has a nice “query” API endpoint, and I’m usually running Fava, so I was hoping I could just hit their endpoint. The downside is that it returns its data in HTML format. I thought about trying to parse the HTML to get the data out (a very reasonable, fixed amount of time), but decided I’d try to make a little web-server in Rust to serve my queries instead (a maybe less reasonable, more “fun” way).

First I need a web server that I can query from Observable. I started out with the exact example code from the warp docs:

use warp::Filter;

#[tokio::main]
async fn main() {
    // GET /hello/warp => 200 OK with body "Hello, warp!"
    let hello = warp::path!("hello" / String)
        .map(|name| format!("Hello, {}!", name));

    warp::serve(hello)
        .run(([127, 0, 0, 1], 3030))
        .await;
}

I then added a block to my Observable notebook:

(await fetch('http://localhost:3030/hello/there')).text()
TypeError: Failed to fetch

Oh no! It failed. It’s likely due to CORS, as detailed in the soFetch notebook, so I’ll have to figure out how to allow cross-origin requests on my simple server.

We can add some CORS handling like this:

    let cors = warp::cors()
        .allow_origin("https://pcarleton.static.observableusercontent.com")
        .allow_methods(vec!["GET"]);

    // GET /hello/warp => 200 OK with body "Hello, warp!"
    let hello = warp::path!("hello" / String)
        .map(|name| format!("Hello, {}!", name))
        .with(cors);

And voila!

(await fetch('http://localhost:3030/hello/there')).text()
"Hello, there!"

Querying

Now we just need to access bean query.

To do that, I’ll use a crate called duct which has some convenience functions for calling other commands:

fn execute_query(query: &String) -> std::io::Result<String> {
    cmd!("bean-query", "-f", "csv", BEAN_PATH, query).read()
}

Then we just need to add some URI decoding so we can accept the query through the URL:

fn wrap_execute(query: &String) -> String {
    let decoded = match urlencoding::decode(query) {
        Ok(s) => s,
        Err(_) => return String::from("error decoding query")
    };
    
    match execute_query(&decoded) {
        Ok(s) => s,
        Err(e) => e.to_string()
    }
}


#[tokio::main]
async fn main() {
    let cors = warp::cors()
        .allow_origin("https://pcarleton.static.observableusercontent.com")
        .allow_methods(vec!["GET"]);

    // GET /query/$QUERY_STRING
    let query = warp::path!("query" / String)
        # this is the new part
        .map(|q| wrap_execute(&q))
        .with(cors);

    warp::serve(query)
        .run(([127, 0, 0, 1], 3030))
        .await;
}

Then we do some encodeURI in our notebook and we’re off to the races:

query = `SELECT
    account, CONVERT(sum(position), 'USD') as position
FROM
    date > 2020-01-01 AND date < 2022-01-01
group by account`

(await fetch(`http://localhost:3030/query/${encodeURI(query)}`)).text()

This still returns in CSV format, where I still want to add a JSON conversion layer.

Other notes

I found this StackOverflow post about debugging CORS issues useful, the tl;dr is if set the Origin header like curl -H "Origin: myorigin.com" it should simulate a cross-origin situation.

I also learned that I needed to use localhost and not 127.0.0.1 to query with fetch. I didn’t dig too deeply into why that is.

If this was useful to you, let me know on Twitter!