fbpx

JavaScript common API

JSQL JavaScript API common docs

JavaScript common API

Promises

Each implementation of the JSQL library for the framework has a slightly different promises structure.

For pure JavaScript it’s ex “then”, “catch” and “always” but for ex jQuery it’s “done”, “fail” and “always”.

For details, please visit the pages of the respective framework plugins.

RxJS support

Each implementation of the JSQL library for the framework has a slightly different structure.

For some of frameworks we use RxJS default convention using Observables.

See the documentation of the framework that interests you to find out if it supports RxJS.

Using rx object as in-build Observable

Due to the fact that the JSQL object itself is a promise, to use the Observable we must use the rx object

Example usage:

 jsqlService.select("@sql select * from person where id = :id")
.param('id', 5) .rx.subscribe((result: any) => { //Query result }, (error: any) => { // Database errors });

Initiating JSQL

To use JSQL, you have to create main JSQL object:

var jsql = new JSQL(config);

List of configuration parameters during JSQL object creation:

host(String): your backend application host (Required)

path(String): your backend application path (Optional)
Default: empty

hideErrors(Boolean): enables/disables error showing in console (Optional)
Default: false

rxjs(Boolean): enables/disables RxJS support (Optional)
Default: false

var config = {
host: 'https://mysite.com'
path: '/api/'
hideErrors: false,
rxjs: false
};

var jsql = new JSQL(config);

Getting data

Downloading data using JSQL is done via the select and selectOne functions.

Functions for executing “delete” type queries include delete and remove.

The function for performing data updates is update.
The function for inserting data is insert.

It is important not to use functions for queries they are not intended for, i.e. it is forbidden to use select queries in the insert function.

select(query)

Execute the “select” query given as an argument. Returns the JSQL promise.

Arguments

query (String): encoded query

Returns

(JSQL Promise): Returns JSQL Promise

Note: In most cases, it returns an array

Example

var jsql = new JSQL({
host: 'https://mysite.com/'
});

jsql.select('@sql select * from person')
.then(function(result){
// Query result
})
.catch(function(error){
// Database errors
});

selectOne(query)

Execute the “select” query given as an argument. Returns the JSQL promise.

Note: Unlike select it always returns single row (even more results exists)

Arguments

query (String): encoded query

Returns

(JSQL Promise): Returns JSQL Promise

Example

var jsql = new JSQL({
host: 'https://mysite.com/'
});

jsql.selectOne('@sql select * from person')
.then(function(result){
// Query single result
})
.catch(function(error){
// Database errors
});

delete(query)

Execute the “delete” query given as an argument. Returns the JSQL promise.

Arguments

query (String): encoded query

Returns

(JSQL Promise): Returns JSQL Promise

Example

var jsql = new JSQL({
host: 'https://mysite.com/'
});

jsql.delete('@sql delete from person')
.then(function(result){
// Query result
})
.catch(function(error){
// Database errors
});

remove(query)

Substitute function to delete.

Example

var jsql = new JSQL({
host: 'https://mysite.com/'
});

jsql.remove('@sql delete from person')
.then(function(result){
// Query result
})
.catch(function(error){
// Database errors
});

update(query)

Execute the “update” query given as an argument. Returns the JSQL promise.

Arguments

query (String): encoded query

Returns

(JSQL Promise): Returns JSQL Promise

Example

var jsql = new JSQL({
host: 'https://mysite.com/'
});

jsql.update('@sql update person set age = 30')
.then(function(result){
// Query result
})
.catch(function(error){
// Database errors
});

insert(query)

Execute the “insert” query given as an argument. Returns the JSQL promise.

Arguments

query (String): encoded query

Returns

(JSQL Promise): Returns JSQL Promise

Example

var jsql = new JSQL({
host: 'https://mysite.com/'
});

jsql.insert("@sql insert into person (id, name) values(1, 'Kristian')")
.then(function(result){
// Query result
})
.catch(function(error){
// Database errors
});

JSQL Promise

Each of the functions for database operations returns the JSQL promise.

It is a special object that has functions for handling callbacks in JavaScript and TypeScript, configuration of a JSQL object, a promise and an observable object, and functions for binding parameters.

From the JSQL promise, you can extract the following data:

Exposed data

xhrPromise (Object): promise constructed by the mechanism of the framework used (plain XHR, AJAX, Angular $http etc)

type(String): query type

headers(Map): request headers

Configuration

To use JSQL, you have to create main JSQL object:

var jsql = new JSQL(config);

List of configuration parameters during JSQL object creation:

host(String): your backend application host (Required)

path(String): your backend application path (Optional)
Default: empty

hideErrors(Boolean): enables/disables error showing in console (Optional)
Default: false

rxjs(Boolean): enables/disables RxJS support (Optional)
Default: false

var config = {
host: 'https://mysite.com'
path: '/api/'
hideErrors: false,
rxjs: false
};

var jsql = new JSQL(config);

Query parameters

The naturnal thing when performing SQL queries is to provide parameters.

JSQL has a simple and flexible functionality that allows you to substitute parameters for both individual objects and arrays.

param(name, value)

Binds a single parameter to query. Returns the JSQL Promise.

Arguments

name (String): parameter name (used in query)

value (any): parameter value (compatible with the used database)

Returns

(JSQL Promise): Returns JSQL Promise

Example

var jsql = new JSQL({
host: 'https://mysite.com/'
});

jsql.select("@sql select * from person where id = :id")
.param('id', 5)
.then(function(result){
// Query result
})
.catch(function(error){
// Database errors
});

params(object)

Binds a array of parameters or object properties to query. Returns the JSQL Promise.

Arguments

object (Array, Object): array of parameters or object

Returns

(JSQL Promise): Returns JSQL Promise

Example with object

var jsql = new JSQL({
host: 'https://mysite.com/'
});

var person = {
name: 'Krisian',
surname: 'Christiansen',
age: 35
};

jsql.insert('@sql insert into person values (:name, :surname, :age)')
.params(person)
.then(function(result){ // Query result }) .catch(function(error){ // Database errors });

Example with array of parameters

var jsql = new JSQL({
host: 'https://mysite.com/'
});

var ranges= [20, 30];

jsql.select('@sql select * from person where age > ? and age < ?)
.params(ranges)
.then(function(result){
// Query result
})
.catch(function(error){
// Database errors
});

Query repositories

It is difficult to manage a large number of queries in the application without creating any structures.

JSQL facilitates the creation of structures by offering a number of functions for storing queries and reusing their grouping functions in the named queries repositories.

set(name, value)

Saves the query in memory.

It can take a function that returns a Query object or a query as String.

Arguments

name (String): name of query (must be unique)

value (String, function): query as String or function returning a Query object.

The function accepts parameters that are later used when retrieving a query using the get function

Returns

(JSQL Object): Returns JSQL object

Example with String query

var jsql = new JSQL({
host: 'https://mysite.com/'
});

jsql.set('getPeople', '@sql select * from person');
jsql.set('getPersonById', '@sql select * from person where id = :id');

jsql
.set('getPeople', '@sql select * from person')
.set('getPersonById', '@sql select * from person where id = :id');

Example result query

var query = jsql.get('getPeople');
//Returns 'select * from person'

Example with function parameter

var jsql = new JSQL({
host: 'https://mysite.com/'
});

jsql.set('getPeopleFilter', function(ageEquals, ageRange) {

 var query = jsql.query('@sql select * from person where ');

 if(ageEquals){
  query.append('@sql age = :age');
 }else if(ageRange){
  query.append('@sql age > :ageLowerLimit and age < :ageUpperLimit');
 }

 return query;

});

Example result query

var finalQuery = jsql.get('@sql getPeopleFilter', false, true);
//Returns 'select * from person where 
//age > :ageLowerLimit and age < :ageUpperLimit'

get(name, ...argument)

Returns the saved query.

If a function has been saved, it executes it and returns a ready query.

Arguments

name (String): name of query

argument (any): argument passed to function returning Query object. (Optional)

The function accepts parameters that are passed to function created using the set function

Returns

(String): Returns query

Example with String query

var jsql = new JSQL({
host: 'https://mysite.com/'
});

jsql.set('getPeople', '@sql select * from person');
jsql.set('getPersonById', '@sql select * from person where id = :id');

jsql
.set('getPeople', '@sql select * from person')
.set('getPersonById', '@sql select * from person where id = :id');

Example result query

var query = jsql.get('getPeople');
//Returns 'select * from person'

Example with function parameter

var jsql = new JSQL({
host: 'https://mysite.com/'
});

jsql.set('getPeopleFilter', function(ageEquals, ageRange) {

 var query = jsql.query('@sql select * from person where ');

 if(ageEquals){
  query.append('@sql age = :age');
 }else if(ageRange){
  query.append('@sql age > :ageLowerLimit and age < :ageUpperLimit');
 }

 return query;

});

Example result query

var finalQuery = jsql.get('getPeopleFilter', false, true);
//Returns 'select * from person where 
//age > :ageLowerLimit and age < :ageUpperLimit'

repo(name)

Creates named repository for queries.

Arguments

name (String): name of repository (Optional)

If name not provided, returns all repositories.

If name provided, then allows to modify repository of given name.

If repository not exists, creates it.

Returns

If name provided:

(Repo Object): Returns repository object with namedQuery function.

If name not provided:

(Map): Returns all repositories

Example creating repository

var jsql = new JSQL({
host: 'https://mysite.com/'
});

jsql.repo('person')
.namedQuery('list', '@sql select * from person')
.namedQuery('byId', '@sql select * from person where id = :id');

Example getting query

var query = jsql.repo('person').list;
//Returns 'select * from person'

var query2 = jsql.repo().person.byId;
//Returns 'select * from person where id = :id'

Example more proffessional structure

var repository = jsql.repo();

var personRepository = jsql.repo('person');

personRepository.namedQuery('list', '@sql select * from person');
personRepository.namedQuery('byId', '@sql select * from person where id = :id');

var listOfPeople = personRepository.list;

var personById = repository.person.byId;

namedQuery(name, ..argument)

Saves the query in memory in current working repository.

It can take a function that returns a Query object or a query as String.

Arguments

name (String): name of query (must be unique)

value (String, function): query as String or function returning a Query object.

The function accepts parameters that are later used when retrieving a query using the function.

Returns

(Repo Object): Returns repository object.

Example creating repository

var jsql = new JSQL({
host: 'https://mysite.com/'
});

jsql.repo('person')
.namedQuery('list', '@sql select * from person')
.namedQuery('byId', '@sql select * from person where id = :id');

Example getting query

var query = jsql.repo('person').list;
//Returns 'select * from person'

var query2 = jsql.repo().person.byId;
//Returns 'select * from person where id = :id'

Example with function as value

var repository = jsql.repo('person');

repository .namedQuery('inAgeRange', function(ageEquals, ageRange) {

 var query = jsql.query('@sql select * from person where ');

 if(ageEquals){
  query.append('@sql age = :age');
 }else if(ageRange){
  query.append('@sql age > :ageLowerLimit and age < :ageUpperLimit');
 }

 return query;

});

var query = repository.inAgeRange(false, true);
//Returns 'select * from person where '
//'age > :ageLowerLimit and age < :ageUpperLimit'

Appending queries

JSQL offers a mechanism for sticking queries in a convenient form using a builder pattern.

Such an object can also be transferred to operational functions.

query(query)

Creates a Query object that is the query builder.

Arguments

query (String): query as string; base query

Returns

(Query Object): Returns Query object.

Example

var jsql = new JSQL({
host: 'https://mysite.com/'
});

var complexQuery = jsql.query('@sql select * from person');

complexQuery.append('@sql where id = :id');
complexQuery.append('@sql and name = :name');

complexQuery.build();
//Returns 'select * from person where 
//id = :id and name = :name'

Example passing into operational function

var jsql = new JSQL({
host: 'https://mysite.com/'
});

var complexQuery = jsql.query('@sql select * from person');
complexQuery.append('@sql where id = :id');

// Direct Query object
jsql.select(complexQuery)
//or builded as String
jsql.select(complexQuery.build())

append(query)

Appends new query part into existing Query object.

Arguments

query (String): query as string

Returns

(Query Object): Returns Query object.

Example

var jsql = new JSQL({
host: 'https://mysite.com/'
});

var complexQuery = jsql.query('@sql select * from person');

complexQuery.append('@sql where id = :id');
complexQuery.append('@sql and name = :name');

complexQuery.build();
//Returns 'select * from person where 
//id = :id and name = :name'

build()

Concats every queries appended to existing Query object.

Returns

(String): Returns concated query.

Example

var jsql = new JSQL({
host: 'https://mysite.com/'
});

var complexQuery = jsql.query('@sql select * from person');

complexQuery.append('@sql where id = :id');
complexQuery.append('@sql and name = :name');

complexQuery.build();
//Returns 'select * from person where 
//id = :id and name = :name'
Close Menu