Hacker News Reader

I'm building out Trustfall[0] with most of the same goals in mind: query everything with a single composable syntax and fast performance, incrementally evolve the schema, and build up the types of objects it can understand and query into your own personal (or shared!) graph.

I recently gave a conference talk on how we use queries over Dockerfiles + project configuration + deployment info to triage bugs and prevent them from coming back at my workplace. An example query is "find Python projects whose pyproject.toml specifies Python 3.9 but which get deployed with a Dockerfile that installs Python 3.7 instead." Here's the talk link: https://www.hytradboi.com/2022/how-to-query-almost-everythin...

If you'd like to see Trustfall in action, I recently used it to build a Rust semver-checking tool[1] that's just a bunch of Trustfall queries like "find structs that used to be public but now aren't public anymore"[2] etc.

Trustfall definitely doesn't yet do everything I'd like it to (e.g. caching), and the Adapter API needs some more polish to improve ergonomics. But if you're interested in trying it out despite those caveats, I'd love to hear what you think!

[0]: https://github.com/obi1kenobi/trustfall

[1]: https://crates.io/crates/cargo-semver-checks

[2]: https://github.com/obi1kenobi/cargo-semver-check/blob/main/s... -- the query is in GraphQL syntax, you can copy-paste it into an editor to get syntax highlighting

You should look at Apache Calcite[0]. Like OctoSQL, you can join data from different data sources. It's also relatively easy to add your own data sources ("adapters" in Calcite lingo) and rules to efficiently query those sources. Calcite already has adapters that do things like read from HTML tables over HTTP, files on your file system, running processes, etc. This is in addition to connecting to a bunch of traditional databases including SQL databases, MongoDB, Redis, and several others.

Disclaimer: I am a member of the PMC, but not unfortunately not particularly active atm.

[0] https://calcite.apache.org/

That looks pretty cool. I might reach for that next time.

I was hoping for something where sources were more "on the fly" though. Like where I could run an arbitrary command to make a row. It seems like making an "adapter" is a bit more of an investment here.

I'm curious what you mean by "arbitrary command to make a row." Could you give more of an example of a potential use case?

The point is that it is very flexible. For example I could compute a hash of an input string, run a virus scan on a file or whatever else I wanted.

In one concrete example I was trying to explore some things about Super Mario Bros so the command would be to run the emulator based on the input and output some information about the game state (character location, speed, level...)

I'm still not totally following your example. If it helps, it wouldn't be too challenging to make an adapter which pulls data from arbitrary shell commands.

Exposing a "row" is not enough for the query system though, it has to be able to do filtering and joining. If it has to scan every row for any operation, you might as well have an endpoint that returns every row... and then you're back at the currently supported workflow.

That's what the complexity is.

Some data sources will be expensive and can't be filtered so you want to avoid looking up rows as much as possible (do the filtering using other predicates if possible, do the loopup only if required). Some data sources will support efficient filtering with pushed down predicates.

Ideally when "asking questions" I don't need to worry about it. The optimizer can use the info provided to do something sensible.

How can the info be provided if you don't write a full adapter? I feel like I'm misunderstanding what you were asking for.

I guess you do end up a "full adapter" but you can do it piecemeal. For most of my use cases my custom tables don't need to be highly optimal, I can just give a rough expectation of cost, and if I don't provide a batch get then obviously you can't do push-down of predicates.

Some metadata can be provided by the definition, and maybe some can be learned on-the-fly (like latency and data distribution?)

I designed Trustfall[0] out of frustration with existing systems that either:

- didn't allow optimizations like batch get and predicate pushdown even when I knew they'd help, or

- made it really difficult to add them piecemeal after-the-fact on an existing schema, by either demanding everything be batched and optimized, or that nothing be batched and optimized.

In my experience, it's usually a small component of the schema that is causing most of the runtime cost, and it's not always predictable ahead of time where it's going to be. It's dependent on the queries and the data size and shape, so it changes over time.

So I wanted to be able to write a working schema and adapter quickly, and then optimize the parts that were slow in practice for my workload and not just ones that "hypothetically might be slow," and this is what Trustfall lets me do.

More info in my reply on an ancestor comment in the thread.

[0]: https://github.com/obi1kenobi/trustfall

FWIW, you can do this with Calcite. All you really need to write an adapter is something which gives all the data in a table. If that isn't efficient enough, you can write some rules for the planner which allow operators to pushed down.

Echoing this, you have essentially described Calcite.

The community is incredible, too.

We've done something like this at Hasura.

It allows users to write adapters for datasources in any language, expose them over an HTTP API, and then combine them together into a federated GraphQL API:

(Disclaimer: I work on this feature)

https://techcrunch.com/2022/06/28/hasura-now-lets-developers...

https://hasura.io/blog/hasura-graphql-data-connectors/

Based on your "model everything as a table" sensibility, you might enjoy https://www.visidata.org/. It basically treats everything (CSVs, Excel files, the filesystem, SQLite files, etc) as tables and then lets you interact with them through a uniform table manipulation interface.

Yeah, something like a client side only GraphQL would be nice. Define your service/object relations and then query remote (REST or otherwise) services as a connected graph. Data is woven together client side but the benefit is services are agnostic to it and you could weave together many public services.

Yeah, something like that would be really cool. Although I think the actual computational abilities of graphql are less than I am envisioning, but I'm not too familiar. I'm thinking of SQL-like (or even more powerful) aggregations and computations.

Trustfall (see sibling comment: https://news.ycombinator.com/item?id=32120007 ) is my attempt at what you're describing: GraphQL syntax with custom directives like @recurse / @optional / aggregations / arbitrary filters, executed client-side over whatever remote APIs are available (REST or otherwise).

It's by no means fully polished yet and lacks many features that'd be useful to have, but I'd love your thoughts if you decide to take a peek! It supports Rust, Python, and (extremely soon!) WASM in the browser.

https://github.com/obi1kenobi/trustfall

The rest sounds great too, but I particularly like the analogy of higher levels of abstraction as compost -- using composability to enrich your queries.

Hey!

> I think the main fundamental difference is that this wants all of the data upfront in a data file.

Absolutely not! Moreover, OctoSQL can push down predicates to databases so that it only has to download a small subset of the table, if the datasource and query allow it.

> Very easy to model HTTP APIs as a table.

"Very easy" is relative, but you can take a look at the random_data[0] datasource which is exactly this. I'm also planning to add a GitHub datasource fairly soon. That said, there is Steampipe[1] for which this is the main use case afaik (hitting API's and exposing them as tables through Postgres FWD's written in Go), so it might be a smoother and more polished experience. There's also tons of plugins already available for it.

> Easy to model basically anything as a table for example files on my filesystem.

Yep, definitely. That's the idea behind OctoSQL. Strive to create a tool for easily exposing anything through SQL (like your machine's processes list, an API, and join that with a file, or database). There's still lot's of documentation work left to do though, in order to make the plugin authoring experience easier.

> A decent query planner so that I can avoid expensive things (like API calls) if I can determine if I need the object based on something cheaper (like a local disk access).

Probably depends on the use-case, and it sometimes needs you to be fairly explicit, but OctoSQL does in fact do that. It will push down predicates to underlying databases, which means joining something small with something very big (while only taking very small amounts of the latter) can be very fast with LOOKUP JOIN's.

> I want something that is easy to extend to sources that are possibly non-listable or at the very least I don't want to have all of the data available.

Doable. An example of this is the `plugins.available_versions` table[2]. It requires you to provide the plugin name as a predicate, as the versions need to be downloaded from the plugin's own repository (and listing all plugin repositories on each query isn't really what you want to be doing; You can also LOOKUP JOIN with the `plugins.available_plugins` table if that is indeed what you want).

[0]: https://github.com/cube2222/octosql-plugin-random_data

[1]: https://steampipe.io

[2]: https://github.com/cube2222/octosql/blob/main/datasources/pl...