About

Database neutral microservice interface with advanced Query and revolutionary Transaction builder.

Auto-configuration based on process environment variables and connection pooling by design.

At the moment, the following databases are supported our of the box:

MySQL
PostgreSQL
SQLite
Any custom plugin extending L?Service and implementing IDriver query builder helper which should be registered through AutoConfig.register and QueryBuilder.addDriver() calls.

Note: note specific database support is to be split into separate packages at some point. Meanwhile, please use yarn install --ignore-optional to minimize deps.

Documentation --> FutoIn Guide

Reference implementation of:

FTN17: FutoIn Interface - Database
Version: 1.0

Spec: FTN17: FutoIn Interface - Database v1.x

Author: Andrey Galkin

Installation for Node.js

Command line:

$ npm install futoin-database --save

Concept

Interface is split into several levels which are combined in inheritance chain.

Fundamental difference from traditional interfaces is lack of large result set support, cursors and explicit transaction control. This is done by intention to forbid undesired database operation patterns.

Level 1

The very basic level for query execution with minimal safety requirements.

Level 2

Transaction execution abstraction with "single call" pattern.

The overall idea is to execute a list of statements on DB side in single transaction one-by-one. After each xfer, trivial validation is done like amount of affected rows or count of rows in result. This allows creating complex intermediate checks in native DB query. Such pattern avoids blocking usually expensive DB connections and forces to execute transaction with no client-side delays. Also, proper release of connection to DB connection pool is ensured.

For cases, when one of later queries requires result data of previous queries (e.g. last insert ID) a special mechanism of back references is implemented which supports single and multiple (IN/NOT IN ops) value mode.

If at any step an error occurs then whole transaction is rolled back.

Note: internally, it's assumed that there is a limited number of simultaneous DB connection allowed which are managed in connection pool for performance reasons, but such details are absolutely hidden from clients.

2.3. Level 3

Large result streaming through BiDirectional channel. Database metadata and ORM-like abstraction. TBD.

2.4. Implementation details

Auto-configuration

DB_TYPE, DB_HOST, DB_PORT, DB_USER, DB_PASS, DB_DB and DB_MAXCONN environment variables are used to autoconfigure "default" connection.

DB_{NAME}_TYPE, DB_{NAME}_HOST, DB_{NAME}_PORT and other variable names are used to configure any arbitrary "{name}" connection. The list of expected connection names must be supplied to AutoConfig() function.

It possible to supply required/supported database type as "type" option of preconfigured connection. Example:

AutoConfig(as, ccm, {
    // DB_MAIN_TYPE must be either of these
    main : { type: ['mysql', 'postgresql'] },
    // DB_DWH_TYPE must be exactly this
    dwh : { type: 'postgresql' },
});

All connections are accessible through dependency-injection approach by fundamental design of FutoIn Invoker CCM pattern. Specific CCM instance is extended with .db(name='default').

All connections are registered with '#db.' prefix in CCM. Therefore, use of ccm.db() instead of ccm.iface(#db.${name}) is strongly encouraged.

Results

There is a single "raw" result object format:

.rows - array of array of values
.fields - array of field names in the same order as values in rows
.affected - amount of affected rows by last operation (it's quite database specific).
- Note: to get actual changed row count, try to use extra .where() conditions with <> of set values

As such transmission efficient format is not very handy for practical programming the result can be manually associated through iface.associateResult() call.

.rows - array of objects with key => value pairs
.affected - the same as in original raw response

The same can be implicitly achieved through using QueryBuilder#executeAssoc(), XferBuilder#executeAssoc() and Prepared#executeAssoc(). Format of next AsyncStep:

(as, rows, affected) => {}
- rows and affected directly correspond to fields of associated result

Transaction results

The format of inividual query is the same as for single queries, but extended with .seq field corresponding to # of query in original list. It can be used for safety checks.

Note: query result is not returned, unless { result: true} query option is set - that's done by intention as normally result of only a single SELECT is required while the rest is covered with Transaction Conditions.

Insert ID

It's a known painful moment in many abstractions. For databases like MySQL last insert ID is always "selected" as special $id result field.

For QueryBuilder abstraction please use getInsertID(id_field) call for cross-database compatibility.

Conditions

WHERE, HAVING and JOIN support the same approach to conditions:

raw string is treated as is and joined with outer scope AND or OR operator
Object and Map instance is treated as key=>value pairs joined with AND operator
- all values are auto-escaped, unless wrapped with QueryBuilder#expr() call
- all keys may have "{name} {op}" format, where {op} is one of:
  - = - equal
  - <> - not equal
  - > - greater
  - >= - greater or equal
  - < - less
  - <= - less or equal
  - IN - in array or sub-query (assumed)
  - NOT IN - not in array or sub-query (assumed)
  - BETWEEN - two value tuple is assumed for inclusive range match
  - NOT BETWEEN - two value tuple is assumed for inverted inclusive range match
  - LIKE - LIKE match
  - NOT LIKE - NOT LIKE match
  - other ops may be implicitly supported
Array - the most powerful condition builder - almost internal representation
- first element is operator for entire scope: 'AND' (default) or 'OR'
- all following elements can be:
  - raw strings
  - Objects or Maps
  - inner arrays with own scope operator
  - another QueryBuilder instance to be used as sub-query

Transaction conditions

Normally, during transaction execution, we are interested that some operation does modifications successfully, but we do not need its actual result.

The following query options are supported inside transaction:

result=false - if true then result must be returned in result list of L2Face#xfer() call
affected=null
- boolean - check if there are affected rows (true) or no affected rows (false)
- integer - check if affected row count exactly matches the value
selected=null
- boolean - check if there are selected rows (true) or no selected rows (false)
- integer - check if selected row count exactly matches the value

Transaction result value back references

Very often, we insert main object and then insert associated object and need to use auto-generated values like primary ID keys from the previous query.

Another case, is when we do SELECT-FOR-UPDATE query and need to modify exactly those rows.

There is a special XferQueryBuilder#backref(qb, field, multi=false) placeholder supported. The function must be called on exactly the target XferQueryBuilder object. Example:

const xfer db.newXfer();
const sel_q = xfer.select('Tbl').get('id').where('name LIKE', 'abc%').forUpdate();
const upd_q = xfer.update('Tbl');
upd_q.set('name', upd_q.expr("CONCAT('UPD_', name)")
    .where('id IN', upd_q.backref(sel_q, 'id', true);
xfer.execute(as);

Transaction isolation levels

All standard ACID isolation levels are supported: READ UNCOMMITTED, READ COMMITTED, REPEATEABLE READ and SERIALIZABLE. Please use related L2Face constants. Example:

const db = ccm.db();
db.newXfer(db.REPEATABLE_READ);

Sub-queries

Everywhere specific database implementation allows sub-queries, they can be used:

As select or join entity - alias must be provided in array format:
- [QueryBuilder(), 'Alias']
As any condition value part, except raw strings
As expression for .get(alias, expr) calls

Efficient execution (prepared QueryBuilder & XferQueryBuilder)

Obviously, executing the same steps to create the same query again and again is not efficient, if only parameters change. Therefore, named value placeholders are supported in format of ":name" in raw queries or wrapped with .param('name') calls in QueryBuilder.

Both query and transaction builders support .prepare() call. All queries get built into string templates for efficient repetitive execution.

For purpose of re-using already prepared statement or transaction there is L1Face#getPreapred(symbol, prepare_callback) API. See example #8.

Multi-row insert

It's quite inefficient to insert large amount of data with individual statements. It's possible call QueryBuilder#newRow() on INSERT type instance. It's safe to call before or after - empty rows get ignored.

At the moment, all supported databases have this feature.

Error handling

All errors are regular FutoIn exceptions with error code and error info:

InvalidQuery - broken query due to syntax or semantics
Duplicate - unique key constraint violation
LimitTooHigh - more than 1000 rows in result
DeadLock - database deadlock detected in transaction
XferCondition - violation of transaction condition constraints
XferBackRef - invalid transaction value back reference
OtherExecError - any other execution error

Example of use:

as.add(
    (as) => db.query(...),
    (as, err_code) => {
        // AsyncSteps convention for error info and last exception
        console.log(as.state.error_info);
        console.log(as.state.last_exception);
        
        if (err_code === 'Duplicate') {
            // Example of ignoring Duplicate error
            as.success();
        }
    }
);

QueryBuilder & XferBuilder cloning

Sometimes, 80+% of queries are the same and only a small part like filter-based conditions or selected values are changed. For such cases, a special .clone() member is provided. Example:

// standalone query
const base_qb = db.select('SomeTbl').get(['id', 'name']);
base_qb.clone().where('id', 1(.execute(as);
base_qb.clone().where('id', 1(.execute(as);

// transaction
const base_xfer = db.newXfer();
base_xfer.select('SomeTbl').get(['id', 'name']).forSharedRead();

let xfer = base_xfer.clone();
base_xfer.insert('OtherTbl').set('name', 'abc');
xfer.executeAssoc(as);

Examples

1. Raw queries

/**
 * Process ENV variables:
 *
 * DB_TYPE=mysql
 * DB_HOST=127.0.0.1
 * DB_PORT=3306
 * DB_USER=testuser
 * DB_PASS=testpass
 * DB_DB=testdb
 * DB_MAXCONN=10
 */

const $as = require('futoin-asyncsteps');
const AdvancedCCM = require('futoin-invoker/AdvancedCCM');
const DBAutoConfig = require('futoin-database/AutoConfig');

$as() // Root FutoIn AsyncSteps
    .add(
        // Root step body
        (as) => {
            // Setup main application CCM
            const ccm = new AdvancedCCM();
            // Configure default connection based on environment variables
            DBAutoConfig(as, ccm);
            
            // Next -> do query
            as.add((as) => {
                ccm.db().query(as, 'SELECT 1+2 AS Sum');
            });
            // Next -> handle result
            as.add((as, res) => {
                res = ccm.db().associateResult(res);
                console.log(`Sum: ${res[0].Sum}`);
            });
            // Ensure proper shutdown
            // All DB pools are automatically closed
            as.add((as) => {
                ccm.close();
            });
        },
        // Overall error handler
        (as, err) => {
            console.log(`${err}: ${as.state.error_info}`);
            console.log(as.state.last_exception);
        }
    )
    // Start execution
    .execute();

2. Query Builder

// Setup main application CCM
const ccm = new AdvancedCCM();
// Configure default connection based on environment variables
DBAutoConfig(as, ccm);

// Next -> run queries
as.add((as) => {
    const db = ccm.db();
    let q;
    
    // prepare table
    // ---
    db.query(as, 'DROP TABLE IF EXISTS SomeTbl');
    db.query(as, 'CREATE TABLE SomeTbl(' +
            'id int auto_increment primary key,' +
            'name varchar(255) unique)');
    
    // insert some data
    // ---
    // - simple set
    db.insert('SomeTbl').set('name', 'abc').execute(as);
    // - set as object key=>value pairs
    db.insert('SomeTbl').set({name: 'klm'}).execute(as);
    // - set with Map key=>value pairs
    db.insert('SomeTbl')
        .set(new Map([['name', 'xyz']]))
        .getInsertID('id')
        .executeAssoc(as);
    // use insert ID
    as.add((as, res, affected) => console.log(`Insert ID: ${res[0].$id}`));
    
    // INSERT-SELECT like query
    // ---
    // sub-query must be the only parameter for .set()
    db.insert('SomeTbl').set(
        // DANGER: .get() expects expressions and does not escape them!
        db.select('SomeTbl').get('name', "CONCAT('INS', name)").where('id <', 3)
    ).execute(as);
    
    // update data
    const qb = db.queryBuilder(); // generic query builder for helper API
    
    q = db.update('SomeTbl')
        // - .set can be called multiple times
        .set('id', 10)
        // - please note that set auto-escapes all values, unless wrapped with .expr()
        .set('name', qb.expr('CONCAT(id, name)'))
        // - simple condition
        .where('name', 'klm')
        // - extra calls are implicit "AND"
        // - Most powerful array-based definition which is
        //      very close to how all conditions are handled internally.
        .where([
            'OR', // The scope of OR is only children of this array
            // object as member, all fields are AND assumed
            {
                // there are various generic suppported operators
                'name LIKE': 'kl%',
                // another example
                'id >': 1,
            },
            // Inner complex array
            [
                'AND', // this can be omitted as "AND" is implicit for arrays
                // raw expression as string - DANGER of SQLi, please avoid
                'name NOT LIKE \'xy%\'',
                // another example of operator with two values
                { 'id BETWEEN': [1, 10] }
            ],
            // Note: Map object can also be used
        ]);

    // Dump raw query for inspection
    console.log(`Query: ${q}`);
    // UPDATE SomeTbl SET id=10,name=CONCAT(id, name) WHERE name = 'klm' AND (name LIKE 'kl%' OR id > 1 OR (name NOT LIKE 'xy%' AND id BETWEEN 1 AND 10))
    
    // Finally, execute it
    q.execute(as);

    // Select without entity
    // ---
    db.select().get('atm', 'NOW()').executeAssoc(as);
    as.add((as, res) => console.log(`At the moment: ${res[0].atm}`));
    
    // Select with join of result of sub-query (instead of normal table)
    // ---
    q = db.select('SomeTbl')
        .innerJoin(
            // sub-query
            // NOTE: use of .escape() for .get()
            [ db.select().get('addr', qb.escape('Street 123')), 'Alias'],
            // all where-like conditions are supported here
            '1 = 1' // can be omitted
        );
    console.log(`Query: ${q}`);
    // SELECT * FROM SomeTbl INNER JOIN (SELECT 'Street 123' AS addr) AS Alias ON 1 = 1
    q.executeAssoc(as);
    // inspect result
    as.add((as, res) => console.log(res));
    /*
     * [
     *  { id: 10, name: '10klm', addr: 'Street 123' },
     *  { id: 1, name: 'abc', addr: 'Street 123' },
     *  { id: 4, name: 'INSabc', addr: 'Street 123' },
     *  { id: 5, name: 'INSklm', addr: 'Street 123' },
     *  { id: 3, name: 'xyz', addr: 'Street 123' },
     * ]
     */
});

3. Efficient Query Builder (prepared)

// create a prepared statement with query builder
// ---
const qb = db.queryBuilder(); // helper instance
const prepared_q = db.insert('SomeTbl')
    // notice .param() placeholder
    .set('name', qb.param('nm'))
    .getInsertID('id')
    .prepare();

for (let nm of ['abc', 'klm', 'xyz']) {
    // prepared_q is not QueryBuilder, but Prepared object
    prepared_q.executeAssoc(as, {nm});
    as.add((as, res) =>
        console.log(`Inserted ${nm} ID ${res[0].$id}`));
}

// Inserted abc ID 1
// Inserted klm ID 2
// Inserted xyz ID 3

// raw param query
// ---
// Not recommended raw example  with the same execution speed
const raw_q = `INSERT INTO SomeTbl SET name = :nm`;

for (let nm of ['abc2', 'klm2', 'xyz2']) {
    db.paramQuery(as, raw_q, {nm});
    as.add((as, res) =>
        console.log(`Inserted ${nm} ID ${res.rows[0][0]}`));
}

// Inserted abc2 ID 4
// Inserted klm2 ID 5
// Inserted xyz2 ID 6

4. Simple Transaction Builder

// create a transaction with builder
// ---
const xfer = db.newXfer(); // Read Committed by default

// already known QueryBuilder without execute() call
xfer.insert('SomeTbl').set('name', 'abc');
xfer.insert('SomeTbl').set('name', 'xyz');

// Note the the "result" option to include result in
// overall xfer result
xfer.select('SomeTbl', {result: true})
    .get('C', 'COUNT(*)')
    // add FOR-clause, if supported by DB
    .forUpdate();

// Return result of update and check that any rows are affected
xfer.update('SomeTbl', {result: true, affected: true})
    .set('name', 'klm').where('name', 'xyz');
    
// Run again making sure zero rows are affected
xfer.update('SomeTbl', {affected: 0})
    .set('name', 'klm').where('name', 'xyz');

// Execute of transaction itself
xfer.executeAssoc(as);

as.add((as, results) => {
    console.log(`Count: ${results[0].rows[0].C}`);
    console.log(`First UPDATE affected: ${results[1].affected}`);
});

// Count: 2
// First UPDATE affected: 1

5. Efficient Transaction Builder (prepared)

// create a prepared transaction with builder
// ---
const xfer = db.newXfer(); // Read Committed by default

// already known QueryBuilder without execute() call
xfer.insert('SomeTbl').set('name', xfer.param('n1'));
xfer.insert('SomeTbl').set('name', xfer.param('n2'));

// Note the the "result" option to include result in
// overall xfer result
xfer.select('SomeTbl', {result: true})
    .get('C', 'COUNT(*)')
    .forSharedRead(); // another locking example

// Prepare transaction
const prepared_xfer = xfer.prepare();

// test data
const data = [
    { n1: 'abc', n2: 'xyz' },
    { n1: 'cba', n2: 'zyx' },
];
data.forEach((params, i) => {
    // Efficiently execute prepared transaction
    prepared_xfer.executeAssoc(as, params);

    as.add((as, results) => {
        console.log(`Count for ${i}: ${results[0].rows[0].C}`);
    });
});

// Count for 0: 2
// Count for 1: 4

6. Advanced Transaction Builder (prepared with back references)

// create a prepared transaction with value back references
// ---
const xfer = db.newXfer(db.SERIALIZABLE);

// Insert some parametrized data
const ins1_q = xfer.insert('SomeTbl')
    .set('name', xfer.param('n1'))
    .getInsertID('id');
const ins2_q = xfer.insert('SomeTbl')
    .set('name', xfer.param('n2'))
    .getInsertID('id');

// Ensure two items are selected with brain-damaged conditions
const sel_q = xfer.select('SomeTbl', {selected: 2});
sel_q
    .get('id')
    .where([
        'OR',
        {'name': xfer.param('n1')},
        'id = ' + sel_q.backref(ins2_q, '$id'), // note object of .backref()
    ])
    .forUpdate();
    
// Make sure one row is updated with brain-damaged conditions
const upd_q = xfer.update('SomeTbl', {affected: 1});
upd_q
    .set('name',
            upd_q.expr(`CONCAT('klm', ${upd_q.backref(ins1_q, '$id')})`))
    .where('id IN', upd_q.backref(sel_q, 'id', true))
    .where('name', xfer.param('n1'));

// Prepare transaction
const prepared_xfer = xfer.prepare();

// test data
// ---
const data = [
    { n1: 'abc', n2: 'xyz' },
    { n1: 'cba', n2: 'zyx' },
];
data.forEach((params, i) => {
    // Efficiently execute prepared transaction
    prepared_xfer.executeAssoc(as, params);
});

// Let's see what we have
// ---
db.select('SomeTbl').executeAssoc(as);
as.add((as, res) => console.log(res));

// [ { id: 1, name: 'klm1' },
//   { id: 3, name: 'klm3' },
//   { id: 2, name: 'xyz' },
//   { id: 4, name: 'zyx' } ]

7. Multiple connections per application

/**
 * Process ENV variables:
 *
 * DB_FIRST_TYPE=mysql
 * DB_FIRST_HOST=127.0.0.1
 * DB_FIRST_PORT=3306
 * DB_FIRST_USER=testuser
 * DB_FIRST_PASS=testpass
 * DB_FIRST_DB=testdb
 * DB_FIRST_MAXCONN=10
 * 
 * DB_SECOND_TYPE=postgresql
 * DB_SECOND_HOST=127.0.0.1
 * DB_SECOND_PORT=5432
 * DB_SECOND_USER=testuser
 * DB_SECOND_PASS=testpass
 * DB_SECOND_DB=testdb
 * DB_SECOND_MAXCONN=10
 */

// Configure required connections based on environment variables
DBAutoConfig(as, ccm, {
    first: {},
    second: {},
});

// Next -> do query
as.add((as) => {
    ccm.db('first').query(as, 'SELECT 1+2 AS Sum');
    as.add((as, res) => console.log(`First: ${res.rows[0][0]}`));
    
    ccm.db('second').query(as, 'SELECT 3+2 AS Sum');
    as.add((as, res) => console.log(`Second: ${res.rows[0][0]}`));
    
    // First: 3
    // Second: 5
});

8. Efficient caching of prepared statements for re-use across calls

This is rewritten example #3. The same can be used for prepared transactions.

// create a prepared statement with query builder using L1Face#getPrepared()
// ---
const sym = Symbol('arbitrary');

for (let nm of ['abc', 'klm', 'xyz']) {
    const prepared_q = db.getPrepared(sym, (db) => {
        // executed once
        const qb = db.insert('SomeTbl');
        return qb.set('name', qb.param('nm'))
            .getInsertID('id')
            .prepare();
    });
    
    // prepared_q is not QueryBuilder, but Prepared object
    prepared_q.executeAssoc(as, {nm});
    as.add((as, res) =>
        console.log(`Inserted ${nm} ID ${res[0].$id}`));
}

API documentation

The concept is described in FutoIn specification: FTN17: FutoIn Interface - Database v1.x

Classes

L1Face

Level 1 Database Face

L1Service

Base for Level 1 Database service implementation

XferQuery

L2Face

Level 2 Database Face

L2Service

Base for Level 2 Database service implementation

MySQLService

MySQL service implementation for FutoIn Database interface.addEventListener()

PostgreSQLService

PostgreSQL service implementation for FutoIn Database interface

Expression

Wrapper for raw expression to prevent escaping

Prepared

Interface for prepared statement execution

Helpers

Additional helpers interface

SQLHelpers

Basic logic for SQL-based helpers

QueryBuilder

Neutral query builder

SQLiteService

SQLite service implementation for FutoIn Database interface.addEventListener()

QueryOptions

XferQueryBuilder

Version of QueryBuilder which forbids direct execution.

XferBuilder

Transction builder.

Overall concept is build inividual queries to be executed without delay. It's possible to add result constraints to each query for intermediate checks:

affected - integer or boolean to check DML result
selected - integer or boolean to check DQL result
result - mark query result to be returned in response list

Members

AutoConfig

L1Face

Level 1 Database Face

Kind: global class

L1Face
- instance
  - .query
  - .callStored
  - .getFlavour(as)
  - .queryBuilder(type, entity) ⇒ QueryBuilder
  - .helpers() ⇒ Helpers
  - .delete(entity) ⇒ QueryBuilder
  - .insert(entity) ⇒ QueryBuilder
  - .select(entity) ⇒ QueryBuilder
  - .update(entity) ⇒ QueryBuilder
  - .paramQuery(as, q, params)
  - .associateResult(as_result) ⇒ array
  - .getPrepared(sym, cb) ⇒ Prepared
- static

l1Face.query

Kind: instance property of L1Face
Note: AS result has "rows", "fields" and "affected" members

Param	Type	Description
as	`AsyncSteps`	steps interface
q	`string`	raw query

l1Face.callStored

Kind: instance property of L1Face
Note: see query() for results

Param	Type	Description
as	`AsyncSteps`	steps interface
name	`string`	stored procedure name
args	`array`	positional arguments to pass

l1Face.getFlavour(as)

Get type of database

Kind: instance method of L1Face

Param	Type	Description
as	`AsyncSteps`	steps interface

l1Face.queryBuilder(type, entity) ⇒ `QueryBuilder`

Get neutral query builder object.

Kind: instance method of L1Face
Returns: QueryBuilder - associated instance

Param	Type	Default	Description
type	`string`	`null`	Type of query: SELECT, INSERT, UPDATE, DELETE, ...
entity	`string`	`null`	table/view/etc. name

l1Face.helpers() ⇒ `Helpers`

Get query builder helpers

Helps avoiding temporary variables for cleaner code.

Kind: instance method of L1Face
Returns: Helpers - for specific type

l1Face.delete(entity) ⇒ `QueryBuilder`

Get neutral query builder for DELETE

Kind: instance method of L1Face
Returns: QueryBuilder - associated instance

Param	Type	Description
entity	`string`	table/view/etc. name

l1Face.insert(entity) ⇒ `QueryBuilder`

Get neutral query builder for INSERT

Kind: instance method of L1Face
Returns: QueryBuilder - associated instance

Param	Type	Description
entity	`string`	table/view/etc. name

l1Face.select(entity) ⇒ `QueryBuilder`

Get neutral query builder for SELECT

Kind: instance method of L1Face
Returns: QueryBuilder - associated instance

Param	Type	Default	Description
entity	`string`	`null`	table/view/etc. name

l1Face.update(entity) ⇒ `QueryBuilder`

Get neutral query builder for UPDATE

Kind: instance method of L1Face
Returns: QueryBuilder - associated instance

Param	Type	Description
entity	`string`	table/view/etc. name

l1Face.paramQuery(as, q, params)

Execute raw parametrized query

Kind: instance method of L1Face
Note: Placeholders must be in form ":name"
Note: see query() for results

Param	Type	Description
as	`AsyncSteps`	steps interface
q	`string`	raw query with placeholders
params	`object`	named parameters for replacement

l1Face.associateResult(as_result) ⇒ `array`

Convert raw result into array of associated rows (Maps)

Kind: instance method of L1Face
Returns: array - Array of maps.
Note: original result has "rows" as array of arrays and "fields" map

Param	Type	Description
as_result	`object`	$as result of query() call

l1Face.getPrepared(sym, cb) ⇒ `Prepared`

A handy way to store prepared objects and created on demand

Kind: instance method of L1Face
Returns: Prepared - - associated prepared statement

Param	Type	Description
sym	`Symbol`	unique symbol per prepared statement
cb	`callable`	a callback returning a prepared statement

L1Face.LATEST_VERSION

Latest supported FTN17 version

Kind: static property of L1Face

L1Face.PING_VERSION

Latest supported FTN4 version

Kind: static property of L1Face

L1Face.register(as, ccm, name, endpoint, [credentials], [options])

CCM registration helper

Kind: static method of L1Face

Param	Type	Default	Description
as	`AsyncSteps`		steps interface
ccm	`AdvancedCCM`		CCM instance
name	`string`		CCM registration name
endpoint	`*`		see AdvancedCCM#register
[credentials]	`*`		see AdvancedCCM#register
[options]	`object`	`{}`	interface options
[options.version]	`string`	`"1.0"`	interface version to use

L1Service

Base for Level 1 Database service implementation

Kind: global class

L1Service.register(as, executor, options) ⇒ `L1Service`

Register futoin.db.l1 interface with Executor

Kind: static method of L1Service
Returns: L1Service - instance

Param	Type	Description
as	`AsyncSteps`	steps interface
executor	`Executor`	executor instance
options	`object`	options to pass to constructor
options.host	`string`	database host
options.port	`string`	database port
options.database	`string`	database name
options.user	`string`	database user
options.password	`string`	database password
options.conn_limit	`string`	max connections

XferQuery

Kind: global class
Properties

Name	Type	Description
q	`string`	raw query
affected	`interger` \| `boolean` \| `null`	expected count of rows to be affected
selected	`interger` \| `boolean` \| `null`	expected count of rows to be selected
result	`boolean` \| `null`	mark to return result in response

L2Face

Level 2 Database Face

Kind: global class

L2Face
- instance
- static

l2Face.READ_UNCOMMITTED

Read Uncomitted isolation level constant

Kind: instance property of L2Face

l2Face.READ_COMMITTED

Read Comitted isolation level constant

Kind: instance property of L2Face

l2Face.REPEATABL_READ

Repeatable Read isolation level constant

Kind: instance property of L2Face

l2Face.SERIALIZABLE

Serializable

Kind: instance property of L2Face

l2Face.newXfer([iso_level]) ⇒ `XferBuilder`

Get new transcation builder.

Kind: instance method of L2Face
Returns: XferBuilder - transaction builder instance
See

L2Face#READ_UNCOMMITTED
L2Face#READ_COMMITTED
L2Face#REPEATABL_READ
L2Face#SERIALIZABLE

Param	Type	Default	Description
[iso_level]	`string`	`"RC"`	RU, RC, RR or SRL

L2Face.READ_UNCOMMITTED

Read Uncomitted isolation level constant

Kind: static property of L2Face

L2Face.READ_COMMITTED

Read Comitted isolation level constant

Kind: static property of L2Face

L2Face.REPEATABL_READ

Repeatable Read isolation level constant

Kind: static property of L2Face

L2Face.SERIALIZABLE

Serializable

Kind: static property of L2Face

L2Service

Base for Level 2 Database service implementation

Kind: global class

L2Service.register(as, executor, options) ⇒ `L2Service`

Register futoin.db.l2 interface with Executor

Kind: static method of L2Service
Returns: L2Service - instance

Param	Type	Description
as	`AsyncSteps`	steps interface
executor	`Executor`	executor instance
options	`object`	options to pass to constructor
options.host	`string`	database host
options.port	`string`	database port
options.database	`string`	database name
options.user	`string`	database user
options.password	`string`	database password
options.conn_limit	`string`	max connections

MySQLService

MySQL service implementation for FutoIn Database interface.addEventListener()

Kind: global class
Note: If host is localhost then 'socketPath' is from 'port' option.

PostgreSQLService

PostgreSQL service implementation for FutoIn Database interface

Kind: global class

Expression

Wrapper for raw expression to prevent escaping

Kind: global class

expression.toString() ⇒ `string`

Allows easy joining with raw query

Kind: instance method of Expression
Returns: string - as is

Prepared

Interface for prepared statement execution

Kind: global class

Prepared
- .execute(as, [params])
- .executeAsync(as, [params])

prepared.execute(as, [params])

Kind: instance method of Prepared

Param	Type	Default	Description
as	`AsyncSteps`		step interface
[params]	`object`		parameters to subsitute

prepared.executeAsync(as, [params])

Kind: instance method of Prepared

Param	Type	Default	Description
as	`AsyncSteps`		step interface
[params]	`object`		parameters to subsitute

Helpers

Additional helpers interface

Kind: global class

SQLHelpers

Basic logic for SQL-based helpers

Kind: global class

QueryBuilder

Neutral query builder

Kind: global class
Internal:

QueryBuilder
- new QueryBuilder(qb_or_lface, db_type, type, entity)
- instance
  - .getDriver() ⇒ IDriver
  - .clone() ⇒ QueryBuilder
  - .escape(value) ⇒ string
  - .identifier(name) ⇒ string
  - .expr(expr) ⇒ Expression
  - .param(name) ⇒ Expression
  - .helpers() ⇒ Helpers
  - .get(fields, [value]) ⇒ QueryBuilder
  - .getInsertID(field) ⇒ QueryBuilder
  - .newRow() ⇒ QueryBuilder
  - .set(field, [value]) ⇒ QueryBuilder
  - .where(conditions, [value]) ⇒ QueryBuilder
  - .having(conditions, [value]) ⇒ QueryBuilder
  - .group(field_expr) ⇒ QueryBuilder
  - .order(field_expr, [ascending]) ⇒ QueryBuilder
  - .limit(count, [offset]) ⇒ QueryBuilder
  - .join(type, entity, conditions) ⇒ QueryBuilder
  - .innerJoin(entity, conditions) ⇒ QueryBuilder
  - .leftJoin(entity, conditions) ⇒ QueryBuilder
  - .execute(as, unsafe_dml)
  - .executeAssoc(as, unsafe_dml)
  - .prepare(unsafe_dml) ⇒ ExecPrepared
- static
  - .IDriver
  - .SQLDriver
  - .Expression
  - .Prepared
  - .Helpers
  - .SQLHelpers
  - .addDriver(type, module)
  - .getDriver(type) ⇒ IDriver

new QueryBuilder(qb_or_lface, db_type, type, entity)

Param	Type	Default	Description
qb_or_lface	`QueryBuilder` \| `L1Face`		ref
db_type	`string`	`null`	type of driver
type	`string`	`null`	type of driver
entity	`string` \| `null`	`null`	primary target to operate on

queryBuilder.getDriver() ⇒ `IDriver`

Get related QB driver

Kind: instance method of QueryBuilder
Returns: IDriver - actual implementation of query builder driver

queryBuilder.clone() ⇒ `QueryBuilder`

Get a copy of Query Builder

Kind: instance method of QueryBuilder
Returns: QueryBuilder - copy which can be processed independently

queryBuilder.escape(value) ⇒ `string`

Escape value for embedding into raw query

Kind: instance method of QueryBuilder
Returns: string - driver-specific escape

Param	Type	Description
value	`*`	value, array or sub-query to escape

queryBuilder.identifier(name) ⇒ `string`

Escape identifier for embedding into raw query

Kind: instance method of QueryBuilder
Returns: string - driver-specific escape

Param	Type	Description
name	`string`	raw identifier to escape

queryBuilder.expr(expr) ⇒ `Expression`

Wrap raw expression to prevent escaping.

Kind: instance method of QueryBuilder
Returns: Expression - wrapped expression

Param	Type	Description
expr	`string`	expression to wrap

queryBuilder.param(name) ⇒ `Expression`

Wrap parameter name to prevent escaping.

Kind: instance method of QueryBuilder
Returns: Expression - wrapped expression

Param	Type	Description
name	`string`	name to wrap

queryBuilder.helpers() ⇒ `Helpers`

Get additional helpers

Kind: instance method of QueryBuilder
Returns: Helpers - - db-specific helpers object

queryBuilder.get(fields, [value]) ⇒ `QueryBuilder`

Set fields to retrieve.

Can be called multiple times for appending.

Kind: instance method of QueryBuilder
Returns: QueryBuilder - self
P: fields can be a Map or object:

keys are field names as is
values - any expression which is not being escaped automatically
P: fields can be a list of field names (array)
values - field names
P: fields can be a single string
optional @p value is expresion

Value can be another QueryBuilder instance.

Param	Type	Description
fields	`Map` \| `object` \| `string` \| `array`	see concept for details
[value]	`*`	optional value for

queryBuilder.getInsertID(field) ⇒ `QueryBuilder`

Database neutral way to request last insert ID

For databases without RETURNING or OUTPUT clause in INSERT it is expected to always return '$id' field on insert.

For others, it would build a valid RETURNING/OUTPUT clause.

Kind: instance method of QueryBuilder
Returns: QueryBuilder - self

Param	Type	Description
field	`string`	field name with auto-generated value

queryBuilder.newRow() ⇒ `QueryBuilder`

Save current set() context and start new INSERT row

Kind: instance method of QueryBuilder
Returns: QueryBuilder - self

queryBuilder.set(field, [value]) ⇒ `QueryBuilder`

Add fields to set in UPDATE query.

Kind: instance method of QueryBuilder
Returns: QueryBuilder - self
P: fields can be Map or object to setup multiple fields at once.

keys - key name as is, no escape
value - any value to be escaped or QueryBuilder instance

Single field => value can be used as shortcut for object form.

Param	Type	Description
field	`Map` \| `object` \| `string`	field(s) to assign
[value]	`string` \| `number` \| `null` \| `QueryBuilder`	value to assign

queryBuilder.where(conditions, [value]) ⇒ `QueryBuilder`

Control "WHERE" part

Kind: instance method of QueryBuilder
Returns: QueryBuilder - self

Param	Type	Description
conditions	`*`	constraints to add
[value]	`*`	optional value for single field

queryBuilder.having(conditions, [value]) ⇒ `QueryBuilder`

Control "HAVING" part

Kind: instance method of QueryBuilder
Returns: QueryBuilder - self
See: QueryBuilder.where

Param	Type	Description
conditions	`*`	constraints to add
[value]	`*`	optional value for single field

queryBuilder.group(field_expr) ⇒ `QueryBuilder`

Append group by

Kind: instance method of QueryBuilder
Returns: QueryBuilder - self

Param	Type	Description
field_expr	`string`	field or expressions

queryBuilder.order(field_expr, [ascending]) ⇒ `QueryBuilder`

Append order by

Kind: instance method of QueryBuilder
Returns: QueryBuilder - self

Param	Type	Default	Description
field_expr	`string`		field or expressions
[ascending]	`Boolean`	`true`	ascending sorting, if true

queryBuilder.limit(count, [offset]) ⇒ `QueryBuilder`

Limit query output

Kind: instance method of QueryBuilder
Returns: QueryBuilder - self
Note: if @p count is omitted then @p start is used as count!

Param	Type	Default	Description
count	`integer`		size
[offset]	`integer`	`0`	offset

queryBuilder.join(type, entity, conditions) ⇒ `QueryBuilder`

Add "JOIN" part

Kind: instance method of QueryBuilder
Returns: QueryBuilder - self
See: QueryBuilder.where

Param	Type	Description
type	`string`	e.g. INNER, LEFT
entity	`string` \| `array`	fornat is the same as of QueryBuilder
conditions	`*`	constraints to add

queryBuilder.innerJoin(entity, conditions) ⇒ `QueryBuilder`

Add "INNER JOIN"

Kind: instance method of QueryBuilder
Returns: QueryBuilder - self
See: QueryBuilder.where

Param	Type	Description
entity	`string` \| `array`	fornat is the same as of QueryBuilder
conditions	`*`	constraints to add

queryBuilder.leftJoin(entity, conditions) ⇒ `QueryBuilder`

Add "LEFT JOIN"

Kind: instance method of QueryBuilder
Returns: QueryBuilder - self
See: QueryBuilder.where

Param	Type	Description
entity	`string` \| `array`	fornat is the same as of QueryBuilder
conditions	`*`	constraints to add

queryBuilder.execute(as, unsafe_dml)

Complete query and execute through associated interface.

Kind: instance method of QueryBuilder
See: L1Face.query

Param	Type	Default	Description
as	`AsyncSteps`		steps interface
unsafe_dml	`Boolean`	`false`	raise error, if DML without conditions

queryBuilder.executeAssoc(as, unsafe_dml)

Complete query and execute through associated interface.

Kind: instance method of QueryBuilder
See

L1Face.query
L1Face.associateResult

Param	Type	Default	Description
as	`AsyncSteps`		steps interface
unsafe_dml	`Boolean`	`false`	raise error, if DML without conditions

queryBuilder.prepare(unsafe_dml) ⇒ `ExecPrepared`

Prepare statement for efficient execution multiple times

Kind: instance method of QueryBuilder
Returns: ExecPrepared - closue with prepared statement

Param	Type	Default	Description
unsafe_dml	`Boolean`	`false`	raise error, if DML without conditions

QueryBuilder.IDriver

Base for QB Driver implementation

Kind: static property of QueryBuilder

QueryBuilder.SQLDriver

Base for SQL-based QB Driver implementation

Kind: static property of QueryBuilder

QueryBuilder.Expression

Wrapper for raw expressions

Kind: static property of QueryBuilder

QueryBuilder.Prepared

Interface of Prepared statement

Kind: static property of QueryBuilder

QueryBuilder.Helpers

Base for Helpers

Kind: static property of QueryBuilder

QueryBuilder.SQLHelpers

Base for SQLHelpers

Kind: static property of QueryBuilder

QueryBuilder.addDriver(type, module)

Register query builder driver implementation

Kind: static method of QueryBuilder

Param	Type	Description
type	`string`	type of driver
module	`IDriver` \| `function` \| `string` \| `object`	implementation

QueryBuilder.getDriver(type) ⇒ `IDriver`

Get implementation of previously registered driver

Kind: static method of QueryBuilder
Returns: IDriver - actual implementation of query builder driver

Param	Type	Description
type	`string`	type of driver

SQLiteService

SQLite service implementation for FutoIn Database interface.addEventListener()

Kind: global class
Note: database filename is to supplied in options.port parameter.

new SQLiteService(options)

Please use SQLiteService.register() for proper setup.

Param	Type	Default	Description
options	`object`		see SQLiteService.register() for common options
[options.raw]	`objecT`	`{}`	raw options
[options.raw.filename]	`string`	`"options.port"`	database file
[options.raw.mode]	`integer`	`OPEN_READWRITE`	OPEN_CREATE
[options.raw.busyTimeout]	`integer`	`10000`	busyTimeout configuration value
[options.raw.pragma]	`array`	`[]`	list of pragma statements to execute on DB open

QueryOptions

Kind: global class
Properties

Name	Type	Description
affected	`integer` \| `boolean` \| `null`	affected rows constaint
selected	`integer` \| `boolean` \| `null`	selected rows constaint
return	`boolean` \| `null`	return result in response

XferQueryBuilder

Version of QueryBuilder which forbids direct execution.

Kind: global class

XferQueryBuilder
- .backref(xqb, field, [multi]) ⇒ Expression
- .forUpdate() ⇒ XferQueryBuilder
- .forSharedRead() ⇒ XferQueryBuilder

xferQueryBuilder.backref(xqb, field, [multi]) ⇒ `Expression`

Get transaction back reference expression

Kind: instance method of XferQueryBuilder
Returns: Expression - with DB-specific escape sequence

Param	Type	Default	Description
xqb	`XferQueryBuilder`		any previous transaction query builder instances.
field	`string`		field to reference by name
[multi]	`boolean`	`false`	reference single result row or multiple

xferQueryBuilder.forUpdate() ⇒ `XferQueryBuilder`

Mark select FOR UPDATE

Kind: instance method of XferQueryBuilder
Returns: XferQueryBuilder - self

xferQueryBuilder.forSharedRead() ⇒ `XferQueryBuilder`

Mark select FOR SHARED READ

Kind: instance method of XferQueryBuilder
Returns: XferQueryBuilder - self

XferBuilder

Transction builder.

Overall concept is build inividual queries to be executed without delay. It's possible to add result constraints to each query for intermediate checks:

affected - integer or boolean to check DML result
selected - integer or boolean to check DQL result
result - mark query result to be returned in response list

Kind: global class

XferBuilder
- .clone() ⇒ XferBuilder
- .getDriver() ⇒ IDriver
- .escape(value) ⇒ string
- .identifier(name) ⇒ string
- .expr(expr) ⇒ Expression
- .param(name) ⇒ Expression
- .helpers() ⇒ Helpers
- .lface() ⇒ L2Face
- .query(type, entity, [query_options]) ⇒ XferQueryBuilder
- .delete(entity, [query_options]) ⇒ XferQueryBuilder
- .insert(entity, [query_options]) ⇒ XferQueryBuilder
- .update(entity, [query_options]) ⇒ XferQueryBuilder
- .select(entity, [query_options]) ⇒ XferQueryBuilder
- .call(name, [args], [query_options])
- .raw(q, [params], [query_options])
- .execute(as, unsafe_dml)
- .executeAssoc(as, unsafe_dml)
- .prepare(unsafe_dml) ⇒ ExecPrepared

xferBuilder.clone() ⇒ `XferBuilder`

Get a copy of XferBuilder for independent processing.

Kind: instance method of XferBuilder
Returns: XferBuilder - transaction builder instance

xferBuilder.getDriver() ⇒ `IDriver`

Get related QV driver

Kind: instance method of XferBuilder
Returns: IDriver - actual implementation of query builder driver

xferBuilder.escape(value) ⇒ `string`

Escape value for embedding into raw query

Kind: instance method of XferBuilder
Returns: string - driver-specific escape

Param	Type	Description
value	`*`	value, array or sub-query to escape

xferBuilder.identifier(name) ⇒ `string`

Escape identifier for embedding into raw query

Kind: instance method of XferBuilder
Returns: string - driver-specific escape

Param	Type	Description
name	`string`	raw identifier to escape

xferBuilder.expr(expr) ⇒ `Expression`

Wrap raw expression to prevent escaping.

Kind: instance method of XferBuilder
Returns: Expression - wrapped expression

Param	Type	Description
expr	`string`	expression to wrap

xferBuilder.param(name) ⇒ `Expression`

Wrap parameter name to prevent escaping.

Kind: instance method of XferBuilder
Returns: Expression - wrapped expression

Param	Type	Description
name	`string`	name to wrap

xferBuilder.helpers() ⇒ `Helpers`

Get additional helpers

Kind: instance method of XferBuilder
Returns: Helpers - - db-specific helpers object

xferBuilder.lface() ⇒ `L2Face`

Get reference to L2 interface. Valid use case - sub-queries.

Kind: instance method of XferBuilder
Returns: L2Face - - associated L2 interface implementation