DbClientPg

DbClientPg is a DbClient descendant for PostgreSQL: a wrapper above pg for using in doix Jobs.

Methods

See parent class

General

invoke (routineName, params)

Calls a user defined function or procedure (described by the model). Returns the result for a DbFunction, a DbCall instance () otherwise.

Data manipulation

insertRecord (tableName, record, options)

This inherited method supports an extra option:

Name	Type	Description
onlyIfMissing	Boolean	If true, the INSERT statement is appended with the ON CONFLICT DO NOTHING clause, so in case of any unique key violation no data is changed at all. (Note: the ON CONFLICT DO UPDATE feature is available through the use of the upsert method)
result	String	Type of the expected return value (see below)

result options

Name	Default	Return value
call	unless onlyIfMissing	The DbCall instance, as per the inherited implementation
status	when onlyIfMissing	true if a new record was inserted, false otherwise
record		The record just created, if any, undefined otherwise

upsert (tableName, record, options)

This asynchronous method asserts that there exists a record in a given table with given values.

The record may be created or updated to the new state. Technically, an INSERT ... ON CONFLICT ... DO UPDATE statement is executed. (Note: the ON CONFLICT DO NOTHING feature is available through the use of the insert method with the onlyIfMissing option).

await db.upsert (
  'user_options', 
   {
     id_user: 1, 
     id_option: 10, 
     value: true.
   }, 
   {key: [
     'id_user', 
     'id_option',
   ]}
)

The 1st argument is the name of a DbRelation (most probably DbTable) in a db.model.

The 2nd agrument is the data object: the record to be stored. Normally, its properties must correspond by name to the columns. Unknown properties are silently ignored. And so are undefined properties -- unlike null valued fields that do appear in the generated SQL (and may cause errors when set to NOT NULL columns).

The 3rd agrument is the object containing the key option: the array of column names to be mentioned in the ON CONFLICT clause.

putObjectStream (tableName, columns, options)

This asynchronous method returns an instance of DbCsvPrinter piped into the putBinaryStream (tableName, columns, options) result. So, this is a writable object stream ready to accept records to be inserted into tableName. Only columns will be written, other records' fields are ignored.

putBinaryStream (tableName, columns, options)

This asynchronous method returns a binary Writable stream corresponding to a COPY FROM STDIN statement.

The result is an instance of a class provided by pg-copy-streams.

const {db} = this, os = await db.putStream (
  'payments'
  , ['article', 'amount'] // mandatory
  , {                     // optional; names are case insensitive
//  FORMAT: 'text',
//  FREEZE: false,
//  DELIMITER: '\t',
//  NULL '',
//  HEADER: false,
//  QUOTE: '"',
//  ESCAPE: '\',
//  FORCE_QUOTE: ['article'], // '*'
//  FORCE_NOT_NULL: ['article'],
//  FORCE_NULL: ['article'],
//  ENCODING: 'utf-8'    
  }
)

await new Promise ((ok, fail) => {

  os.on ('error', fail)
  os.on ('finish', ok)

  fs.createReadStream ('/tmp/payments.txt').pipe (os)

}

Transaction support

begin ()

This asynchronous method executes BEGIN and sets the txn property to {}.

commit ()

This asynchronous method executes COMMIT and sets the txn property to null.

rollback ()

This asynchronous method executes ROLLBACK and sets the txn property to null.

Special

getStreamOfExistingTables ()

This asynchronous method fetches the list of all tables of a currently connected database as a Readable stream of DbTable objects.