{"id":18615,"library":"node-red-node-sqlite","title":"node-red-node-sqlite","description":"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.","status":"active","version":"2.0.1","language":"javascript","source_language":"en","source_url":"https://github.com/node-red/node-red-nodes","tags":["javascript","node-red","sqlite"],"install":[{"cmd":"npm install node-red-node-sqlite","lang":"bash","label":"npm"},{"cmd":"yarn add node-red-node-sqlite","lang":"bash","label":"yarn"},{"cmd":"pnpm add node-red-node-sqlite","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Peer dependency - requires Node-RED runtime to load nodes; not installed automatically","package":"node-red","optional":false},{"reason":"Underlying SQLite library for v2.x; provides native bindings and requires compile step","package":"better-sqlite3","optional":false}],"imports":[{"note":"Node-RED nodes are loaded via the runtime, not directly imported in user scripts.","wrong":"attempting require('node-red-node-sqlite') manually outside Node-RED","symbol":"default (node)","correct":"nodes are loaded automatically by Node-RED after npm install; no manual import required"},{"note":"The node is used visually in the flow editor; no code import needed.","wrong":"importing SQLite directly as a module from user code","symbol":"SQLite Node (configuration)","correct":"configure via Node-RED editor under 'storage' category"},{"note":"Query mode: 'Via msg.topic' expects query in msg.topic and optional parameters in msg.payload array.","wrong":"using msg.payload as the query string without msg.topic","symbol":"msg.topic / msg.payload","correct":"set msg.topic to SQL query string, msg.payload for parameters"}],"quickstart":{"code":"// Assuming Node-RED flow environment.\n// 1. Install: npm i --unsafe-perm node-red-node-sqlite\n// 2. Restart Node-RED.\n// 3. Add an inject node and a sqlite node to the canvas.\n// 4. Configure sqlite node: enter database path (e.g., /data/test.db).\n// 5. Set SQL Query: \"Via msg.topic\"\n// 6. Set SQL Statement: SELECT * FROM users WHERE name = $name\n// 7. In inject node, set:\n//    msg.topic = \"SELECT * FROM users WHERE name = $name\"\n//    msg.payload = [\"John\"]\n//    msg.params = { $name: \"John\" }   // if using prepared statement\n// 8. Deploy and inject. Output returns rows in msg.payload.\n// For batch: msg.topic = \"INSERT INTO logs VALUES('test'); INSERT INTO logs VALUES('test2');\"\n// Return in msg.payload: array of row objects or success/error status.","lang":"javascript","description":"Shows basic SQLite node usage: install, configure, inject query via msg.topic with parameters, and retrieve results."},"warnings":[{"fix":"Run `cd ~/.node-red/node_modules/sqlite3 && npm run rebuild` or similar depending on install location.","message":"Version 2.x updates underlying library from sqlite3 to better-sqlite3; may require native rebuild.","severity":"breaking","affected_versions":">=2.0.0"},{"fix":"Use 'Prepared Statement' mode with msg.params and parameterized queries.","message":"SQL injection vulnerability when using 'Via msg.topic' with raw queries; user is responsible for sanitizing.","severity":"gotcha","affected_versions":">=0.1.0"},{"fix":"Ensure msg.params keys start with $, e.g., { $id: 1, $name: 'John' }","message":"Prepared statement parameter object keys must include $ prefix (e.g., $name). Missing $ leads to SQLITE_RANGE error.","severity":"gotcha","affected_versions":">=0.1.0"},{"fix":"Upgrade to v2.x and rebuild native dependencies.","message":"Version 1.x requires Node.js >=12; not compatible with newer Node versions due to native binding changes.","severity":"deprecated","affected_versions":"<2.0.0"},{"fix":"Set `--unsafe-perm` flag during install; be patient.","message":"Native compile can take 15-20 minutes on low-end devices like Raspberry Pi.","severity":"gotcha","affected_versions":">=0.1.0"}],"env_vars":null,"last_verified":"2026-04-25T00:00:00.000Z","next_check":"2026-07-24T00:00:00.000Z","problems":[{"fix":"Rebuild sqlite3 binary: `cd ~/.node-red/node_modules/sqlite3 && npm run rebuild`","cause":"Precompiled binary incompatible with system libc version.","error":"GLIBC_2.38 not found"},{"fix":"Ensure msg.params keys match query parameters with $ prefix, e.g., { $id: 1 }","cause":"Prepared statement parameter object keys missing $ prefix or mismatch with query parameters.","error":"SQLITE_RANGE: bind or column index out of range"},{"fix":"Run `npm rebuild` in Node-RED user directory or specific module folder.","cause":"Node.js version mismatch after upgrade, requiring native module rebuild.","error":"Error: The module '.../better_sqlite3.node' was compiled against a different Node.js version"},{"fix":"Reinstall with `npm install --unsafe-perm node-red-node-sqlite` ensuring build tools available.","cause":"Missing native dependency after install.","error":"Cannot find module 'better-sqlite3'"}],"ecosystem":"npm","meta_description":null,"install_score":null,"install_tag":null,"quickstart_score":null,"quickstart_tag":null}