pg-model
Takes a relational database structure and returns model objects for noSQL-like abilities.
Install
$ npm install pg-model --save
Usage
const pg = require('pg')
const pgInfo = require('pg-info')
// Make a new Postgres client
const client = new pg.Client('postgres://postgres:postgres@localhost:5432/my_test_db')
client.connect()
//Now get the structure of the 'space' database schema
pgInfo(
{
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.
jsonData
, options
)
create (Inserts the supplied JSON documents into relational tables.
Resolves to the document's id properties.
Example
models.hr.people.create(
{
employeeNo: 1
firstName: 'Homer',
lastName: 'Simpson',
age: 39
},
{}
).then(idProperties => {
// idProperties ==
// {
// idProperties:
// {
// employeeNo: 1
// }
// }
}
)
id
)
findById (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
// }
}
)
options
)
find (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
// }
// ]
}
)
options
)
findOne (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
// }
}
)
doc
, options
)
update (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 */ })
doc
, options
)
patch (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 */ })
doc
, options
)
upsert (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
// }
// }
}
)
id
)
destroyById (Deletes one 'document' by ID - all nested docs will be cascade-deleted too.
Example
models.hr.people.destroyById(1)
.then(() => { /* All done */ })
doc
, options
)
parseDoc (Takes a single doc and parses it into a form that's usable by several of the other methods.
Example
const parsedDoc = 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