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

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

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 , { main : { type : [ ' mysql ' , ' postgresql ' ] } , 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

- array of array of values .fields - array of field names in the same order as values in rows

- 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

- amount of affected rows by last operation (it's quite database specific).

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

- 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

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

format, where is one of: 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

- if true then result must be returned in result list of 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 ) ;

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

- broken query due to syntax or semantics Duplicate - unique key constraint violation

- unique key constraint violation LimitTooHigh - more than 1000 rows in result

- more than 1000 rows in result DeadLock - database deadlock detected in transaction

- database deadlock detected in transaction XferCondition - violation of transaction condition constraints

- violation of transaction condition constraints XferBackRef - invalid transaction value back reference

- invalid transaction value back reference OtherExecError - any other execution error

Example of use:

as . add ( ( as ) => db . query ( ... ) , ( as , err_code ) => { console . log ( as . state . error_info ) ; console . log ( as . state . last_exception ) ; if ( err_code === ' Duplicate ' ) { 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:

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

const $as = require ( ' futoin-asyncsteps ' ) ; const AdvancedCCM = require ( ' futoin-invoker/AdvancedCCM ' ) ; const DBAutoConfig = require ( ' futoin-database/AutoConfig ' ) ; $as ( ) . add ( ( as ) => { const ccm = new AdvancedCCM ( ) ; DBAutoConfig ( as , ccm ) ; as . add ( ( as ) => { ccm . db ( ) . query ( as , ' SELECT 1+2 AS Sum ' ) ; } ) ; as . add ( ( as , res ) => { res = ccm . db ( ) . associateResult ( res ) ; console . log ( ` Sum: ${ res [ 0 ] . Sum } ` ) ; } ) ; as . add ( ( as ) => { ccm . close ( ) ; } ) ; } , ( as , err ) => { console . log ( ` ${ err } : ${ as . state . error_info } ` ) ; console . log ( as . state . last_exception ) ; } ) . execute ( ) ;

2. Query Builder

const ccm = new AdvancedCCM ( ) ; DBAutoConfig ( as , ccm ) ; as . add ( ( as ) => { const db = ccm . db ( ) ; let q ; db . query ( as , ' DROP TABLE IF EXISTS SomeTbl ' ) ; db . query ( as , ' CREATE TABLE SomeTbl( ' + ' id int auto_increment primary key, ' + ' name varchar(255) unique) ' ) ; db . insert ( ' SomeTbl ' ) . set ( ' name ' , ' abc ' ) . execute ( as ) ; db . insert ( ' SomeTbl ' ) . set ( { name : ' klm ' } ) . execute ( as ) ; db . insert ( ' SomeTbl ' ) . set ( new Map ( [ [ ' name ' , ' xyz ' ] ] ) ) . getInsertID ( ' id ' ) . executeAssoc ( as ) ; as . add ( ( as , res , affected ) => console . log ( ` Insert ID: ${ res [ 0 ] . $id } ` ) ) ; db . insert ( ' SomeTbl ' ) . set ( db . select ( ' SomeTbl ' ) . get ( ' name ' , " CONCAT('INS', name) " ) . where ( ' id < ' , 3 ) ) . execute ( as ) ; const qb = db . queryBuilder ( ) ; q = db . update ( ' SomeTbl ' ) . set ( ' id ' , 10 ) . set ( ' name ' , qb . expr ( ' CONCAT(id, name) ' ) ) . where ( ' name ' , ' klm ' ) . where ( [ ' OR ' , { ' name LIKE ' : ' kl% ' , ' id > ' : 1 , } , [ ' AND ' , ' name NOT LIKE \' xy% \' ' , { ' id BETWEEN ' : [ 1 , 10 ] } ] , ] ) ; console . log ( ` Query: ${ q } ` ) ; q . execute ( as ) ; db . select ( ) . get ( ' atm ' , ' NOW() ' ) . executeAssoc ( as ) ; as . add ( ( as , res ) => console . log ( ` At the moment: ${ res [ 0 ] . atm } ` ) ) ; q = db . select ( ' SomeTbl ' ) . innerJoin ( [ db . select ( ) . get ( ' addr ' , qb . escape ( ' Street 123 ' ) ) , ' Alias ' ] , ' 1 = 1 ' ) ; console . log ( ` Query: ${ q } ` ) ; q . executeAssoc ( as ) ; as . add ( ( as , res ) => console . log ( res ) ) ; } ) ;

3. Efficient Query Builder (prepared)

const qb = db . queryBuilder ( ) ; const prepared_q = db . insert ( ' SomeTbl ' ) . set ( ' name ' , qb . param ( ' nm ' ) ) . getInsertID ( ' id ' ) . prepare ( ) ; for ( let nm of [ ' abc ' , ' klm ' , ' xyz ' ] ) { prepared_q . executeAssoc ( as , { nm } ) ; as . add ( ( as , res ) => console . log ( ` Inserted ${ nm } ID ${ res [ 0 ] . $id } ` ) ) ; } 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 ] } ` ) ) ; }

4. Simple Transaction Builder

const xfer = db . newXfer ( ) ; xfer . insert ( ' SomeTbl ' ) . set ( ' name ' , ' abc ' ) ; xfer . insert ( ' SomeTbl ' ) . set ( ' name ' , ' xyz ' ) ; xfer . select ( ' SomeTbl ' , { result : true } ) . get ( ' C ' , ' COUNT(*) ' ) . forUpdate ( ) ; xfer . update ( ' SomeTbl ' , { result : true , affected : true } ) . set ( ' name ' , ' klm ' ) . where ( ' name ' , ' xyz ' ) ; xfer . update ( ' SomeTbl ' , { affected : 0 } ) . set ( ' name ' , ' klm ' ) . where ( ' name ' , ' xyz ' ) ; xfer . executeAssoc ( as ) ; as . add ( ( as , results ) => { console . log ( ` Count: ${ results [ 0 ] . rows [ 0 ] . C } ` ) ; console . log ( ` First UPDATE affected: ${ results [ 1 ] . affected } ` ) ; } ) ;

5. Efficient Transaction Builder (prepared)

const xfer = db . newXfer ( ) ; xfer . insert ( ' SomeTbl ' ) . set ( ' name ' , xfer . param ( ' n1 ' ) ) ; xfer . insert ( ' SomeTbl ' ) . set ( ' name ' , xfer . param ( ' n2 ' ) ) ; xfer . select ( ' SomeTbl ' , { result : true } ) . get ( ' C ' , ' COUNT(*) ' ) . forSharedRead ( ) ; const prepared_xfer = xfer . prepare ( ) ; const data = [ { n1 : ' abc ' , n2 : ' xyz ' } , { n1 : ' cba ' , n2 : ' zyx ' } , ] ; data . forEach ( ( params , i ) => { prepared_xfer . executeAssoc ( as , params ) ; as . add ( ( as , results ) => { console . log ( ` Count for ${ i } : ${ results [ 0 ] . rows [ 0 ] . C } ` ) ; } ) ; } ) ;

6. Advanced Transaction Builder (prepared with back references)

const xfer = db . newXfer ( db . SERIALIZABLE ) ; 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 ' ) ; 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 ' ) , ] ) . forUpdate ( ) ; 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 ' ) ) ; const prepared_xfer = xfer . prepare ( ) ; const data = [ { n1 : ' abc ' , n2 : ' xyz ' } , { n1 : ' cba ' , n2 : ' zyx ' } , ] ; data . forEach ( ( params , i ) => { prepared_xfer . executeAssoc ( as , params ) ; } ) ; db . select ( ' SomeTbl ' ) . executeAssoc ( as ) ; as . add ( ( as , res ) => console . log ( res ) ) ;

7. Multiple connections per application

DBAutoConfig ( as , ccm , { first : { } , second : { } , } ) ; 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 ] } ` ) ) ; } ) ;

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

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

const sym = Symbol ( ' arbitrary ' ) ; for ( let nm of [ ' abc ' , ' klm ' , ' xyz ' ] ) { const prepared_q = db . getPrepared ( sym , ( db ) => { const qb = db . insert ( ' SomeTbl ' ) ; return qb . set ( ' name ' , qb . param ( ' nm ' ) ) . getInsertID ( ' id ' ) . prepare ( ) ; } ) ; 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

L1Face

Level 1 Database Face

Kind: global class

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

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

Get type of database

Kind: instance method of L1Face

Param Type Description as AsyncSteps steps interface

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

Get query builder helpers

Helps avoiding temporary variables for cleaner code.

Kind: instance method of L1Face

Returns: Helpers - for specific type



Get neutral query builder for DELETE

Kind: instance method of L1Face

Returns: QueryBuilder - associated instance

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

Get neutral query builder for INSERT

Kind: instance method of L1Face

Returns: QueryBuilder - associated instance

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

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

Get neutral query builder for UPDATE

Kind: instance method of L1Face

Returns: QueryBuilder - associated instance

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

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

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

Latest supported FTN17 version

Kind: static property of L1Face



Latest supported FTN4 version

Kind: static property of L1Face



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



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

Read Uncomitted isolation level constant

Kind: instance property of L2Face



Read Comitted isolation level constant

Kind: instance property of L2Face



Repeatable Read isolation level constant

Kind: instance property of L2Face



Serializable

Kind: instance property of L2Face



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

Read Uncomitted isolation level constant

Kind: static property of L2Face



Read Comitted isolation level constant

Kind: static property of L2Face



Repeatable Read isolation level constant

Kind: static property of L2Face



Serializable

Kind: static property of L2Face



L2Service

Base for Level 2 Database service implementation

Kind: global class



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



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:

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



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

Wrap raw expression to prevent escaping.

Kind: instance method of QueryBuilder

Returns: Expression - wrapped expression

Param Type Description expr string expression to wrap

Wrap parameter name to prevent escaping.

Kind: instance method of QueryBuilder

Returns: Expression - wrapped expression

Param Type Description name string name to wrap

Get additional helpers

Kind: instance method of QueryBuilder

Returns: Helpers - - db-specific helpers object



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)

: fields can be a list of field names (array) values - field names

P : fields can be a single string

: 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

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

Save current set() context and start new INSERT row

Kind: instance method of QueryBuilder

Returns: QueryBuilder - self



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

Control "WHERE" part

Kind: instance method of QueryBuilder

Returns: QueryBuilder - self

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

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

Append group by

Kind: instance method of QueryBuilder

Returns: QueryBuilder - self

Param Type Description field_expr string field or expressions

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

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

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

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

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

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

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

Base for QB Driver implementation

Kind: static property of QueryBuilder



Base for SQL-based QB Driver implementation

Kind: static property of QueryBuilder



Wrapper for raw expressions

Kind: static property of QueryBuilder



Interface of Prepared statement

Kind: static property of QueryBuilder



Base for Helpers

Kind: static property of QueryBuilder



Base for SQLHelpers

Kind: static property of QueryBuilder



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

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

Mark select FOR UPDATE

Kind: instance method of XferQueryBuilder

Returns: XferQueryBuilder - self



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

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

Wrap raw expression to prevent escaping.

Kind: instance method of XferBuilder

Returns: Expression - wrapped expression

Param Type Description expr string expression to wrap

Wrap parameter name to prevent escaping.

Kind: instance method of XferBuilder

Returns: Expression - wrapped expression

Param Type Description name string name to wrap

Get additional helpers

Kind: instance method of XferBuilder

Returns: Helpers - - db-specific helpers object



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

Kind: instance method of XferBuilder

Returns: L2Face - - associated L2 interface implementation



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

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

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

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

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

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

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

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

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 " ] , } , readonly : { type : " mysql " } , preset : { type : " postgresql " , host : " 127.0.0.1 " , port : 5432 , user : " test " , password : " test " , database : " test " , conn_limit : 10 , } , } )

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

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.