DbClientPg

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

Methods

See parent class

insert (tableName, record, options)

This inherited method supports an extra option:

Name	Description
onlyIfMissing	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)

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.

putStream (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

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

  const 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'    
    }
  )

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

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

}