[h3rald]

Yep, SQLite is awesome. I did actually build exactly that: a document database powered by SQLITE [1], and it works well, especially considering that SQLite has built-in support for full text search and indexes for JSON content.

[1]: https://h3rald.com/litestore/

[pupdogg]

LiteStore looks fantastic! Do you have any performance metrics (specifically limits) for the various use cases you mention in the documentation? Considering it's written in Nim, I wouldn't be surprised if it actually outperformed bulk of the other documentDB solutions that currently exist out there.

[h3rald]

Not published unfortunately but I should, really!

In the past I tested importing dumps of the full HTML and CSS documentation docset from MDN and: - Importing files in a directory is relatively fast: I import batches and it can import hundreds of pages in a few seconds - sadly there's no bulk operation yet to insert arbitrary json documents (unless they are stored as files in a folder), so that's quite slow. - full text search and querying indexed (JSON) properties is fast (double-digit milliseconds for searching several hundred of relatively large documents with paged results)

I do need to do some compared benchmarks with similar data stores though.

[throwaway888abc]

Just discovered from parent. Great project! Thank you

[triangleman]

This looks great, and way more capable than simply "prototyping and testing" as you put it on your web site.

How did you get sqlite3 to index JSON? I don't see anything in the product that does that. I'm searching through your source code now but I don't see anything specific.

[h3rald]

Well yes it started off for prototyping and then I kept adding more features like JavaScript middleware and token validation... I have used it as a backend for quite a few private projects of mine.

Regarding indexing json... here's how I do it:

     proc createIndex*(store: Datastore, indexId, field: string) =
       let query = sql("CREATE INDEX json_index_$1 ON documents(json_extract(data, ?) COLLATE NOCASE) WHERE json_valid(data)" % [indexId])
       store.begin()
       store.db.exec(query, field)
       store.commit()

Basically, SQLite supports indexes in arbitrary expressions and so you can use it in conjunction with json_extract. In this case, field is an arbitrary json path.

I then added a REST API in LiteStore to create (JSON) indexes... in that way you can make your queries more performant based on the specific json documents that you store in the data store.