# \[APP\]\[PRO\] MySQL-Client **URL:** \ **Category:** Apps\ **Tags:** app, tools, homey-pro\ **Created:** [April 7, 2022, 7:18pm UTC](https://community.homey.app/t/app-pro-mysql-client/62459 "2022-04-07T19:18:16Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![RonnyW](https://sea1.discourse-cdn.com/flex025/user_avatar/community.homey.app/ronnyw/32/10879_2.png) [@RonnyW](https://community.homey.app/u/RonnyW)\ **Post date:** [April 7, 2022, 7:18pm UTC](https://community.homey.app/t/app-pro-mysql-client/62459/1 "2022-04-07T19:18:16Z")
[![small](https://us1.discourse-cdn.com/flex025/uploads/athom/original/3X/4/2/4221ab32eb0a8b863b09a39df2480f9554a1a0ca.png)](https://homey.app/de-de/app/de.ronnywinkler.mysql/MySQL/) **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. > **[MySQL](https://homey.app/de-de/app/de.ronnywinkler.mysql/MySQL/)** > > Verwende deine MySQL-Datenbank in Homey * * * 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](https://us1.discourse-cdn.com/flex025/uploads/athom/original/3X/6/d/6d36f536e736dfeab883c325fe045d49eb2a6793.png) ](https://paypal.me/winklerronny) ---
**Author:** ![RonnyW](https://sea1.discourse-cdn.com/flex025/user_avatar/community.homey.app/ronnyw/32/10879_2.png) [@RonnyW](https://community.homey.app/u/RonnyW)\ **Post date:** [April 7, 2022, 7:19pm UTC](https://community.homey.app/t/app-pro-mysql-client/62459/2 "2022-04-07T19:19:02Z")
**Examples:** - **SELECT** Select one value from your database in the THEN part of your flow: ![grafik](https://us1.discourse-cdn.com/flex025/uploads/athom/original/3X/c/1/c1eac31cc13ac3ca5ae6f98a6c9417d6d26187ba.png) 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](https://us1.discourse-cdn.com/flex025/uploads/athom/original/3X/a/4/a4a6a8414da6c56b6a40ff59e7fa5b0e37224f89.png) Use the value in your flow. Take a look at the local flow tags you can use: ![grafik](https://us1.discourse-cdn.com/flex025/uploads/athom/original/3X/9/3/93eda918c4d1bc8f95fd2efa6603ef1b5f87e25b.png) Use the first valus as number or text, write the complete JSON result to the timeline or what ever you need 🙂 * * * - **INSERT / UPDATE** Insert a line into your database. This way you can periodically log capabilities. Use a trigger to execute the query: ![grafik](https://us1.discourse-cdn.com/flex025/uploads/athom/original/3X/f/b/fbbaf3a906d99101a39e6ebc4c37299384fc4196.png) 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](https://us1.discourse-cdn.com/flex025/uploads/athom/original/3X/9/9/9936f9aa5d9db368d72dd6b16a0f78b1b0676f5f.png) If needed you can print your result in the timeline: ![grafik](https://us1.discourse-cdn.com/flex025/uploads/athom/original/3X/6/c/6c95885a76de0df2956544351ea64da3c781b50e.png) * * * - **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. ![grafik](https://us1.discourse-cdn.com/flex025/uploads/athom/original/3X/e/9/e9f62f4ca1fe74a728d7377710c07776f4a5d8b1.png) 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. ![grafik](https://us1.discourse-cdn.com/flex025/uploads/athom/original/3X/7/5/75d174467e1e6682e13581857ea490d18111e291.png) ---
**Author:** ![RonnyW](https://sea1.discourse-cdn.com/flex025/user_avatar/community.homey.app/ronnyw/32/10879_2.png) [@RonnyW](https://community.homey.app/u/RonnyW)\ **Post date:** [April 7, 2022, 7:19pm UTC](https://community.homey.app/t/app-pro-mysql-client/62459/3 "2022-04-07T19:19:14Z")
**FAQ:** reserved ---
**Author:** ![RonnyW](https://sea1.discourse-cdn.com/flex025/user_avatar/community.homey.app/ronnyw/32/10879_2.png) [@RonnyW](https://community.homey.app/u/RonnyW)\ **Post date:** [April 7, 2022, 7:19pm UTC](https://community.homey.app/t/app-pro-mysql-client/62459/4 "2022-04-07T19:19:51Z")
**Versions:** reserved ---
**Author:** ![Undertaker](https://sea1.discourse-cdn.com/flex025/user_avatar/community.homey.app/undertaker/32/53278_2.png) [@Undertaker](https://community.homey.app/u/Undertaker)\ **Post date:** [April 10, 2022, 3:28pm UTC](https://community.homey.app/t/app-pro-mysql-client/62459/5 "2022-04-10T15:28:21Z")
Hi Ronny, will this app also be able to query a MariaDB database ? ---
**Author:** ![RonnyW](https://sea1.discourse-cdn.com/flex025/user_avatar/community.homey.app/ronnyw/32/10879_2.png) [@RonnyW](https://community.homey.app/u/RonnyW)\ **Post date:** [April 10, 2022, 3:36pm UTC](https://community.homey.app/t/app-pro-mysql-client/62459/6 "2022-04-10T15:36:26Z")
Yes. Maria DB is compatible in common SQL syntax. I use this app with a MariaDB on a Qnap NAS. ---
**Author:** ![Undertaker](https://sea1.discourse-cdn.com/flex025/user_avatar/community.homey.app/undertaker/32/53278_2.png) [@Undertaker](https://community.homey.app/u/Undertaker)\ **Post date:** [April 10, 2022, 4:39pm UTC](https://community.homey.app/t/app-pro-mysql-client/62459/7 "2022-04-10T16:39:09Z")
👍👍 ![Screenshot_20220410-183637_Chrome](https://us1.discourse-cdn.com/flex025/uploads/athom/original/3X/9/f/9f3003b155d25dc4f1a586a8697a0a2355ab8848.jpeg) Great ! I have been looking for a way to use the HA database on Homey for a long time. ---
**Author:** ![Edgar](https://avatars.discourse-cdn.com/v4/letter/e/b487fb/32.png) [@Edgar](https://community.homey.app/u/Edgar)\ **Post date:** [April 10, 2022, 8:11pm UTC](https://community.homey.app/t/app-pro-mysql-client/62459/8 "2022-04-10T20:11:26Z")
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. ---
**Author:** ![RonnyW](https://sea1.discourse-cdn.com/flex025/user_avatar/community.homey.app/ronnyw/32/10879_2.png) [@RonnyW](https://community.homey.app/u/RonnyW)\ **Post date:** [April 10, 2022, 8:19pm UTC](https://community.homey.app/t/app-pro-mysql-client/62459/9 "2022-04-10T20:19:49Z")
> [@Edgar](#): > > I wish there was an universal sql client 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. ---
**Author:** ![robertklep](https://sea1.discourse-cdn.com/flex025/user_avatar/community.homey.app/robertklep/32/160628_2.png) [@robertklep](https://community.homey.app/u/robertklep)\ **Post date:** [April 11, 2022, 5:31am UTC](https://community.homey.app/t/app-pro-mysql-client/62459/10 "2022-04-11T05:31:47Z")
> [@RonnyW](#): > > If you read again you will see that it’s a universal client I think they meant a client that can connect to other databases besides MySQL/MariaDB. ---
**Author:** ![RonnyW](https://sea1.discourse-cdn.com/flex025/user_avatar/community.homey.app/ronnyw/32/10879_2.png) [@RonnyW](https://community.homey.app/u/RonnyW)\ **Post date:** [April 11, 2022, 5:40am UTC](https://community.homey.app/t/app-pro-mysql-client/62459/11 "2022-04-11T05:40:35Z")
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. ---
**Author:** ![robertklep](https://sea1.discourse-cdn.com/flex025/user_avatar/community.homey.app/robertklep/32/160628_2.png) [@robertklep](https://community.homey.app/u/robertklep)\ **Post date:** [April 11, 2022, 5:44am UTC](https://community.homey.app/t/app-pro-mysql-client/62459/12 "2022-04-11T05:44:20Z")
> [@RonnyW](#): > > Hm, could be. But why this comment “but then i saw you can only use it for certain tasks, like energy”? Yeah you’re right 😅 ---
**Author:** ![Edgar](https://avatars.discourse-cdn.com/v4/letter/e/b487fb/32.png) [@Edgar](https://community.homey.app/u/Edgar)\ **Post date:** [April 11, 2022, 7:44am UTC](https://community.homey.app/t/app-pro-mysql-client/62459/13 "2022-04-11T07:44:09Z")
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? ---
**Author:** ![RonnyW](https://sea1.discourse-cdn.com/flex025/user_avatar/community.homey.app/ronnyw/32/10879_2.png) [@RonnyW](https://community.homey.app/u/RonnyW)\ **Post date:** [April 11, 2022, 8:02am UTC](https://community.homey.app/t/app-pro-mysql-client/62459/14 "2022-04-11T08:02:32Z")
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. ---
**Author:** ![Edgar](https://avatars.discourse-cdn.com/v4/letter/e/b487fb/32.png) [@Edgar](https://community.homey.app/u/Edgar)\ **Post date:** [April 11, 2022, 9:13am UTC](https://community.homey.app/t/app-pro-mysql-client/62459/15 "2022-04-11T09:13:15Z")
oke cool… then it sounds like what i like 🙂 looking forward for it! thnx ---
**Author:** ![Undertaker](https://sea1.discourse-cdn.com/flex025/user_avatar/community.homey.app/undertaker/32/53278_2.png) [@Undertaker](https://community.homey.app/u/Undertaker)\ **Post date:** [April 11, 2022, 9:45am UTC](https://community.homey.app/t/app-pro-mysql-client/62459/16 "2022-04-11T09:45:45Z")
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. ![Screenshot_20220411-114451_RemoDB](https://us1.discourse-cdn.com/flex025/uploads/athom/original/3X/6/f/6f6b0a08a53fe8460f4277d8d207cf4754972cba.jpeg) ---
**Author:** ![RonnyW](https://sea1.discourse-cdn.com/flex025/user_avatar/community.homey.app/ronnyw/32/10879_2.png) [@RonnyW](https://community.homey.app/u/RonnyW)\ **Post date:** [April 12, 2022, 12:36pm UTC](https://community.homey.app/t/app-pro-mysql-client/62459/17 "2022-04-12T12:36:44Z")
🥳 The app is online now 😃 > **[MySQL](https://homey.app/de-de/app/de.ronnywinkler.mysql/MySQL/)** > > Verwende deine MySQL-Datenbank in Homey 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. ---
**Author:** ![Undertaker](https://sea1.discourse-cdn.com/flex025/user_avatar/community.homey.app/undertaker/32/53278_2.png) [@Undertaker](https://community.homey.app/u/Undertaker)\ **Post date:** [April 12, 2022, 3:23pm UTC](https://community.homey.app/t/app-pro-mysql-client/62459/18 "2022-04-12T15:23:19Z")
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 ? ![Screenshot_20220412-164818_RemoDB](https://us1.discourse-cdn.com/flex025/uploads/athom/original/3X/0/6/06c5b93b3f3a3abee005a77f70681290544fce2c.jpeg) [https://homey.app/f/scbnu0](https://homey.app/f/scbnu0) [https://homey.app/f/0-jnu0](https://homey.app/f/0-jnu0) ---
**Author:** ![robertklep](https://sea1.discourse-cdn.com/flex025/user_avatar/community.homey.app/robertklep/32/160628_2.png) [@robertklep](https://community.homey.app/u/robertklep)\ **Post date:** [April 12, 2022, 4:35pm UTC](https://community.homey.app/t/app-pro-mysql-client/62459/19 "2022-04-12T16:35:14Z")
Assuming you want to find the latest state value of the `sensor.power_usage` sensor, in plain SQL the query should be: ```auto SELECT state FROM states WHERE entity_id = "sensor.power_usage" ORDER BY last_changed DESC LIMIT 1; ``` ---
**Author:** ![RonnyW](https://sea1.discourse-cdn.com/flex025/user_avatar/community.homey.app/ronnyw/32/10879_2.png) [@RonnyW](https://community.homey.app/u/RonnyW)\ **Post date:** [April 12, 2022, 4:50pm UTC](https://community.homey.app/t/app-pro-mysql-client/62459/20 "2022-04-12T16:50:40Z")
Hi Uwe, are these table rows? What’s the table name? To get the last “state”: `SELECT state from 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. [Next page](https://community.homey.app/t/app-pro-mysql-client/62459.md?page=2)