@steeltype/node-red-contrib-steeltype-sqlite-wrapper 1.1.0

Node-RED nodes wrapping sqlite3 v3 for simple queries and CRUD operations.

npm install @steeltype/node-red-contrib-steeltype-sqlite-wrapper

Node-RED SQLite Wrapper

This module provides simple Node-RED nodes that wrap the sqlite3 package. It offers a configuration node for selecting the database file and an action node for running queries and basic CRUD operations.

Installation

npm install @Steeltype/node-red-contrib-steeltype-sqlite-wrapper

Nodes

sqlite-config

Configuration node that holds the path to the SQLite database. Set the path using the dbPath property or credential. The resolved path is exposed as db for use by other nodes.

sqlite-action

Performs database actions using the configured database connection. Select the operation type in the node configuration:

• query – run a SQL query from msg.topic with optional parameters msg.params.
• get – run a query that returns a single row.
• insert – insert msg.payload object into the configured table.
• update – update rows using msg.payload.data and msg.payload.where.
• delete – delete rows using msg.payload.where.
• exec – execute raw SQL from msg.topic without returning rows.
• transaction – run an array of statements in msg.payload within a single transaction.

Results are returned in msg.payload.

Further usage information for each node is available from the Node-RED info panel when the node is selected.

All statements are validated for basic SQL syntax before execution. If an error is found, the node throws a Node-RED style exception that includes the database message for easier troubleshooting.

For a transaction, provide an array of statement objects:

msg.payload = [
  { sql: "INSERT INTO users (name) VALUES (?)", params: ["Alice"] },
  { sql: "INSERT INTO users (name) VALUES (?)", params: ["Bob"] }
];

Examples

Create a flow:

1. Add a sqlite-config node and specify the path to your db.sqlite.
2. Add a sqlite-action node connected to an inject or function node.
3. Configure the sqlite-action node with the desired operation.

Below are example messages for common operations.

Query

msg.topic = 'SELECT * FROM users WHERE id = ?';
msg.params = [1];

The output message will contain an array of rows.

Insert

msg.payload = { name: 'Alice' };

The result includes msg.payload.lastID.

Update

msg.payload = {
  data: { name: 'Bob' },
  where: 'id = ?',
  params: [1]
};

Delete

msg.payload = { where: 'id = ?', params: [1] };

Get

msg.topic = 'SELECT name FROM users WHERE id = ?';
msg.params = [1];

Exec

msg.topic = 'VACUUM';

Transaction

msg.payload = [
  { sql: 'INSERT INTO users (name) VALUES (?)', params: ['Carol'] },
  { sql: 'INSERT INTO users (name) VALUES (?)', params: ['Dave'] }
];

Development

Run unit tests with:

npm test

Publishing

Before publishing to npm, ensure all tests pass:

npm test

Then publish the package:

npm publish --access public

License

Apache-2.0