5 ms·

asked this on a sqllite thread before but: anyone know a single file nosqldb that has good python bindings. i know I could just write to json or something but I

asked this on a sqllite thread before but: anyone know a single file nosqldb that has good python bindings. i know I could just write to json or something but I'd like something that supports queries. right now I'm using MongoDB and it's completely Overkill

codefreakxff 7y ago
Postgres supports querying json
- paulhodge 7y ago
  Has anyone actually used json-based queries in PG in a nontrivial way, and had a good experience? I looked into it but the SQL syntax for doing json operations was crazy complicated, and I had trouble finding an ORM that did a good job of simplifying it.
  - nicoburns 7y ago
    What kind of non-trivial use cases do you have. I've found you generally just need to pick out a field using the JSON syntax, then you can use all of the normal SQL tools for fancier stuff...
  - striking 7y ago
    Yeah, I don't mind it. Oftentimes when you want to manipulate JSON, you'll embed a raw snippet of PG-specific SQL into the query you're building with your ORM. I do wish the operators were a little more ergonomic though. One of these days I'll print out the list and stick it to my monitor.
  - lacampbell 7y ago
    If your queries are that complicated are you not better off normalising your data, then writing views to jsonify it?
  - choicenotchance 7y ago
    For millions plus rows, the queries get crazy slow. If your jsonb field contains lot of data, JOINs on toast storage tables takes chunk of the query times. My 2 cents: Best to keep data normalized and in separate columns. If you gotta keep data in jsonb, keep only the data that you don't need to query on. Anything you need to query, you better put it as a column. You'll thank me later.
fermuch 7y ago
I'm unsure of what you mean by "single file", but rocksdb is embedded and has python bindings.
icebraining 7y ago
What kind of queries? There's LMDB, but it's mostly k/v: https://en.wikipedia.org/wiki/Lightning_Memory-Mapped_Database https://en.wikipedia.org/wiki/Lightning_Memory-Mapped_Databa...
diek 7y ago
It depends on what you mean when you say you want it to "support queries", but then also ask for a "nosqldb". It really depends on whether you want a full query grammar (in that case, why not SQL?), or if a key/value store is enough. I use lmdb pretty extensively. It is single-file (well, it has a lock file) key/value store, can be read-from/written-to by multiple processes, and embeds nicely. It has Python bindings, but I've only used it from C. https://symas.com/lmdb/ https://symas.com/lmdb/
- mlevental 7y ago
  i'm imagining something like the sqlite for mongo. ie you can do aggregations and nested k/v queries and filters and things like that.
simonw 7y ago
SQLite has really good JSON support these days. Any reason not to use that?
- mlevental 7y ago
  as far as i could tell you have to jump through some hoops to use it (compile extension from source?). and i couldn't tell if it supported querying
  - lacampbell 7y ago
    JSON on Sqlite works out of the box in ubuntu, but it doesn't in OpenBSD. Not sure about other environments. It supports querying, see json_extract function. https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html I use it a lot to return JSON from normalized data, for apps to consume.
  - bruce_one 7y ago
    The extension is included in the standard sqlite [amalgamation](https://www.sqlite.org/amalgamation.html https://www.sqlite.org/amalgamation.html), hence it's basically always available (ie you'd have to manually disable it) (ime). It's worth noting that sqlite's typing is very flexible, so you probably want to add a `check (data is null or json_valid(data) = 1)` to ensure the data is _actually_ valid json. You can then index based on json queries, eg `create index some_index on something((json_extract(data, '$.someField')))` and then do an indexed query, eg `select * from something where json_extract(data, '$.someField') = 'blah'`. In our experience it's all working quite well :-) (Groking json_each and json_tree took a few goes, but now is working well (eg we join onto json_each to allow a row "labels" containing a list of label (rather than a one-to-many table join for something_labels)).)

codefreakxff 7y ago

Postgres supports querying json

paulhodge 7y ago
Has anyone actually used json-based queries in PG in a nontrivial way, and had a good experience? I looked into it but the SQL syntax for doing json operations was crazy complicated, and I had trouble finding an ORM that did a good job of simplifying it.
- nicoburns 7y ago
  What kind of non-trivial use cases do you have. I've found you generally just need to pick out a field using the JSON syntax, then you can use all of the normal SQL tools for fancier stuff...
- striking 7y ago
  Yeah, I don't mind it. Oftentimes when you want to manipulate JSON, you'll embed a raw snippet of PG-specific SQL into the query you're building with your ORM. I do wish the operators were a little more ergonomic though. One of these days I'll print out the list and stick it to my monitor.
- lacampbell 7y ago
  If your queries are that complicated are you not better off normalising your data, then writing views to jsonify it?
- choicenotchance 7y ago
  For millions plus rows, the queries get crazy slow. If your jsonb field contains lot of data, JOINs on toast storage tables takes chunk of the query times. My 2 cents: Best to keep data normalized and in separate columns. If you gotta keep data in jsonb, keep only the data that you don't need to query on. Anything you need to query, you better put it as a column. You'll thank me later.

fermuch 7y ago

I'm unsure of what you mean by "single file", but rocksdb is embedded and has python bindings.

icebraining 7y ago

What kind of queries? There's LMDB, but it's mostly k/v: https://en.wikipedia.org/wiki/Lightning_Memory-Mapped_Database https://en.wikipedia.org/wiki/Lightning_Memory-Mapped_Databa...

diek 7y ago

It depends on what you mean when you say you want it to "support queries", but then also ask for a "nosqldb". It really depends on whether you want a full query grammar (in that case, why not SQL?), or if a key/value store is enough. I use lmdb pretty extensively. It is single-file (well, it has a lock file) key/value store, can be read-from/written-to by multiple processes, and embeds nicely. It has Python bindings, but I've only used it from C. https://symas.com/lmdb/ https://symas.com/lmdb/

mlevental 7y ago
i'm imagining something like the sqlite for mongo. ie you can do aggregations and nested k/v queries and filters and things like that.

simonw 7y ago

SQLite has really good JSON support these days. Any reason not to use that?

mlevental 7y ago
as far as i could tell you have to jump through some hoops to use it (compile extension from source?). and i couldn't tell if it supported querying
- lacampbell 7y ago
  JSON on Sqlite works out of the box in ubuntu, but it doesn't in OpenBSD. Not sure about other environments. It supports querying, see json_extract function. https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html I use it a lot to return JSON from normalized data, for apps to consume.
- bruce_one 7y ago
  The extension is included in the standard sqlite [amalgamation](https://www.sqlite.org/amalgamation.html https://www.sqlite.org/amalgamation.html), hence it's basically always available (ie you'd have to manually disable it) (ime). It's worth noting that sqlite's typing is very flexible, so you probably want to add a `check (data is null or json_valid(data) = 1)` to ensure the data is _actually_ valid json. You can then index based on json queries, eg `create index some_index on something((json_extract(data, '$.someField')))` and then do an indexed query, eg `select * from something where json_extract(data, '$.someField') = 'blah'`. In our experience it's all working quite well :-) (Groking json_each and json_tree took a few goes, but now is working well (eg we join onto json_each to allow a row "labels" containing a list of label (rather than a one-to-many table join for something_labels)).)