Need Help with Home Assistant SQL Sensors

Configuration
1

Hi everyone,

I’m trying to set up custom SQL sensors in Home Assistant to pull data from my MySQL/MariaDB database. I’ve tried configuring the configuration.yaml file, but the sensors aren’t showing up. I’ve confirmed the query works in DBeaver, but I’m stuck on the integration side.

Here’s the query I’m using:

sensor:
  - platform: sql
    db_url: sqlite:////homeassistant/energy_readings.db
    queries:
      - name: Energy Readings Total
        query: SELECT readingsTotal FROM energy_readings ORDER BY timestamp DESC LIMIT 1
        column: readingsTotal
        unit_of_measurement: kWh

Any advice on debugging or tips for getting the sensor to display would be appreciated!

2

If you are going to use YAML configuration, you need to use the current format. Use sql as the top-level key instead of sensor and follow the format show in the docs:

3

Thanks! Any idea how often the energy dashboard will pull the data?

Also I get the following error via sql:
SQL Query invalid
SELECT readingsTotal FROM energy_readings ORDER BY timestamp DESC LIMIT 1

Database URL
sqlite:////homeassistant/energy_readings.db