n1-sql v0.1.8
n1-sql
An npm package for interacting with an N1 SQL service endpoint.
Getting Started
Install n1-sql
npm.
npm install immutable
Then require it into any module.
const n1_sql = require('n1-sql');
n1_sql('http://localhost:9000')
.vdbs('mock-20')
.useTable('dp1', 'orgA.t')
.useTable('dp2', 'orgB.t')
.join(c => c.column('dp1.id').eq().column('dp2.id'))
.selectSum('dp2.spend', 'spend')
.where(c => c.column('dp1.age').gt(40))
.exec().then(j => {
console.info(j.rows);
});
Browser
To use n1-sql
from a browser, download dist/n1-sql.js
.
Then, add it as a script tag to your page:
<script src="n1-sql.js"></script>
<script>
var base = N1Sql('http://localhost:9000')
.vdbs('mock-20')
.useTable('dp1', 'orgA.t')
.useTable('dp2', 'orgB.t')
.join(c => c.column('dp1.id').eq().column('dp2.id'));
....
</script>
API
The API is implemented in a purely functional and fluent design pattern. Specifically, any construction of an API call starts with identifying the N1 SQL Service through the single function provided by the library.
const n1_sql = require('n1-sql');
n1_sql(...)
The intial function call and most subsequent ones will return a new context providing a set of functions to further shape that context. Each context is immutable and can be used multiple times without any side effects.
const rel = n1_sql(...).vdbs('foo').useTable(...)
.join(...).selectCount();
const q1 = rel.where(c => c.column('dp1.spend').gt(1000)));
const q2 = rel.where(c => c.column('dp2.age').lt(35)));
Constructor(url)
Create a new connection pool. The initial probe connection is created to find out whether the configuration is valid.
Arguments
- url - URL of the N1 SQL endpoint
Example
const n1_sql = require('n1-sql');
n1_sql('http://localhost:9000')
vdbs(vdbName)
Select the name of the virtual database to use on the REST endpoint.
Arguments
- vdbName - Name of virtual database provided
Example
n1_sql(...)
.vdbs('mock-20')
...
useTable(alias, tableName)
Select one of the tables available via vdbs
and bind an alias to it. The alias will be used for identifiying respective columns.
Arguments
- alias - The alias to use for
tableName
- tableName - The name of a table in
vdbs
.
Example
const n1_sql = require('n1-sql');
n1_sql('http://localhost:9000')
.vdbs('mock-20')
.useTable('dp1', 'orgA.t')
join(callback)
Defines on how all the selected tables (useTable
) will be joined to create one single relation.
Arguments
- callback(joinBuilder) - A callback providing a reference to a
joinBuilder
The joinBuilder
provides the following chainable functions.
- column(colId) selecting a column of the format
tblAlias.colName
. - eq() declares an
equal
constraint between the preceeding column and the following one.
Example
n1_sql(...)
...
.useTable('dp1', 'orgA.t')
.useTable('dp2', 'orgB.t')
.useTable('dp3', 'orgC.t')
.join(cb => cb.column('dp1.id')
.eq()
.column('dp2.id')
.eq()
.column('dp3.id'))
selectSum(colId, alias)
Add a column to the result which holds the sum of all the values in column colId
in the final relation.
Arguments
- colId - The selected column ID of the format
tblAlias.colName
. - alias - The name of result column
Example
n1_sql(...)
...
.selectSum('dp2.spend', 'spend')
selectCount(alias)
Add a column to the result which holds the number of rows in the final relation.
Arguments
- colId - The selected column ID of the format
tblAlias.colName
.
Example
n1_sql(...)
...
.selectCount('count')
select(colId, alias)
Add a column to the result which holds the value for the column defined by groupBy
.
Arguments
- colId - The selected column ID of the format
tblAlias.colName
. - alias - The name of result column
Example
n1_sql(...)
...
.select('dp2.gender', 'gender')
groupBy(colId)
Group the result by the value of this column.
Arguments
- colId - The selected column ID of the format
tblAlias.colName
.
Example
n1_sql(...)
...
.groupBy('dp2.gender')
where(callback)
Defines the constraints on the relation defined by join
.
Multiple where
constraints can be defined and will be AND
combined for the final SQL statement.
Arguments
- callback(whereBuilder) - A callback providing a reference to a
whereBuilder
The whereBuilder
provides the following chainable functions.
column(colId) selecting a column of the format
tblAlias.colName
.gt(value): '>'
- ge(value): '>='
- eq(value): '='
- neq(value): '!='
- lt(value): '<'
le(value): '<='
or(): 'OR'
- and(): 'AND'
Example
// WHERE dp1.foodSpend < dp1.travelSpend OR dp1.age >= 50
n1_sql(...)
...
.where(wb => wb.column('dp1.foodSpend')
.lt()
.column('dp1.travelSpend')
.or()
.column('dp1.age')
.ge(50))
exec()
Turn the defined query into a proper SQL expression, call the defined N1 SQL service endpoint, and peridically query for a result if it isn't returned immediately (rather unlikely).
The result of the service call is returned in a Promise.
Arguments
Example
n1_sql(...)
...
.exec()
.then(j => {
console.log(j.rows);
})
.catch(console.error);
toSql()
Convert the current context into an SQL string and return it.