Node-RED MySQL Database Connector

3.0.0 · active · verified Sun Apr 19

This package provides a Node-RED node for connecting to and interacting with MySQL databases. It allows users to execute SQL `query` operations, including `INSERT`, `UPDATE`, `DELETE`, and `SELECT` statements, by passing the SQL query in `msg.topic` and parameters in `msg.payload`. The results are returned in `msg.payload`, typically as an array of rows or null if no results are found. The current stable version is 3.0.0. While a specific release cadence isn't published, the project maintains an active development presence on GitHub. A key differentiator is its seamless integration into the Node-RED visual programming environment, abstracting direct `mysql` library usage into a configurable, GUI-driven node. It supports parameterized queries, both positional and named, which helps mitigate basic SQL injection risks, but explicitly warns users to remain vigilant about security. The node also offers configuration options for database reconnection timeouts and character sets.

Common errors

```
ER_ACCESS_DENIED_ERROR: Access denied for user 'user'@'host' (using password: YES/NO)
```
cause Incorrect database username, password, or host provided in the MySQL node configuration.

fix Verify the database credentials (username, password, host, port) in the Node-RED MySQL node configuration against your MySQL server settings. Ensure the user has the necessary permissions.
```
ER_BAD_DB_ERROR: Unknown database 'your_database_name'
```
cause The database name configured in the Node-RED MySQL node does not exist on the specified MySQL server.

fix Correct the database name in the Node-RED MySQL node configuration, or create the database on your MySQL server if it's missing.
```
UnhandledPromiseRejectionWarning: Connection lost: The server closed the connection.
```
cause The connection to the MySQL server was unexpectedly closed, often due to server-side timeouts, network issues, or the MySQL server being restarted.

fix Check the MySQL server's status and network connectivity. In the Node-RED MySQL node configuration, you can adjust the `mysqlReconnectTime` setting (in `settings.js`) to control the retry timeout for reconnecting.
```
SyntaxError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '...' at line X
```
cause The SQL query provided in `msg.topic` contains a syntax error, or includes invalid characters/keywords for your specific MySQL version.

fix Carefully review the SQL query string in your Node-RED Function node (or wherever `msg.topic` is set). Test the query directly in a MySQL client to isolate and fix syntax issues. Ensure parameterized queries are correctly formatted.

Warnings

gotcha The node explicitly states that by its nature it allows SQL injection. Users are solely responsible for sanitizing input and constructing safe queries. While parameterized queries (`?` or `:name`) are supported and recommended, misuse or direct concatenation of untrusted input can lead to severe security vulnerabilities.
Fix: Always use parameterized queries (`?` for positional, `:name` for named parameters) and ensure user-provided data is passed within `msg.payload` for automatic escaping. Never concatenate raw user input directly into `msg.topic` (the SQL query string).
breaking As of version 3.0.0, the package requires Node.js version 18.0.0 or higher. Running on older Node.js environments will result in errors.
Fix: Upgrade your Node.js runtime environment to version 18.0.0 or later to ensure compatibility and stability.
gotcha The default character set used by the MySQL connection is often 'old' Mysql 3-byte UTF. If your application needs to handle emojis or extended Unicode characters, you must explicitly configure the database and the connection to use `UTF8MB4`.
Fix: Ensure your MySQL database and tables are configured for `UTF8MB4` character set. In the Node-RED MySQL node's configuration, verify or set the character set if the option is exposed, or refer to the underlying `node-mysql` package documentation for advanced configuration options if available.
gotcha The README mentions that `null` is returned if nothing is found for a key. However, database operations can fail for many other reasons (connection errors, SQL syntax errors, permissions). Without explicit error handling within the Node-RED flow (e.g., wiring the error output of the MySQL node), these failures can go unnoticed.
Fix: Always connect the second (error) output of the MySQL node to an error handling mechanism (e.g., a `Catch` node, a `Debug` node for logging, or another function to send notifications). Implement robust error handling for all database interactions to prevent silent failures.

Install

npm install node-red-node-mysql npm
yarn add node-red-node-mysql yarn
pnpm add node-red-node-mysql pnpm

Imports

Node-RED MySQL Node

This package is a Node-RED node, installed via the Node-RED palette manager or `npm i node-red-node-mysql` in your Node-RED user directory. It does not expose symbols for direct JavaScript `import` or `require` by end-user applications; interaction occurs graphically within the Node-RED editor by dragging and configuring the 'MySQL' node.

Node-RED nodes are primarily consumed through the Node-RED visual editor and are not designed for direct programmatic import into user JavaScript files. Attempting to `import` or `require` this package directly in a standard Node.js application is incorrect and will not provide the expected functionality.

Quickstart

This Node-RED Function node prepares `msg.topic` and `msg.payload` to perform an `INSERT` query using named parameters, which can then be fed into a configured Node-RED MySQL database node. It includes commented-out examples for `UPDATE` and `SELECT` operations.

{
  "id": "b1c2d3e4.f5g6h7",
  "type": "function",
  "name": "Prepare MySQL Query",
  "func": "// This code is designed to be placed inside a Node-RED Function node.\n// It prepares the 'msg.topic' and 'msg.payload' objects\n// which are then passed to a `MySQL` database node (from this package).\n\n// First, ensure you have a `MySQL` node configured in your Node-RED flow\n// with appropriate database credentials and connection details.\n// Connect the output of this Function node to the input of your MySQL node.\n\n// Example: Inserting a new user with a unique username\n// Make sure your 'users' table exists, e.g.:\n// CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(255) UNIQUE NOT NULL);\n\nconst uniqueUsername = `user_${Date.now()}_${Math.floor(Math.random() * 1000)}`;\nmsg.payload = { username: uniqueUsername };\nmsg.topic = \"INSERT INTO users (username) VALUES (:username);\";\n\n// --- Alternative: Updating an existing user ---\n// Uncomment the following lines to demonstrate an update operation.\n// This requires a user with a specific ID to exist.\n/*\nmsg.payload = { userId: 1, newUsername: \"updated_user_name\" };\nmsg.topic = \"UPDATE users SET username = :newUsername WHERE id = :userId;\";\n*/\n\n// --- Alternative: Selecting data ---\n// Uncomment the following lines to demonstrate a select operation.\n/*\nmsg.payload = { searchTerm: \"%user%\" };\nmsg.topic = \"SELECT id, username FROM users WHERE username LIKE :searchTerm;\";\n*/\n\nreturn msg;",
  "outputs": 1,
  "noerr": 0,
  "initialize": "",
  "finalize": "",
  "libs": [],
  "x": 100,
  "y": 100,
  "wires": [
    [
      "a8b9c0d1.e2f3g4" // Connect this to your Node-RED MySQL node's ID
    ]
  ]
}

view raw JSON →