Endatabas: A SQLite-inspired, SQL document database with full history | Hacker News Reader

Endatabas: A SQLite-inspired, SQL document database with full history

docs.endatabas.com

117 pointsthunderbong20 comments

I'm working on something similar for the JVM, however with no document semantics, but on a much more fine granular level.

JSON is shredded during an initial import into a tree structure with fine granular nodes. Thus, an import can be done with very low memory consumption (permitted that auto-commit issues a sync to disk before RAM space is exceeded). Furthermore, it doesn't require a WAL for consistency. Instead the indexes are stored in a log-structure using a persistent tree (as in every commit creates a new tree root). A sliding snapshot algorithm makes sure, that only a fragment of a page has to be copied on a write.

As thus, it's also a perfect candidate for an event store, storing both the (lightweight) snapshots and tracking the changes optionally.

https://github.com/sirixdb/sirix

The architecture is described over here:

https://sirix.io/docs/concepts.html

Furthermore I'm working on a tutorial for a local client usage (work in progress):

https://sirix.io/docs/jsoniq-tutorial.html

Kind regards

Johannes

Clearer examples highlighting the mechanics of Endb:

https://docs.endatabas.com/tutorial/sql_basics.html

At first I was like "nooo!" because of my MongoDB schema-less PTSD, but pushing through it I find the design and operation of this architecture appears to be interesting and novel with intriguing potential. I haven't encountered another database with semantics quite like this one (not to say one doesn't exist, I just haven't seen it).

xtdb v2.0 ? it's bitemporal, will be SQL-compatible, and also has another query-language - XTQL :

https://github.com/xtdb/xtdb/tree/2.x/dev/xtql

from https://docs.endatabas.com/index.html > Endatabas is a SQL document database with complete history. It will store anything, forever.

> The best way to understand Endatabas is to use it. Head on over to the Quickstart to try it out.

How about a well-written and concise explanation of what it is and what it does, instead of asking me to install stuff on my computer just to understand what it is?

FWIW the "Why?" page does a good job (IMHO) of what it is and what it's trying to achieve. Well-written, although perhaps not exactly concise. https://docs.endatabas.com/appendix/why.html

I took a stab at writing up a "What?" page to pair with this: https://docs.endatabas.com/appendix/what.html

It tries to be more concise. :) Would love your feedback, if you have any. Our Discord is on the homepage, if that's easier than email.

Fun name. It literally translates to "A database" from Swedish.

The five pillars in https://docs.endatabas.com/appendix/why.html sound similar to XTDB (though I don't know the analytics story there).

Edit: prompted by the below I went looking in the repo for the language used. Turns out it's SBCL (Common Lisp) and Rust.

Hakan and Steven used to be associated with Juxt, the makers of XTDB, but I don't see them listed on the Juxt About page anymore.

They have a "Why?" page:

https://docs.endatabas.com/appendix/why.html

It's overly wordy though. :/

As mentioned in another branch, I took a stab at writing up a "What?" page to pair with the "Why?" page: https://docs.endatabas.com/appendix/what.html

It tries to be less wordy. :)

Same as above, I'd love your feedback, if you have any. Our Discord is on the homepage, if that's easier than email.

> It tries to be less wordy. :)

Cool. Now halve the number of words again. :)

Seems interesting. But how is this different than using JSON functions with SQLite?

https://www.sqlite.org/json1.html

Do they give you the ability to query history?

I've worked on a schemaless version of SQLite for a few years now - https://neuml.github.io/txtai/embeddings/query/

It's backed by the JSON extension. You can use autoids to ensure all data is insert only. I'm sure this project has some really nice features but for the schemaless piece, just saying that SQLite has a similar component in case some don't know.

txtai looks cool! It's not obvious from the title of the post, but Endatabas isn't really comparable to SQLite in this way. Endb is a cloud-native columnar immutable database with separated storage and compute. Only the SQL grammar is SQLite-inspired.

That said, the significant dialect difference is that Endb's JSON-like documents are strongly-typed. One of the places the Endb SQL dialect diverges from SQLite's is typing. In this respect, SQLite is "more schemaless"... at least at the level of weakly-typed scalars and nested JSON (since JSON supports a diminutive set of types).

Endb's data types, their exposed JSON-LD types, and internals: https://docs.endatabas.com/reference/data_types.html

Endb's SQL data types: https://docs.endatabas.com/sql/data_types.html

...all of these can be used at any nesting level, which makes nested data in Endb quite different from JSON.

Hope that helps explain the difference!

(I'm involved in the Endatabas project and happy to answer other questions or clarify this.)

Sounds very interesting, appreciate the clarifications. Definitely will keep an eye on the project, best of luck!

The problem with JSON in SQLite is that indexing arrays is not supported.

https://sqlite.org/forum/forumpost/dfd4739c57

This is cool. I like that it makes it super easy to get data into the database rather than having to meddle with schemas in advance.

Looked through the documentation briefly but couldn't figured out how selecting nested properties or filtering on nested properties works?

I think the documentation you're looking for is here:

https://docs.endatabas.com/sql/path_navigation.html

I've just pushed a change mentioning that paths can be used in both SELECT and WHERE clauses. Sorry for the ambiguity.

For example:

`select * from stores where addresses[0].city = 'New Jersey';`

...can be used on the demo data, found here:

https://www.youtube.com/watch?v=oDHGjUMqPvI&t=140s - demo

https://github.com/endatabas/marketing/tree/main/2023-09-dem... - code

HTH!

Reply on news.ycombinator.com