4 ms·

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

by h3rald 6y ago

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/ https://h3rald.com/litestore/

triangleman 6y ago
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 6y ago
  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.
pupdogg 6y ago
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 6y ago
  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 6y ago
    Just discovered from parent. Great project! Thank you

by h3rald 6y ago

triangleman 6y ago

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 6y ago
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.

pupdogg 6y ago

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 6y ago
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 6y ago
  Just discovered from parent. Great project! Thank you