RBQL is both a library and a command line tool which provides SQL-like language with JavaScript expressions
In order to make RBQL work in browser as a library for your App you need just one single file: rbql.js
To get it you can either use npm:
$ npm install rbql
Now you can just source rbql.js and it will work:
<script src="rbql.js"></script>
The following two functions are avilable in the browser version:
Run user query against input array of records and put the result set in the output array:
async function query_table(user_query, input_table, output_table, output_warnings, join_table=null, input_column_names=null, join_column_names=null, output_column_names=null, normalize_column_names=true)
Allows to run queries against any kind of structured data.
You will have to implement special wrapper classes for your custom data structures and pass them to the rbql.query(...) function.
async function query(user_query, input_iterator, output_writer, output_warnings, join_tables_registry=null)
Very simple test to make sure that RBQL library works:
<!DOCTYPE html>
<html><head>
<script src="../../rbql-js/rbql.js"></script>
<script>
let output_table = [];
let warnings = [];
let error_handler = function(exception) {
console.log('RBQL finished with error: ' + String(exception));
}
let success_handler = function() {
console.log('warnings: ' + JSON.stringify(warnings));
console.log('output table: ' + JSON.stringify(output_table));
}
rbql.query_table('select a2 + " test", a1 limit 2', [[1, 'foo'], [2, 'bar'], [3, 'hello']], output_table, warnings).then(success_handler).catch(error_handler);
</script>
<title>RBQL Generic Test</title>
</head><body>
<div><span>Open browser console</span></div>
</body></html>
Save the code above as rbql_test.html; put rbql.js in the same folder; open rbql_test.html in your browser and make sure that console output contains the expected result.
A little more advanced, but still very simple demo test with JSFiddle
It uses the same rbql.js script file.
$ npm install rbql
The following 3 functions are avilable in Node version:
Run user query against input_path CSV file and save it as output_path CSV file.
async function rbql.query_csv(user_query, input_path, input_delim, input_policy, output_path, output_delim, output_policy, csv_encoding, output_warnings, with_headers=false, comment_prefix=null)
'simple', 'quoted''binary', 'utf-8'true treat the first records in input (and join) file as header.const rbql = require('rbql')
let input_table = [
['Roosevelt',1858,'USA'],
['Napoleon',1769,'France'],
['Dmitri Mendeleev',1834,'Russia'],
['Jane Austen',1775,'England'],
['Hayao Miyazaki',1941,'Japan'],
];
let user_query = 'SELECT a1, a2 % 1000 WHERE a3 != "USA" LIMIT 3';
let output_table = [];
let warnings = [];
let error_handler = function(exception) {
console.log('Error: ' + String(exception));
}
let success_handler = function() {
console.log('warnings: ' + JSON.stringify(warnings));
console.log('output table: ' + JSON.stringify(output_table));
}
rbql.query_table(user_query, input_table, output_table, warnings).then(success_handler).catch(error_handler);
const rbql = require('rbql');
let user_query = 'SELECT a1, parseInt(a2) % 1000 WHERE a3 != "USA" LIMIT 5';
let error_handler = function(exception) {
console.log('Error: ' + String(exception));
}
let warnings = [];
let success_handler = function() {
if (warnings.length)
console.log('warnings: ' + JSON.stringify(warnings));
console.log('output table: output.csv');
}
rbql.query_csv(user_query, 'input.csv', ',', 'quoted', 'output.csv', ',', 'quoted', 'utf-8', warnings).then(success_handler).catch(error_handler);
You can also check rbql-js cli app code as a usage example: rbql-js cli source code
To use RBQL as CLI app you can install it in global (-g) mode:
$ npm install -g rbql
RBQL can also be installed locally with $ npm install rbql, but then you would have to run it with $ npx rbql-js ... instead of $ rbql-js ....
$ rbql-js --query "select a1, a2 order by a1" < input.tsv
In interactive mode rbql-js will show input table preview so it is easier to type SQL-like query.
$ rbql-js --input input.csv --output result.csv
All keywords have the same meaning as in SQL queries. You can check them online
RBQL for CSV files provides the following variables which you can use in your queries:
UPDATE query produces a new table where original values are replaced according to the UPDATE expression, so it can also be considered a special type of SELECT query.
RBQL supports the following aggregate functions, which can also be used with GROUP BY keyword:
COUNT, ARRAY_AGG, MIN, MAX, ANY_VALUE, SUM, AVG, VARIANCE, MEDIAN
Limitation: aggregate functions inside JavaScript expressions are not supported. Although you can use expressions inside aggregate functions.
E.g. MAX(float(a1) / 1000) - valid; MAX(a1) / 1000 - invalid.
There is a workaround for the limitation above for ARRAY_AGG function which supports an optional parameter - a callback function that can do something with the aggregated array. Example:
SELECT a2, ARRAY_AGG(a1, v => v.sort().slice(0, 5)) GROUP BY a2
Join table B can be referenced either by its file path or by its name - an arbitrary string which the user should provide before executing the JOIN query.
RBQL supports STRICT LEFT JOIN which is like LEFT JOIN, but generates an error if any key in the left table "A" doesn't have exactly one matching key in the right table "B".
Table B path can be either relative to the working dir, relative to the main table or absolute.
Limitation: JOIN statements can't contain JavaScript expressions and must have the following form: <JOIN_KEYWORD> (/path/to/table.tsv | table_name ) ON a... == b... [AND a... == b... [AND ... ]]
SELECT EXCEPT can be used to select everything except specific columns. E.g. to select everything but columns 2 and 4, run: SELECT * EXCEPT a2, a4
Traditional SQL engines do not support this query mode.
UNNEST(list) takes a list/array as an argument and repeats the output record multiple times - one time for each value from the list argument.
Example: SELECT a1, UNNEST(a2.split(';'))
RBQL does not support LIKE operator, instead it provides "like()" function which can be used like this:
SELECT * where like(a1, 'foo%bar')
You can set whether the input (and join) CSV file has a header or not using the environment configuration parameters which could be --with_headers CLI flag or GUI checkbox or something else.
But it is also possible to override this selection directly in the query by adding either WITH (header) or WITH (noheader) statement at the end of the query.
Example: select top 5 NR, * with (header)
You can chain consecutive queries via pipe | syntax. Example:
SELECT a2 AS region, count(*) AS cnt GROUP BY a2 | SELECT * ORDER BY a.cnt DESC
RBQL supports User Defined Functions
You can define custom functions and/or import libraries in a special file: ~/.rbql_init_source.js
SELECT TOP 100 a1, a2 * 10, a4.length WHERE a1 == "Buy" ORDER BY parseInt(a2) DESCSELECT a.id, a.weight / 1000 AS weight_kgSELECT * ORDER BY Math.random() - random sortSELECT TOP 20 a.vehicle_price.length / 10, a2 WHERE parseInt(a.vehicle_price) < 500 && ["car", "plane", "boat"].indexOf(a['Vehicle type']) > -1 limit 20 - referencing columns by names from headerUPDATE SET a3 = 'NPC' WHERE a3.indexOf('Non-playable character') != -1SELECT NR, * - enumerate records, NR is 1-basedSELECT a1, b1, b2 INNER JOIN ./countries.txt ON a2 == b1 ORDER BY a1, a3 - example of join querySELECT MAX(a1), MIN(a1) WHERE a.Name != 'John' GROUP BY a2, a3 - example of aggregate querySELECT ...a1.split(':') - Using JS "destructuring assignment" syntax to split one column into many. Do not try this with other SQL engines!$ pip install rbqlFAQs
Rainbow Query Language
We found that rbql demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Axios compromise traced to social engineering, showing how attacks on maintainers can bypass controls and expose the broader software supply chain.
Security News
Node.js has paused its bug bounty program after funding ended, removing payouts for vulnerability reports but keeping its security process unchanged.
Security News
The Axios compromise shows how time-dependent dependency resolution makes exposure harder to detect and contain.