Hello,
I like to access the mysql / mariadb database from within the js-code of a node-red function node.
A flow with separate nodes for sql-access (node-red-node-mysql) works but I would like to include the sql-statements (read / write) within the JavaScript code of a function node.
Does anybody know how to do this?
To access a MySQL or MariaDB database from within the JavaScript code of a Node-RED function node, you can use the node-red-node-mysql package.
This package provides a Node-RED node that you can use to execute SQL queries and retrieve the results from within your JavaScript code.
To use the node-red-node-mysql node, you will first need to install the package using npm. You can do this by running the following command in your Node-RED installation directory:
npm install node-red-node-mysql
Once the package is installed, you can use the mysql node in your flow by dragging it from the palette and configuring it with the necessary connection details for your database.
You can then use the mysql node in your function node by specifying it as an input or output, depending on whether you want to use it to execute queries or retrieve results.
Here is an example of how you can use the mysql node in your function node to execute a SQL query and retrieve the results:
// Initialize the MySQL node
var mysql = context.get('mysql');
// Define the SQL query to be executed
var sql = 'SELECT * FROM users WHERE id = 1';
// Execute the query and retrieve the results
var result = await mysql.query(sql);
// Use the results in your JavaScript code
msg.payload = result;
Hope this is helpful.
Thanks for input. node-red-node-mysql I had installed. But this example doesn’t work.
I get the error: function : (error) “TypeError: Cannot read properties of undefined (reading ‘query’)”
Isn’t there an “open connection” statement missing?
And is there a need to make an entry in settings.js within functionGlobalContext: { ?
The specific error message you mention, TypeError: Cannot read properties of undefined (reading 'query') , indicates that the query property is undefined when it is being accessed.
As you have installed the package, you will need to configure the node-red-node-mysql node in your Node-RED flow.
In the node configuration, you will need to specify the connection details for your MySQL database, such as the hostname, port, username, and password.
In addition to configuring the node itself, you will also need to make an entry in the functionGlobalContext section of your Node-RED settings.js file.
This entry should specify the node-red-node-mysql package as a global context, so that it can be used in your Node-RED flows. The specific configuration will vary depending on your setup, but an example entry in the functionGlobalContext section might look like this:
functionGlobalContext: {
// other context entries...
mysql: require('node-red-node-mysql')
},
Once you have made these configuration changes and restarted Node-RED, you should be able to use the node-red-node-mysql node in your flows without encountering the TypeError: Cannot read properties of undefined (reading 'query') error.
Hi all,
thanks for the details so far, as I am trying to achieve something similar…
I have replicated your steps so far… In my case it is the unmodified default sqllite database.
However, I am still struggeling with accessing the database, getting the error message: Error: connect ECONNREFUSED ::1:3306
I assume that I am still using the wrong database name, user and pwd.
So far I have tested with:
But I am not sure, if the value Database is correct?
Furthermore, do I have to use a generic user or a specific user which I am using to log in e.g. via the webinterface?
Any help would be appreciated…
hi there,
the configuration as described here doesnt work for me…
if i edit the settings.js with:
then node red doesnt start any more:
any idea why this happend?
regards
Daniel
