postgresjs

postgresjs
=================
PostgreSQL database wrapper that provides helpers to query the database.

* Select, Insert, Update and Delete Query Builders
* Merge Command allowing insert or update in one command
* Suspend integration for generator-based async control-flow
* Idle Connection Auto Closer
* No transpiling required

$3

* ECMAScript 2015 (ES6)
* Node.JS 6.2.2 or later (tested on 6.2.2)

$3

https://cdn.rawgit.com/phanxgames/postgresjs/master/jsdoc/

$3

``
npm install postgresjs
`

Copy the dbConfig.ex.json file into your project source folder, rename to dbConfig.json,
and update with your database connection information.

$3

All methods that include a callback (cb) have been designed to be used with the
suspend library and may be placed behind a yield command. Be sure to leave the cb
parameter null to use the suspend.resume functionality automatically.

IMPORTANT: You will also need to set the resume reference in the constructor or the
setResume() method, before the suspend.resume functionality will
be enabled.

If you do provide a callback, the 3rd parameter, "next" (ex: cb(err,result, next))
will be the suspend.resume function reference so you may resume execution
to move past the next yield command.

$3

`
var suspend = require("suspend");
var Postgresjs = require("postgresjs");

//Attach your dbConfig to the Postgresjs module
Postgresjs.config = require('./dbConfig.json');

var db = new Postgresjs(suspend.resume);

suspend(function*() {
yield db.start();

//... place query methods here ...

yield db.end();
})();
`

$3

There are 3 ways to loop over the resulting rows of a query.

1: Standard callback.

`
yield db.query("select email from friends where user_id=? ;",[userid],
function(err,rows,cbResume) {
for (let row of rows) {
console.log(row.email);
}

//move past the yield
cbResume();
});
`

2: Rows Getter.

`
yield db.query("select email from friends where user_id=? ;",[userid]);

for (let row of db.rows) {
console.log(row.email);
}
`

3: Async Looping (non-blocking)

`
yield db.query("select email from friends where user_id=? ;",[userid]);

yield db.asyncForEach(function(index,row,cbNext) {
console.log(row.email);
cbNext();
});

`

$3

After every query you should check if it was an error.
`
if (db.error()) {
console.error("Database error: ",db.error());
return;
}
`

And you may also want to check how many rows were returned before looping.
`
if (db.rowCount > 0) {
//.. loop

} else {
console.log("No rows found.");
}
`

$3

Enabling auto closer in the dbConfig.json file allows database connections that you
leave open to be automatically close after a timeout interval provided in minutes.

By default this is not enabled, however you may want to keep this enabled and watch the
console to see if the auto closer picks up on any open connections so you can address
it and proprely close it when you are done.

$3

Conditionally insert or update a row.
If the row exists, update the columns; otherwise, insert as a new row.

See Merge Helper below.

$3

The Helpers will help you build SQL statements and provide parameterized values which
safeguard your queries from SQL injections. Any property that is labled as "value" will
be converted to a parameter internally.

##### Select Helper

`
//..
yield db.selectHelper({
table:"users",
columns:["username","email"],
where: db.whereHelper({
"username -like":"t%"
}),
orderBy: db.orderByHelper({
"email":"ASC"
})
});

for (let row of db.rows) {
console.log(row);
//Output example: {username:"Tester",email:"test@test.com"}
}
//..
`

##### Insert Helper
`
//..
yield db.insertHelper({
table:"users",
columns:{
"username":"tester",
"email":"oldemail@test.com"
}
});
//..
`

##### Update Helper
`
//..
yield db.updateHelper({
table: "users",
columns: {
"email":"newemail@test.com"
},
where: db.whereHelper({
"username":"tester"
})
});
//..
`

##### Delete Helper
`
//..
yield db.deleteHelper({
table: "users",
where: db.whereHelper({
"username":"tester"
})
});
//..
`

##### Merge Helper
`
//..
yield db.mergeHelper({
table: "users",
columns: {
"username":"tester",
"email":"test@test.com"
},
where: db.whereHelper({
"username":"tester"
})
});
//..
``

$3

- node-postgres
- suspend
- dictionaryjs