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.
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.
Great ! I have been looking for a way to use the HA database on Homey for a long time.
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.
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.
I think they meant a client that can connect to other databases besides MySQL/MariaDB.
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.
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.
oke cool… then it sounds like what i like
looking forward for it!
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.
The app is online now
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.
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;
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
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.