Node-red-contrib-mydb NPM

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: