Knex JS for Node Red

A plugin allowing interaction with databases using knex.js.

Why Knex JS

Due to the chainable nature of knex, it lends itself easily to work within Node Red. The aim of this node is to allow users to create queries using chainable methods which can be executed at the end of the chain.

Compatiblity Notes

This plugin was developed with Node-RED version v2.1.4 on Node.js version v14.15.4. Due to limited time and resources I cannot ensure compatibility with other versions of Node-RED or Node.js.

Dependancies

You may need to install the knex library. You may also need to install the appropriate database library: pg for PostgreSQL, CockroachDB and Amazon Redshift, pg-native for PostgreSQL with native C++ libpq bindings (requires PostgresSQL installed to link against), mysql or mysql2 for MySQL or MariaDB, sqlite3 for SQLite3, or tedious for MSSQL.

$ npm install knex --save

# Then add one of the following (adding a --save) flag:
$ npm install pg
$ npm install pg-native
$ npm install sqlite3
$ npm install mysql
$ npm install mysql2
$ npm install oracledb
$ npm install tedious

NOTE: Only the mysql2 and pg libraries have been tested. Feedback on the remaining libraries is welcome

Documentation

I've made every effort to make the nodes as easy to understand as possible, with links to the various knex.js methods built-in to their nodes. There are a few "undocumented" methods added such as:

into

This node is used as the table selector for insert and upsert queries

selectRaw

This allows a raw SQL statement to be used in the select statement. This is actually achieved by using .select(knex.raw('{{statment}}'))

Common Issues

Error: self signed certificate in certificate chain

This is caused by (you guessed it) an issue validating the certificate of the database you're connecting to. There are 3 solutions:

Allow untrusted certificates

Setting NODE_TLS_REJECT_UNAUTHORIZED='0' will tell NodeJS to ignore unauthorized certificate errors. You can set it by using one of the following commands in the CLI:

Set as environmental variable globally

export NODE_TLS_REJECT_UNAUTHORIZED='0'

Set as environmental variable for specific command

NODE_TLS_REJECT_UNAUTHORIZED='0' ...

If you don't want to set an environment variable or need to do this for multiple applications npm has a strict-ssl config you set to false

npm config set strict-ssl=false

Resolution sourced from this StackOverFlow Answer

Set the file path to the CA certificate

In the connection node, there is a field for "CA Certificate Absolute Path" where you can set the absolute path of the CA Certificate which you wish to use for the connection. This is then accessible in the configuration using the {{caCert}}

No Migrations

Because Node-RED is not a CLI utility, there are no migrations. However, the functionality of migrations can be accomplished by the Schema Builder nodes. Since migrations are meant to be run manually, it is suggested that any schema building functionality is triggered through manually activated nodes such as the Inject node.

No .on* Nodes

Because all .on* methods are exclusivly used in sub-queries of .join* methods, they are not included as top-level nodes. If you wish to use .on* methods, simply change the type of .join* from Standard to Callback and you will gain direct access to the builder.

No union* or intersect* Nodes

These nodes are especially tricky to implements since they're supposed to accept multuple queries as inputs (like a "join" or "batch" node). If these become a requested feature I will consider how to implement this in the future.

No transacting, forUpdate, forShare, forNoKeyUpdate, forKeyShare, or transaction Nodes

If these become a requested feature I will consider how to implement this in the future.

No hintComment, denseRank, rank. rowNumber, partitionBy. modify, connection, options or queryContext Nodes

If these become a requested feature I will consider how to implement this in the future.

truncate has a different signature than the Knex method

Because truncate is a special type of query, instead of adding additional chained methods like we do for insert to determine the table, it seemed more accurate to include the table name as part of the node.