What is the best way to use PostgreSQL JSON types with NodeJS

Question

I am facing some analysis paralysis here. There are so many options for programming databases with NodeJS that I am a bit lost.

I am building an API server using Express that will talk to a HTML5 app on mobile. I decided to use PostgreSQL because my data is "very relational" and PostgreSQL new JSON data type will make my life a lot easier.

Unfortunately, I can't find any library for PostgreSQL taking advantage of the new JSON datatype or exposing it. I thought about using Sequelize and having a nice ORM or rolling my own stuff by using the raw pgsql module.

Can someone shed a clue? I'd ask this on some NodeJS stackexchange but I don't think we have one as specific as this.

I use sequelize and just stringify JSON going in and parse it coming out. I like the setup. More-so then when I used mongo and mongoose. — MikeSmithDev, Mar 12 '14 at 19:24
With sequelize you won't be able to query rows that has some data inside jsonb column, nor you can patch just certain field inside jsonb column. — Mikael Lepistö, Oct 11 '17 at 20:52

score 9 · Answer 1 · answered Mar 12 '14 at 18:38

9

I like https://github.com/brianc/node-postgres. It's actively developed, and just a nice thin layer.

To use the json types in a prepared query, just JSON.stringify whatever you are trying to store as json (that's how postgres wants it anyway).

answered Mar 12 '14 at 18:38

Tim Brown

3,173
1
18
15

Mikael Lepistö · Answer 2 · 2017-01-03T23:05:23.567

Objection.js has really good support for relational data as well as for JSONB data.

You don't have to do any tricks to parse / stringify json data. It all is done automatically. You can declare schemas to allow validating data you are going to put DB etc.

One can insert nested relational object hierarchies to DB and rows will be generated to correct tables and you have javascript API to query data inside JSON columns so no need to write RAW SQL for that either.

EDIT:

No idea why the down votes here (its -2 currently), Objection.js still has the best support for Postgresql's JSONB operations in node world (and the only choice in current answers, which has any special support for postgresql jsonb handling).

Latest addition was support for patching only parts of data inside JSONB column, where objection.js automatically constructs jsonb_set() calls for you.

for example:

ModelWithJsonColumn.query().update({
  'jsonColumn:attribute' : 'new value',
  otherColum: ref('jsonColumn:extractThisAttribute')
}).where('id', 1).returning('*')

will create update query like this:

update "ModelWithJsonColumn" set 
    "jsonColumn" = jsonb_set("jsonColumn", '{attribute}', to_jsonb('new value'), true), 
    "otherColumn" = "jsonColumn"#>'{extractThisAttribute}'
where "id" = 1 returning *

Also one can use ref() syntax in pretty much every query builder method like in

.select(['id', ref('jsonArrayColumn:[0]')])

or

.where('name', ref('jsonColumn:middleName'))

or even with joins

.join('PetTable', 
      ref('PetTable.jsonColumn:details.name'), 
      '=', 
      ref('ThisTable.someOtherJsonbColumn:dogName'))

score 0 · Answer 3 · answered Mar 12 '14 at 19:22

0

Any-db with the pg extension is working great for me.

answered Mar 12 '14 at 19:22

leszek.hanusz

5,152
2
38
56

Long unsupported and unusable these days. – vitaly-t Mar 11 '17 at 22:59

score -1 · Answer 4 · answered Jul 05 '15 at 21:59

-1

pg-promise is the easiest way to use PostgreSQL with Node JS, which extends node-postgres with promises, for automated connections and transactions.

answered Jul 05 '15 at 21:59

vitaly-t

24,279
15
116
138

score -3 · Answer 5 · edited May 23 '17 at 12:18

I've also searched on answer to this question and found solution like that in related question.

var pg = require("pg");
var Promise = require("bluebird");

Object.keys(pg).forEach(function(key) {
    var Class = pg[key];
    if (typeof Class === "function") {
        Promise.promisifyAll(Class.prototype);
        Promise.promisifyAll(Class);
    }
})
Promise.promisifyAll(pg);

This allows you to use pg with Promises. Details here Manually promisifying pg.connect with Bluebird

What is the best way to use PostgreSQL JSON types with NodeJS

5 Answers5

Linked