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.

Answer 1

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

Answer 2

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'))