V2NodesMySql
MySql
V2

MySql

MySQL is a relational database platform used to store and query structured application and business data.

The Flowgear MySql Node queries databases and upserts rows through a direct connection or an optional SSH tunnel.

Revision History

0.0.0.2 - Initial release.
0.0.0.3 - Fixed templates.
0.0.0.7 - Fixed Node attributes.
0.0.0.9 - Optimized template discovery.

Connection

Use the Connection to configure the MySQL database credentials and optional SSH tunnel.

Property Type Description
Server String The name or IP address of the server hosting the MySQL instance.
Database String The name of the database to connect to.
Username String The MySQL username used for the Connection.
Password Masked The MySQL password used for the Connection.
Use SSH Boolean When true, the Connection uses an SSH tunnel. The default is false.
SSH Server String The SSH server used to establish the tunnel.
SSH Port Integer The SSH server port. The default is 22.
Database Port Integer The MySQL database port. The default is 3306.
SSH Username String The username used to authenticate with the SSH server.
SSH Password Masked The SSH password used when key authentication is not provided.
SSH Key File String The SSH private-key contents used for key authentication.
SSH Key File Pass Phrase Masked The pass phrase for the SSH private key.
Connection Timeout Integer The time in seconds to wait while establishing a Connection. The minimum and default value is 15.

Setup Notes

Enter Server, Database, and Username for a direct database Connection. Enter Password when the database account requires one.

To connect through SSH:

  • Set Use SSH to true.
  • Enter SSH Server, SSH Port, and SSH Username.
  • Enter either SSH Password or the private-key contents in SSH Key File.
  • Enter SSH Key File Pass Phrase if the private key is encrypted.
  • Set Database Port to the MySQL port that the SSH server can reach.

Test the Connection after saving it.

Methods

The MySql Node exposes methods for running SQL queries and upserting rows into a table.

Query

Executes a query against a MySQL database and returns the results.

Parameter Type Description
Connection Connection The MySql Connection.
Query String The SQL query to execute.
Return Type Description
Items Array The rows returned by the query. Each row is emitted as an object.

Upsert

Upserts data into a MySQL database table.

Parameter Type Description
Connection Connection The MySql Connection.
TableName String The name of the table to upsert.
KeyFields String Comma-delimited key fields used to identify rows to update.
Items Array The items to upsert.
BatchSize Integer The number of rows to upsert per batch. The default is 100.
Return Type Description
RowsAffected Integer The number of rows affected by the upsert operation.

Usage Notes

  • Query emits one output item per row in the result set.
  • Query templates are generated from tables in the configured database and select up to ten rows by default.
  • Upsert templates use the current table schema to describe Items.
  • For Upsert, use item Property names that match the destination column names.
  • Supply KeyFields when existing rows must be updated. A duplicate-key error is returned when an insert conflicts and no suitable key fields identify the row to update.
  • SSH authentication requires either SSH Password or SSH Key File contents.