npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

node-red-contrib-alasql

v2.0.2

Published

Node-RED wrapper for AlaSQL database and XLSX import/export

Downloads

802

Readme

AlaSQL and AlaFile node-red nodes

(c) 2017 Mathias Rangel Wulff, Andrey Gershun & Kim McKinley

node-red-contrib-alasql

The Node-red alasql node lets you access javascript objects as if they were a SQL database. The alafile in/out nodes lets you transform the AlaSQL results into .xlsx, .xls, .csv, .json, .tab, .tsv, or .txt files stored on the node-red server.

The package is a Node-red wrapping of AlaSQL providing fast SQL based in-memory data processing for BI and ERP applications and import-export files into XLSX, XLS, and other data formats. Especially useful when you have (many) different sources coming with data you want to join, filter and format.

Install

Go to your Node-RED user directory and install node-red-contrib-alasql from npm:

cd ~/.node-red
npm install node-red-contrib-alasql

How to use an AlaSQL node

  1. Write a valid SQL query in the SQL query parameter. It can hold several SQL queries separated by ;.

  2. Refer to input data in msg.payload with $0 in your SQL. If msg.payload is an array the first value will be $0, the second $1 and so forth.

  3. The result will be returned in msg.payload

Returned values

Default return format is an array of objects:

[{name:'foo', age: 86}, {name:'bar', age:64}]

To manipulate output format please consult the use of VALUE OF, MATRIX OF, COLUMN OF, ROW OF, and RECORDSET OF.

If several queries are executed (separated by ;) the returned value will be an array with the result from each.

So - what can I do?

Execute SQL on your data and output the result.

This includes INSERT, UPDATE, DELETE, VIEW indexes and multiple levels of JOIN, GROUP BY, UNION, ROLLUP(), CUBE(), GROUPING SETS(), CROSS APPLY, OUTER APPLY, WITH SELECT, and subqueries. See the wiki to compare supported features with SQL standards.

Please consult the AlaSQL wiki to understand the flexible nature of the library.

AlaFile - Import and Export files

You also can use alafile component to perform import and export operations with files. The parameters include:

  • Name - the name of the node
  • File name - path to import or export file
  • Format - select file format
  • Colums - specify * (star) for all columns or list them with comma like one, two, three. You also can use AlaSQL functions or even rename columns with AS operator (see the AlaSQL SELECT statement documentation)
  • Headers - include first line headers

Test Suite

The node-red-contrib-alasql test suite for AlaSQL flows is provided to ensure AlaSQL nodes are operating properly.

node-red-contrib-alasql contains a set of test flows to ensure the nodes are handling exceptions properly and running as expected. Existing flows can be exported/imported into the test suite for verification.

The test suite is kindly contributed by Kim McKinley (PotOfCoffee2Go). Requests and comments are appreciated. Create an issue and please mention @PotOfCoffee2Go somewhere in the issue.

Running Alasql Test Suite

To run the Test Suite from an existing node-red installation that uses node-red-contrib-alasql please do:

cd ~/.node-red/node_modules/node-red-contrib-alasql
npm run test-setup
npm test

This will

  1. Create a node-red user directory containing AlaSQL test flows of the version installed in ~/.node-red
  2. Setup the needed files for testing
  3. Start the test serving on port 8081

Next time you want to run your test you can skip step 2.

To change the port do a export PORT=8081 on Mac or set PORT=8081 on Win prior to running npm test.

Running stand alone Test Suite

If you wish to test the current release of AlaSQL prior to going into production. In any directory:

git clone https://github.com/AlaSQL/node-red-contrib-alasql.git
cd node-red-contrib-alasql
npm install
npm run test-setup
npm test

The test suite uses the npm link command which allows node-red to automatically add AlaSQL nodes to node-red by default. To remove this behaviour, issue a npm unlink from the node-red-contrib-alasql directory cloned above.

Please note

As default the library works in-memory - so all unsaved data are reset when Node-RED closes. Please consult the wiki to read more about how to let data be persistent.

If you are not sure why this is funny - please find out more about "SQL injections":

xkcd