[APP][PRO] MySQL-Client

small
A MySQL client for Homey

Add this app to use SQL queries in Homey to read or write datasets from/to a local MySQL compatible database.

The app provides a driver/device to connect to a database server and one of its databases.


The following flow actions can be used from this device:

  • SELECT a dataset with a SQL query (read periodical logged values to use in Homey).
  • UPDATE/INSERT lines using global tokens (log device capabilities to your database).

The following flow triggers can be used to show the result in your timeline or to handle the result data:

  • Result of a SELECT query. Take the first value directly as flow token to use it in your flow (e.g. push it to a virtual sensor)
  • Result of a INSERT/UPDATE query. Check the result like affected rows or success state.

Check the flow tokens and flow description for details.

In addition you can use date variables to use calculated date values in the SQL query like:

  • [date-1d] = yesterday / [date+1d] = tomorrow / [date+0d] = today (or use the default date token)
  • [date-1m] = one month in the past
  • [date-1y] = one year in the past

This way you can easily select a period (SUM, AVG…).

SQL errors are written to the timeline.


Thank you for supporting app development with a small donation via PayPal.

btn_donate_LG1

7 Likes

Examples:

  • SELECT
    Select one value from your database in the THEN part of your flow:
    grafik
    Use the flow trigger in the IF part of your flow to receive the first value of the result.
    You can use the Query-ID used in the SELECT above to filter your flow (react only on this kind of query).
    grafik
    Use the value in your flow. Take a look at the local flow tags you can use:
    grafik
    Use the first valus as number or text, write the complete JSON result to the timeline or what ever you need :slight_smile:

  • INSERT / UPDATE
    Insert a line into your database. This way you can periodically log capabilities.
    Use a trigger to execute the query:
    grafik
    Insert your query and use global tokens or the date variable. This way it’s possible to log a daily sum at a new day using the date of yesterday:
    grafik
    If needed you can print your result in the timeline:

  • Combining SELECT & INSERT
    If you have only a meter capability and want to log the hourly usage, you can first select the last meter value from your database and insert a new line with meter and calculated usage value.
    1st: SELECT the last value.
    Start the flow every hour. Set a Query ID to be able to react in a trigger only on that selection. Read the newest value from meter column.

    2nd: Use this value in a second flow to insert a new line:
    Use the Query ID as filter for the flow trigger. Insert a line with date/time, the calculated difference (current meter minus flow token containing the result of your SELECT) and the new meter value.

FAQ:

reserved

Versions:

reserved

Hi Ronny,
will this app also be able to query a MariaDB database ?

Yes. Maria DB is compatible in common SQL syntax.
I use this app with a MariaDB on a Qnap NAS.

:+1::+1:

Great ! I have been looking for a way to use the HA database on Homey for a long time.

1 Like

first i smiled… but then i saw you can only use it for certain tasks, like energy.
I wish there was an universal sql client. so you can configure db in the settings and then use the flows with queries.

If you read again you will see that it’s a universal client.:wink:
You can execute all SQL queries you want and use global/local tokens.
For Selects you have only tokens for one field (numeric+text) at the moment because these tokens mut be dwfined in the flow trigger.
For Insert/Update you can use all global tokens you need.
And becaus it’s the default token field, you use MathJS expressions, too.

The examples above should only demonstrate the usage with a common task: logging capabilities to a database.

1 Like

I think they meant a client that can connect to other databases besides MySQL/MariaDB.

1 Like

Hm, could be. But why this comment “but then i saw you can only use it for certain tasks, like energy”?

And yes, the app uses a NodeJS library for MySQL. So you can only use it for compatible databases.

Yeah you’re right :sweat_smile:

1 Like

Indeed. He was right. Hahaha

But why you need to add a device as een switch, energy thing etc. But none of them are for general purpose.

Maybe add an extra device as in: General
Or something with an icon of a db?

Sorry, I don’t know what’s your question.
Please wait until the app is approved and try. I’ll post a information when it’s ready.

You will add a database as Homey device (for credentials and flows). Then you define flows (as action for that device) to execute your SQL querys. That’s all. Everything else is up to you.

1 Like

oke cool… then it sounds like what i like :slight_smile:
looking forward for it!

thnx

1 Like

The Homey Apps Insights2SQL only works with Microsoft databases and MYSQL does not work for me.

With such an app you can save storage space at Homey At least that’s how it’s meant to be for me.
I run HA, which stores energy consumption in MariaDB, for example. But on Homey I have to install additional apps to get this value. It would be easier if I could query the database from HA.
I also hope that this app can perhaps write Homey values to the same database to make them available to the other side.
I have already tried this, but failed.

:partying_face: The app is online now :smiley:

Please keep in mind that it’s still a beta version.
You can post questions, feature request or bugs. Anything that makes the app better is welcome.

4 Likes

Hi Ronny,
something is going wrong with me.
I have a connection to the HA database and I also understand that I need two flows to write a value, e.g. to the timeline.
What is not quite clear to me is what exactly the entry in the 1st flow must look like in order to query the value ?

Assuming you want to find the latest state value of the sensor.power_usage sensor, in plain SQL the query should be:

SELECT state FROM states WHERE entity_id = "sensor.power_usage" ORDER BY last_changed DESC LIMIT 1;
1 Like

Hi Uwe,

are these table rows? What’s the table name?
To get the last “state”:
SELECT state from <TABLE> ORDER BY last_changed DESC LIMIT 1;
Insert the table name and don’t forget the semikolon at the end :wink:

You select row “state”, from table ,
then you sort by timestamp (descending),
then you take only 1 row (the first).
The flow trigger will give you the first field of first row (you only select that).
In addition you can use the “Result as text” token to print the complete response as JSON-String (if would select mot columns/rows.

1 Like