8 ms·

Show HN: BedquiltDB – A Mongo-like JSON doc store built on Postgres

145 points by s_kilk 11y ago

kevincox 11y ago
From their examples: projects.insert({ '_id': "BedquiltDB", 'description': "A ghastly hack.", 'quality': "pre-alpha", 'tags': ["json", "postgres", "api"] }) Does anyone actually mix quotes like this in python? It's horrible.
- bpicolo 11y ago
  Single-quotes is the typical way to go. I do know when I'm not linting that I mix and match unintentionally. Easy to forget
  - illumen 11y ago
    I wish this was defined by pep8, but sort of glad it's not. Much python code uses single quotes, with double quotes for doc strings. Except where it is more convenient. More convenient places include structures when escaping strings. "Joe's easier here's why." Is better than 'Joe\'s easier here\'s why.' Also structures which are JSON structures too (makes copy/pasta easier).
    - sago 11y ago
      Another commenter articulated this, but my general style was single quotes on 'atoms' (e.g. mostly dictionary keys or set values), double quotes on anything intended to be read by a person. For that reason: to avoid \' all over. Though with py3, using alt-shift-] on my machine gives me ’, so I can do 'Joe’s easier, here’s why.'
      - bpicolo 11y ago
        Or py2 via # -- coding: utf-8 --
- jxcl 11y ago
  It depends. It's useful to be able to contain quotes in other quotes without escaping them. i.e. '"hello there"' or "it's nice to see you" Also, on many projects I've worked on, single quotes were for strings that were not meant to be shown to users (keys etc), and double quotes were for strings that would eventually be shown on a screen. It looks like that's what they're doing here.
  - kevincox 11y ago
    > Also, on many projects I've worked on, single quotes were for strings that were not meant to be shown to users (keys etc), and double quotes were for strings that would eventually be shown on a screen. It looks like that's what they're doing here. Interesting, I've never seen that convention before.
    - aidos 11y ago
      Not sure where it comes from but I do the same. Single for most things like atoms etc and double for text (user facing / error messages / logging).
    - masida 11y ago
      Well, I do it too. It's just for convenience, you use the apostroph character much more frequently in ordinary language.
- s_kilk 11y ago
  Huh, good catch. I guess I default to using single-quotes for dictionary keys, and double quotes for text strings
  - Mahn 11y ago
    I do this too, single quotes for simple/static strings, double quotes for (potentially) complex/dynamic strings. Nothing wrong with that as long as you are consistent, though I suppose it's a very subjective thing.
- derefr 11y ago
  I have no idea why I'd do that in Python. In Ruby, I use single-quoted strings for things that can't possibly, by their nature, contain any string interpolation, to indicate that this is so; and then double-quoted strings for more arbitrary text that either contains interpolation, or might, in the future, get interpolation added. The coder here might be used to Ruby programming. Though, also, it occurs to me that—since this is a dictionary that will become JSON—its type is {String => Object}. The single-quoted strings sort of read to me as indicating strings that couldn't be any other type; while the double-quoted strings are strings just because strings happen to be the response there, but could be any other object.
- abiox 11y ago
  > It's horrible. i can dig it. works decently as visual articulation.
s_kilk 11y ago
Hi All, I've been working on this project on-and-off for a few years now, and thought it was time to show it to the public. The Gist: BedquiltDB is a vaguely mongodb-inspired json store built on top of PostgreSQL's jsonb column type. It does the things you'de expect, creating _id fields automatically, creating tables on the first write, etc. It is implemented as a postgres extension (a horrible lump of PL/PgSQL), and client libraries for python, node and clojure. While I'm not using it in production for anything, it is pretty well tested, and I had fun making it, which is what really matters :) Questions welcome!
- e12e 11y ago
  Any particular reason to choose PL/PgSQL over other extension languages?
  - s_kilk 11y ago
    It seemed to be the most performant option available, aside from C. I wouldn't be opposed to re-writing in a more-performant extension language if needed. EDIT: also, PL/PgSQL is installed by default, and I wanted to see if I could get this project to work without any other dependencies aside from a base PostgerSQL 9.4 installation.
    - Dowwie 11y ago
      Yes, it makes sense to write it in whatever was most performant
    - metalliqaz 11y ago
      Not requiring any external dependencies is a huge plus in my book.
      - X86BSD 11y ago
        +23478723497894235787345897435 for this! I loathe and detest dependencies! Just saying.
chris_wot 11y ago
There are more and more relational databases that are beginning to accomplish what MongoDB is (sort of) doing. And ironically, doing it better. They all seem to use PostgreSQL (one that springs to mind is ToroDB). I'm trying to understand why anyone would pick MongoDB over PostgreSQL these days?
- s_kilk 11y ago
  Eh, Mongo still has a better distribution/sharding/failover story, which really matters for some.
  - chris_wot 11y ago
    Doesn't ToroDB do this as a drop-in replacement though?
    - e1ven 11y ago
      I didn't see anything about ToroDB dealing with sharding/replication on their site - It looked like it was only client protocol. If it can handle the replication elements, can you help me find a doc for that part?
      - chris_wot 11y ago
        Try this presentation: http://www.slideshare.net/8kdata/torodb-scaling-postgresql-like-mongodb http://www.slideshare.net/8kdata/torodb-scaling-postgresql-l... Edit: does this help? ToroDB do certainly need to get better documentation :( https://github.com/torodb/torodb/wiki/Setting-up-ToroDB-as-hidden-slave-of-a-MongoDB-Replica-Set https://github.com/torodb/torodb/wiki/Setting-up-ToroDB-as-h...
  - brandur 11y ago
    It might be worth taking a look at Citus, which provides a roughly equivalent distribution and sharing story for Postgres. It's also recently been reworked as an extension and now available as open source [1] so that you can run it yourself. Also, speaking as someone who was an operator of a large Postgres DB and who is now an operator of a large Mongo DB, although Mongo has a nominal lead in this one area for the time being, by going to Mongo you sacrifice a lot (I really can't emphasize this point enough; the distribution/sharding story is really the only thing that Mongo is doing a better job of right now, Postgres is a clear winner in every other way). I'm really looking forward to (and hoping for) extensions like Citus becoming more mature so that optimal choice of database will be obvious under all circumstances. [1] https://www.citusdata.com/blog/17-ozgun-erdogan/403-citus-unforks-postgresql-goes-open-source https://www.citusdata.com/blog/17-ozgun-erdogan/403-citus-un...
ThePhysicist 11y ago
A while ago I wrote a similar system, called BlitzDB: https://github.com/adewes/blitzdb https://github.com/adewes/blitzdb It provides a uniform, mongo-like interface over various storage backends, most notably SQL (Postgres, SQLite) via SQLAlchemy, MongoDB itself (well, naturally) and a flat-file based backend that doesn't have any external dependencies. Feel free to check it out, feedback is welcome.
- fiatjaf 11y ago
  That's very nice. It reminds me of my own https://github.com/fiatjaf/pgjson https://github.com/fiatjaf/pgjson, but of course yours is bigger, seems much more feature-complete and battle-tested. I think I'm going to use it for something and see how it goes. Is there a list of people using it somewhere? Is someone using it for serious things?
  - ThePhysicist 11y ago
    We use it in production at QuantifiedCode, so it's pretty stable but still a bit undocumented (which we will fix soon).
ape4 11y ago
I like this. You can use traditional SQL along side it. It proves NoSql is a special case of SQL. I'd like to see Postgres eventually offer an official NoSql api.
- chris_wot 11y ago
  The biggest issue with the traditional relational model is that it doesn't do trees very well. Aside from that, it's pretty robust.
  - ape4 11y ago
    Yes, that would be hard! Maybe you could use Postgres's newish JSON for that. (Of course, that's not traditional SQL)
    - chris_wot 11y ago
      Oracle does have something - CONNECT BY (part of their hierachical queries). Sadly, to do something similar in PostreSQL you need to use a recursive CTE.
alfonsodev 11y ago
I have to mention here postgrest I'm very fan of it, to me it's a nice equilibrium between documents and relational. For complex queries I just create db views that become very easy http requests, returning json "documents". I don't want to discredit the technical merit of BedquiltDB or similar approaches, but if you already know http and sql what are the benefits of going BedquiltDB over postgrest ?
- fiatjaf 11y ago
  Since you're a user, I'm going to ask: what do you think as Postgrest with its UI working as a standalone app for boring data management and that kind of stuff? The case I have in mind is one in which a small company wants to get some data into the computer, for no specific reason, and wants to pay for an expensive software. Postgrest for the rescue! Or isn't it a good idea? (I have never used it aside from the demo)
  - alfonsodev 11y ago
    Yes! postgrest + ng-admin for boring CRUD admin panels is perfect. https://github.com/marmelab/ng-admin https://github.com/marmelab/ng-admin And here a repo that has the http interceptor configured for postrest. https://github.com/marmelab/ng-admin-postgrest https://github.com/marmelab/ng-admin-postgrest ngAdmin it's based on Angularjs, but marmelab has a version based on Reactjs too, that I did't have time to test yet. [edit] Just to clarify, you said "its UI" but postgrest and ng-admin are independent pieces of software, that happened to work very nicely together.
    - fiatjaf 11y ago
      Thank you!
sandGorgon 11y ago
this would be killer .. indeed something I would pay for .. if you make it API compatible with Mongodb. Meaning I can dropin use node-mongo, mongo-ruby, pymongo with your extension.
- stephenboyd 11y ago
  If it becomes API compatible with MongoDB, it could be especially popular with Meteor developers. I'm developing an app on Meteor now where the CRUD operations for the largest class of users work well with the MongoDB conventions, but I also need to prepare for the other portion of users who will need more complex queries to analyze the data produced by the first portion.
latenightcoding 11y ago
I have been looking for something like this for a while, but I didn't find it so I made my own, it has been a lot of fun.
philliphaydon 11y ago
I've been using Marten which is a document DBA built on PostgreSQL for .net, https://github.com/JasperFx/marten https://github.com/JasperFx/marten It's awesome, PostgreSQL is awesome.
deleted 11y ago
[deleted]
fiatjaf 11y ago
Seems like something that would be built soon, by NoSQL enthusiasts, as Postgres implements JSONB with more and more cool features.

145 points by s_kilk 11y ago

From their examples: projects.insert({ '_id': "BedquiltDB", 'description': "A ghastly hack.", 'quality': "pre-alpha", 'tags': ["json", "postgres", "api"] }) Does anyone actually mix quotes like this in python? It's horrible.

bpicolo 11y ago
Single-quotes is the typical way to go. I do know when I'm not linting that I mix and match unintentionally. Easy to forget
- illumen 11y ago
  I wish this was defined by pep8, but sort of glad it's not. Much python code uses single quotes, with double quotes for doc strings. Except where it is more convenient. More convenient places include structures when escaping strings. "Joe's easier here's why." Is better than 'Joe\'s easier here\'s why.' Also structures which are JSON structures too (makes copy/pasta easier).
  - sago 11y ago
    Another commenter articulated this, but my general style was single quotes on 'atoms' (e.g. mostly dictionary keys or set values), double quotes on anything intended to be read by a person. For that reason: to avoid \' all over. Though with py3, using alt-shift-] on my machine gives me ’, so I can do 'Joe’s easier, here’s why.'
    - bpicolo 11y ago
      Or py2 via # -- coding: utf-8 --
jxcl 11y ago
It depends. It's useful to be able to contain quotes in other quotes without escaping them. i.e. '"hello there"' or "it's nice to see you" Also, on many projects I've worked on, single quotes were for strings that were not meant to be shown to users (keys etc), and double quotes were for strings that would eventually be shown on a screen. It looks like that's what they're doing here.
- kevincox 11y ago
  > Also, on many projects I've worked on, single quotes were for strings that were not meant to be shown to users (keys etc), and double quotes were for strings that would eventually be shown on a screen. It looks like that's what they're doing here. Interesting, I've never seen that convention before.
  - aidos 11y ago
    Not sure where it comes from but I do the same. Single for most things like atoms etc and double for text (user facing / error messages / logging).
  - masida 11y ago
    Well, I do it too. It's just for convenience, you use the apostroph character much more frequently in ordinary language.
s_kilk 11y ago
Huh, good catch. I guess I default to using single-quotes for dictionary keys, and double quotes for text strings
- Mahn 11y ago
  I do this too, single quotes for simple/static strings, double quotes for (potentially) complex/dynamic strings. Nothing wrong with that as long as you are consistent, though I suppose it's a very subjective thing.
derefr 11y ago
I have no idea why I'd do that in Python. In Ruby, I use single-quoted strings for things that can't possibly, by their nature, contain any string interpolation, to indicate that this is so; and then double-quoted strings for more arbitrary text that either contains interpolation, or might, in the future, get interpolation added. The coder here might be used to Ruby programming. Though, also, it occurs to me that—since this is a dictionary that will become JSON—its type is {String => Object}. The single-quoted strings sort of read to me as indicating strings that couldn't be any other type; while the double-quoted strings are strings just because strings happen to be the response there, but could be any other object.
abiox 11y ago
> It's horrible. i can dig it. works decently as visual articulation.

s_kilk 11y ago

Hi All, I've been working on this project on-and-off for a few years now, and thought it was time to show it to the public. The Gist: BedquiltDB is a vaguely mongodb-inspired json store built on top of PostgreSQL's jsonb column type. It does the things you'de expect, creating _id fields automatically, creating tables on the first write, etc. It is implemented as a postgres extension (a horrible lump of PL/PgSQL), and client libraries for python, node and clojure. While I'm not using it in production for anything, it is pretty well tested, and I had fun making it, which is what really matters :) Questions welcome!

e12e 11y ago
Any particular reason to choose PL/PgSQL over other extension languages?
- s_kilk 11y ago
  It seemed to be the most performant option available, aside from C. I wouldn't be opposed to re-writing in a more-performant extension language if needed. EDIT: also, PL/PgSQL is installed by default, and I wanted to see if I could get this project to work without any other dependencies aside from a base PostgerSQL 9.4 installation.
  - Dowwie 11y ago
    Yes, it makes sense to write it in whatever was most performant
  - metalliqaz 11y ago
    Not requiring any external dependencies is a huge plus in my book.
    - X86BSD 11y ago
      +23478723497894235787345897435 for this! I loathe and detest dependencies! Just saying.

chris_wot 11y ago

There are more and more relational databases that are beginning to accomplish what MongoDB is (sort of) doing. And ironically, doing it better. They all seem to use PostgreSQL (one that springs to mind is ToroDB). I'm trying to understand why anyone would pick MongoDB over PostgreSQL these days?

s_kilk 11y ago
Eh, Mongo still has a better distribution/sharding/failover story, which really matters for some.
- chris_wot 11y ago
  Doesn't ToroDB do this as a drop-in replacement though?
  - e1ven 11y ago
    I didn't see anything about ToroDB dealing with sharding/replication on their site - It looked like it was only client protocol. If it can handle the replication elements, can you help me find a doc for that part?
    - chris_wot 11y ago
      Try this presentation: http://www.slideshare.net/8kdata/torodb-scaling-postgresql-like-mongodb http://www.slideshare.net/8kdata/torodb-scaling-postgresql-l... Edit: does this help? ToroDB do certainly need to get better documentation :( https://github.com/torodb/torodb/wiki/Setting-up-ToroDB-as-hidden-slave-of-a-MongoDB-Replica-Set https://github.com/torodb/torodb/wiki/Setting-up-ToroDB-as-h...
- brandur 11y ago
  It might be worth taking a look at Citus, which provides a roughly equivalent distribution and sharing story for Postgres. It's also recently been reworked as an extension and now available as open source [1] so that you can run it yourself. Also, speaking as someone who was an operator of a large Postgres DB and who is now an operator of a large Mongo DB, although Mongo has a nominal lead in this one area for the time being, by going to Mongo you sacrifice a lot (I really can't emphasize this point enough; the distribution/sharding story is really the only thing that Mongo is doing a better job of right now, Postgres is a clear winner in every other way). I'm really looking forward to (and hoping for) extensions like Citus becoming more mature so that optimal choice of database will be obvious under all circumstances. [1] https://www.citusdata.com/blog/17-ozgun-erdogan/403-citus-unforks-postgresql-goes-open-source https://www.citusdata.com/blog/17-ozgun-erdogan/403-citus-un...

ThePhysicist 11y ago

A while ago I wrote a similar system, called BlitzDB: https://github.com/adewes/blitzdb https://github.com/adewes/blitzdb It provides a uniform, mongo-like interface over various storage backends, most notably SQL (Postgres, SQLite) via SQLAlchemy, MongoDB itself (well, naturally) and a flat-file based backend that doesn't have any external dependencies. Feel free to check it out, feedback is welcome.

fiatjaf 11y ago
That's very nice. It reminds me of my own https://github.com/fiatjaf/pgjson https://github.com/fiatjaf/pgjson, but of course yours is bigger, seems much more feature-complete and battle-tested. I think I'm going to use it for something and see how it goes. Is there a list of people using it somewhere? Is someone using it for serious things?
- ThePhysicist 11y ago
  We use it in production at QuantifiedCode, so it's pretty stable but still a bit undocumented (which we will fix soon).

ape4 11y ago

I like this. You can use traditional SQL along side it. It proves NoSql is a special case of SQL. I'd like to see Postgres eventually offer an official NoSql api.

chris_wot 11y ago
The biggest issue with the traditional relational model is that it doesn't do trees very well. Aside from that, it's pretty robust.
- ape4 11y ago
  Yes, that would be hard! Maybe you could use Postgres's newish JSON for that. (Of course, that's not traditional SQL)
  - chris_wot 11y ago
    Oracle does have something - CONNECT BY (part of their hierachical queries). Sadly, to do something similar in PostreSQL you need to use a recursive CTE.

alfonsodev 11y ago

I have to mention here postgrest I'm very fan of it, to me it's a nice equilibrium between documents and relational. For complex queries I just create db views that become very easy http requests, returning json "documents". I don't want to discredit the technical merit of BedquiltDB or similar approaches, but if you already know http and sql what are the benefits of going BedquiltDB over postgrest ?

fiatjaf 11y ago
Since you're a user, I'm going to ask: what do you think as Postgrest with its UI working as a standalone app for boring data management and that kind of stuff? The case I have in mind is one in which a small company wants to get some data into the computer, for no specific reason, and wants to pay for an expensive software. Postgrest for the rescue! Or isn't it a good idea? (I have never used it aside from the demo)
- alfonsodev 11y ago
  Yes! postgrest + ng-admin for boring CRUD admin panels is perfect. https://github.com/marmelab/ng-admin https://github.com/marmelab/ng-admin And here a repo that has the http interceptor configured for postrest. https://github.com/marmelab/ng-admin-postgrest https://github.com/marmelab/ng-admin-postgrest ngAdmin it's based on Angularjs, but marmelab has a version based on Reactjs too, that I did't have time to test yet. [edit] Just to clarify, you said "its UI" but postgrest and ng-admin are independent pieces of software, that happened to work very nicely together.
  - fiatjaf 11y ago
    Thank you!

sandGorgon 11y ago

this would be killer .. indeed something I would pay for .. if you make it API compatible with Mongodb. Meaning I can dropin use node-mongo, mongo-ruby, pymongo with your extension.

stephenboyd 11y ago
If it becomes API compatible with MongoDB, it could be especially popular with Meteor developers. I'm developing an app on Meteor now where the CRUD operations for the largest class of users work well with the MongoDB conventions, but I also need to prepare for the other portion of users who will need more complex queries to analyze the data produced by the first portion.

latenightcoding 11y ago

I have been looking for something like this for a while, but I didn't find it so I made my own, it has been a lot of fun.

philliphaydon 11y ago

I've been using Marten which is a document DBA built on PostgreSQL for .net, https://github.com/JasperFx/marten https://github.com/JasperFx/marten It's awesome, PostgreSQL is awesome.

deleted 11y ago

[deleted]

fiatjaf 11y ago

Seems like something that would be built soon, by NoSQL enthusiasts, as Postgres implements JSONB with more and more cool features.