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

Question

Here I came across some analysis paralysis. There are so many database programming options with NodeJS that I am a little lost.

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

Unfortunately, I cannot find any library for PostgreSQL using the new JSON data type or exposing it. I was thinking about using Sequelize and had a good ORM or rolled my own things using the pgsql source module.

Can someone shed a key? I would ask about it in some NodeJS stackexchange, but I don’t think we have such a specific one like this.

Answer 1

I like https://github.com/brianc/node-postgres . It is actively developing and just a nice thin layer.

To use json types in a prepared request, just JSON.stringify everything you try to save as json (this is what postgres wants anyway).

Answer 2

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

Answer 3

Objection.js really supports relational as well as for JSONB data .

You do not need to do any tricks for parsing / formatting json data. All this is done automatically. You can declare schemes that allow you to check the data that you are going to place in the database, etc.

You can insert nested hierarchies of relational objects into the database, and the rows will be generated to correct the tables, and you have a javascript API for querying data inside JSON columns, so you do not need to write RAW SQL for this.

EDIT:

I have no idea why the voices given here (currently -2), Objection.js still have the best support for Postgresql JSONB operations in the node world (and the only choice in current answers that has special support for postgresql jsonb).

The latest addition was support for fixing only a part of the data inside the JSONB column, where objection.js automatically creates jsonb_set() calls for you.

eg:

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

will create an update request as follows:

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

You can also use the ref() syntax in almost every query building method, such as

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

or

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

or even with compounds

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

Answer 4

Any-db with the pg extension works fine for me.

Answer 5

I also searched for the answer to this question and found a solution in this 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 Promise s. More info here Manually promises pg.connect with Bluebird