db

↓ Members.

↓ Methods.

module:db

Description:

  • The Database API, a thin abstraction layer on top of SQLite, PostgreSQL, DynamoDB, Elasticsearch.

    The goal is not to create another ORM to support all databases but to make the API usable for simple/common use cases and easily fallback to native access for each database.

    By simple access it means:

    • read a row by primary key
    • update a row by primary key
    • delete a row by primary key
    • select rows by primary key and/or other keys

    On the source code level access to all databases will be possible using this API but any specific usage like SQL queries syntax or data types available only for some databases will not be unified or automatically converted. Instead it is passed to the database directly.

    Conversion between JavaScript types and database types is unified to some degree meaning JavaScript data types will be converted into the corresponding data types supported by any particular database and vice versa.

    Basic CRUD operations are supported for all database and modelled after NoSQL usage, this means no SQL joins are supported by the API, only single table access. SQL joins can be passed as SQL statements directly to the database using low level module:db.sql API call, all high level operations like add/put/del perform SQL generation for single table on the fly.

    All queries and update operations ignore properties that starts with underscore.

    All database methods can use default db pool or any other available db pool by using pool: name in the options. If not specified, then default db pool is used, none is default if no -db-pool config parameter specified in the command line or the config file.

    Even if the specified pool does not exist, the default pool will be returned, this allows to pre-configure the app with different pools in the code and enable or disable any particular pool at any time.

    To use PostgreSQL db pool to get a record and update the dynamodb pool:

    db-pg-pool=pg://localhost/dbname

    db.get("users", { id: "123" }, { pool: "pg" }, (err, row) => {
   if (row) {
      ......
      db.update("users", row, { pool: "dynamodb" });
   }
});

const { data } = await db.aget("users", { id: "123" });

const { err, data } = await db.aselect("users", { type_$in: ["admin", "manager"] });

const { err } = await db.aupdate("users", { id: 123, type: ["admin", "staff"] });

const { err } = await db.adel("users", { id: 123 });

    Most database pools can be configured with options min and max for number of connections to be maintained, so no overload will happen and keep warm connection for faster responses.

    db-pg-pool-max = 100
db-sqlite-pool-max = 100

    The following databases are supported with the basic db methods: Sqlite, PostgreSQL, DynamoDB, Elasticsearch

    Multiple pools of the same type can be opened, just add N suffix to all database config parameters where N is a number, referer to such pools in the code as poolN or by an alias.

    db-sqlite1-pool = /path/billing
db-sqlite1-pool-max = 10
db-sqlite1-pool-options = journal_mode:OFF
db-sqlite1-pool-alias = billing

    and use in Javascript by alias using callback or await:

    db.select("bills", { status: "ok" }, { pool: "billing" }, lib.log)

await db.aselect("bills", { status: "ok" }, { pool: "billing" })
Source:

Members

(static) args :Array.<ConfigOptions>

Source:
Default Value:
  • [
  {
    "name": "cap-(.+)",
    "type": "number",
    "strip": "cap-",
    "sametype": 1,
    "descr": "Capability parameters"
  },
  {
    "name": "none",
    "type": "bool",
    "descr": "disable all db pools"
  },
  {
    "name": "pool",
    "descr": "Default pool to be used for db access without explicit pool specified"
  },
  {
    "name": "name",
    "key": "db-name",
    "descr": "Default database name to be used for default connections in cases when no db is specified in the connection url"
  },
  {
    "name": "config",
    "descr": "Configuration database pool to be used to retrieve config parameters from the database, must be defined to use remote db for config parameters"
  },
  {
    "name": "config-map",
    "obj": "config-map",
    "type": "map",
    "merge": 1,
    "descr": "Config options: `.interval` between loading configuration from the database configured with -db-config, in minutes, 0 disables refreshing config from the db, `.count` max records to load in one select, see the docs about `.top`, `.main`, `.other` config parameters"
  },
  {
    "name": "aliases-(.+)",
    "obj": "aliases",
    "nocamel": 1,
    "reverse": 1,
    "onparse": "",
    "descr": "Table aliases to be used instead of the requested table name, only high level db operations will use it, low level utilities use the real table names"
  },
  {
    "name": "describe-tables",
    "type": "callback",
    "callback": "describeTables",
    "descr": "A JSON object with table descriptions to be merged with the existing definitions"
  },
  {
    "name": "describe-column-([a-z0-9_]+)-([a-zA-Z0-9_.]+)",
    "obj": "columns.$1",
    "make": "$2",
    "type": "map",
    "nocamel": 1,
    "dot": 1,
    "descr": "Describe a table column properties, can be a new or existing column, overrides existing property",
    "example": "-db-describe-column-users-name.check max:255"
  },
  {
    "name": "cache-tables",
    "obj": "cache",
    "array": 1,
    "type": "list",
    "descr": "List of tables that can be cached: users, bk_counter. This list defines which DB calls will cache data with currently configured cache. This is global for all db pools."
  },
  {
    "name": "cache-pools",
    "obj": "cache",
    "array": 1,
    "type": "list",
    "descr": "List of pools which trigger cache flushes on update."
  },
  {
    "name": "cache-sync",
    "obj": "cache",
    "array": 1,
    "type": "list",
    "descr": "List of tables that perform synchronized cache updates before returning from a DB call, by default cache updates are done in the background"
  },
  {
    "name": "cache-keys-([a-z0-9_]+)-(.+)",
    "obj": "cache.keys.$1",
    "make": "$2",
    "nocamel": 1,
    "type": "list",
    "descr": "List of columns to be used for the table cache, all update operations will flush the cache if the cache key can be created from the record columns. This is for ad-hoc and caches to be used for custom selects which specified the cache key."
  },
  {
    "name": "cache-ttl-(.+)",
    "type": "int",
    "obj": "cache.ttl",
    "nocamel": 1,
    "descr": "TTL in milliseconds for each individual table being cached, use * as default for all tables"
  },
  {
    "name": "cache-name-(.+)",
    "obj": "cache.name",
    "nocamel": 1,
    "make": "$1",
    "descr": "Cache client name to use for cache reading and writing for each table instead of the default in order to split cache usage for different tables, it can be just a table name or `pool.table`, use `*` to set default cache for all tables"
  },
  {
    "name": "cache-update-(.+)",
    "obj": "cache.update",
    "nocamel": 1,
    "make": "$1",
    "descr": "Cache client name to use for updating only for each table instead of the default in order to split cache usage for different tables, it can be just a table name or `pool.table` or `*`. This cache takes precedence for updating cache over `cache-name` parameter"
  },
  {
    "name": "cache2-max",
    "type": "int",
    "min": 1,
    "obj": "lru",
    "make": "max",
    "descr": "Max number of items to keep in the LRU Level 2 cache"
  },
  {
    "name": "cache2-(.+)",
    "obj": "cache2",
    "type": "int",
    "nocamel": 1,
    "min": 0,
    "descr": "Tables with TTL for level2 cache, i.e. in the local process LRU memory. It works before the primary cache and keeps records in the local LRU cache for the given amount of time, the TTL is in ms and must be greater than zero for level 2 cache to work"
  },
  {
    "name": "skip-tables",
    "obj": "skip",
    "array": 1,
    "type": "list",
    "descr": "List of tables that will not be created or modified, this is global for all pools"
  },
  {
    "name": "skip-pools",
    "obj": "skip",
    "array": 1,
    "type": "list",
    "descr": "List of pools to be skipped during initialization"
  },
  {
    "name": "skip-drop",
    "obj": "skip",
    "type": "regexpobj",
    "descr": "A pattern of table names which will skipp in db.drop operations to prevent accidental table deletion"
  },
  {
    "name": "custom-column-([a-zA-Z0-9_]+)-(.+)",
    "obj": "customColumn.$1",
    "make": "$2",
    "nocamel": 1,
    "descr": "A column that is allowed to be used in any table, the name is a column name regexp with the value to be a type",
    "example": "-db-custom-column-users-^stats=counter"
  },
  {
    "name": "cleanup-(.+)",
    "obj": "cleanup.$1",
    "type": "map",
    "maptype": "bool",
    "merge": 1,
    "nocamel": 1,
    "descr": "Rules for the db.cleanupResult per table, only allows on/off condition for columns",
    "example": "db-cleanup-bk_user = email:0,phone:1"
  },
  {
    "name": "([a-z0-9]+)-pool",
    "obj": "_config.$1",
    "make": "url",
    "dflt": "default",
    "descr": "A database pool name, depending on the driver it can be an URL, name or pathname",
    "example": "`-db-pg-pool`\n`-db-dynamodb-pool`\nurl format: `protocol://[user:password@]hostname[:port]/dbname`\n`default`"
  },
  {
    "name": "([a-z0-9]+)-pool-(disabled)",
    "obj": "_config.$1",
    "make": "$2",
    "type": "bool",
    "descr": "Disable the specified pool but keep the configuration"
  },
  {
    "name": "([a-z0-9]+)-pool-(max)",
    "obj": "_config.$1",
    "make": "$2",
    "type": "number",
    "min": 1,
    "descr": "Max number of open connections for a pool, default is Infinity"
  },
  {
    "name": "([a-z0-9]+)-pool-(min)",
    "obj": "_config.$1",
    "make": "$2",
    "type": "number",
    "min": 1,
    "descr": "Min number of open connections for a pool"
  },
  {
    "name": "([a-z0-9]+)-pool-(idle)",
    "obj": "_config.$1",
    "make": "$2",
    "type": "number",
    "min": 1000,
    "descr": "Number of ms for a db pool connection to be idle before being destroyed"
  },
  {
    "name": "([a-z0-9]+)-pool-(tables)",
    "obj": "_config.$1.config",
    "make": "$2",
    "array": 1,
    "type": "list",
    "onupdate": "applyPoolOptions",
    "descr": "Tables to be created only in this pool, to prevent creating all tables in every pool"
  },
  {
    "name": "([a-z0-9]+)-pool-connect",
    "obj": "_config.$1.connect",
    "type": "map",
    "merge": 1,
    "logger": "warn",
    "descr": "Connect options for a DB pool driver for new connection, driver specific"
  },
  {
    "name": "([a-z0-9]+)-pool-options",
    "obj": "_config.$1.config",
    "type": "map",
    "merge": 1,
    "onupdate": "applyPoolOptions",
    "descr": "General options for a DB pool, a simple map case"
  },
  {
    "name": "([a-z0-9]+)-pool-options-([a-zA-Z0-9_.-]+)$",
    "obj": "_config.$1.config",
    "camel": "-",
    "autotype": 1,
    "make": "$2",
    "onupdate": "applyPoolOptions",
    "descr": "General options for a DB pool by name with specific type"
  },
  {
    "name": "([a-z0-9]+)-pool-table-map",
    "obj": "_config.$1.config.tableMap",
    "type": "map",
    "merge": 1,
    "onupdate": "applyPoolOptions",
    "descr": "Table mapping, aliases"
  },
  {
    "name": "([a-z0-9]+)-pool-(skip-tables)",
    "obj": "_config.$1.config",
    "make": "$2",
    "array": 1,
    "type": "list",
    "descr": "Tables not to be created in this pool"
  },
  {
    "name": "([a-z0-9]+)-pool-(metrics-tables)",
    "obj": "_config.$1.config",
    "make": "$2",
    "array": 1,
    "type": "list",
    "descr": "Tables to collect metrics in this pool"
  },
  {
    "name": "([a-z0-9]+)-pool-cache2-(.+)",
    "obj": "cache2",
    "nocamel": 1,
    "strip": {},
    "type": "int",
    "descr": "Level 2 cache TTL for the specified pool and table, data is JSON strings in the LRU cache"
  },
  {
    "name": "([a-z0-9]+)-pool-alias",
    "obj": "poolAliases",
    "make": "$1",
    "reverse": 1,
    "descr": "Pool alias to refer by an alternative name"
  }
]

(static) concurrency :number

Description:

  • How many simultaneous tasks to run at the same time inside one process

Source:

(static) config :string

Description:

  • Database pool for config

Source:

(static) configMap :object

Description:

  • database config reload settings, set via db-config-map

Source:
Properties:
Name Type Description
configMap.count int

max number of records to load from the DB
configMap.interval int

how often to refresh config from DB, in mins

(static) keys :object

Description:

  • All primary keys for each table by name

Source:

(static) modules :object

Description:

  • Database modules by type

Source:

(static) pool :string

Description:

  • Default database pool for the backend

Source:

(static) pools :object

Description:

  • Database connection pools by pool name

Source:

(static) tables :object

Description:

  • All tables from all modules by table name

Source:

(static) tryCatch :number

Description:

  • run db callbacks via lib.tryCatch

Source:

Methods

(async, static) aadd(table, query, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
table string

db table name
query object

properties to add with primary keys
options DbRequestOptions <optional>
Example
const { err, data, info } = await db.aadd("users", { id: '123', login: 'admin', name: 'test' })

(async, static) abatch(list, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
list Array.<object>

an array of objects to get/put/update/delete from the database in the format:
options DbRequestOptions <optional>

(async, static) abulk(list, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
list Array.<object>

an array of objects to get/put/update/delete from the database in the format:
options DbRequestOptions <optional>

(async, static) acopy(table, query, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
table string

name of the table to copy
query object

a query condition for the table
options DbRequestOptions <optional>

(async, static) acreate(table, schema, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
table string

table name
schema DbTable

an object with table schema
options DbConfigOptions <optional>

options for the database driver

(static) add(table, query, optionsopt, callbackopt)

Description:

  • Insert new object into the database

Source:
Parameters:
Name Type Attributes Description
table string

table to use
query object

an actual record to be updated, primary key properties must be specified
options DbRequestOptions <optional>
callback DbResultCallback <optional>
Example
db.add("users", { id: '123', login: 'admin', name: 'test' }, (err, rows, info) => {
});

(async, static) adel(table, query, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
table string

db table name
query object

condition
options DbRequestOptions <optional>
Example
const { err, info } = db.adel("users", { login: '123' });
console.log('deleted:', info.affected_rows);

(async, static) adelAll(table, query, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
table string

db table name
query object

condition
options DbRequestOptions <optional>

(async, static) adrop(table, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
table string

table name
options DbRequestOptions <optional>

(async, static) aget(table, query, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
table string

db table name
query object

an object with primary keys
options DbRequestOptions <optional>
Example
const { err, data } = await db.aget("users", '12345');

const { err, data } = await db.aget("users", { login: '12345' });

// Assuming config primary key is name,type
const { err, data } = await db.aget("config", ['key1', 'type1']);

(async, static) agetConfig()

Description:
Source:

(async, static) aincr(table, query, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
table string

db table name
query object

properties to update with primary keys
options DbRequestOptions <optional>
Example
const { err, data, info } = await db.aincr("bk_counter", { id: '123', like0: 1, invite0: 1 })

(async, static) alist(table, query, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
table string

table to use
query Array.<string> | Array.<object>

list of records
options DbRequestOptions <optional>
Examples
const { err, data } = db.alist("users", ["id1", "id2"]);
console.log(data);
const { err, data } = db.alist("config", [{ name: "id1", type: "t1" },{ name: "id2", type: "t2" }]);

(static) applyPoolOptions()

Description:

  • Apply a config parameter to live DB pool, used in config args update callback to make a config value live

Source:

(async, static) aput(table, query, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
table string

db table name
query object

properties to update with primary keys
options DbRequestOptions <optional>
Example
const { err, data } = await db.aput("users", { id: '123', login: 'test', name: 'test' })

(async, static) aquery(req) → {Promise}

Description:

  • Async version of module:db.query with the same parameters, no exceptions are raised or reject, only resolve is called with an object { err, data, info }

Source:
Parameters:
Name Type Description
req DbRequest
Returns:
Type Description
Promise
Example
const { err, data } = await db.aquery({ text: "SELECT ....." }, { pool: "sqlite" });

(async, static) ascan(table, query, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
table string

table name
query DbTable

an object with query condition
options DbRequestOptions <optional>

(async, static) asearch(table, query, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
table string

db table name
query object

condition
options DbRequestOptions <optional>
Example
await db.asearch("users", { roles: "admin", q: "john*" }, { pool: "elasticsearch" });
await db.asearch("users", "john*", { pool: "elasticsearch" });

(async, static) aselect(table, query, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
table string

db table name
query object

condition
options DbRequestOptions <optional>
Example
const { err, data } = await db.aselect("users", { roles: 'admin' });

(async, static) asql(test, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
test string

SQL statement
Array.<any>

values for position placeholders
options DbRequestOptions <optional>
Example
const { err, data, info } = await db.asql("SELECT * FROM users WHERE name=$1 LIMIT $2", ["admin", 1])

(async, static) atransaction(list, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
list Array.<object>

an array of objects to get/put/update/delete from the database in the format:
options DbRequestOptions <optional>

(async, static) aupdate(table, query, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
table string

db table name
query object

properties to update with primary keys
options DbRequestOptions <optional>
Example
const { err, data, info } = await db.aupdate("users", { login: 'test', name: 'Test' })
if (!err && !info?.affected_rows) logger.error("no updates")

(async, static) aupdateAll(table, query, update, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
table string

db table name
query object

condition
update object

properties to update
options DbRequestOptions <optional>

(async, static) aupgrade(table, schema, optionsopt)

Description:
Source:
Parameters:
Name Type Attributes Description
table string

table name
schema DbTable

an object with table schema
options DbConfigOptions <optional>

options for the database driver

(static) batch(list, optionsopt, callbackopt)

Description:

  • Perform a batch of operations at the same time, all operations for the same table will be run together one by one but different tables will be updated in parallel.

    NOTE: it may use different DB client(session) even for the same table due to each op running independently.

    On return the second arg to the callback is a list of records with errors as the input record with added property errstatus and errmsg, for get ops the result will be retrieved record if exists.

    One advantage using this with op: get over module:db.list is caching, module:db.get may use both local and remote caches but db.list always hits the database.

Source:
Parameters:
Name Type Attributes Description
list Array.<object>

an array of objects to get/put/update/delete from the database in the format:

Properties
Name Type Attributes Default Description
op string <optional>
 add

is one of get, add, incr, put, update, del
table string

which table to use
query object

an object with data to be saved
options DbRequestOptions <optional>

params for the operation
options DbRequestOptions <optional>

default options with additional specific below

Properties
Name Type Description
concurrency number

number of how many operations to run at the same time for a table, 1 means sequential
no_errors boolean

will stop on first error, because operations will be run in parallel some operations still may be performed
factorCapacity number

a capacity factor to apply to the write capacity if present, by default it is used write capacity at 100%
callback DbResultCallback <optional>
Example
var ops = [ { table: "bk_counter", query: { id:1, like:1 } },
             { op: "put", table: "users", query: { login: "test", id:1, name:"test" }]
 db.batch(ops, { factorCapacity: 0.5 }, lib.log);

(static) bulk(list, optionsopt, callbackopt)

Description:

  • Bulk operations, it will be noop if the driver does not support it. The input format is the same as for the module:db.batch method.

    The following databases support bulk:

    • DynamoDB: only supports add/put/del and 25 at a time, if more specified it will send multiple batches sequentially
    • ElasticSearch: supports all update ops
    • SQL: supports all ops, runs sequentially or concurrently if options.concurrency is provided, uses the same client(session)
      • rqlite uses native bulk writes
Source:
Parameters:
Name Type Attributes Description
list Array.<object>

an array of objects to get/put/update/delete from the database in the format:
options DbRequestOptions <optional>
callback DbResultCallback <optional>

second arg is a list of records with errors, same input record with added property error
Example
var ops = [
   { op: "add", table: "bk_counter", query: { id:1, like:1 } },
   { op: "del", table: "users", query: { login: "test1" } },
   { op: "incr", table: "bk_counter", query: { id:2, like:1 } },
   { op: "add", table: "users", query: { login: "test2", id:2, name:"test2" } }
]
db.bulk(ops, { pool: "elasticsearch" }, lib.log);

(static) cacheColumns(options, callbackopt)

Description:

  • Reload all columns into the cache for the pool,

Source:
Parameters:
Name Type Attributes Description
options string | object

a pool name or an object

Properties
Name Type Attributes Description
pool string <optional>

pool name
callback function() <optional>

(static) checkCached()

Description:

  • Returns true if the operation used the cache, this is just a wrapper around module:db.getCached with consistent check when to use caching logic

Source:

(static) checkCapacity()

Description:

  • Check if number of requests exceeds the capacity per second, delay if necessary, for DynamoDB only but can be used for pacing requests with any database or can be used generically. The cap must be initialized with db.getCapacity call.

Source:

(static) cleanupResult(tables, data, optionsopt) → {object|Array.<object>}

Description:

  • Process records and keep only public properties as defined in the table columns. This method is supposed to be used in the post process callbacks after all records have been processes and are ready to be returned to the client, the last step would be to cleanup all non public columns if necessary.

    This method is useful in case when no new object shapes are required to return but existing table shape, in such case removing private properties requires less code to keep in sync.

    The cleanup property in DbTableColumn is used to determine if a column can be present in the result or stripped away, absense of the cleanup property means never return the column, i.e. it is private.

Source:
Parameters:
Name Type Attributes Description
tables string | Array.<string>

can be a single table name or a list of table names which combined public columns need to be kept in the rows.
data object | Array.<object>

row(s) to be cleaned up
options object | RequestContext <optional>
Properties
Name Type Attributes Description
user object <optional>

current user record to check for roles, this can be RequestContext if no other properies atre needed
cleanup object <optional>

can be an object similar to the cleanup object in the table, these rules take precedence
Returns:
Type Description
object | Array.<object>

cleaned records, a new object/array if any field must be excluded
Example
// assuming the users table is
users: {
    id: { type: "test", cleanup: false },
    name: { type: "test", cleanup: false },
    secret: { type: "test" },
}

// and the API route handler
api.app.get("/user/:id", async (context) => {
     const { err, data } = db.aget("users", { id: context.params.id });
     if (!data) return context.reply(err || { status: 404 })

     context.json(db.cleanupResult('users', data, context));
})

// added a user somewhere
db.add("users", { id: 123, name: "test", secret: "...." });

// asking for that user will return only public properties
curl localhost:8000/user/123
{ "id": 123, "name": "test "}

(static) configTypes()

Description:

  • Build a list of all config types we want to retrieve, based on the db-config-map parameters that defines which fields to use for config types.

    • for each top item it create a mix of all main items below
    • for each main item it creates a mix of all `other`` items

    top... -> each of top-main... -> each of top-main-other...

    Most common config parameters: roles, role, tag, region

Source:
Example
top is roles(prod,test), main is role(shell),tag(local), other is region(none)
 - prod
 - prod-shell
 - prod-shell-none
 - prod-local
 - prod-local-none
 - test
 - test-shell
 - test-shell-none
 - test-local
 - test-local-none

(static) configureCollectStats()

Description:

  • Collect stats for enabled pools, this is called by the stats module

    To enable stats collection for a pool it must be explicitly set via config options, additionally collect stats individually for a list of tables

    db-dynamodb-pool-options = metrics:1
db-dynamodb-pool-metrics-tables = bk_config, users
Source:

(static) convertError(req, err) → {Error|object}

Description:

  • Convert native database error in some generic human readable string

Source:
Parameters:
Name Type Description
req DbRequest
err Error | object
Returns:
Type Description
Error | object

(static) convertRows(req, rows, optionsopt)

Description:

  • Convert rows returned by the database into the Javascript format or into the format defined by the table columns, most use cases are json, lists, defaults

Source:
Parameters:
Name Type Attributes Description
req DbRequest
rows Array.<any>
options DbRequestOptions <optional>
Example
db.describeTables([ { user: { id: {}, name: {}, pair: { join: ["left","right"] } } ]);

db.put("test", { id: "1", type: "user", name: "Test", left: "123", right: "000" })
db.select("test", {}, lib.log)

(static) copy(table, query, optionsopt, callbackopt)

Description:

  • Copy records from one table to another between different DB pools or regions Returns stats how many copied or errors

Source:
Parameters:
Name Type Attributes Description
table string

name of the table to copy
query object

a query condition for the table
options DbRequestOptions <optional>

default options with additional specific below

Properties
Name Type Attributes Description
sourcePool string <optional>

source DB pool, default if not provided
pool string <optional>

destination DB pool
minCapacity int <optional>

capacity minimum for read/writes, it will override actual DB capacity
stopOnError boolean <optional>

stop the copy on first DB error, otherwise ignore errors
region string <optional>

other region where to copy for DynamoDB
file string <optional>

dump the data into a file as JSON
preprocess function() <optional>

a function(table, row, options) to be called before the update, if it returns true the record will be skipped
posprocess function() <optional>

a function(table, row, options, next) to be called after the record is copied, for recursive or joined cases
reget boolean <optional>

if set the actual record will read using db.get, for cases when db.scan returns only partial record as in DynamoDB cases with indexes
incremental boolean <optional>

if set, try to read the latest record in the other table and continue from there, uses sort index in desc order
queryOptions object <optional>

pass options to scan operations
updateOptions object <optional>

pass options to update/bulk operations
callback DbResultCallback <optional>

(static) create(table, schema, optionsopt, callbackopt)

Description:

  • Create a table using column definitions represented as a list of objects.

    Some properties may be defined multiple times with number suffixes like: unique1, unique2, index1, index2 to create more than one index for the table, same properties define a composite key in the order of definition or sorted by the property value, for example: { a: { index:2 }, b: { index:1 } } will create index (b,a) because of the index: property value being not the same. If all index properties are set to 1 then a composite index will use the order of the properties.*

    Each database pool also can support native options which are passed directly to the driver in the options, these properties are defined in the object with the same name as the db driver: pg, sql, sqlite, dynamodb, elasticsearch

    DynmamoDB:

    To create a DynamoDB secondary index, the first index property if not the same as primary key hash defines global index, if it is the same then local, or if the first property contains _$dynamodb.global property then it is a global index.

    DynamoDB projections are defined by the _$dynamodb.projections[N] property in the first index property as an array with names of properties to include in projection or ALL for all properties.

    When using real DynamoDB creating a table may take some time, for such cases if options.waitTimeout is not specified it defaults to 1min, so the callback is called as soon as the table is active or after the timeout whichever comes first.

    SQL

    Special table object can be used to customize CREATE TABLE syntax with properties:

    _$db

    • sql: - full create statement with placeholders:
      • @table@ - table name
      • @columns@ - column names
      • @sql_columns@ - built SQL with column definitions
    • sql_extra: - only extra text to be added after the columns section to CREATE TABLE
    • index[N] - object custom index properties, similar
      • sql - full SQL for the index with placeholders: @table, @index@, @columns@
      • sql_extra - extra to append to CREATE INDEX
Source:
Parameters:
Name Type Attributes Description
table string

table name
schema DbTable

an object with table schema
options DbConfigOptions <optional>

options for the database driver
callback DbResultCallback <optional>

response handler
Examples

Generic standard table

db.create("table2", {
     id: { type: "int", primary: 1 },
     name: { index: 1 },
     flags: { type: "list" },
     mtime: { type: "mtime" },
     descr: { type: "text" },
});

DynamoDB global index `id_mtime` will include `flags` property in the projection 

db.create("table1", {
    id: {
        type: "int",
        primary: 1,
        index: 1,
        // DynamoDB specific projections instructions
        _$dynamodb: { projections: ["flags"] }
    },
    name: { primary: 2 },
    flags: { type: "list" },
    mtime: { type: "mtime", index: 2 },
    descr: { },
});

const { err, data } = await db.aselect("table2", { id: 123, name_$begins_with: "john" })

PostgreSQL GIN index or special search column

db.create("table2", {
     id: { type: "int" },
     name: { },
     flags: { type: "list" },
     mtime: { type: "mtime" },
     descr: { type: "text" },
     search: {
        type: "tsvector",
        index: 1,
        // PG specific extra instructions for this column
        _$pg: { sql_extra: "GENERATED ALWAYS AS (to_tsvector('english', concat_ws(' ', name, descr))) STORED" }
     },

     _$pg: {
          index: "CREATE INDEX @index@ ON @table@ USING GIN (@columns@)"
     }
});

const { err, data } = await db.asearch("table2", { search_$@@: "john & smith" })

db.create("table2", {
     id: { type: "int" },
     name: { index: 1 },
     flags: { type: "list" },
     mtime: { type: "mtime" },
     descr: { type: "text" },

     // PG specific full create index statement
     _$pg: {
          index: "CREATE INDEX @index@ ON @table@ USING GIN (to_tsvector('english', concat_ws(' ', @columns@)))"
     }
});

const { err, data } = await db.asql("SELECT * FROM table2 WHERE to_tsvector('english', descr) @@ $1", ["john & smith"])

SQLite FTS5 virtual table using _$db object

db.create("table2", {
     id: { type: "int" },
     name: {},
     flags: { type: "list" },
     mtime: { type: "mtime" },
     descr: { type: "unindexed" },

     // sqlite specific full create statement
     _$db: {
          sql: "CREATE VIRTUAL TABLE @table@ USING fts5 (@columns@, tokenizer = 'porter')",
     }
});

const { err, data } = await db.asearch("table2", { table2_$match: "john smith" })

(static) createTables(options, callbackopt)

Description:

  • Create or upgrade the tables for all or given pools

Source:
Parameters:
Name Type Attributes Description
options objects | string
Properties
Name Type Attributes Description
pools Array.<string> <optional>

only create in these pools
callback function() <optional>

(static) del(table, query, optionsopt, callbackopt)

Description:

  • Delete an object in the database, no error if the object does not exist

Source:
Parameters:
Name Type Attributes Description
table string

table to use
query object

object with column values
options DbRequestOptions <optional>
callback DbResultCallback <optional>
Example
db.del("users", { login: '123' }, (err, rows, info) => {
   console.log('deleted:', info.affected_rows);
});

(static) delAll(table, query, optionsopt, callbackopt)

Description:

  • Delete all records that match given condition in query

Source:
Parameters:
Name Type Attributes Description
table string

db table name
query object

condition
options DbRequestOptions <optional>
callback DbResultCallback <optional>
Example
db.delAll("users", { type: "tester" }, lib.log)

(static) describeTables()

Description:

  • Define new tables or extend/customize existing tables. Table definitions are used with every database operation, on startup, the backend read all existing table columns from the database and cache them in the memory but some properties like public columns are only specific to the backend so to mark such columns the table with such properties must be described using this method. Only columns with changed properties need to be specified, other columns will be left as it is.

Source:
Example
db.describeTables({
 bk_user: {
    name: { pub: 1 },
    test: { id: { primary: 1, type: "int" },
    name: { pub: 1, index: 1 }
}});

(static) drop(table, optionsopt, callbackopt)

Description:

  • Drop a table

Source:
Parameters:
Name Type Attributes Description
table string

table name
options DbRequestOptions <optional>
callback DbResultCallback <optional>

(static) existsPool()

Description:

  • Returns true if a pool exists

Source:

(static) get(table, query, optionsopt, callback)

Description:

  • Retrieve one record from the database by primary keys, returns found record or null if not found

Source:
Parameters:
Name Type Attributes Description
table string

table to read
query object | string | Array.<any>

query by primary key(s)
options DbRequestOptions <optional>
callback DbResultCallback

NOTE: On return the info.cached will be set to

  • 1 if retrieved from top level cache
  • 2 if retrieved from level 2 cache
  • 0 if retrieved from db and put in the cache
Example
db.get("users", '12345', (err, row) => {
   if (row) console.log(row.name);
});

db.get("users", { login: '12345' }, (err, row) => {
   if (row) console.log(row.name);
});

// Assuming config primary key is name,type
db.get("config", ["key1", "type1"], (err, row) => {
   if (row) console.log(row.name);
});

(static) getCached()

Description:

  • Retrieve cached result or put a record into the cache prefixed with table:key[:key...] Options accept the same parameters as for the usual get action but it is very important that all the options be the same for every call, especially select parameters which tells which columns to retrieve and cache. Additional options:

    • prefix - prefix to be used for the key instead of table name
Source:
Example
db.getCached("get", "bk_user", { login: req.query.login }, { select: "latitude,longitude" }, function(err, row) {
     var distance = lib.geoDistance(req.query.latitude, req.query.longitude, row.latitude, row.longitudde);
 });

(static) getCapacity(table, options[opt) → {object}

Description:

  • Return an object with capacity property which is the max write capacity for the table, for DynamoDB only. By default it checks writeCapacity property of all table columns and picks the max.

Source:
Parameters:
Name Type Attributes Description
table string
options[ object <optional>
  • useCapacity - what to use for capacity rating, can be write, read or a number with max capacity to use
  • factorCapacity - a number between 0 and 1 to multiple the rate capacity
  • rateCapacity - if set it will be used for rate capacity limit
  • maxCapacity - if set it will be used as the max burst capacity limit
  • minCapacity - if set it will be used as the minimum threshold
  • intervalCapacity - default is 1000 ms
  • sort - an index to use for capacity, for systems like DynamoDB which has different capacity for global indexes, it makes sense for indexed reads or partial updates where only global index is affected and not the whole record
Returns:
Type Description
object

(static) getColumn()

Description:

  • Return the column definition for a table, for non-existent columns it returns an empty object

Source:

(static) getColumnType(req, type)

Description:

  • Return actual column type to be used in column definition, performs possible conversions using table's or pool's typesMap, the priority is

    • table's typesMap[type || ""]
    • pool config's typesMap[type || ""]
    • tables's typesMap[*]
    • pool config's typesMap[*]
    • type
    • ""
Source:
Parameters:
Name Type Description
req DBRequest
type string

(static) getColumns()

Description:

  • Return columns for a table or null, columns is an object with column names and objects for definition.

Source:

(static) getConfig()

Description:

  • Return all config records for the given instance, the result will be sorted most relevant at the top

Source:

(static) getIndexColumns(table) → {object}

Description:

  • Returns column names for all indexes sorted

Source:
Parameters:
Name Type Description
table string | object
Returns:
Type Description
object

as { primary: [...], index: [..], index1: [..], index2: [..] }

(static) getIndexes(table, optionsopt) → {Array.<string>}

Description:

  • Return indexes for a table or empty object, each item in the object is an array with index columns

Source:
Parameters:
Name Type Attributes Description
table string
options object <optional>
Returns:
Type Description
Array.<string>

(static) getKeys(table, optionsopt) → {Array.<string>}

Description:

  • Return primary keys for a table or empty array

Source:
Parameters:
Name Type Attributes Description
table string
options object <optional>
Returns:
Type Description
Array.<string>

(static) getPool(options)

Description:

  • Return database pool by name or default pool, options can be a pool name or an object with { pool: name } to return the pool by given name. This call always returns a valid pool object, in case no requested pool found, it returns the default pool, in case of invalid pool name it returns none pool. A special pool none always returns empty result and no errors.

Source:
Parameters:
Name Type Description
options string | object

a pool name or an object with pool property

(static) getPoolTables()

Description:

  • Return all tables know to the given pool, returned tables are in the object with column information merged from cached columns from the database with description columns given by the application. If options.names is 1 then return just table names as a list.

Source:

(static) getPools()

Description:

  • Return a list of all active database pools, returns list of objects with name: and type: properties

Source:

(static) getProcessRows()

Description:

  • Returns a list of hooks to be used for processing rows for the given table

Source:

(static) getQueryForKeys(keys, query) → {object}

Description:

  • Returns an object based on the list of keys, basically returns a subset of query properties.

Source:
Parameters:
Name Type Description
keys Array.<string>
query object
Returns:
Type Description
object

(static) incr(table, query, optionsopt, callbackopt)

Description:

  • Counter operation, increase or decrease column values, similar to update but all specified columns except primary key will be incremented, use negative value to decrease the value.

    If no options.ops object specified or no 'incr' operations are provided then all columns with type 'counter' will use the update op incr, i.e. all counters will be incremented by the provided number

    Note: The record must exist already for SQL databases without UPSERT support, for DynamoDB and Elasticsearch a new record will be created if it does not exist yet.

Source:
Parameters:
Name Type Attributes Description
table string

table to use
query object

an actual record to be updated, primary key properties must be specified
options DbRequestOptions <optional>
callback DbResultCallback <optional>
Example
db.incr("bk_counter", { id: '123', like0: 1, invite0: 1 }, (err, rows, info) => {
});

(static) init(callback)

Description:

  • Initialize all database pools. the options may containt the following properties:

Source:
Parameters:
Name Type Description
callback function()

(static) initColumns()

Description:

  • Merge cached columns into tables

Source:

(static) initConfig()

Description:

  • Load configuration from the config database, must be configured with db-config pointing to the database pool where bk_config table contains configuration parameters.

    The priority of the parameters goes from the most broad to the most specific, most specific always wins, this allows for very flexible configuration policies defined by the app or place where instances running and separated by the run mode. See `db.getConfigTypes`` for more details.

    The options takes the following properties:

    • force - if true then force to refresh and reopen all db pools
    • table - a table where to read the config parameters, default is bk_config

    Do not create/upgrade tables and indexes when reloading the config, this is to avoid situations when maintenance is being done and any process reloading the config may create indexes/columns which are not missing but being provisioned or changed.

    To reload config from DB periodically set the interval in the config like db-config-map = interval:180000, it take saffect only on startup.

Source:

(static) initConfigTable()

Description:

  • Make DB config table available in the db.tables in order to be used in queries, can be run anytime

Source:

(static) initTables()

Description:

  • Merge all tables from all modules

Source:

(static) list(table, query, optionsopt, callback)

Description:

  • Convenient helper to retrieve all records by primary key, the obj must be a list with key property or a string with list of primary key columns

Source:
Parameters:
Name Type Attributes Description
table string

table to use
query string | Array.<string> | Array.<object>

list of primary keys
options DbRequestOptions <optional>
callback DbResultCallback
Example
db.list("users", ["id1", "id2"], (err, rows) => { console.log(err, rows) });

db.list("users", "id1,id2", (err, rows) => { console.log(err, rows) });

db.list("users", [{ login: "id1" },{ login: "id2" }], (err, rows) => { console.log(err, rows) });

db.list("config", [{ name: "id1", type: "t1" },{ name: "id2", type: "t2" }], (err, rows) => { console.log(err, rows) });

(static) paginatePage(req, data, info) → {object}

Description:

  • Return an object to be returned to the client as a page of result data with possibly next token if present in the info. This result object can be used for pagination responses.

Source:
Parameters:
Name Type Description
req DbRequest

request object

Properties
Name Type Attributes Description
options.total boolean <optional>

return count only from rows[0].count
data object | Array.<object>
info object
Properties
Name Type Attributes Description
next_token any <optional>

if present returned in result, this is from DB pagination, only numbers are returned as is, if it is a not number the next_token is base64 encoded with module:lib.jsonToBase64. When processing requests with JSON tokens in module:api.validate use the "token" type
Returns:
Type Description
object

with properties { count, data, next_token, total }

(static) parseNameOp(name) → {Array.<string>}

Description:

  • Split column name into pure name and possible op from the format: NAME[_$[OP]]

Source:
Parameters:
Name Type Description
name string
Returns:
Type Description
Array.<string>

as [name, op]

(static) prepare(options) → {DbRequest}

Description:

  • Prepare for execution for the given operation: add, del, put, update,... Returns prepared object to be passed to the driver's .query method.

Source:
Parameters:
Name Type Description
options object | DbRequest
Properties
Name Type Attributes Description
pool string | DbPool <optional>
op string <optional>
table string <optional>
query object <optional>
text string <optional>
values Array.<any> <optional>
options DbRequestOptions <optional>
callback DbRequestCallback <optional>
Returns:
Type Description
DbRequest
  • a request object

(static) prepareColumn(reqopt, name, value) → {DbRequestColumn}

Description:

  • Prepare a column for a retrieval operation, convert aliases, types and ops according to the request and pool mapping

    The op in the name after _$op takes precedence over the options.ops

    NOTE: op is lowercase and all underscores are converted into spaces

Source:
Parameters:
Name Type Attributes Description
req DbRequest <optional>

request object or null, it is not required to be strict DbRequest
name string

column name
value any

query or update value
Returns:
Type Description
DbRequestColumn
Example
{ name_$contains: "cat" } // means use the `contains` op for the column `name`
{ $or: { id: 1, id_$: 2, id_$$: 3 } }  // means id=1 OR id=2 OR id=3
{ $$or: { id_$in: [4, 5, 6], name: "a" } }  // means id IN (4, 5, 6) OR name='a'

(static) prepareQuery(req)

Description:

  • Preprocess a query object for a given operation, convert types, assign defaults...

Source:
Parameters:
Name Type Description
req DbRequest

(static) prepareValue(reqopt, options) → {object}

Description:

  • Possibly convert the value according to the column op or type, upates the options.value in place

Source:
Parameters:
Name Type Attributes Description
req DbRequest <optional>

request object or null, it is not required to be strict DbRequest
options object
Properties
Name Type Attributes Description
type any <optional>

converted native type
value any <optional>
op any <optional>
col any <optional>
Returns:
Type Description
object

same options

(static) put(table, query, optionsopt, callbackopt)

Description:

  • Add/update an object in the database, if object already exists it will be replaced with all new properties from the obj

Source:
Parameters:
Name Type Attributes Description
table string

table to use
query object

an actual record to be updated, primary key properties must be specified
options DbRequestOptions <optional>
callback DbResultCallback <optional>
Example
db.put("users", { id: '123', login: 'test', name: 'test' }, function(err, rows, info) {
});

(static) query(req, callbackopt)

Description:

  • Execute query using native database driver, the query is passed directly to the driver.

Source:
Parameters:
Name Type Attributes Description
req DbRequest

an object with request data prepared by module:db.Request or similar, missing required fileds will be filled by db.Request
callback DbResultCallback <optional>

an error or result
Example
var req = new db.Request({
    pool: "sqlite",
    text: "SELECT a.id,c.type FROM users a,bk_icon c WHERE a.id=c.id and a.id=?",
    values: ['123']
});
db.query(req, (err, rows, info) => {  ... });

// same as

db.query({
    pool: "sqlite",
    text: "SELECT a.id,c.type FROM users a,bk_icon c WHERE a.id=c.id and a.id=?",
    values: ['123']
}, (err, rows, info) => {  ... });

(static) queryProcessSync()

Description:

  • Post process hook to be used for replicating records to another pool, this is supposed to be used as this:

    The conditions when to use it is up to the application logic.

    It does not deal with the destination pool to be overloaded, all errors will be ignored, this is for simple and light load only

    The destination poll must have tables to be synced configured:

Source:
Example
db-elasticsearch-pool-tables=table1,table2
db.setProcessRow("post", "*", (req, row) => { db.queryProcessSync("elasticsearch", req, row) });

(static) refreshColumns()

Description:

  • Refresh columns for all pools which need it

Source:

(static) runProcessRows()

Description:

  • Run registered pre- or post- process callbacks.

    • type is one of the pre or 'post`
    • table - the table to run the hooks for, usually the same as req.table but can be '*' for global hooks
    • req is the original db request object with the following required properties: op, table, obj, options, info,
    • rows is the result rows for post callbacks and the same request object for pre callbacks.
Source:

(static) scan(table, query, optionsopt, rowCallback, endCallbackopt)

Description:

  • Convenient helper for scanning a table for some processing, rows are retrieved in batches and passed to the callback until there are no more records matching given criteria. The query is the same as in module:db.select method which definedsa condition which records to get. The rowCallback must be present and is called for every row or batch retrieved and second parameter which is the function to be called once the processing is complete. At the end, the callback will be called just with 1 argument, err, this indicates end of scan operation. Basically, db.scan is the same as db.select but can be used to retrieve large number of records in batches and allows async processing of such records. To hint a driver that scanning is in progress the options.scanning will be set to true.

    Parameters:

Source:
Parameters:
Name Type Attributes Description
table string

table to scan
query object

an object with query conditions, same as in module:db.select
options DbRequestOptions <optional>

default options with additional specific below

Properties
Name Type Attributes Description
search number <optional>

use module:db.search instead of module:db.select
limit number <optional>

total number of records to scan
batch boolean <optional>

if true rowCallback will be called with all rows from the batch, not every row individually, batch size is defined by the options.count property, used in scan
sync boolean <optional>

as batch mode but the rowCallback is called synchronously as rowCallback(row, info)
concurrency number <optional>

how many rows or operations to process at the same time
noscan boolean <optional>

if true no scan will be performed if no primary keys are specified to prevent scanning large tables
fullscan boolean <optional>

if true force to perform a full scan, this is slightly different for each database:

  • DynamoDB: used full table scan, the query condition still is checked, can be expensive for large table because pricing is by every object checked not by what is actually returned
  • Elasticsearch: performs streaming scan, using native order but all conditions are checked still, very effective for large scans
rowCallback function()

process records when called like this `callback(rows, next, info)
endCallback DbResultCallback <optional>

end of scan when called like this: `callback(err)
Example
// Copy all users from one db into another
db.scan("users", {}, { count: 10, pool: "dynamodb" }, (row, next) => {
   db.add("users", row, { pool: "pg" }, next);
}, console.log;

(static) search(table, query, optionsopt, callbackopt)

Description:

  • Perform full text search on the given table, the database implementation may ignore table name completely in case of global text index.

    Query in general is an object or text string with the format that is supported by the underlying driver, the db module DOES NOT PARSE the query at all.

    Options make take the same properties as in the select method.

    A special query property q may be used for generic search in all fields (Elasticsearch)

    Without full text search support in the driver this may return nothing or an error.

Source:
Parameters:
Name Type Attributes Description
table string

db table name
query object | string

condition
options DbRequestOptions <optional>
callback DbResultCallback <optional>
Example
db.search("users", { roles: "admin", q: "john*" }, { pool: "elasticsearch" }, lib.log);
db.search("users", "john*", { pool: "elasticsearch" }, lib.log);

(static) select(table, query, optionsopt, callback)

Description:

  • Select objects from the database that match supplied conditions.

Source:
Parameters:
Name Type Attributes Description
table string

table to read
query object

an object with properties for the condition, all matching records will be returned. Columns may contain deep property path can be used as well, like prop.subprop, the top level column must be defined to work, this is for DBs that support JSON objects like DymamoDB, Elasticsearch.
options DbRequestOptions <optional>
callback DbResultCallback

as (err, rows, info)
Examples

get by primary key

db.select("users", { login: 'admin' }, (err, rows) => {  });

get all users by roles, only 2 columns

db.select("users", { role: 'admin' }, { select: 'login,role' }, (err, rows, info) => { });

all Johns sorted by time, recent first

db.select("users", { name: 'John:' }, { sort: "mtime", desc: 1, ops: { name: "begins_with" } }, (err, rows) => { });

select users by type modified in last 24 hours

db.select("users", { role: 'admin', mtime: Date.now()-86400000 }, { ops: { mtime: "gt" } }, (err, rows) => { });

same as above with column alias

db.select("users", {
   role_$in: ['admin', 'user'],
   mtime_$gt: Date.now()-86400000
}, (err, rows) => { });

same as above OR created within 24hrs

db.select("users", {
   role: 'admin',
   $or: {
      mtime_$gt: Date.now()-86400000
      ctime_$gt: Date.now()-86400000
   },
}, (err, rows) => { });

select users by JSON sub property using Elasticsearch

db.select("users", { "settings.status": "ok" }, { pool: "elasticsearch" }, (err, rows, info) => { });

select admin users with tags/roles using simple SQL join

const query = {
   role_$in: ["admin", "user"],
   $join: { tag: "tags.id" },
}
const select = "users.*, tags.name as tagname"

db.select("users", query, { select, pool: "pg" }, lib.log)

(static) setConfig()

Description:

  • Update and create a DB config record for the given name/type, updates the last record only

Source:

(static) setProcessColumns()

Description:

  • Add a callback to be called after each cache columns event, it will be called for each pool separately. The callback to be called may take options argument and it is called in the context of the pool.

    The primary goal for this hook is to allow management of the existing tables which are not own by the backendjs application. For such tables, because we have not created them, we need to define column properties after the fact and to keep column definitions in the app for such cases is not realistic. This callback will allow to handle such situations and can be used to set necessary propeties to the table columns.

Source:
Example

a few public columns, allow an admin to see all the columns

db.setProcessColumns(function() {
  var cols = db.getColumns("users", { pool: this.name });
  for (var name in cols) {
    if (["id","name"].includes(name)) cols[name].pub = 1; else cols[name].admin = 1;
  }
})

(static) setProcessRow()

Description:

  • Assign a processRow callback for a table, this callback will be called for every row on every result being retrieved from the specified table thus providing an opportunity to customize the result.

    type defines at what time the callback will be called:

    • pre - making a request to the db on the query record
    • post - after the request finished to be called on the result rows

    All assigned callback to this table will be called in the order of the assignment.

    The callback accepts 2 arguments: function(req, row) where:

    • req - the original request for a db operation with required
    • row - a row from the result

    When producing complex properties by combining other properties it needs to be synchronized using both pre and post callbacks to keep the record consistent.

    For queries returning rows, if the callback returns true for a row it will be filtered out and not included in the final result set.

Source:
Example
db.setProcessRow("post", "bk_user", (req, row) => {
   if (row.birthday) row.age = Math.floor((Date.now() - lib.toDate(row.birthday))/(86400000*365));
});

db.setProcessRow("post", "icons", (req, row) => {
   if (row.type == "private" && row.id != req.options.user.id) return true;
});

(static) sql(test, optionsopt, callbackopt)

Description:

  • Execute arbitrary SQL-like statement if the pool supports it, values must be an Array with query parameters or can be omitted.

Source:
Parameters:
Name Type Attributes Description
test string

SQL statement
Array.<any>

values for position placeholders
options DbRequestOptions <optional>
callback DbResultCallback <optional>
Example
db.sql("SELECT id,name FROM users WHERE name=$1 LIMIT $2", ["admin", 1], { pool: "sqlite" }, lib.log)

db.sql("SELECT * FROM users", { pool: "dynamodb" }, lib.log)

db.sql("SELECT * FROM users WHERE name=$1", ["John"], { pool: "pg", count: 10 }, lib.log)

db.sql("SELECT * FROM users", lib.log);

(static) transaction(list, optionsopt, callbackopt)

Description:

  • Same as the module:db.bulk but in transaction mode, all operations must succeed or fail. Not every driver can support it

    • DynamoDB: only 25 operations can be done at the same time, if the list is larger then it will be sequentially run with batches of 25 records.

    • Elasticsearch: same as regular bulk

    • SQL: wraps all ops into standard SQL transaction using (BEGIN/COMMIT/ROLLBACK TRANSACTION).

      NOTE: For specific SQL syntax the first statement can be BEGIN ..., in this case only COMMIT/ROLLBACK will be added.

Source:
Parameters:
Name Type Attributes Description
list Array.<object>

an array of objects to get/put/update/delete from the database in the format:
options DbRequestOptions <optional>
callback DbResultCallback <optional>

second arg is a list of records with errors, same input record with added property error
Examples
var ops = [
   { text: "BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE" },
   { op: "del", table: "users", query: { login: "test1" } },
   { op: "incr", table: "bk_counter", query: { id:2, like:1 } },
   { table: "users", query: { login: "test2", id:2, name:"test2" } }
]
db.transaction(ops, { pool: "pg" }, lib.log);
db.transaction([
  { op: "del", table: "users", query: { login: "test1" } },
  { op: "add", table: "users", query: { login: "test2", name: "tester 2" } },
], lib.log);

(static) update(table, query, optionsopt, callbackopt)

Description:

  • Update existing object in the database. The primary use case is to update one row by the primary key, this way all columns and values are in the query object.

Source:
Parameters:
Name Type Attributes Description
table string

table to use
query object

an actual record to be updated, primary key properties may be specified here or in the expected object
options DbRequestOptions <optional>
callback DbResultCallback <optional>
Examples

update users by primary key login

db.update("users", { login: 'test', id: '123' }, (err, rows, info) => {
  console.log('updated:', info.affected_rows);
});

db.update("users", { login: 'test', id: '123', first_name: 'Mr' }, { pool: 'pg' }, (err, rows, info) => {
   console.log('updated:', info.affected_rows);
});

update a row with primary key login = 'test' and first_name='Carl' to 'John'

db.update("users", { login: 'test', first_name: 'John' }, { query: { first_name: "Carl" } }, (err, rows, info) => {
   console.log('updated:', info.affected_rows);
});

update all rows with first_name='Carl' or NULL to first_name='John'

db.update("users", { first_name: 'John' }, { query: { $or: { first_name: "Carl", first_name_$: null } }, { pool: "elasticsearch" }, (err, rows, info) => {
   console.log('updated:', info.affected_rows);
});

remove "admin" role from the roles column by primary key

db.update("users", { login: "test", roles_$del: "admin" }, { pool: "dynamodb" }, (err, rows, info) => {
   console.log('updated:', info.affected_rows);
});

(static) updateAll(table, query, update, optionsopt, callbackopt)

Description:

  • Update all records that match given query using properties from the data object

Source:
Parameters:
Name Type Attributes Description
table string

db table name
query object

condition for update
update object

properties to update
options DbRequestOptions <optional>
callback DbResultCallback <optional>
Example

set status ok for all users with type = 'admin' or 'staff'

db.updateAll("users", { type: ["admin", "staff"] }, { status: "ok" }, lib.log)

(static) upgrade(table, schema, optionsopt, callbackopt)

Description:

  • Upgrade a table with missing columns from the definition list, if after the upgrade new columns must be re-read from the database then info.affected_rows must be non zero.

Source:
Parameters:
Name Type Attributes Description
table string

table name
schema DbTable

an object with table schema
options DbConfigOptions <optional>

options for the database driver
callback DbResultCallback <optional>

response handler