
Crux SQL - yogthos
https://juxt.pro/blog/crux-sql
======
profchaos69
Does this mean I can use tools like
[https://github.com/metabase/metabase](https://github.com/metabase/metabase)
with crux? Awesome if so!

~~~
arjunnarayan
I don't believe Metabase supports the Calcite SQL dialect
([https://github.com/metabase/metabase/issues/6230](https://github.com/metabase/metabase/issues/6230)),
which is what Crux is using for the SQL layer. So I believe the answer is no -
but I'm not an expert here, so don't take this answer as definitive.

~~~
refset
As it happens there was a comment on a related issue about Dremio support [0]
(which also uses Calcite) where someone shared that they got a Dremio driver
working. I was able to fork their driver and get Metabase working against Crux
in time for a live demo of the crux-sql module back in May [1].

I just switched the forked driver repo to public if anyone wants to test it
out [2]. The Metabase driver docs are pretty straightforward to get things
running. There's definitely work to be done though and I didn't get very deep
into it really - but I hope to pick it up again soon!

[0]
[https://github.com/metabase/metabase/issues/5562](https://github.com/metabase/metabase/issues/5562)

[1] [https://youtu.be/StXLmWvb5Xs?t=996](https://youtu.be/StXLmWvb5Xs?t=996)

[2] [https://github.com/crux-labs/crux-metabase-
driver](https://github.com/crux-labs/crux-metabase-driver)

(I work on Crux :)

EDIT: it's also worth a mention that the linked issue #6230 discusses a lot of
problems deriving from Druid's lack of support for prepared statements, but
Crux and Dremio don't have that limitation. Although reading the most recent
comment it looks like Druid may have overcome that hurdle now, so with a bit
of luck there might now be more traction to get mainline Metabase support for
a generic Calcite driver!

------
wry_discontent
I learned datalog through Crux and I really enjoyed it. After not very long,
it was more intuitive and easier to reason about that similar sql.

~~~
slifin
I'm trying to learn datalog casually, there's lots of queries that I still
don't know how they translate

I.e. group by, window aggregates, order by, limit

I'd be interested in seeing the equivalent datalog that Crux makes given some
of those statements for learning what efficient datalog looks like for some of
those problems

~~~
fnordsensei
I highly recommend
[http://www.learndatalogtoday.org/](http://www.learndatalogtoday.org/) as a
learning resource.

I also enjoyed this talk:
[https://youtu.be/oo-7mN9WXTw](https://youtu.be/oo-7mN9WXTw) though it’s not
so much about syntax as the logic of it.

~~~
capableweb
Just finished watching that talk, I recommend it too! Information dense and
fast with lots of useful examples on how to use Datalog, especially comparing
it to what most programmers use today such as relational, key/value and
document stores.

------
krick
Oh dear, I feel like there is some gap between the set of technologies I'm
aware of and all this stuff. First of, can somebody (preferably not involved
with crux) tell me, if I'm actually should be aware of crux? Is it "yet
another Cassandra"? Is it preferable in any way to Cassandra (or whatever "yet
another of" it is)?

~~~
refset
(I am involved with Crux - sorry!)

I see 4 main reasons why someone may want to be aware of Crux:

\- if you have a bitemporal problem

\- if you have a graph problem, i.e. something you might initially look to
Neo4j to help with

\- if you want to use Datalog because it can make writing an application
simpler

\- if you are thinking of building something similar (immutable event log +
indexes) and want to save time

Crux is very different from Cassandra (strong consistency, fat nodes,
arbitrary joins etc.), but you could definitely use Cassandra in your Crux
architecture.

The closest "yet another" comparison would be Datomic. Crux and Datomic both
strive to reimagine what a "general purpose" DBMS should look like, with the
primary goal being developer productivity/sanity, whereas Cassandra's goal is
simply to be a highly-scalable document store.

Hope that helps!

~~~
krick
Thank you, it explains a lot. However, I'm struggling to imagine from the top
of my head a problem, where I'd need a DB that is both bitemporal and graph-
based, while keeping in mind that both conditions are relatively "expensive"
compared to what more common relational design could offer — so I really
better have a good reason to chose something exotic like that! Do you perhaps
have some example?

~~~
lgessler
Part of the thesis of Datomic (and also Crux, as I understand it) is that
cycles and storage have gotten so cheap that some of the design assumptions
made by traditional databases no longer necessarily make sense for large
segments of the application software market. One of the major ones being
challenged is that storage is so expensive that your updates to the DB need to
be destructive. That's still true for some domains, but for many, many
domains, e.g. ones where historical reporting is important and write volume
isn't terribly high, it'd be much better to have an immutable database
(facilitating historical queries), even if it means paying a little more in
hosting costs, than to build the necessary features on top of a destructive
database.

------
lukashrb
Anyone with experience has some detailed info about the limitation, trade-offs
and problems of using Datalog instead of SQL? Datalog seems like a natural
choice as declarative query language but has not yet become mainstream. I
wonder why?

~~~
lgessler
Using Crux's Datalog query language from Clojure is great. Normally if your
query building library is giving you a data-like interface, then you're
relying on some abstraction that will inevitably leak in places (and let's not
talk about ORMs), but since Crux really does natively use EDN (Clojure's data
syntax, roughly analogous to JSON) for its query language, you've eliminated
an entire layer of complexity in your application, and you also have the
entire programming language at your disposal for compositionally building
queries.

That's not to say that using the SQL API is a bad idea, but if you're using
Crux from Clojure, you'd be missing out on some stuff.

------
liminal
This looks really interesting - especially the temporal aspects. Does anyone
have any insight into using it with RDF/SPARQL data/queries?

~~~
lgessler
From their docs[0]:

> The REST API also provides an experimental endpoint for SPARQL 1.1 Protocol
> queries under /sparql/, rewriting the query into the Crux Datalog dialect.
> Only a small subset of SPARQL is supported and no other RDF features are
> available.

[0]: [https://opencrux.com/docs](https://opencrux.com/docs)

~~~
refset
The context here is that we benchmark ourselves against the likes of Neo4j and
RDF4J using SPARQL test suites, in particular WatDiv [0] and LUBM [1] which
are specifically designed for stress testing various subgraph matching queries
[2]. Therefore we had to translate the data sets and queries from RDF/SPARQL
to edn/Datalog etc.

There is an open issue in regards more general RDF support with details on the
kinds of things Crux would need to add:
[https://github.com/juxt/crux/issues/317](https://github.com/juxt/crux/issues/317)

[0] [https://dsg.uwaterloo.ca/watdiv/](https://dsg.uwaterloo.ca/watdiv/)

[1]
[http://swat.cse.lehigh.edu/projects/lubm/](http://swat.cse.lehigh.edu/projects/lubm/)

[2]
[https://en.wikipedia.org/wiki/Subgraph_isomorphism_problem](https://en.wikipedia.org/wiki/Subgraph_isomorphism_problem)

------
ledgerdev
What's the hosting story for crux if one wanted to try it out on a side
project?

~~~
refset
:) well I already answered your same question on r/Clojure but here's a link
for others:
[https://www.reddit.com/r/Clojure/comments/i3gzxy/crux_sql/g0...](https://www.reddit.com/r/Clojure/comments/i3gzxy/crux_sql/g0h3ltl/)

~~~
ledgerdev
Thanks!

------
LambdaComplex
Any relation to the CRUX Linux distro? Or is this just an unfortunate overlap
of names?

~~~
0xdeadb00f
Doesn't look like there's any relation.

Off-topic, but I quite liked CRUX linux when I did use it. It introduced me to
BSD-style init scripts

