ArrayQL

Treat array of objects as a table SQL-alike way. Paginate, select, sort, update, insert, delete. Add calculated and default values. Runs in browser and Node.

USERS.select("id, name").where("age").between(20,30).limit(0,15).getResult();

Mainly intended for mock servers, test, debugging, prototyping purposes.

Installation

npm install array-ql

Usage

const ArrayQL = require("array-ql");
 
const table = new ArrayQL(dataArray, options);
 
const selected = table.select("id, name").where("age").between(20,30).getList();

Keywords

select(keys?: string)	Resets previous select results, sets necessery keys. keys - is optional argument, comma separated string, including renaming, for example: "id, name as username". If no "keys" is set, then complete entries will be returned
where(key: string)	Sets active key, subsequent conditions will be applied to it
and(key: string)	Sets active key, and logic to "AND". Subsequent conditions will reduce resulting selection
or(key: string)	Sets active key, and logic to "OR". Subsequent conditions will extend resulting selection
sort(key: string, direction?: "asc"|"desc")	Sorts resulting selection
orderBy(key: string, direction?: "asc"|"desc")	Alias to sort()

Conditions

equalTo(val: any)	Includes entries where value of active key is stirictly equal to given value (including type)
is(val:any)	Alias to equalTo()
isNull()	Includes entries where value of active key is NULL
notNull()	Includes entries where value of active key is not NULL
like(val: string)	Includes entries where active key has value which has partial case-insensitive match with given value
lessThen(val: number)	Includes entries where active key has value which is less then given one
lt(val: number)	alias to lessThen()
lessThenOrEqual(val: number)	Includes entries where active key has value which is less or equal to given one
lte(val: number)	alias to lessThenOrEqual()
greaterThen(val: number)	Includes entries where active key has value which is greater then given one
gt(val: number)	alias to lessThen()
greaterThenOrEqual(val:number)	Includes entries where active key has value which is greater or equal to given one
gte(val:number)	alias to greaterThenOrEqual()
between(min: number, max: number)	Includes entries where active key has value between "min" and "max", including "min" and "max"
notBetween(min: number, max: number)	Excludes entries where active key has value between "min" and "max", excluding "min" and "max"
in(list: any[])	Leaves in resulting array entries where active key has value from "list"
notIn(list: any[])	Excludes entries where active key has value from "list"
limit(offset: number, limit: number)	Leaves only "limit" elements from resulting array, beginning from "offset" element. Necessery for pagination . Remembers "unlimited" count for getResult() method

Fetch result

getById(id: number|string)	Returns row with given id
get(id: number|string)	Alias to getById()
getResult()	Returns object with data necessery for pagination - {content: array, totalElements: number, totalPages: number, last: boolean, first: boolean}
getList()	Returns array with selected elements
count()	Returns length of resulting array
unlimitedCount()	Returns length of resulting array BEFORE last limit() condition. Limit() must be a last condition in selection

Data manipulation (CRUD)

insert(row: any) => row	Adds a new entry to the array, taking into account getters and default entry. If new row contains "id" and entry with such id already exists, exception will be thrown. Returns inserted row.
update(rowData: any) => row	Updates elements by id. "Id" must be set in rowData. Deep merge does not supported Returns updated row.
delete(removingIds: number[]|string[]) => row[]	Deletes elements by id. removedIds - array of ids. Returns array - deleted rows

Additional

filter( callback(row: any) => boolean )	Applies standart Array.filter() method to selection and changes it, returns ArrayQL instance (thus, can be used in chaining).
map( callback(row: any) => any )	Applies standart Array.map() method to selection and transforms it, returns ArrayQL instance (thus, can be used in chaining).

Options

idName: string	Default is "id". Name of identification key
default: object	Default row, for example: {name: null, age: "Not set"}
getters: {[index: string]: getter(row:object)=>any}	Computed fields. Index (keys) of object - keynames of resulting row, "getter" is a function with only argument - row data, must return calculated value.

Examples

const ArrayQL = require("array-ql");
 
// regular array of objects with same structure
const arr = [
  { id: 1, firstName: "Clyde",  lastName: "Griffiths", gender: "male",   age: 24 },
  { id: 5, firstName: "Sondra", lastName: "Finchley",  gender: "female", age: 22 }
]
 
const options = {
  idName: "id",
  // default field values
  default: { firstName: "Unknown", lastName: "", gender: null, age: null },
  getters: {
    // getter for field "name"
    name(row){ return `${row.firstName} ${row.lastName}`; }
  }
}
 
const users = new ArrayQL(arr, options);
 
users.select("id, name").where("gender").is("male").getList(); // [{id: 1, name: "Clyde Griffiths"}]
 
users.insert({firstName: "Agrafena"}); // {id: 6, firstName: "Agrafena",  lastName: "", name: "Agrafena ", gender: null, age: null}
 
users.update({lastName: "Svetlova"}); // Error: "No id specified for update"
 
users.update({id: 6, lastName: "Svetlova", gender: "female", age: 31}); // {id: 6, firstName: "Agrafena",  lastName: "Svetlova", name: "Agrafena Svetlova", gender: female, age: 31}
 
users.select("name as username").where("age").gt(30).getList(); // [{username: "Agrafena Svetlova"}]
 
users.select("id, name").limit(0, 2).getResult(); // {content: [{id: 1, name: "Clyde Griffiths"}, {id: 5, name "Sondra Finchley"}], totalElements: 3, totalPages: 2, last: false, first: true}