db

Members

(inner) args :Array.<ConfigOptions>

Type:

• Array.<ConfigOptions>

(inner, constant) bk_config

Description:

• Config table schema

Methods

(static) aadd(table, query, optionsopt)

Description:

• Async version of db.add

Parameters:

Example

const { err, data, info } = await db.aadd("bk_user", { id: '123', login: 'admin', name: 'test' })

(static) abatch()

Description:

• Async version of db.batch

(static) abulk()

Description:

• Async version of db.bulk

(static) acopy()

Description:

• Async version of db.copy

(static) acreate()

Description:

• Async version of db.create

(static) add(table, query, optionsopt)

Description:

• Insert new object into the database

Parameters:

Example

db.add("bk_user", { id: '123', login: 'admin', name: 'test' }, (err, rows, info) => {
});

(static) adel(table, query, optionsopt)

Description:

• Async version of db.del

Parameters:

Example

const { err, info } = db.adel("bk_user", { login: '123' });
console.log('deleted:', info.affected_rows);

(static) adelAll(table, query, optionsopt)

Description:

• Async version of db.delAll

Parameters:

(static) adrop()

Description:

• Async version of db.drop

(static) aget(table, query, optionsopt)

Description:

• Async version of db.get

Parameters:

Example

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

(static) aincr(table, query, optionsopt)

Description:

• Async version of db.incr

Parameters:

Example

const { err, data, info } = await db.aincr("bk_counter", { id: '123', like0: 1, invite0: 1 })

(static) alist()

Description:

• Async version of db.list

Example

const { err, data } = db.alist("bk_user", ["id1", "id2"]);
console.log(data);

(static) applyPoolOptions()

Description:

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

(static) aput(table, query, optionsopt)

Description:

• Async version of db.put

Parameters:

Example

const { err, data } = await db.aput("bk_user", { id: '123', login: 'test', name: 'test' })

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

Parameters:

Returns:

Example

const { err, data } = await db.aquery({ text: "SELECT ....." }, { pool: "sqlite" });

(static) ascan()

Description:

• Async version of db.scan

(static) asearch(table, query, optionsopt)

Description:

• Async version of db.search

Parameters:

Example

await db.asearch("bk_user", "john*", { pool: "elasticsearch" });

(static) aselect(table, query, optionsopt)

Description:

• Async version of db.select

Parameters:

Example

const { err, data } = await db.aselect("bk_user", { roles: 'admin' });

(static) asql()

Description:

• Async version of db.sql

(static) atransaction()

Description:

• Async version of db.transaction

(static) aupdate(table, query, optionsopt)

Description:

• Async version of db.update

Parameters:

Example

const { err, data, info } = await db.aupdate("bk_user", { login: 'test', name: 'Test' })
if (!err && !info?.affected_rows) logger.error("no updates")

(static) aupdateAll(table, data, query, optionsopt)

Description:

• Async version of db.updateAll

Parameters:

(static) aupgrade()

Description:

• Async version of db.upgrade

(static) batch(list)

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.

Parameters:

Example

var ops = [ { table: "bk_counter", query: { id:1, like:1 } },
             { op: "put", table: "bk_user", query: { login: "test", id:1, name:"test" }]
 db.batch(ops, { factorCapacity: 0.5 }, lib.log);

(static) bulk()

Description:

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

  On return the second arg to the callback is a list of records with errors, same input record with added property errstatus and errmsg

  NOTE: DynamoDB only supports add/put/del only and 25 at a time, if more specified it will send multiple batches

Example

var ops = [ { table: "bk_counter", query: { id:1, like:1 } },
            { op: "del", table: "bk_user", query: { login: "test1" } },
            { op: "incr", table: "bk_counter", query: { id:2, like:1 } },
            { table: "bk_user", 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,

Parameters:

(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: runMode, role, roles, tag, region

Example

top is runMode(prod), main is role(shell),tag(local), other is region(none)
 - prod
 - prod-shell
 - prod-shell-none
 - prod-local
 - prod-local-none

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

Description:

• Convert native database error in some generic human readable string

Parameters:

Returns:

(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

Parameters:

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

Parameters:

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

Description:

• Create a table using column definitions represented as a list of objects. Each column definition may contain the following properties:

  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.*

  NOTE: Index creation is not required and all index properties can be omitted, it can be done more effectively using native tools for any specific database, this format is for simple and common use cases without using any other tools but it does not cover all possible variations for every database. But all indexes and primary keys created outside of the backend application will be detected properly by module:db.cacheColumns and by each pool cacheIndexes methods.

  Each database pool also can support native options that are passed directly to the driver in the options, these properties are defined in the object with the same name as the db driver, all properties are combined, for example to define provisioned throughput for the DynamoDB index:

  DynmamoDB:

  To create a DynamoDB table with global 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 second key column contains global property then it is a global index as well, below we create global secondary index on property 'name' only, in the example above it was local secondary index for id and name. Also a local secondary index is created on id,title.

  DynamoDB projection is defined by a projections property, can be a number/boolean or an array with index numbers for regular columns but the hash property can provide its own projections for whole index at once, can be ALL or a list of attributes.

  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.

Parameters:

Example

db.create("test_table", {
    id: { primary: 1, type: "int", index: 1, custom: { dynamodb: { readCapacity: 50, writeCapacity: 50 } } },
    type: { primary: 2, pub: 1, projections: 1 },
    name: { index: 1, pub: 1 } }
});

db.create("test_table", {
    id: { primary: 1, type: "int", index1: 1 },
    type: { primary: 2, projections: [0] },
    name: { index: 1, projections: 1 },
    title: { index1: 1, projections: [1] } },
    descr: { index: 2, projections: [0, 1] },
});

(static) createTables()

Description:

• Create or upgrade the tables for the given pool

(static) del(table, query, optionsopt)

Description:

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

Parameters:

Example

db.del("bk_user", { 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

Parameters:

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

Example

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

(static) drop()

Description:

• Drop a table

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

Description:

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

Parameters:

Example

db.get("bk_user", { login: '12345' }, function(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

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) getConfig()

Description:

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

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

Parameters:

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

(static) getPools()

Description:

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

(static) getProcessRows()

Description:

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

(static) incr(table, query, optionsopt)

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.updateOps 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, for DynamoDB and Elasticsearch a new record will be created if it does not exist yet. To disable upsert pass noupsert in the options.

Parameters:

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:

Parameters:

(static) initColumns()

Description:

• Merge cached columns into tables

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

(static) initTables()

Description:

• Merge all tables from all modules

(static) list(table, query, optionsopt)

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

Parameters:

Example

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

(static) prepare() → {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.

Returns:

(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 _$ takes precedence over the options.ops

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

Parameters:

Returns:

Example

{ name_$contains: "cat" } // means use the `contains` op for the column `name`
{ $or: { id: 1, id_$: 2 } }  // means id=1 OR id=2

(static) prepareQuery()

Description:

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

(static) prepareRequest(options) → {DBRequest}

Description:

• Prepare a DB request object with required properties

Parameters:

Returns:

(static) put(table, query, optionsopt)

Description:

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

Parameters:

Example

db.put("bk_user", { 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.

Parameters:

Example

var req = db.prepareRequest({
    pool: "sqlite",
    text: "SELECT a.id,c.type FROM bk_user 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 bk_user 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:

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

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

(static) scan(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 obj is the same as passed to the db.select method which defined a 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:

  • table - table to scan
  • query - an object with query conditions, same as in db.select
  • options - same as in db.select, with the following additions:

Parameters:

Example

// Copy all users from one db into another
db.scan("bk_user", {}, { count: 10, pool: "dynamodb" }, (row, next) => {
   db.add("bk_user", row, { pool: "pg" }, next);
}, (err) => { });

(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 a text string with the format that is supported by the underlying driver, the db module DOES NOT PARSE the query at all if the driver supports full text search, otherwise it behaves like select.

  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.

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

Parameters:

Example

db.search("bk_user", { roles: "admin", q: "john*" }, { pool: "elasticsearch" }, lib.log);
db.search("bk_user", "john*", { pool: "elasticsearch" }, lib.log);

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

Description:

• Select objects from the database that match supplied conditions.

Parameters:

Example

// get by primary key

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

// get all users by roles, only 2 columns

db.select("bk_user", { roles: ['admin','user'] }, { select: 'login,roles' }, (err, rows) => { });

// all Johns sorted by time, recent first

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

// select users by type modified in last 24 hours

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

// same as above with column alias

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

// select users by JSON sub property using Elasticsearch

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

(static) setConfig()

Description:

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

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

  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 p in  cols) {
              if (["id","name"].indexOf(p) > -1) cols[p].pub = 1; else cols[p].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.

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

Description:

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

Example

db.sql("SELECT * FROM bk_user WHERE name=$1 LIMIT $2", ["admin", 1], { pool: "sqlite" }, lib.log)
db.sql("SELECT * FROM bk_user", { pool: "dynamodb" }, lib.log)
db.sql("SELECT * FROM bk_user", { pool: "dynamodb", count: 10 }, lib.log)

(static) transaction()

Description:

• Same as the db.bulk but in transaction mode, all operations must succeed or fail. Not every driver can support it, in DynamoDB case only 10 operations can be done at the same time, if the list is larger then it will be sequentially run with batches of 25 records.

  In case of error the second arg will contain the records of the failed batch

(static) update(table, query, optionsopt)

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.

Parameters:

Example

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

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

// update a row with login = 'test' and first_name='Carl' to 'John'
db.update("bk_user", { 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 'John'
db.update("bk_user", { first_name: 'John' }, { query: { or$: { first_name: "Carl", first_name_$: null } }, (err, rows, info) => {
   console.log('updated:', info.affected_rows);
});

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

Description:

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

Parameters:

Example

// set status ok for all users with type = 'admin' or 'staff'
db.updateAll("bk_user", { status: "ok" }, { type: ["admin", "staff"] }, lib.log)

(static) upgrade()

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.