[schmichael]

Wow, this is written by an early mentor of mine who introduced me to CouchDB and MongoDB circa 2008! I went on to take MongoDB to Urban Airship (now Airship), make a complete mess out of things (lots of evidence of that on HN even), and eventually port it all to a Postgres cluster. Meanwhile jamwt went to Dropbox and ended up managing thousands of SQL instances of his own!

And now both of us are back on non-SQL datastores: Convex for him, and Nomad's combination of Raft+MemDB for me. While Convex sounds influenced by transactional memory (define your critical section and let the platform keep trying to apply it until it succeeds), Nomad opted for Raft's serialization of mutations. On the read side we both opted for explicit index use, so it seems fair to assume both of us feel like SQL's decoupling of indexes from queries does more harm than good. Query planners are great until they're not.

I would love a SQL (well SELECT) interface to Nomad's datastore (MemDB). SELECT is like the C ABI for data instead of code: imperfect, but a lowest common denominator that if implemented unlocks a ton of workflows.

I wonder if jamwt feels the same and both projects will eventually grow a SQL adapter (or at least a SQL-like DSL... Nomad is close with bexpr filtering on a number of APIs, but JOINs would be really useful).

[jamwt]

Hi there Michael! We continue to love nomad.

SQL is the C ABI of querying for sure. BI tools will never adapt to use Convex directly, and nor should they.

So... yes, Convex actually had a prototype SQL adapter for the read side of things back in the early few months when we were figuring things out. Convex's read semantics are very compatible with SQL.

We've kept this adapter on ice in part because of point #3 in the article -- we don't want to overpromise things which are a bad idea.

Meaning, if we exposed SQL on the thing as-is, this would presumably be for more analytical type queries involving patterns normal Convex queries can't express. Right now that would be a Bad Idea because your website would slow down just like every other database system allows you to.

So the current recommended practice is use our Airbyte Egress connector (https://airbyte.com/connectors/convex) and get yourself into an offline Clickhouse/MySQL/Snowflake whatever and jam SQL over there to your heart's content. That's basically what we do.

We may one day abstract this away by embedding some sort of mirrored column store sql thing (maybe DuckDB based? who knows) so you can do your analytical work without impact on your OLTP or integrating 3 more systems. But the team hasn't invested in that yet and probably won't for some time.

[schmichael]

Hello!

> Convex actually had a prototype SQL adapter

Ha, this sounds like the heaps of scripts we Nomad developers have lying around for converting ad hoc bits of Nomad state to sqlite for debugging particularly stateful problems.

> So the current recommended practice is use our Airbyte Egress connector

Ah this is where we would like to end up with Nomad's event stream API: https://developer.hashicorp.com/nomad/api-docs/events#event-...

Sadly we've been unable to identify that First Sink to implement and open source. It's a lot of work to build and maintain production quality connectors, so it would be nice to do just one like your Airbyte Egress. This is an area where Nomad's onprem nature makes it hard to pick one solution that will work for a wide number of users. Someday!

[jamwt]

Also, yes, great pickup re: transactional memory. We talk about this internally all the time, this is the inspiration.

Haskell: IO -> STM -> (pure)

Convex: (The browser / Convex Actions / other effectful environments) -> Mutations -> queries

All the same benefits re: retry and memoization.

Steal steal from Haskell, so many great ideas there.