pg-model

Takes a relational database structure and returns model objects for noSQL-like abilities.

Install

$ npm install pg-model --save

Usage

constpg=require('pg')constpgInfo=require('pg-info')// Make a new Postgres clientconstclient=newpg.Client('postgres://postgres:postgres@localhost:5432/my_test_db')client.connect()//Now get the structure of the 'space' database schemapgInfo({client: client,schemas: ['space',]},function(err,dbStructure){// 'dbStructure' describes the content of the 'space' schema// (i.e. tables, columns, indexes, foreign-key constraints etc.)// Now make some models from that description...models=pgModel({client: client,dbStructure: dbStructure})// We've now one-model-per table.// So, assuming the 'space' schema contained tables named 'planets', 'moons' and 'craters'...// This sort of thing is possible...models.space.planets.create({name: 'mars',title: 'Mars',type: 'Terrestrial',diameter: 6700,color: 'red',url: 'http://en.wikipedia.org/wiki/Mars',moons: [{title: 'Phobos',discoveredBy: 'Asaph Hall',discoveryYear: 1875,craters: [{title: 'Stickney',diameter: 10}]},{title: 'Deimos',discoveredBy: 'Asaph Hall',discoveryYear: 1875}]},{}).then(()=>{{// * Four rows have been inserted amongst the 'space.planets', 'space.moons' and 'space.craters' tables// * PostgreSQL's column defaults have been used to populate the missing primary key values// * The foreign-key values for 'space.moons' and 'space.craters' have been auto-filled by// inspecting FK constraints})})

API

Each model offers the following methods.

create (jsonData, options)

Inserts the supplied JSON documents into relational tables.
Resolves to the document's id properties.

Example

models.hr.people.create({employeeNo: 1firstName: 'Homer',lastName: 'Simpson',age: 39},{}).then(idProperties=>{// idProperties ==// {// idProperties:// {// employeeNo: 1// }// }})

findById (id)

Finds one 'document' by ID - all nested docs will be assembled too.

Example

models.hr.people.findById(1).then(doc=>{// doc ==// {// employeeNo: 1,// firstName: 'Homer',// lastName: 'Simpson',// age: 39,// created: 2017-06-02T22:00:55.221Z,// createdBy: null,// modified: 2017-06-02T22:00:55.221Z,// modifiedBy: null // }})

find (options)

Find zero-or-more docs - can be filtered, ordered, paginated etc. Resolves to the found document array.

Example

models.hr.people.find({where: {firstName: {equals: 'Homer'},lastName: {equals: 'Simpson'}}}).then(docs=>{// docs ==// [// {// employeeNo: 1,// firstName: 'Homer',// lastName: 'Simpson',// age: 39,// created: 2017-06-02T22:00:55.221Z,// createdBy: null,// modified: 2017-06-02T22:00:55.221Z,// modifiedBy: null // }// ]})

findOne (options)

Like find but resolves to a single doc.

Example

models.hr.people.findOne({orderBy: ['age'],nullsLast: true,offset: 1}).then(doc=>{// doc ==// {// employeeNo: 1,// firstName: 'Homer',// lastName: 'Simpson',// age: 39,// created: 2017-06-02T22:00:55.221Z,// createdBy: null,// modified: 2017-06-02T22:00:55.221Z,// modifiedBy: null // }})

update (doc, options)

Updates a single 'document'. The top-level primary key is inferred from the data - automatically inserts/updates/deletes nested docs.

Example

models.hr.people.update({employeeNo: 1,firstName: 'Homer',lastName: 'Simpson',age: 39},{}).then(()=>{/* All done */})

patch (doc, options)

Same as update, but any omitted properties will be retained (i.e. they won't be turned into null values like update will).

Example

models.hr.people.patch({employeeNo: 1,age: 39},{}).then(()=>{/* All done */})

upsert (doc, options)

A combination of create and update. If a document already exists then upsert will update it, else it'll create it.

Example

models.hr.people.upsert({employeeNo: 1,firstName: 'Homer',lastName: 'Simpson',age: 39},{}).then(idProperties=>{// idProperties ==// {// idProperties:// {// employeeNo: 1// }// } })

destroyById (id)

Deletes one 'document' by ID - all nested docs will be cascade-deleted too.

Example

models.hr.people.destroyById(1).then(()=>{/* All done */})

parseDoc (doc, options)

Takes a single doc and parses it into a form that's usable by several of the other methods.

Example

constparsedDoc=models.hr.people.parseDoc({employeeNo: 1,firstName: 'Homer',lastName: 'Simpson',age: 39},{includeNullFks: false})

Testing

Before running these tests, you'll need a test PostgreSQL database available and set a PG_CONNECTION_STRING environment variable to point to it, for example:

PG_CONNECTION_STRING=postgres://postgres:postgres@localhost:5432/my_test_db

$ npm test

License

MIT