futoin-database

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:

1. raw string is treated as is and joined with outer scope AND or OR operator
2. 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
3. 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:

1. As select or join entity - alias must be provided in array format:
   • [QueryBuilder(), 'Alias']
2. As any condition value part, except raw strings
3. 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
    • .LATEST_VERSION
    • .PING_VERSION
    • .register(as, ccm, name, endpoint, [credentials], [options])

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
    • .READ_UNCOMMITTED
    • .READ_COMMITTED
    • .REPEATABL_READ
    • .SERIALIZABLE
    • .newXfer([iso_level]) ⇒ XferBuilder
  • static
    • .READ_UNCOMMITTED
    • .READ_COMMITTED
    • .REPEATABL_READ
    • .SERIALIZABLE

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

xferBuilder.query(type, entity, [query_options]) ⇒ XferQueryBuilder

Get generic query builder

Kind: instance method of XferBuilder
Returns: XferQueryBuilder - individual query builder instance

Param	Type	Default	Description
type	string		query type
entity	string | null		man subject
[query_options]	QueryOptions	{}	constraints

xferBuilder.delete(entity, [query_options]) ⇒ XferQueryBuilder

Get DELETE query builder

Kind: instance method of XferBuilder
Returns: XferQueryBuilder - individual query builder instance

Param	Type	Default	Description
entity	string | null		man subject
[query_options]	QueryOptions	{}	constraints

xferBuilder.insert(entity, [query_options]) ⇒ XferQueryBuilder

Get INSERT query builder

Kind: instance method of XferBuilder
Returns: XferQueryBuilder - individual query builder instance

Param	Type	Default	Description
entity	string | null		man subject
[query_options]	QueryOptions	{}	constraints

xferBuilder.update(entity, [query_options]) ⇒ XferQueryBuilder

Get UPDATE query builder

Kind: instance method of XferBuilder
Returns: XferQueryBuilder - individual query builder instance

Param	Type	Default	Description
entity	string | null		man subject
[query_options]	QueryOptions	{}	constraints

xferBuilder.select(entity, [query_options]) ⇒ XferQueryBuilder

Get SELECT query builder

Kind: instance method of XferBuilder
Returns: XferQueryBuilder - individual query builder instance

Param	Type	Default	Description
entity	string | null		man subject
[query_options]	QueryOptions	{}	constraints

xferBuilder.call(name, [args], [query_options])

Add CALL query

Kind: instance method of XferBuilder

Param	Type	Default	Description
name	string		stored procedure name
[args]	array	[]	positional arguments
[query_options]	QueryOptions	{}	constraints

xferBuilder.raw(q, [params], [query_options])

Execute raw query

Kind: instance method of XferBuilder
Note: Pass null in {@p params}, if you want to use prepare()

Param	Type	Default	Description
q	string		raw query
[params]	object		named argument=>value pairs
[query_options]	QueryOptions	{}	constraints

xferBuilder.execute(as, unsafe_dml)

Complete query and execute through associated interface.

Kind: instance method of XferBuilder
See: L1Face.query

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

xferBuilder.executeAssoc(as, unsafe_dml)

Complete query and execute through associated interface.

Kind: instance method of XferBuilder
See

• L1Face.query
• L1Face.associateResult

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

xferBuilder.prepare(unsafe_dml) ⇒ ExecPrepared

Prepare statement for efficient execution multiple times

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

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

AutoConfig

Kind: global variable
Brief: Automatically configure database connections and related internal Executors.

For each config entry an instance of dedicated Executor with registered database service is created and related interface is registered on CCM.

Interfaces are registered as "#db.{key}". The "default" one is also aliased as "#db".

Env patterns to service configuration:

• DB_{name}_HOST -> host
• DB_{name}_PORT -> port
• DB_{name}_SOCKET -> port (overrides DB_PORT)
• DB_{name}_USER -> user
• DB_{name}_PASS -> password
• DB_{name}_DB -> database
• DB_{name}_MAXCONN -> conn_limit
• DB_{name}_TYPE - type of database, fails if mismatch configuration Note: the variables names are driven by CodingFuture CFDB Puppet module.

The "default" key also tries env without "{name}_" infix.

Example:

 AutoConfig(ccm, {
     "default": {
         type: ["mysql", "postgresql"],
         // DB_DEFAULT_TYPE or DB_TYPE must match any of them
     },
     readonly: {
         type: "mysql"
         // fail, if DB_READONLY_TYPE != mysql
     },
     preset: {
         type: "postgresql",
         host: "127.0.0.1",
         port: 5432,
         user: "test",
         password: "test",
         database: "test",
         conn_limit: 10,
         // no need to env variables - all is preset
     },
 })

Note: it also monkey patches CCM with #db(name="default") method

Param	Type	Default	Description
as	AsyncSteps		async steps interface
ccm	AdvancedCCM		CCM instance
[config]	object		expected connection key => type map
[env]	object	process.env	source of settings

AutoConfig.register

Register database service type.

Kind: static property of AutoConfig

Param	Type	Description
type	string	type of database
factory	string | callable | object	module name, factory method or a subclass of L1Service

documented by jsdoc-to-markdown.