DbClient is an abstract class implementing some methods common to all database clients. Instances of its descendants are available in Job context as resources. By convention, the basic Job's DbClient should be visible as this.db.

const {db} = this

await db.call ('CREATE TEMP TABLE log (id INTEGER PRIMARY KEY, dt DATETIME DEFAULT NOW (), message VARCHAR (255))').exec ()
await db.do ('INSERT INTO log (id, dt, message) VALUES (?, ?)', [1, 'Test']) // call + exec
// await db.insert ('log', {id: 1, message: 'Test'}) // same

await db.doAll ([
  ['UPDATE log SET message = ? WHERE id = ?', params: ['The test', 1]]
])
// await db.do ('UPDATE log SET message = ? WHERE id = ?', ['The test', 1])
// await db.update ('log', {id: 1, message: 'The test'}) // same

const object = await db.getObject ('SELECT * FROM log WHERE id = ?', [1]) // same
// const object = await db.getObject (['log', {filters: [['id', '=', 1](/do-/node-doix-db/wiki/'log',-{filters:-[['id',-'=',-1)}]]) // same
// const object = await db.getObject ('log', [1])

const dt = await db.getScalar ('SELECT dt FROM log WHERE id = ?', [1]) // same
// const dt = await db.getScalar (['log', {columns: ['dt'], filters: [['id', '=', 1](/do-/node-doix-db/wiki/'log',-{columns:-['dt'],-filters:-[['id',-'=',-1)}]])

const slice = await db.getArray (['log', {order: ['id'], limit: 10, offset: 0}](/do-/node-doix-db/wiki/'log',-{order:-['id'],-limit:-10,-offset:-0}))
const total = slice [Symbol.for ('count')]
// const slice = await db.getArray ('SELECT * FROM log ORDER BY id LIMIT ? OFFSET ?', [10, 0])
// const total = await db.getScalar ('SELECT COUNT(*) FROM log ORDER BY id', []) 

const ids = await db.getArray ('SELECT id FROM log', [], {rowMode: 'scalar'})
// const ids = await db.getArray ([['log'], {columns: ['dt']}], [], {rowMode: 'scalar'}) // same

const oStream = await db.getStream ('SELECT * FROM log', [])
// const oStream = await db.getStream (['log'](/do-/node-doix-db/wiki/'log')) // same

const idStream = await db.getStream ('SELECT id FROM log', [], {rowMode: 'scalar'})
// const idStream = await db.getStream ([['log'], {columns: ['dt']}], [], {rowMode: 'scalar'}) // same

Methods

DB Interaction

In two first sections, all methods have the following parameters:

Name	Type	Description
q	String or DbQuery	The query to be executed
p	Array	For a SQL string q, the list of parameters to be bound. For a DbQuery, ignored
options	Object	see DbCall

General purpose

call (q, p, options)

This synchronous method creates and returns a new DbCall instance. No actual request is sent to the database, the DbCall created is left as is for further manipulations. This is the base for all other DB interaction methods.

do (q, p, options)

This asynchronous method executes call (sql, p, options) and returns the DbCall instance after its .exec () is complete and 'finish' is emitted. It's designed mostly for DML, DDL statements like 'INSERT', 'CREATE TABLE' and so on.

For a string valued q, sql = q. Other than that, q may be a [sql, params] array or a {sql, params} object. In the latter case, p is ignored.

options.maxRows is forcibly set to 0 to communicate that no result data are expected.

doAll (statements, streamOptions)

This asynchronous method takes an Iterable (e. g. an array) or a readable stream of objects and executes do with each of them.

The streamOptions are passed to batch (see below).

batch (streamOptions)

This synchronous method returns a writable stream ready to accept {sql, params} objects and execute each sql with the corresonding params.

The streamOptions are passed to the Writable constructor: highWaterMark etc.

Data Fetching

Single Values

getObject (q, p, options)

This asynchronous method executes call (q, p, options) and returns only the first record of the result set.

Here, q and p may also be the name of a table and the array of its primary key values.

options.minRows and options.maxRows are forcibly set to 1.

For an empty result, options.notFound is returned if it was set explicitly. Otherwise, an Error is thrown.

getScalar (q, p, options)

This asynchronous method executes call (q, p, options) and returns only the first field of the first record of the result set.

options.minRows and options.maxRows are forcibly set to 1, and options.rowMode to 'scalar'.

For an empty result, options.notFound is returned, undefined by default. Unlike getObject, getScalar never throw Errors when no data are found.

Result Sets

Objects returned by methods described in this section have the following non-enumerable properties (if applicable):

Name	Type	Description
Symbol.for ('call')	DbCall	the call behind this result
Symbol.for ('columns')	[{name, type}]	result column definitions
Symbol.for ('query')	DbQuery	copy of q, if it was a DbQuery
Symbol.for ('count')	int	SELECT COUNT (*)... with LIMIT omitted

So, for example, rs = getArray (sql) returns a regular JS Array rs, yet the source SQL is available via rs [Symbol.for ('call')].sql.

getStream (q, p, options)

This asynchronous method executes call (q, p, options) and returns the corresponding result set a Readable stream.

options.maxRows is forcibly set to Infinity to communicate that the stream result is expected.

The stream returned is always in object mode, but the nature of objects depends on options.rowMode: other than default name-value POJOs, it may be flat Arrays of field values (rowMode: 'array') or arbitrary JS values representing the 1st column (rowMode: 'scalar').

In the latter case, due to a node.js streams limitation, NULLs are represented by special Symbol.for ('NULL') values instead of plain nulls.

getArray (q, p, options)

This asynchronous method executes call (q, p, options) and returns the corresponding result set an Array.

options.maxRows is guaranteed to be a finite positive number, 1000 unless provided.

By default, records are represented by name-value POJOs, but this may be changed to flat Arrays of field values (rowMode: 'array') or arbitrary JS values representing the 1st column (rowMode: 'scalar').

If q is a DbQuery instance with offset option, an additional SELECT COUNT... (query.toCountingQuery) query is executed and its result is published as the Symbol.for ('count') property.

Data modification

In this section, the following parameter names are in use:

Name	Type	Description
tableName	String	The name of a table
record	Object	A data record
records	[Object]	An array of data records
recordStream	Readable	An object mode readable of data records
options	Object	Extra parameters, mostly vendor dependent

insert (tableName, record[s], options)

The convenience method invoking insertRecord, insertArray or insertStream based on the type of the 2nd argument.

insertRecord (tableName, record, options)

This is a superposition of genInsertParamsSql and this.do (sql, params). Inserts a single record in the specified table. In the base implementation, returns the corresponding DbCall instance.

insertArray (tableName, records, options)

Insert an array of records in the specified table. In the base implementation, uses insertStream.

insertStream (tableName, recordStream, {columns,...})

Pipes a readable object stream into the specified table. Based on the putObjectStream (tableName, columns, options) method absent from the base implementation. For each backend, those methods return writable object streams emitting a non standard complete event upon each successful operation. The column option, if provided, must contain the array of column names to be written. If absent, this list is calculated from the first record read: in this case it must contain all necessary fields, or a data loss is possible. In the base implementation the return value is undefined.

update (tableName, record, options)

This is a superposition of genUpdateParamsSql and this.do (sql, params).

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) that must contain:

• all primary key (pk) components;
• all properties with known new values.

undefined and unknown properties are silently ignored.

nulls for non-nullable columns mean reset to DEFAULT values.

Other

createMigrationPlan ()

This synchronous method creates a new DbMigrationPlan instance bound to this database connection.

const plan = this.db.createMigrationPlan ()

createTempTable (tableOrName, options)

This asynchronous method creates a temporary table by definition. options.onlyIfMissing triggers IF NOT EXISTS addition.

toCsv ()

This synchronous method creates a new DbCsvPrinter with the given set of options.

myReadableObjectStream.pipe (db.toCsv () ({
   table: db.model.find ('users'),
   columns: ['uuid', 'label'],
// columns: {uuid: 'UUID!', label: 'VARCHAR (255)'}, // ad hoc definition
// NULL: '\\N',
})).pipe (myWritableBinaryStream)

Properties

Name	Type	Description
pool	DbPool	what brought up this connection
model	DbModel	the database structure
lang	DbLang	the database language dialect
count	Number	The number of times call was invoked
txn	Object	Vendor dependent info about the transaction in progress: null by default and after successful COMMIT/ROLLBACK, it maybe just {} after BEGIN or may contain some information about isolation level, savepoints etc.