WARNING: Work in progress.

Note: This documentation is for dbh-pg v2.x . The v1.x was never documented and is deprecated.

#API Reference

Concepts
Classes
- DBH
- Connection
utils
- sanitize.js
- sql.js

##Concepts

###Parameterized Queries Consists in SQL commands string as 'insert into book (name, author_id) values ($1, $2) ' in which $1 and $2 are placeholders that must be replaced with the real values by the library.

####Placeholders types

#####Index placeholders $1, $2, ... uses an array for replacement. ######Example

'select * from animals where type=$1 and stains > $2'

['cat', 3]

$1 is for the 0-index position in the array.

#####Named placeholders $name, $author_id, etc. Uses an plain object for replacement. ######Example

'select * from animals where type=$type and stains > $stains'

{ type: 'cat', stains: 3 }

Named placeholders have not natively support by PostgreSQL therefore are bypass by the library.

####why? For security reasons. The library uses Prepared Statements and therefore help to be secure against SQL Injection.

###Query Object Is a plain object with these parameters:

optional string name: If is given the created query uses a prepared statement.
string text: The SQL command. Can be an index parameterized query query.
optional array values: An array of string values for replace in the text. Default [].

This object is used by the native pg module.

named query object is a query object in that the name is not empy.

###Result Object Is a plain object with these parameters:

string command:
- The sql command that was executed (e.g. "SELECT", "UPDATE", etc.)
int rowCount:
- The number of rows affected by the SQL statement (more information)
oid:
- the oid returned
array rows:
- an array of rows

pg documentation

###sortRule Object

Is a plain object with these parameters:

string attr:
- The attribute name.
optional boolean asc:
- default: true
- true is the sort is ascending, false is is descending.

This object is used by sql.orderBy.

###Promises TODO

###Security TODO

##Classes

###DBH var DBH = require('dbh-pg')

####object DBH.sanitize Proxy to sanitize. ####object DBH.sql Proxy to sql.

####DBH.prepare(string query) -> Function Creates a function that return a named query object, useful for use Prepared Statements.

string query
- an index parameterized query.

Returns a function in which the parameters are the index placeholders and return a named query object.

See: pg docummentation about prepared statement

#####Examples: Creates a function getAccount(id, pass):

var getAccount = DBH.prepare('select * from account where name=$1 and pass=$2')

Calling the generated function return the named query object:

getAccount('admin', 'admin123')
-> { name: '...', text: 'select * from account where name=$1 and pass=$2', 
values: ['admin', 'admin123'] }

The function returned can be use with conn.exe(query) method:

using(dbh.conn(), function (conn) {
    return conn.exec( getAccount( 'mail@example.com', 'abc123') )
})

####DBH.one([ int index ]) -> Function Return a function that receive a result object and return result.rows[index].

optional int index
- default: 0
- the index of the array to return

#####Usage Example

using(dbh.conn(), function (conn) {
  return conn
    .update({...}, { pk: 2 }, '*')
    .then(DBH.one())
})
.then(function (updatedItem) {
  // instead an array of one item,
  // here we have only the item
})

definition

####DBH.{shorthand}({args}) -> Function

The DBH shorthands are utilities functions for the Connection methods.

Each shorthands returns a function that can be used as fulfilledHandler in a promise.

DBH.{shorthand}({args})
-> function () {  return this.{shorthand}({args}) }

#####Examples: With shorthand (count)

using(dbh.conn(), function (conn) {
    return conn
        .insert('animal', { name: 'Cenala', type: 'cat' })
        .then(DBH.count('animals')) // shorthand count
})

Without shorthand

using(dbh.conn(), function (conn) {
    return conn
        .insert('animal', { name: 'Cenala', type: 'cat' })
        .then(function () {
            return this.count('animals')
        })
})

#####All Shorthands

DBH	Shorthand to...
`DBH.exec`	`.exec`
`DBH.fetchOne`	`.fetchOne`
`DBH.fetchAll`	`.fetchAll`
`DBH.fetchColumn`	`.fetchColumn`
`DBH.fetchScalar`	`.fetchScalar`
`DBH.insert`	`.insert`
`DBH.update`	`.update`
`DBH.delete`	`.delete`
`DBH.exists`	`.exists`
`DBH.count`	`.count`
`DBH.begin`	`.begin`
`DBH.commit`	`.commit`
`DBH.rollback`	`.rollback`
`DBH.done`	`.done`

####new DBH(string conextionString [ , object driver ]) -> DBH Instantiates the database handler.

string conextionString
- a connection string in the format anything://user:password@host:port/database
- a socket path, like /var/run/postgresql
- a socket path, with a specific database, like /var/run/postgresql a_db_name
- a socket connection string socket:/some/path/?db=database_name&encoding=utf8
optional object driver
- The result of call require('pg') or require('pg').native.

DBH is a lightweight wrapper to pg module. This use a pool of connections.

#####Example:

var dbh = new DBH('postgres://postgres@localhost/my_db')

####new DBH(object settings [ , object driver ]) -> DBH Instantiates the database handler.

object settings:
- optional string .user:
  - default value: process.env.USER
  - PostgreSQL user
- optional string .password:
  - default value: null
  - user's password for PostgreSQL server
- optional string .database:
  - default value: process.env.USER
  - database to use when connecting to PostgreSQL server
- optional int .port:
  - default value: 5432
  - port to use when connecting to PostgreSQL server
- optional string .host:
  - default value: null
  - host address of PostgreSQL server
- optional bool .ssl:
  - default value: false
  - whether to try SSL/TLS to connect to server
optional object driver
- The result of call require('pg') or require('pg').native.

#####Example:

var dbh = new DBH({
  user: 'myuser',
  password: 'mypass',
  database: 'mydatabase'
})

###.conn([ object scope ]) -> Promise Get a connection from the pool.

optional object scope
- default value: {}
- The value that is in .scope in ever promise callback.

When you call dbh.conn() you get a promise that will be fulfilled when a connection from the pool of connections is released.

Is extremely important that the last promise call the .done method.

dbh.conn()
    .exec(...)
    .then(DBH.update(...))
    .then(DBH.done(), DBH.done()) // Here we call the .done
                                  // then the connection is
                                  // release to the
                                  // connection pool

The best way to do this is using Promise.using, therefore the conn.done is called automatically.

####Examples: #####Base:

// note that "using" is for "Promise.using"
var DBH = require('dbh-pg'),
    Promise = require('bluebird'),
    using = Promise.using;

using(dbh.conn(), function (conn) {
    // here we have the conn (Connection) object
    
    // You can use the Connection methods, and
    // EVER return the promise
    return conn;
    // or "return conn.exec(DBH.count('users'))", etc...
})

#####Using scope:

this.hi = 'hello'

using(dbh.conn(this), function (conn) {
    return conn.count('users')
        .then(function () {
            console.log(this.scope.hi)
            // output: 'hello'
        })
})

##Connection

###.exec(string query [ , object|array data ]) -> Promise Send the given SQL query to the database.

string query:
- the SQL command to send to the database
optional object|array data:
- The data to use with the query if it is parameterized

Returns a result object

####Example: #####Simple Query:

using(dbh.conn(), function (conn) {
  return conn.exec('select * from book where price <= 20.0')
})
.then(function (result) {
  console.log(result)
  // { rows: [{item1}, ...], rowCount: 0, command: 'SELECT', ...  }
})

#####Indexed Parameterized Query

using(dbh.conn(), function (conn) {
  return conn.exec('select * from book where price <= $1', [20.0])
})
.then(function (result) {
  console.log(result)
  // { rows: [{item1}, ...], rowCount: 0, command: 'SELECT', ...  }
})

#####Named Parameterized Query

using(dbh.conn(), function (conn) {
  return conn.exec('select * from book where price <= $price', {
    price: 20.0
  })
})
.then(function (result) {
  console.log(result)
  // { rows: [{item1}, ...], rowCount: 0, command: 'SELECT', ...  }
})

###conn.exec(object query) -> Promise Execute the query.

See: query object

using(dbh.conn(), function (conn) {
  return conn.exec({
    text: 'select * from book where author_id=$1',
    values : [ 32 ]
  })
})
.then(function (result) {
  console.log(result)
  // { rows: [{item1}, ...], rowCount: 0, command: 'SELECT', ...  }
})

###.fetchOne(string query [ , object|array data ]) -> Promise Shortcut to result.rows[0] of .exec

string query:
- the SQL command to send to the database
optional object|array data:
- The data to use with the query if it is parameterized

Returns the first row as object.

####Example #####With .fetchOne

using(dbh.conn(), function (conn) {
  return conn.fetchOne(
    'select * from planet where habitants > $1 limit 1'
    , [3000]
  )
})
.then(function (planet) {
  console.log(planet)
  // { name: 'Kolobo', habitants:... }
})

#####Without .fetchOne

using(dbh.conn(), function (conn) {
  conn.exec(
    'select * from planet where habitants > $1 limit 1'
    , [3000]
  )
  .then(DBH.one()) // extra step
})
.then(function (planet) {
  console.log(planet)
  // { name: 'Kolobo', habitants:... }
})

tip: Use LIMIT 1 in the SQL. .fetchOne not add this for you.

###.fetchAll(string query [ , object|array data ]) -> Promise Shortcut to result.rows of .exec

string query:
- the SQL command to send to the database
optional object|array data:
- The data to use with the query if it is parameterized

Returns an array of tuplas.

####Example #####With .fetchAll

using(dbh.conn(), function (conn) {
  return conn.fetchAll('select * from planet where habitants > $1', [3000])
})
.then(function (planets) {
  console.log(planets)
  // [{ name: 'Kolobo', habitants:... }, ...]
})

#####Without .fetchAll

using(dbh.conn(), function (conn) {
  return conn.exec('select * from planet where habitants > $1', [3000])
    .then(function (result) {
      // extra step
      return result.rows
    })
})
.then(function (planets) {
  console.log(planets)
  // [{ name: 'Kolobo', habitants:... }, ...]
})

###.fetchColumn(string query [ , object|array data, [ string columnName ] ]) -> Promise

string query:
- the SQL command to send to the database
optional object|array data:
- The data to use with the query if it is parameterized
optional string columnName
- default: the first key in Object.keys(result.row[0])
- The name of the column to return.

In this query:

SELECT name FROM account

The result.rows is:

[{ name: 'Name1'}, { name: 'Name2' }, { name: 'Name3' }, ...]

Instead, .fetchColumn returns

[ 'Name1', 'Name2', 'Name3', ...]

####Example

using(dbh.conn(), function (conn) {
  return conn.fetchColumn('select name from planet where habitants > $1', [3000])
    .then(function (planets) {
      console.log(planets)
      // [ 'Kolobo', 'Saturn', 'Ponnyland' ]
    })
})

###.fetchScalar(string query [ , object|array data, [ string columnName ] ]) -> Promise Shortcut to result.rows[0][columnName] of .exec

string query:
- the SQL command to send to the database
optional object|array data:
- The data to use with the query if it is parameterized
optional string columnName
- default: the first key in Object.keys(result.row[0])
- The name of the column to return.

####Example

using(dbh.conn(), function (conn) {
  conn.fetchScalar('select name from planet where habitants > $1 limit 1', [3000])
    .then(function (name) {
      console.log(name)
      // 'Kolobo'
    })
})

###.insert(string table, object row [ , string returning ]) -> Promise Insert the row in the table.

string table:
- the name of the table in the database.
object row:
- Te row to insert.
optional string returning
- default: undefined
- the data to return. Useful for know autogenerated values.
- is comma separated for each column name, e.g. * or, name,birthday.

If returning is undefined, then return undefined, otherwise an object with the attributes described in returning.

####Examples: #####Simple:

using(dbh.conn(), function (conn) {
  return conn.insert('planet', { name: 'Mart', habitants: 343 })
})

#####To know the autogenerated id:

using(dbh.conn(), function (conn) {
  return conn.insert('planet', { name: 'Mart', habitants: 343 }, 'id')
})
.then(function (planet) {
  console.log(planet.id)
  // 435
})

###.update(string table, object data, object where [ , string returning ]) -> Promise Update the table with the data where match where.

string table:
- the name of the table in the database.
object data:
- the data to update.
object when
- the values to match for updating. e.g. { id: 3 }
optional string returning
- default: undefined
- the data to return. Useful for know autogenerated values.
- is comma separated for each column name, e.g. * or, name,birthday.

If returning is undefined, then return the number of affected rows, otherwise an array with the affected rows.

####Example

using(dbh.conn(), function (conn) {
  conn.update(
    'planet'
    , { name: 'Mart', habitants: 0 }
    , { id: 435 }
  )
})

###.delete(string table, object where [ , string returning ]) -> Promise Update the table with the data where match where.

string table:
- the name of the table in the database.
object data:
- the data to update.
object where
- the values to match for updating. e.g. { id: 3 }
optional string returning
- default: undefined
- the data to return. Useful for know autogenerated values.
- is comma separated for each column name, e.g. * or, name,birthday.

If returning is undefined, then return the number of affected rows, otherwise an array with the affected rows.

####Example

using(dbh.conn(), function (conn) {
  conn.update(
    'planet'
    , { name: 'Mart', habitants: 0 }
    , { id: 435 }
  )
})

###.exists(string table, object where) -> Promise Shortcut for the SQL:

SELECT EXIST (SELECT 1 FROM {table} where {where})

string table:
- the name of the table in the database.
object where
- the values to match e.g. { id: 3 }

Return boolean true the item exitst, false otherwise.

####Example

using(dbh.conn(), function (conn) {
  conn.exists('planet', { id: 1234 })
})
.then(function (exists) {
  console.log(exists)
  // true or false
})

###.count(string table [ , object where ]) -> Promise Count the rows in the table

string table:
- the name of the table in the database.
optional object where
- default: {}
- the values to match to filter rows e.g. { color: 'red' }

Return the number of item that match the where condition.

####Example

using(dbh.conn(), function (conn) {
  conn.count('planet') // count all planets
})
.then(function (count) {
  console.log(count)
  // 342
})

###.begin() -> Promise Start a transaction.

If you not call `.commit then the transaction is auto rollback by the library.

See: Transactions in PostgreSQL

####Example

// send $10.00 from id=10 to id=11
using(dbh.conn(), function (conn) {
  return conn
  .begin() // start a transaction.
  .then(DBH.exec(
    'update wallet \
    set balance=balance-10 where id=10'
  ))
  .then(DBH.exec(
    'update wallet set \
    balance=balance+10 where id=11'
  ))
  .then(DBH.commit())
})

test test autorollback

###.commit() -> Promise Commit a transaction.

Before you must to call .begin. If you not call `.commit then the transaction is auto rollback by the library.

See: Transactions in PostgreSQL

####Example

using(dbh.conn(), function (conn) {
  return conn
  .begin() // start a transaction.
  .then(DBH.exec(
    'update wallet \
    set balance=balance-10 where id=10'
  ))
  .then(DBH.exec(
    'update wallet set \
    balance=balance+10 where id=11'
  ))
  .then(DBH.commit()) // commit the transaction
})

test test autorollback

###.rollback() -> Promise Rollback a transaction.

Before you must to call .begin. If you not call `.commit then the transaction is auto rollback by the library.

See: Transactions in PostgreSQL

####Example

using(dbh.conn(), function (conn) {
  return conn
  .begin() // start a transaction.
  .then(DBH.exec(
    'update wallet \
    set balance=balance-10 where id=10'
  ))
  .then(DBH.exec(
    'update wallet set \
    balance=balance+10 where id=11'
  ))
  .then(function () {
    if (someCondition) {
      return this.commit();
    } else {
      // roolback if someCondition is false
      return this.rollback();
    }
  })
})

test test autorollback

###.done() -> void Return the conextion to the pool of conexions.

To use only if you are not using Promise.using.

Recommendation: Ever use Promise.using, in this way .done is called automatically.

####Examples: #####With Promise.using

using(dbh.conn(), function (conn) {
  return conn.exec('...')
  // We NOT use `conn.done()` because 
  // we are using `Promise.using`.
  // Therefore `.done` is called automatically
  // by the library.
})

#####Without Promise.using

dbh
.conn()
.exec('...')
.then(function () {
  return this.done();
}, function () {
  return this.done();
})
// or .then(DBH.done(), DBH.done())

// Here we use `conn.done` because we
// are not inside the `Promise.using`

##utils

###sanitize.js Utils to sanitize user inputs.

Can be required directed:

var sql = require('dbh-ph/lib/sanitize')

or with DBH:

var DBH = require('dbh-pg'),
    sanitize = DBH.sanitize

test

####.escape(string sql) -> string Escape the given sql.

#####Example:

sanitize.escape('" or 1=1 -- ')
-> '\" or 1=1 -- '

test

####.array(array array, object whitelist) -> array Filter the given array with the whitelist.

array array:
- the array to filter.
object whitelist:
- an object in that the keys are compared with each item in the array.

#####Rules for sanitize One item in the array will be included in the returning array if matches with some key in the whitelist and:

######a) The value of the key in the whitelist is true

sanitize.array(['red', 'blue'], { red: true, blue: false })
-> ['red']

######b) The value of the key in the whitelist is string In this case the ìtem is overwrited by the string.

sanitize.array(['red', 'blue'], { red: true, blue: 'azul' })
-> ['red', 'azul']

######c) The value of the key in the whitelist is a function that returns (a), (b) or (c).

sanitize.array(['name', 'email'], {
  name: 'true',
  email: function (item) {
    // admit the email only if isAdmin is true
    return isAdmin();
  }
})
-> ['name'] // assuming that isAdmin() return false

#####Example: Creation of safe SELECT where the fields come from the user input.

function makeSelect(userInput, isAdmin) {
  var fields = sanitize.array(userInput, {
    name: 'a.name',
    email: function (item) { return isAdmin ? 'a.email' : false }
  })
  return = 'select '
    + fields.join(',')
    + ' from account a'
}

makeSelect(['name', 'pass'], true)
-> 'select u.name from account a'
// because the pass is not in the whitelist, 
// then is not in the returning array

test

####.object(object object, object whitelist) -> object Filter the given object with the whitelist.

object object:
- the object to filter.
object whitelist:
- an object in that the keys are compared with each key in the object.

#####Rules for sanitize One item in the object will be included in the returning object if their key matches with some key in the whitelist and:

######a) The value of the key in the whitelist is true

sanitize.object({ color: 'yellow', type: 'comic sans' }, { color: true, type: false })
-> { color: 'yellow' }

######b) The value of the key in the whitelist is string In this case the key of the item is overwrited by the string.

sanitize.object({ color: 'yellow', type: 'comic sans' }, { color: true, type: 'font' })
-> { color: 'yellow', font: 'comic sans' }

######c) The value of the key in the whitelist is a function that returns (a), (b) or (c).

sanitize.object({ color: 'yellow', type: 'comic sans' }, {
  color: 'true',
  type: function (key, value) {
    // admit the type only if isDesigner is true
    return isDesigner();
  }
})
-> { color: 'yellow' } // assuming that isDesigner() return false

#####Example: Creation of safe SELECT where the WHERE come from the user input.

function makeSelect(userInput) {
  var where = sanitize.object(userInput, {
    name: 'a.name',
    // admit only if the email is from the company
    email: function (key, value) { return /@mycompany/.test(value) ? 'a.email' : false }
  })
  return = 'select a.id, a.name from account a where '
    + sql.toNamed(where)
}

makeSelect({ name: 'Canela', pass: '123', email: 'canela@example.com' })
-> 'select a.id, a.name from account a where a.name=$name'
// because the pass is not in the whitelist, 
// then is not in the returning array.
// email is not because not match with the regExp /@mycompany/

test

####.sort(object sort, object whitelist) -> object TODO

test

###sql.js Utils to create SQL chunks.

Can be required directed:

var sql = require('dbh-ph/lib/sql')

or with DBH:

var DBH = require('dbh-pg'),
    sql = DBH.sql

####.limit(int limit [ , int offset ]) -> string Safe construction of SQL limit string.

int limit
optional int offset

#####Examples:

sql.limit(3, 4)
-> ' LIMIT 3 OFFSET 4 '

sql.limit(3)
-> ' LIMIT 3 '

####.limit(object ctx) -> string Safe construction of SQL limit string.

object ctx:
- optional int *.limit
- optional int .offset

#####Examples:

sql.limit({ limit: 3, offset: 4 })
-> ' LIMIT 3 OFFSET 4 '

sql.limit({ limit: 3 })
-> ' LIMIT 3 '

sql.limit({ })
-> ' '

###.orderBy(array sort) -> string Safe construction of SQL ORDER BY string.

array sort:
- an array of sortRule objects.

####Examples:

sql.orderBy([
    { attr: 'editorial', asc: true },
    { attr: 'name', asc: false }
])
-> ' ORDER BY editorial ASC, name DESC '

sql.orderBy([
    { attr: 'name' }
])
-> ' ORDER BY editorial ASC '

DBH.orderBy([])
-> ' '

###.orderBy(```object``` ctx) -> string Safe construction of sql oder by.

Proxy to:

sql.orderBy(ctx.orderBy || ctx.sort || {})

#####Examples: javascript sql.orderBy({ sort: [{ attr:'name' }] }) -> ' ORDER BY name ASC '

```javascript```
sql.orderBy({  })
-> '  '

####.toNamed(object object [ , string separator [ , string inSeparator ] ]) -> string Format the object to named query chunk.

object object
optional string separator:
- default: 'AND'
optional string inSeparator:
- default: '='

#####Examples:

sql.toNamed({ name: 'Bill', last: 'Puertas' })
-> ' name=$name AND last=$last '

sql.toNamed({ name: 'Bill', last: 'Puertas' }, ',')
-> ' name=$name , last=$last '

definition

####.toIndexed(object object, array refArray [ , string separator [ , string inSeparator ] ]) -> string Format the object to indexed query chunk.

object object
array refArray:
- an array that will be mutated adding the values according to the index in the result string
optional string separator:
- default: 'AND'
optional string inSeparator:
- default: '='

#####Examples:

var arr = []
sql.toIndexed({ name: 'Bill', last: 'Puertas' })
-> ' name=$1 AND last=$2 '

console.log(arr)
-> ['Bill', 'Puertas']

var arr = []
sql.toIndexed({ name: 'Bill', last: 'Puertas' }, ',')
-> ' name=$1 , last=$2 '

console.log(arr)
-> ['Bill', 'Puertas']

definition

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

API.md

API.md

Files

API.md

Latest commit

History

API.md

File metadata and controls