
Show HN: BedquiltDB – A Mongo-like JSON doc store built on Postgres - s_kilk
https://bedquiltdb.github.io
======
s_kilk
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
Any particular reason to choose PL/PgSQL over other extension languages?

~~~
s_kilk
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.

~~~
metalliqaz
Not requiring any external dependencies is a huge plus in my book.

~~~
X86BSD
+23478723497894235787345897435 for this!

I loathe and detest dependencies! Just saying.

~~~
sago
But how else can you make sure your pad_left function is well written and
widely tested?

------
ThePhysicist
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
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
We use it in production at QuantifiedCode, so it's pretty stable but still a
bit undocumented (which we will fix soon).

------
alfonsodev
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
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
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
Thank you!

------
chris_wot
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?

~~~
153354
Err because things like this didn't even exist until 10 minutes ago and aren't
production-ready. I don't really understand people who keep saying this.
Postgresql supports JSON, but it doesn't have all of the code to go on top of
it so that you can use it like mongo. As in, so you can do projects.find or
projects.insert instead of a bunch of sql.

Then there are all kinds of other issues related indexes and the differences
between mongo and PostgreSQL and so on.

~~~
Mahn
There's an unwritten rule here on Hacker News that says that you are supposed
to bash any piece of technology that received praise when it was originally
introduced on HN, but is now 3 years old or older. The moment in time when
something transitions from new and shiny to old and unfashionable is generally
characterized by a comment of the lines of "Why would anyone use x when you
can use y?".

Conversely, the more recent a piece of tech is, the more likely it is go into
production right away, with people only starting to question their decision
after the 3 year new-and-shiny barrier is broken (but usually not because of
its faults, but because something newer and shinier can now replace it).

~~~
abiox
well, in mongo's case, there has been some rather strong criticism leveled at
it over time.[1][2][3] some aspects have probably improved, but (as i don't
follow mongo) i can't say how proactive and public mongo's owner has been in
addressing criticisms.

[1] [http://cryto.net/~joepie91/blog/2015/07/19/why-you-should-
ne...](http://cryto.net/~joepie91/blog/2015/07/19/why-you-should-never-ever-
ever-use-mongodb/)

[2] [http://www.sarahmei.com/blog/2013/11/11/why-you-should-
never...](http://www.sarahmei.com/blog/2013/11/11/why-you-should-never-use-
mongodb/)

[3] [https://aphyr.com/posts/322-call-me-maybe-mongodb-stale-
read...](https://aphyr.com/posts/322-call-me-maybe-mongodb-stale-reads)

------
kevincox
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.

~~~
jxcl
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
> 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
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).

------
sandGorgon
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
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.

------
ape4
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
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
Yes, that would be hard! Maybe you could use Postgres's newish JSON for that.
(Of course, that's not traditional SQL)

~~~
chris_wot
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.

------
philliphaydon
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.

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

------
latenightcoding
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.

