futoin-database

v1.9.12

Published

2 years ago

Neutral database interface with powerful Query and revolution Transaction builders

Downloads

0High
0Medium
0Low

andvgal

futoin async db database querybuilder transaction postgresql mysql mariadb

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

Members

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

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

Kind: static method of L2Service
Returns: L2Service - instance

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 |