Postgres - a document database?

Storing large json documents in SQL servers is painful. You first need to map the object out to a relational structure, then deal with putting it back together when you're querying. In addition you probably need to use some ORM to store your objects. All of which makes me sad.

With Postgres, there is a great feature that lets you store your json object into a column, and do queries on the json natively in the database!

I'm using Massive.js for working with Postgres in node, which has first class support for jsonb storage and makes the query syntax for working with jsonb documents a bit nicer. It also lets you write your own SQL when you need to do some more custom queries.

If you don't want any other columns in your table (it'll give you id and created date for free), you can use the saveDoc function (more on the doc storage syntax here):

db.saveDoc("my_documents", jsonDocument, function(err,res) {
    //the table my_documents was created on the fly with id and created date
    //res is the new document with an ID created for you
});

If you want to store some more data in your table then you can:

• Manually create the table yourself prior to using Massive
• Let Massive create the initial table for you, then add columns as needed (Massive will also create the primary key and auto-incrementing function which is handy)

We'll look at some querying strategies in the next post (there is documentation in the json section of the github repo which is a great place to start)