node-red-contrib-mydb

WARNING

This package has been renamed! Please consider to upgrade to: '@open-kappa/node-red-contrib-mydb' This version is no longer maintained.

node-red-contrib-mydb

This package provides two node-red nodes:

• An input/output node for DB queries
• A DB connection configuration node

At the moment only Postgres is supported, but maybe other DB's will be supported in the future.

How-to and features

node-red-contrib-mydb uses mustache to render queries templates. The node input is a message JSON object with the values to be substituted, e.g.:

msg = {
    "tableName": "test"
}

In the node body, write the query tempalte, e.g.:

select * from {{msg.tableName}};

It is worth noting that the syntax with double curly brackets sanitizes the template parameters. If raw expansion is desired (namely "sql injection"), just uses triple curly brackets:

{{{msg.query}}}

Comparison and incompatibilities with postgrestor

This package can be used as a drop-in replacement of postgrestor, but:

• It has a slightly different behavior in case of multiple SQL commands inside the same node: postgrestor returns just the last query, node-red-contrib-mydb returns a packed object with all queryes (which actually can be considered as a bug-fix). This can require to adapt the code or query, for example, in case of set schema command followed by the actual query.

// Single query result:
"payload": {
    "command": ...,
    "rowCount": ...,
    rows: [...],
    ...
}

// Multiple queries result:
"payload": [
    {
        "command": ...,
        "rowCount": ...,
        rows: [...],
        ...
    },
    {
        "command": ...,
        "rowCount": ...,
        rows: [...],
        ...
    },
    ...
]

• node-red-contrib-mydb supports connections to multiple DB's (postgrestor cannot).

Design and motivation

This package has been initially designed as a drop-in replacement of other available nodes, more precisely postgrestor since it seems currently unmaintained. It also tries to solve some of postgrestor open issues.

We know that there are other Postgres nodes, but:

• Some of them seem unmaintained
• Some of them are for different scopes, or not a simple drop-in replacement

Therefore, we have decided to try to implement a node by ourselves.

Links

• Project homepage: hosted on GitLab Pages

• Project sources: hosted on gitlab.com

License

node-red-contrib-mydb is released under the liberal MIT License. Please refer to the LICENSE.txt project file for further details.

Credits

All the stuff into this package as been created from scratch, with the following exceptions:

• The node icon has been taken from the original postgrestor node.
• The node HTML has been takend and adapted from the original postgrestor node.

Patrons

This node-red module has been sponsored by Gizero Energie s.r.l..