Node-Red中如何为受密码保护的SQLite数据库节点提供密码？

Hey there, let's work through this password-protected SQLite issue in Node-RED! The short answer is: the default Node-RED SQLite node (like node-red-node-sqlite) doesn't natively support password-protected SQLite databases—since SQLite itself doesn't include encryption out of the box, most password-protected SQLite files rely on third-party extensions like SQLCipher. Here's how to get it working smoothly:

Option 1: Use a Custom Function Node (Recommended) #

This approach gives you full control over the encrypted database connection and avoids relying on modified core nodes. Follow these steps:

1. First, install the required dependencies in your Node-RED user directory (usually ~/.node-red):

   npm install sqlite3 sqlcipher
   

   The sqlcipher package adds encryption support to the standard sqlite3 library.

2. Add a Function node to your flow, and use code like this to connect to your encrypted database and run queries:

   // Reuse the database connection using node context to avoid duplicate connections
   let db = node.context().get('encryptedDb');
   
   if (!db) {
       const sqlite3 = require('sqlite3').verbose();
       db = new sqlite3.Database('./path/to/your/db.db', sqlite3.OPEN_READWRITE, (err) => {
           if (err) {
               node.error(`Failed to connect to database: ${err.message}`);
               return;
           }
           // Authenticate with the database password via PRAGMA
           db.run("PRAGMA key = 'your-secure-password-here';", (err) => {
               if (err) {
                   node.error(`Failed to authenticate with database: ${err.message}`);
                   return;
               }
               node.status({fill:"green", shape:"dot", text:"Connected (encrypted)"});
           });
       });
       node.context().set('encryptedDb', db);
   }
   
   // Example: Run a query and send results as the output message
   db.get("SELECT * FROM your_table LIMIT 1", (err, row) => {
       if (err) {
           node.error(`Query failed: ${err.message}`);
           return;
       }
       msg.payload = row;
       node.send(msg);
   });
   
   // Clean up connection when Node-RED stops
   node.on('close', () => {
       if (db) db.close();
   });
   

   Replace ./path/to/your/db.db and your-secure-password-here with your actual database path and password, then adjust the query to match your use case.

Option 2: Modify the Default SQLite Node (Not Recommended) #

If you really want to use the built-in SQLite node instead of a function, you can edit its code—but note that any Node-RED or node updates will overwrite your changes. Here's how:

1. Navigate to the SQLite node's directory (typically ~/.node-red/node_modules/node-red-node-sqlite).
2. Open sqlite.js in a text editor.
3. Find the section where the database connection is created (look for new sqlite3.Database(...)).
4. Add a line to run the PRAGMA key command right after the connection is established:

   db.run("PRAGMA key = 'your-password';");
   

5. Save the file and restart Node-RED.

Important Notes #

• Ensure your SQLite database was encrypted with SQLCipher—other encryption tools might require different handling.
• Never hardcode passwords directly in function nodes if you're sharing your flow! Instead, use Node-RED's credentials system to store passwords securely. You can access stored credentials in a function node with node.credentials.yourPasswordField.

内容的提问来源于stack exchange，提问作者Smithy