@caitken-com/node-red-contrib-json2mysql 1.2.0
JSON to MYSQL
Takes {JSON} payloads which then creates and executes MySQL queries, that are sql injection safe, with automatic quoting for input params, and automatic `table`.`column` back-ticking.
Dependencies
- MySQL server: Here's a tutorial on installing for Raspberry Pi.
- Some prior knowledge: Although I've changed certain keywords to simplify the json, some prior knowledge of MySQL is beneficial.
Config node
Create a config node to connect to your database server
| Field | Comment |
|---|---|
| host | your-domain/IP/localhost. |
| database | Name of database to connect. |
| user | Username to database. |
| password | Password to database. |
Query node
This is the node you'll use in your flows to pass in {JSON} and receive {string|array|object} from the output.
| Field | Comment |
|---|---|
| name | (optional) Node label |
| server | Config node used for database connection. |
| template | (optional) JSON string to create static payloads. Input payload can override/appended via input payload. |
Example template:
{
"select": {
"table": {"users": "user"},
"columns": [
"user.*"
]
}
"where": [
["user.first_name", "=", "?:first_name"]
]
}
Example payload to append to the template:
{
"params": {
"first_name": "John"
}
}
Example payload to override (where clause) of the template, the rest of the template will remain unchanged:
{
"where": [
["user.first_name", "is not", null]
]
}
Payload:
See documentation for available methods as keywords within your payload. (Note: fromJson)
The return keyword:
Takes {string} with value as any of the following to specify desired output of this node:
| Value | Comment |
|---|---|
string |
Returns a {string} of the generated query. Useful for debugging. |
array |
Returns {object[]} of rows, where each row is {column: value, ...} |
array-num |
Returns {array} of rows, where each row is a numeric {array} |
row |
Returns {object} of a single row {column: value, ...}. |
row-num |
Returns a single row, as a numeric {array} [0: value, 1: value, ...] |
map |
Returns {object[]} of {column: value} where the identifier is the first column in the result set, and the value is the second. |
map-array |
Returns {object} of {identifier: {column: value, ...}}, where the identifier is the first column in the result set, and the value an {object} of {column: value} pairs. |
val |
Returns a single value, of the first column of the first row. |
col |
Returns an {array} from the first column of each row [0: value, 1: value, ...]. |
count |
Number of rows returned/affected. |
If omitted from payload the default return type is string.
Complete payload example
Example of a typical payload.
{
"select": {
"table": {"users": "user"},
"columns": [
"user.id",
"user.name",
"YEAR(user.date_added) AS alumni"
]
},
"joins": [
{
"type": "inner",
"table": {"salaries": "salary"},
"conditions": [
["salary.user_id", "=", "user.id"]
]
}
],
"where": [
["salary.amount", ">", 100],
["user.first_name", "!=", "?"]
],
"group": [
"user.id"
],
"order": [
{"salary.amount": "DESC"},
"user.name"
],
"limit": 10,
"params": [
"Sam"
],
"return": "string"
}
Which will return the following:
SELECT `user`.`id`, `user`.`name`, YEAR(`user`.`date_added`) AS `alumni`
FROM `users` AS `user`
INNER JOIN `salaries` AS `salary` ON `salary`.`user_id` = `user`.`id`
WHERE `salary`.`amount` > 100
AND `user`.`first_name` != 'Sam'
GROUP BY `user`.`id`
ORDER BY `salary`.`amount` DESC, `user`.`name`
LIMIT 10
