db

(static) args :Array.<ConfigOptions>

Source:

• db.js, line 356

(static) config :string

Description:

• Databse pool for config

Source:

• db.js, line 428

(static) configMap :object

Description:

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

Source:

• db.js, line 435

Properties:

(static) keys :object

Description:

• All primary keys for each table by name

Source:

• db.js, line 464

(static) modules :object

Description:

• Database modules by type

Source:

• db.js, line 399

(static) pool :string

Description:

• Default database pool for the backend

Source:

• db.js, line 425

(static) pools :object

Description:

• Database connection pools by pool name

Source:

• db.js, line 404

(static) tables :object

Description:

• All tables from all modules by table name

Source:

• db.js, line 461

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

Description:

• Async version of module:db.add

Source:

• db.js, line 1057

Parameters:

Example

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

(async, static) abatch(list, optionsopt)

Description:

• Async version of module:db.batch

Source:

• db.js, line 1476

Parameters:

(async, static) abulk(list, optionsopt)

Description:

• Async version of module:db.bulk

Source:

• db.js, line 1523

Parameters:

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

Description:

• Async version of module:db.copy

Source:

• db.js, line 1855

Parameters:

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

Description:

• Async version of module:db.create

Source:

• db.js, line 1920

Parameters:

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

Description:

• Insert new object into the database

Source:

• db.js, line 1036

Parameters:

Example

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

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

Description:

• Async version of module:db.del

Source:

• db.js, line 1292

Parameters:

Example

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

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

Description:

• Async version of module:db.delAll

Source:

• db.js, line 1331

Parameters:

(async, static) adrop(table, optionsopt)

Description:

• Async version of module:db.drop

Source:

• db.js, line 1998

Parameters:

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

Description:

• Async version of module:db.get

Source:

• db.js, line 877

Parameters:

Example

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

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

Description:

• Async version of module:db.incr

Source:

• db.js, line 1107

Parameters:

Example

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

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

Description:

• Async version of module:db.list

Source:

• db.js, line 1393

Parameters:

Example

const { err, data } = db.alist("users", ["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

Source:

• db/utils.js, line 59

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

Description:

• Async version of module:db.put

Source:

• db.js, line 1148

Parameters:

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:

• db.js, line 618

Parameters:

Returns:

Example

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

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

Description:

• Async version of module:db.scan

Source:

• db.js, line 1687

Parameters:

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

Description:

• Async version of module:db.search

Source:

• db.js, line 1015

Parameters:

Example

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

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

Description:

• Async version of module:db.select

Source:

• db.js, line 965

Parameters:

Example

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

(async, static) asql(test, optionsopt)

Description:

• Async version of module:db.sql

Source:

• db.js, line 2044

Parameters:

Example

const { err, data, info } = await db.asql("SELECT * FROM users WHERE name=$1 LIMIT $2", ["admin", 1])

(async, static) atransaction(list, optionsopt)

Description:

• Async version of module:db.transaction

Source:

• db.js, line 1578

Parameters:

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

Description:

• Async version of module:db.update

Source:

• db.js, line 1210

Parameters:

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, data, query, optionsopt)

Description:

• Async version of module:db.updateAll

Source:

• db.js, line 1251

Parameters:

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

Description:

• Async version of module:db.upgrade

Source:

• db.js, line 1954

Parameters:

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

• db.js, line 1415

Parameters:

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)

Source:

• db.js, line 1494

Parameters:

Example

var ops = [
   { 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 } },
   { 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:

• db/tables.js, line 219

Parameters:

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

• db/utils.js, line 155

(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

Source:

• db/config.js, line 101

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

• db.js, line 2065

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

Description:

• Convert native database error in some generic human readable string

Source:

• db/utils.js, line 89

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

Source:

• db/prepare.js, line 627

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

Source:

• db.js, line 1707

Parameters:

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

Source:

• db.js, line 1875

Parameters:

Example

// DynamoDB global index `name_descr` will include `type` property in the projection but local index `id_title` will include
// name and descr properties
db.create("test_table2", {
    id: { primary: 1, type: "int", index1: 1, dynamodb: { projections1: ["name","descr"] } },
    type: { primary: 2 },
    title: { index1: 2 },
    name: { index: 1, dynamodb: { projections: ["type"] } },
    descr: { index: 2 },
});

(static) createTables(options, callbackopt)

Description:

• Create or upgrade the tables for the given pool

Source:

• db/tables.js, line 41

Parameters:

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

Description:

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

Source:

• db.js, line 1272

Parameters:

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:

• db.js, line 1315

Parameters:

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:

• db/tables.js, line 104

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:

• db.js, line 1973

Parameters:

(static) existsPool()

Description:

• Returns true if a pool exists

Source:

• db/utils.js, line 79

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

Description:

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

Source:

• db.js, line 843

Parameters:

Example

db.get("users", { 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

Source:

• db/cache.js, line 11

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:

• db/utils.js, line 113

Parameters:

Returns:

(static) getConfig()

Description:

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

Source:

• db/config.js, line 60

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

• db/utils.js, line 192

Parameters:

Returns:

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

Description:

• Return primary keys for a table or empty array

Source:

• db/utils.js, line 178

Parameters:

Returns:

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

• db/utils.js, line 12

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.

Source:

• db/utils.js, line 27

(static) getPools()

Description:

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

Source:

• db/utils.js, line 45

(static) getProcessRows()

Description:

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

Source:

• db/prepare.js, line 714

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

Description:

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

Source:

• db/utils.js, line 206

Parameters:

Returns:

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

• db.js, line 1079

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:

Source:

• db.js, line 489

Parameters:

(static) initColumns()

Description:

• Merge cached columns into tables

Source:

• db/tables.js, line 182

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

Source:

• db/config.js, line 11

(static) initTables()

Description:

• Merge all tables from all modules

Source:

• db/tables.js, line 24

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

• db.js, line 1351

Parameters:

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) });

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

Description:

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

Source:

• db/utils.js, line 225

Parameters:

Returns:

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

• db/prepare.js, line 130

Parameters:

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

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

Source:

• db/prepare.js, line 252

Parameters:

Returns:

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

(static) prepareQuery(req)

Description:

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

Source:

• db/prepare.js, line 157

Parameters:

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

Description:

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

Source:

• db/prepare.js, line 291

Parameters:

Returns:

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

• db.js, line 1128

Parameters:

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:

• db.js, line 562

Parameters:

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:

• db.js, line 788

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:

• db/tables.js, line 201

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

• db/prepare.js, line 726

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

• db.js, line 1596

Parameters:

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

Source:

• db.js, line 986

Parameters:

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:

• db.js, line 898

Parameters:

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) => { });

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) => { });

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:

• db/config.js, line 146

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

• db/prepare.js, line 688

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:

• db/prepare.js, line 757

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:

• db.js, line 2016

Parameters:

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:

• db.js, line 1541

Parameters:

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:

• db.js, line 1170

Parameters:

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, data, query, optionsopt, callbackopt)

Description:

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

Source:

• db.js, line 1232

Parameters:

Example

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

db.updateAll("users", { status: "ok" }, { type: ["admin", "staff"] }, 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:

• db.js, line 1939

Parameters: