Hacker News Reader

Comment by fizx | Hacker News Reader

Effectively, this is a pretty cool way to get an OSS version of something like https://www.snowflake.com/guides/public-data. Also something like a lighter version of https://datasette.io/.

The thing it's kinda missing for me is the ability to compose multiple SQLite databases, possibly provided by different domains.

It'd be nice to join together different public datasets. In a weird personal example, if Strava exposed SQLite, I'd love to do a join to weather.com and see when the last time I biked in the rain was.

It'd be cool if one half of some table was at foo.com and I could add a few rows to it on my bar.com domain, and then the combined dataset was queryable as a single unit.

View on HN

I've done some thinking about joining datasets together.

Datasette grew a `--crossdb` option a while back, which means that if you attach multiple SQLite files to the same Datasette instance you can run joins across them: https://docs.datasette.io/en/stable/sql_queries.html#cross-d...

So one option is to download the database you want to join against and run the joins locally. Datasette encourages making the raw SQLite database file available, so if it's less than about 100MB this may be a good way to do it.

If you're willing to do the joins in your client-side code, Datasette's default JSON API can help. You can write an application (including a client-side JavaScript application) which fetches and combines data from multiple different Datasette JSON instances by hitting their APIs.

My last idea is the most out-of-left-field: since Datasette lets you define custom SQL functions using Python code, it would be feasible to create a Python function which itself makes a query via the JSON API against another Datasette instance! You could then use that to simulate joins in SQL queries that you run against a single Datasette instance.

I've not built a prototype of this yet, and to be honest I think combining data fetched from multiple JSON APIs (which is possible today) will provide just-as-good results, but it's an interesting potential option.

Reply on news.ycombinator.com