How to Upload a Database to Nodered

node-ruddy-contrib-alasql 2.0.i

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

npm install node-red-contrib-alasql

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

node-red-contrib-alasql

The Node-red alasql node lets yous admission 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-retentiveness data processing for BI and ERP applications and import-export files into XLSX, XLS, and other data formats. Especially useful when you lot 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-ruby-red-contrib-alasql from npm:

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

How to utilize an AlaSQL node

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

2. Refer to input data in msg.payload with $0 in your SQL. If msg.payload is an assortment the get-go value will be $0, the second $ane and so forth.

3. The result volition be returned in msg.payload

Returned values

Default return format is an array of objects:

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

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

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

Then - what can I do?

Execute SQL on your information and output the outcome.

This includes INSERT, UPDATE, DELETE, VIEW indexes and multiple levels of JOIN, GROUP BY, UNION, ROLLUP(), CUBE(), Group SETS(), CROSS APPLY, OUTER APPLY, WITH SELECT, and subqueries. Come across 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 utilise alafile component to perform import and consign 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 i, two, three. Yous likewise can use AlaSQL functions or even rename columns with AS operator (come across the AlaSQL SELECT statement documentation)
• Headers - include offset line headers

Examination Suite

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

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

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

Running Alasql Test Suite

To run the Exam Suite from an existing node-cherry-red installation that uses node-scarlet-contrib-alasql please practise:

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

This volition

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

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

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

Running stand up alone Test Suite

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

          git clone https://github.com/AlaSQL/node-ruddy-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 together AlaSQL nodes to node-red past default. To remove this behaviour, issue a npm unlink from the node-blood-red-contrib-alasql directory cloned above.

Please note

As default the library works in-memory - so all unsaved information are reset when Node-Ruby closes. Delight consult the wiki to read more nearly how to permit data be persistent.

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

humesmanch1937.blogspot.com

Source: https://flows.nodered.org/node/node-red-contrib-alasql

Share this post