Db

Db

Simple database class for mysql

Constructor

new Db(configopt, sshConfigopt)

Specify connection details for MySQL and optionally SSH

Source:
See:
Parameters:
Name Type Attributes Default Description
config Object <optional>

MySQL connection details such as host, login, password, encoding, database
sshConfig Object <optional>
 null

SSH connection details including host, port, user, privateKey

Members

(static) instances :Array

A list of all the Db instances that have been created

Source:
Type:
  • Array

config :Object

The config used for this instance

Source:
Type:
  • Object

connection :Object

The mysql2 library connection object

Source:
Type:
  • Object

ssh :Ssh

The Ssh instance for tunnelling

Source:
Type:

Methods

(static) destroyAll() → {Db}

Destroy all connections to the database

Source:
Returns:
Type:
Db

(static) endAll() → {Promise}

Close all connections to the database

Source:
Returns:
Type:
Promise

Resolves when all connections have been closed

(static) factory(configopt, sshConfigopt) → {Db}

Create a new QuickDb instance or return the last used one. Specify connection details for MySQL and optionally SSH

Source:
See:
Parameters:
Name Type Attributes Default Description
config Object <optional>

MySQL connection details such as host, login, password, encoding, database
sshConfig Object <optional>
 null

SSH connection details including host, port, user, privateKey
Returns:
Type:
Db

(async, static) withInstance(configopt, sshConfigopt, handler) → {Promise.<(Error|*)>}

Run the given handler by passing a new database instance. Three signatures: Db.withInstance(handler) Db.withInstance(mysqlConfig, handler) Db.withInstance(mysqlConfig, sshConfig, handler)

Source:
Parameters:
Name Type Attributes Description
config Object <optional>

The mysql connection information (or omit to read from env)
sshConfig Object <optional>

The ssh config information (or omit to read from env)
handler function

The function to pass the Db instance to
Returns:
Type:
Promise.<(Error|*)>
Example
const addresses = await Db.withInstance(async db => {
  	const sql = 'SELECT * FROM animals WHERE type = "cat"';
     const { results: cats } = await db.select(sql);
     const homes = await findHomes(cats);
     return {
         homes: homes.map(home => home.address);
     };
  });

beginTransaction() → {Promise.<Object>}

run START TRANSACTION

Source:
Returns:
Type:
Promise.<Object>

bindArgs(sql, args) → {Object}

Bind an arguments to a query

Properties:
Name Type Description
sql String

The final SQL with bound values replaced
Source:
Parameters:
Name Type Description
sql String | Object

The base SQL query
args *

A value, an object with key/value paris, or an array of values to bind
Returns:
Type:
Object
Example
db.select('SELECT * FROM users WHERE id = ?', 100);
db.bindArgs('SELECT * FROM users WHERE id = ?', 100); // SELECT * FROM users WHERE id = '100'
db.select('SELECT * FROM users WHERE id = :id', { id: 100 });
db.bindArgs('SELECT * FROM users WHERE id = :id', { id: 100 }); // SELECT * FROM users WHERE id = '100'

commit() → {Promise.<Object>}

run COMMIT

Source:
Returns:
Type:
Promise.<Object>

(async) connect(overridesopt) → {Promise.<Object>}

Make a new connection to MySQL

Source:
See:
Parameters:
Name Type Attributes Description
overrides Object <optional>

Additional connection params
Returns:
Type:
Promise.<Object>

The mysql connection object

(async) connectOnce()

Make a new connection to MySQL if not already connected

Source:

(async) delete(sql) → {Promise.<Object>}

Run the given DELETE statement

Properties:
Name Type Description
query String

The final SQL that was executed
changedRows Number

The number of rows affected by the statement
Source:
Parameters:
Name Type Description
sql String | Object

The SQL to run
...bindVars *

The values to bind to the each question mark or named binding
Returns:
Type:
Promise.<Object>

(async) deleteFrom(table, where, limit) → {Promise.<Object>}

Construct a DELETE query and run

Properties:
Name Type Description
query String

The final SQL that was executed
affectedRows Number

The number of rows matching the WHERE criteria
changedRows Number

The number of rows affected by the statement
Source:
See:
  • SqlBuilder#buildWheres
Parameters:
Name Type Default Description
table String

The name of the table from which to delete
where Object

WHERE conditions on which to delete - see SqlBuilder#buildWheres
limit Number null

Limit deletion to this many records
Returns:
Type:
Promise.<Object>

destroy() → {Db}

Destroy the connection to the database

Source:
Returns:
Type:
Db

emit(type, data) → {DbEvent}

Emit an event and associated data

Source:
Parameters:
Name Type Description
type String

The event name
data Object

Data to send with event
Returns:
Type:
DbEvent

emitError(subtype, error, data) → {DbEvent}

Emit a dbError event and associated data

Source:
Parameters:
Name Type Description
subtype String

The name of the event that would have been called in a success case
error Error

The error that was raised
data Object

Data to send with event
Returns:
Type:
DbEvent

end() → {Promise}

Close this connection to the database

Source:
Returns:
Type:
Promise

Resolves when connection has been closed

escape(value) → {String}

Escape a value for use in a raw query and surround with apostrophes

Source:
Parameters:
Name Type Description
value *

The value to escape
Returns:
Type:
String

escapeLike(infix, value) → {String}

Get the proper escaping for a LIKE or NOT LIKE clause

Source:
Parameters:
Name Type Description
infix String

One of ?% or %?% or %? or ?
value String

The value to search for
Returns:
Type:
String

escapeQuoteless(value) → {String}

Escape a value for use in a raw query without apostrophes

Source:
Parameters:
Name Type Description
value String | Number | Boolean | null

The value to escape
Returns:
Type:
String

(async) exportAsSql(table, where, options) → {Promise.<Object>}

Construct INSERT statements suitable for a backup

Properties:
Name Type Attributes Default Description
limit Number <optional>
 0

Limit export to this many records
chunkSize Number <optional>
 250

If > 0, restrict INSERT STATEMENTS to a maximum of this many records
discardIds Boolean <optional>
 false

If true, columns selected as "id" will have a NULL value
disableForeignKeyChecks Boolean <optional>
 false

If true, add statements to disable and re-enable foreign key checks
lockTables Boolean <optional>
 false

If true, add statements to lock and unlock tables
results String

The exported SQL
query String

The SQL that was executed to fetch records
fields Array.<Object>

Details on the fields fetched
affectedRows Number

The number of rows matching the WHERE criteria
chunks Number

The number of chunks of rows
Source:
See:
  • SqlBuilder#buildWheres
Parameters:
Name Type Description
table String

The name of the table from which to fetch records
where Object

WHERE conditions on which to fetch records - see SqlBuilder#buildWheres
options Object

Additional options
Returns:
Type:
Promise.<Object>

(async) insert(sql) → {Promise.<Object>}

Run the given INSERT statement

Properties:
Name Type Description
query String

The final SQL that was executed
insertId Number

The id of the last inserted record
Source:
Parameters:
Name Type Description
sql String | Object

The SQL to run
...bindVars *

The values to bind to the each question mark or named binding
Returns:
Type:
Promise.<Object>

insertExtended(table, rows) → {Promise.<Object>}

Build an INSERT statement and run it

Properties:
Name Type Description
query String

The final SQL that was executed
insertId Number

The id of the last inserted record
Source:
Parameters:
Name Type Description
table String

The name of the table
rows Array

An Array of objects, each with column-value pairs to insert
Returns:
Type:
Promise.<Object>

insertInto(table, insert) → {Promise.<Object>}

Build an INSERT statement and run it

Properties:
Name Type Description
query String

The final SQL that was executed
insertId Number

The id of the last inserted record
Source:
Parameters:
Name Type Description
table String

The name of the table
insert Object

column-value pairs to insert
Returns:
Type:
Promise.<Object>

(async) insertIntoOnDuplicateKeyUpdate(table, insert, update) → {Promise.<Object>}

Run an "INSERT INTO ... ON DUPLICATE KEY UPDATE" query where if a key conflicts, update the given fields

Properties:
Name Type Description
insertId Number

The id of the last inserted or updated record
affectedRows Number

The number of rows matching the WHERE criteria
changedRows Number

The number of rows affected by the statement
Source:
Parameters:
Name Type Description
table String

The name of the table
insert Object

An array with column => value pairs for insertion
update Object

An array with column => value pairs for update
Returns:
Type:
Promise.<Object>

(async) multiQuery(sql) → {Promise.<Object>}

Run multiple statements separated by semicolon

Properties:
Name Type Description
query String

The final SQL that was executed
results Array

One element for every statement in the query
fields Array.<Object>

Info about the selected fields
Source:
Parameters:
Name Type Description
sql String | Object

The sql to run
...bindVars *

Values to bind to the sql placeholders
Returns:
Type:
Promise.<Object>

(async) query(sql) → {Promise.<Object>}

Run a statement of any type

Properties:
Name Type Description
query String

The final SQL that was executed
results Array

The result rows
fields Array.<Object>

Info about the selected fields
Source:
Parameters:
Name Type Description
sql String | Object

The sql to run
...bindVars *

Values to bind to the sql placeholders
Returns:
Type:
Promise.<Object>

quote(identifier) → {*}

Escape an identifier such as a table or column

Source:
Parameters:
Name Type Description
identifier
Returns:
Type:
*

rollback() → {Promise.<Object>}

run ROLLBACK

Source:
Returns:
Type:
Promise.<Object>

(async) select(sql) → {Promise.<Object>}

Return result rows for the given SELECT statement

Properties:
Name Type Description
query String

The final SQL that was executed
results Array

The result rows
fields Array.<Object>

Info about the selected fields
Source:
Parameters:
Name Type Description
sql String | Object

The SQL to run
...bindVars *

The values to bind to the each question mark or named binding
Returns:
Type:
Promise.<Object>

selectByKey(table, column, value) → {Promise.<Object>}

Select the record with the given column value

Properties:
Name Type Description
query String

The final SQL that was executed
results Object | undefined

The result row or undefined
fields Array.<Object>

Info about the selected fields
Source:
Parameters:
Name Type Description
table String

The name of the table from which to select
column String

The name of the column from which to select
value String

The value of the record for that column
Returns:
Type:
Promise.<Object>

(async) selectExists(sql) → {Promise.<Object>}

Run the given SELECT statement wrapped in a SELECT EXISTS query

Properties:
Name Type Description
query String

The final SQL that was executed
results Boolean

True if any records match query
fields Array.<Object>

Info about the selected fields
Source:
Parameters:
Name Type Description
sql String | Object

The SQL to run
...bindVars *

The values to bind to the each question mark or named binding
Returns:
Type:
Promise.<Object>

(async) selectFirst(sql) → {Promise.<Object>}

Return first result row for the given SELECT statement

Properties:
Name Type Description
query String

The final SQL that was executed
results Object | undefined

The result row or undefined
fields Array.<Object>

Info about the selected fields
Source:
Parameters:
Name Type Description
sql String | Object

The SQL to run
...bindVars *

The values to bind to the each question mark or named binding
Returns:
Type:
Promise.<Object>

selectFrom(table, fields, criteria, extra) → {Promise.<Object>}

Build a SELECT statement and return result rows

Properties:
Name Type Description
query String

The final SQL that was executed
results Array

The result rows
fields Array.<Object>

Info about the selected fields
Source:
See:
  • SqlBuilder#buildWhere
Parameters:
Name Type Description
table String

The name of the table
fields Array

An array of field names to select
criteria Object

Params to construct the WHERE clause - see SqlBuilder#buildWhere
extra String

Additional raw SQL such as GROUP BY, ORDER BY, or LIMIT
Returns:
Type:
Promise.<Object>

(async) selectGrouped(groupField, sql) → {Promise.<Object>}

Return records all grouped by one of the column's values

Properties:
Name Type Description
query String

The final SQL that was executed
results Object

Result rows grouped by groupField
fields Array.<Object>

Info about the selected fields
Source:
Parameters:
Name Type Description
groupField String

The name of the field to group by
sql String | Object

The SQL to run
...bindVars *

The values to bind to the each question mark or named binding
Returns:
Type:
Promise.<Object>

(async) selectHash(sql) → {Promise.<Object>}

Return result array as col1 => col2 pairs for the given SELECT statement

Properties:
Name Type Description
query String

The final SQL that was executed
results Object

The result object with key-value pairs
fields Array.<Object>

Info about the selected fields
Source:
Parameters:
Name Type Description
sql String | Object

The SQL to run
...bindVars *

The values to bind to the each question mark or named binding
Returns:
Type:
Promise.<Object>

selectId(table, id) → {Promise.<Object>}

Select the record with the given id

Properties:
Name Type Description
query String

The final SQL that was executed
results Object | undefined

The result row or undefined
fields Array.<Object>

Info about the selected fields
Source:
Parameters:
Name Type Description
table String

The name of the table from which to select
id String

The value of the id column
Returns:
Type:
Promise.<Object>

(async) selectIndexed(indexField, sql) → {Promise.<Object>}

Return records all indexed by one of the column's values

Properties:
Name Type Description
query String

The final SQL that was executed
results Object

The results indexed by indexField
fields Array.<Object>

Info about the selected fields
Source:
Parameters:
Name Type Description
indexField String

The name of the field to index by
sql String | Object

The SQL to run
...bindVars *

The values to bind to the each question mark or named binding
Returns:
Type:
Promise.<Object>

(async) selectList(sql) → {Promise.<Object>}

Return result array as col1 for the given SELECT statement

Properties:
Name Type Description
query String

The final SQL that was executed
results Array

The result list
fields Array.<Object>

Info about the selected fields
Source:
Parameters:
Name Type Description
sql String | Object

The SQL to run
...bindVars *

The values to bind to the each question mark or named binding
Returns:
Type:
Promise.<Object>

selectOrCreate(table, criteria, newValues) → {Promise.<Object>}

Find a record or add a new one

Properties:
Name Type Description
query String

The final SQL that was executed
results Object | undefined

The result row or undefined
fields Array.<Object>

Info about the selected fields
insertId Number

The id of the last inserted record
Source:
Parameters:
Name Type Description
table String

The name of the table from which to select
criteria Object

Criteria by which to find the row
newValues Object

The values to use to insert if the record doesn't exist
Returns:
Type:
Promise.<Object>

selectOrCreateId(table, criteria, newValues) → {Promise.<Object>}

Find a record's id or add a new one

Properties:
Name Type Description
query String

The final SQL that was executed
results Object

The result row or new values
fields Array.<Object>

Info about the selected fields
insertId Number

The id of the last inserted record
Source:
Parameters:
Name Type Description
table String

The name of the table from which to select
criteria Object

Criteria by which to find the row
newValues Object

The values to use to insert if the record doesn't exist
Returns:
Type:
Promise.<Object>

selectUuid(table, uuid) → {Promise.<Object>}

Select the record with the given UUID

Properties:
Name Type Description
query String

The final SQL that was executed
results Object | undefined

The result row or undefined
fields Array.<Object>

Info about the selected fields
Source:
Parameters:
Name Type Description
table String

The name of the table from which to select
uuid String

The value of the uuid column
Returns:
Type:
Promise.<Object>

(async) selectValue(sql) → {Promise.<Object>}

Return first column value for the first result row for the given SELECT statement

Properties:
Name Type Description
query String

The final SQL that was executed
results *

The value returned in the first field of the first row
fields Array.<Object>

Info about the selected fields
Source:
Parameters:
Name Type Description
sql String | Object

The SQL to run
...bindVars *

The values to bind to the each question mark or named binding
Returns:
Type:
Promise.<Object>

startTransaction() → {Promise.<Object>}

run START TRANSACTION

Source:
Returns:
Type:
Promise.<Object>

tpl() → {Object}

Return an object with query methods to run on template literals (backticked strings) where interpolated strings are automatically escaped

Properties:
Name Type Description
select function

Same as Db#select()
selectFirst function

Same as Db#selectFirst()
selectList function

Same as Db#selectList()
selectHash function

Same as Db#selectHash()
selectValue function

Same as Db#selectValue()
insert function

Same as Db#insert()
update function

Same as Db#update()
delete function

Same as Db#delete()
Source:
Returns:
Type:
Object

Object with query methods

Example
const db = Db.factory();
const { select, selectValue } = db.tpl();
const users = await select`SELECT * FROM users WHERE id IN(${userIds})`;
const count = await selectValue`SELECT COUNT(*) FROM users WHERE is_active = ${isActive}`;

(async) update(sql) → {Promise.<Object>}

Run the given UPDATE statement

Properties:
Name Type Description
query String

The final SQL that was executed
affectedRows Number

The number of rows matching the WHERE criteria
changedRows Number

The number of rows affected by the statement
Source:
Parameters:
Name Type Description
sql String | Object

The SQL to run
...bindVars *

The values to bind to the each question mark or named binding
Returns:
Type:
Promise.<Object>

updateTable(table, set, where) → {Promise.<Object>}

Build an UPDATE statement and run it

Properties:
Name Type Description
query String

The final SQL that was executed
affectedRows Number

The number of rows matching the WHERE criteria
changedRows Number

The number of rows affected by the statement
Source:
See:
  • SqlBuilder#buildWheres
Parameters:
Name Type Description
table String

The name of the table
set Object

An array of column-value pairs to update
where Object

Params to construct the WHERE clause - see SqlBuilder#buildWheres
Returns:
Type:
Promise.<Object>