fiatjaf/pgjson

use Postgres as a zero-config NoSQL database.

PGJSON: start saving data to Postgres without thinking about schema

Welcome to PGJSON!

A simple, zero-config, API for saving and retrieving JSON documents in a Postgres database. Just import and start using.

Features

• Support for PUT, POST, GET and DEL operations
• Support for listing all docs and all _ids
• Very basic querying API with filtering and ordering
• You can start using this and later create indexes on specific JSON fields, write your own complex queries, create views or materialized views mixing the data in the pgjson.main table with other tables in the same database, or even export all data to other tables with formats and schemas, then forget about pgjson, Postgres is powerful and this library does not intend to stay between your data and all this power.

Example

var Promise = require('bluebird')
var db = new (require('pgjson'))('postgres:///db')

Promise.resolve().then(function () {
  return db.post({
    name: 'tomato',
    uses: ['tomato juice']
  })
})
.then(function (res) {
  console.log(res) /* {ok: true, id: 'xwkfi23syw'} */
  return db.get(res.id)
})
.then(function (doc) {
  console.log(doc) /* {_id: 'xwkfi23syw', name: 'tomato', uses: ['tomato juice']} */
  doc.uses.push('ketchup')
  doc.colour = 'red'
  return Promise.all([
    db.put(doc),
    db.post([{name: 'banana', colour: 'yellow'}, {name: 'strawberry', colour: 'red'}])
  ])
})
.then(function (res1, res2) {
  console.log(res1) /* {ok: true, id: 'xwkfi23syw'} */
  console.log(res2) /* {ok: true, ids: ['xios83bndf', 'dx83hsalpw']} */
  return db.query({
    filter: 'colour = "red"',
    orderby: 'name',
    descending: true
  })
})
.then(function (docs) {
  console.log(docs) /* [{_id: 'xwkfi23syw', name: 'tomato', uses: ['tomato juice', 'ketchup'], colour: 'red'},
                        {_id: 'dx83hsalpw', name: 'strawberry', colour: 'red'}] */
  return db.del(docs[0]._id)
})
.catch(console.log.bind(console))

In the meantime:

postgres=> SELECT * FROM pgjson.main;
     id     |                                doc
------------+-------------------------------------------------------------------
 xwkfi23syw | {"_id": "xwkfi23syw", "name": "tomato", "uses": ["tomato juice"]}
(1 row)
postgres=>
postgres=> select * from pgjson.main 
;
            id             |                                       doc                                        
---------------------------+----------------------------------------------------------------------------------
 xwkfi23syw | {"_id": "xwkfi23syw", "name": "tomato", "uses": ["tomato juice", "ketchup"]}
 xios83bndf | {"_id": "xios83bndf", "name": "banana", "colour": "yellow"}
 dx83hsalpw | {"_id": "dx83hsalpw", "name": "strawberry", "colour": "red"}
(3 rows)

Basically this.

---

API

new PGJSON(options): DB

options is anything pg can take: a connection string, a domain socket folder or a config object. See the link for more details.

DB.get(string or array): Promise -> doc

accepts the id, as string, of some document as a parameter, or an array of ids, and returns a promise for the raw stored JSON document. If passed an array of ids, the promise resolves to an array filled with the documents it could find, or null when it could not, in the correct order. If passed a single id and the target document is not found the promise resolves to null.

DB.post(object or array): Promise -> response

accepts an object or an array of objects corresponding to all the documents you intend to create. Saves them with a random ._id each and returns a promise for a response which will contain {ok: true, id: <the random id>}. If an array of objects was passed, instead returns {ok: true, ids: [<id>, <id>...]} with the ids in the correct order. If any passed object has an _id property this property will be discarded.

DB.put(document): Promise -> response

same as .post, but instead of creating a new document with a random id, this expects a complete document, which is to say an object with an _id property. Updates (or creates, if none is found) the document with the specified id.

DB.del(string or array): Promise -> response

accepts an id, as string, or an array of strings corresponding to all the keys you want to delete. The response is an object of format {ok: true}.

DB.query(query_params): Promise -> array

accepts an object with the following optional parameters:

• filter: a string specifying a condition to match with the document. The left condition should be the path of the attribute in the document; and the right a valid JSON value. Examples:
  • '_id = "mellon"'
  • 'properties.age' = 23
  • 'children[2].name = "Jessica"'
• orderby: a string with the path of the desired attribute in the document. Examples:
  • 'name'
  • 'items[0]'
  • 'billing.creditcard.visa.n'
• descending: true or false -- default: false

DB.allIds(): Promise -> array

returns a promise to an array of ids (strings) of all documents stored in the database.

DB.count(): Promise -> integer

returns a promise to an integer with the count of all documents stored in the database.

DB.purgeAll(): Promise -> null

deletes everything: table, rows, schema. This isn't supposed to be used.

DB.init(): Promise -> null

creates a schema called pgjson, a table called main and a function called upsert. This is idempotent and is called automatically when the DB is instantiated.

pgjson's Issues

Refactoring + suggestions

Your library is fresh, but got some good attention already. There is a bit of code refactoring you're likely to do later on. And as far as the database goes, I'd like to help you ;)

You are using wait everywhere, but it doesn't do anything useful.

this.wait = Promise.resolve()

Some of the code I'm not sure how it is expected to work on your side, like this example:

db.many('SELECT doc FROM unnest(ARRAY[$1]) WITH ORDINALITY AS u(id, ord) LEFT JOIN pgjson.main AS m ON m.id = u.id ORDER BY u.ord', [ids])

When your parameter ids is an array, it will be automatically formatted as array[va1,val2,...], so when you are inserting it as ARRY[$1], it will produce ARRAY[array[val1, val2,...]]. I'm not sure if that's the expected behaviour, so I just wanted to point out at it.

Another example for refactoring, method manyOrNone has alias any, in case you want to use it ;)

If you want to get a good sense of what and how is being executed exactly against the database, I would really suggest to use pg-monitor, and here's an example.

jsonb_set

use the new jsonb_set function for granular updates.

https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.5#jsonb_set_function

Using multi table and Joins possible?

This looks like a nice library. Thanks for your contribution.

From the Readme, it looks like data is stored in "main" table. Is it possible to store in different tables? And cross table joins possible as well?

Typo in README example

In the README example, it seem like the first db.post call is returning an object with an id, and not an _id, like the other api calls.

Promise.resolve().then(function () {
  return db.post({
    name: 'tomato',
    uses: ['tomato juice']
  })
})
.then(function (res) {
  console.log(res) /* {ok: true, id: 'xwkfi23syw'} */
  ...

Shouldn't that id be _id on the last line quoted there?

Debug logging

The following line isn't the best way to provide debug logging:

query: process.env.DEBUG ? function (e) { console.log(e.query) } : undefined

The standard way in Node JS to determine the DEV environment is like this:

process.env.NODE_ENV === "development"; // => DEV environment, do detailed logging;
process.env.NODE_ENV === "production"; // => PROD environment, log errors only;

Also, I would suggest using pg-monitor for DEV environment, as it shows all DB communications, which you can also log separately, if you want, via log event. See complete example.

beware of SQL injection

There are two dangerous points: here, when inserting multiple values and here, when querying.

These could be fixed by using functions and passing the values -- escaped -- to these functions. It is a matter of time until I fix them, but I still want suggestions of a better way to handle these cases and/or the better way to write these functions.

pg-promise refactoring

Just a hint, to consider refactoring this code:

this.wait.then(function () {
    return db.query(
        'INSERT INTO pgjson.main (id, doc) VALUES ($1, $2) RETURNING id',
        [doc._id, doc]
    )
})
    .then(function (rows) {
        return {ok: true, id: rows[0].id}
    });

into this one:

this.wait.then(function () {
    return db.one(
        'INSERT INTO pgjson.main (id, doc) VALUES ($1, $2) RETURNING id',
        [doc._id, doc]
    )
})
    .then(function (main) {
        // when resolves, it is always ok, pointless passing it again ;)
        return {ok: true, id: main.id}
    });

i.e. when you are expecting exactly one row of data to be returned, method one is more appropriate ;)