Please, please, put the SQL sensor back to YAML config!

I love home assistant and all the wonderful people who work on it. Every update is a like a little birthday present, where delightful new features make my experience better.

But the decision to move the SQL sensor to GUI config in the latest update just doesn’t make sense.

I’ve seen the discussion about how the decision was made, like, TWO YEARS AGO man. And yes, I know, the ship has sailed, the horse has bolted; it is done.

But that doesn’t make it right. And it certainly doesn’t make it a good idea.

SQL is code and should be managed as code. By moving to GUI config, I lose version control and everything that goes with it (like seeing when and why I made a change). SQL queries can be long and complex and impossible to edit with any finesse in the GUI editor. I also lose the ability to structure my SQL sensors and to put the SQL code near other YAML code that they are relevant to.

I get that 95% of integrations should be managed in the GUI. It makes perfect sense, because most integration config is simple, static and unlikely to change often. Moreover, YAML can be confusing and people get it wrong. So for the 95% of integrations where it makes sense… do it.

But not the SQL integration. It. just. doesn’t. make. sense.

Please, please put it back?


EDIT: oh - and moving SQL integration config to the GUI also means I lose access to the use of secrets for the db_url. Massive pain in the bum to have to put the connect string in full in the GUI.


Hi again - I’m sorry to reply to my own post, but I’m surprised this post has provoked more of a response (but thanks to those who liked it). Maybe it would help if I were to make this a more general statement - I’m quite concerned about the general trend away from YAML config for integrations for all the reasons I list above. I get that Home Assistant needs to be made more accessible to users who are not so interested in tangling with YAML config and source control, but I don’t understand why these things must be mutually exclusive.

So a better question: when GUI config is made possible for an integration, why must YAML config be outlawed? Why can’t the two co-exist?

YAML and GUI config coexists for automations just fine. Why can’t the same approach be taken for integrations as well? Surely we can keep both camps happy without creating intractable conflicts?

Please? I’d love to hear from the HA devs if I’m missing something.

I’m with you 100%, I use vscode to manage my configuration, store it in GitHub and use shell scripts to replicate config objects across multiple devices and multiple houses. As you state version control is essential and changes like this while helping novices make it more difficult for power users.

As a result, I now also put the .storage folder into GitHub. This helps to be familiar with what is in there and to track changes.

Since the .storage files are just JSON with a little bit of scripting you should be able to replace the SQL statements in there. I do this to automate creating some repetitive Lovelace dashboards. I have not found (or looked very hard) for a shell accessible JSON replacer, so right now it’s a bit of brute forcing appending text.

1 Like

Thanks for the comments @PeteRage - I’m glad to hear I’m not alone.

In the past I’ve included .storage in my source control. I stopped doing it a while back, though, because I found that the level of “background noise” caused by HA updates and JSON reformatting that happens from time to time made it very difficult to track anything useful. Maybe I should start doing it again.

One further comment - I should confess to crossing the streams - I’ve also been commenting in the 2022.5 blog announcement thread. There is plenty of discussion there.

In my most recent comment I share an observation that perhaps the original intent of ADR-0010 has been lost. From what I can tell, it was originally intended to provide clarity for how to deal with devices / services that require complex external authentication using OAuth2 and the like. Fair enough, but I don’t think that’s relevant for something like the SQL integration.

So it looks like the dev fairies quietly put YAML config back for the SQL sensor. I completely stumbled over it after tearing my hair out trying to update a bunch of queries through the GUI. But looking at the docs, it would appear YAML config is an option (again).


1 Like