JSON 2 MySQL for NodeRed

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