node-red-node-sqlite

2.0.1 verified Sat Apr 25 auth: no javascript

Node-RED node for local SQLite database read/write operations. Current stable version is 2.0.1, released as part of the node-red-nodes collection. The package wraps the better-sqlite3 library (v2+) or sqlite3 (v1) to provide SQL query execution with support for prepared statements, parameterized queries, batch execution, SQLite extensions, and configurable reconnect timeouts. Install via npm in the Node-RED user directory with --unsafe-perm flag. Version 2.x introduces a breaking change requiring native binary rebuild on some platforms. Allows SQL injection when using raw queries via msg.topic, but prepared statements mitigate this.

Common errors

error GLIBC_2.38 not found ↓

cause Precompiled binary incompatible with system libc version.

fix

Rebuild sqlite3 binary: cd ~/.node-red/node_modules/sqlite3 && npm run rebuild

error SQLITE_RANGE: bind or column index out of range ↓

cause Prepared statement parameter object keys missing $ prefix or mismatch with query parameters.

fix

Ensure msg.params keys match query parameters with $ prefix, e.g., { $id: 1 }

error Error: The module '.../better_sqlite3.node' was compiled against a different Node.js version ↓

cause Node.js version mismatch after upgrade, requiring native module rebuild.

fix

Run npm rebuild in Node-RED user directory or specific module folder.

error Cannot find module 'better-sqlite3' ↓

cause Missing native dependency after install.

fix

Reinstall with npm install --unsafe-perm node-red-node-sqlite ensuring build tools available.

Warnings

breaking Version 2.x updates underlying library from sqlite3 to better-sqlite3; may require native rebuild. ↓

fix Run `cd ~/.node-red/node_modules/sqlite3 && npm run rebuild` or similar depending on install location.

gotcha SQL injection vulnerability when using 'Via msg.topic' with raw queries; user is responsible for sanitizing. ↓

fix Use 'Prepared Statement' mode with msg.params and parameterized queries.

gotcha Prepared statement parameter object keys must include $ prefix (e.g., $name). Missing $ leads to SQLITE_RANGE error. ↓

fix Ensure msg.params keys start with $, e.g., { $id: 1, $name: 'John' }

deprecated Version 1.x requires Node.js >=12; not compatible with newer Node versions due to native binding changes. ↓

fix Upgrade to v2.x and rebuild native dependencies.

gotcha Native compile can take 15-20 minutes on low-end devices like Raspberry Pi. ↓

fix Set `--unsafe-perm` flag during install; be patient.

Install

npm install node-red-node-sqlite

yarn add node-red-node-sqlite

pnpm add node-red-node-sqlite

Imports

default (node)

wrong

attempting require('node-red-node-sqlite') manually outside Node-RED

correct

nodes are loaded automatically by Node-RED after npm install; no manual import required

Node-RED nodes are loaded via the runtime, not directly imported in user scripts.

SQLite Node (configuration)

wrong

importing SQLite directly as a module from user code

correct

configure via Node-RED editor under 'storage' category

The node is used visually in the flow editor; no code import needed.

msg.topic / msg.payload
wrong
```
using msg.payload as the query string without msg.topic
```
correct
```
set msg.topic to SQL query string, msg.payload for parameters
```
Query mode: 'Via msg.topic' expects query in msg.topic and optional parameters in msg.payload array.

Quickstart

Shows basic SQLite node usage: install, configure, inject query via msg.topic with parameters, and retrieve results.

// Assuming Node-RED flow environment.
// 1. Install: npm i --unsafe-perm node-red-node-sqlite
// 2. Restart Node-RED.
// 3. Add an inject node and a sqlite node to the canvas.
// 4. Configure sqlite node: enter database path (e.g., /data/test.db).
// 5. Set SQL Query: "Via msg.topic"
// 6. Set SQL Statement: SELECT * FROM users WHERE name = $name
// 7. In inject node, set:
//    msg.topic = "SELECT * FROM users WHERE name = $name"
//    msg.payload = ["John"]
//    msg.params = { $name: "John" }   // if using prepared statement
// 8. Deploy and inject. Output returns rows in msg.payload.
// For batch: msg.topic = "INSERT INTO logs VALUES('test'); INSERT INTO logs VALUES('test2');"
// Return in msg.payload: array of row objects or success/error status.