SQL Sensor

The SQL sensor platform enables you to use values from an SQL database supported by the sqlalchemy library, to populate a sensor state (and attributes). This can be used to present statistics about Home Assistant sensors if used with the recorder component database. It can also be used with an external data source.

To configure this sensor, you need to define the sensor connection variables and a list of queries to your configuration.yaml file. A sensor will be created for each query:

To enable it, add the following lines to your configuration.yaml:

# Example configuration.yaml entry to monitor hass database size in MySQL
sensor:
  - platform: sql
    db_url: mysql://user:password@localhost/hass
    queries:
      - name: HASS DB size
        query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="hass" GROUP BY table_schema;'
        column: 'value'
        unit_of_measurement: kB

Configuration Variables

db_url

(string)(Optional)The URL which points to your database. See supported engines.

Default value: Defaults to the recorder db_url.

queries

(map)(Required)List of your queries.

name: (string)(Required)The name of the sensor.
query: (string)(Required)An SQL QUERY string, should return 1 result at most.
column: (string)(Required)The field name to select.
unit_of_measurement: (string)(Optional)Defines the units of measurement of the sensor, if any.
value_template: (template)(Optional)Defines a template to extract a value from the payload.

Examples

In this section you find some real life examples of how to use this sensor.

Current state of an entity

This example shows the previously recorded state of sensor abc123.

SELECT * FROM states WHERE entity_id = 'sensor.abc123' ORDER BY id DESC LIMIT 2

Note that the SQL sensor state corresponds to the last row of the SQL resultset.

Previous state of an entity

This example only works with binary_sensors:

SELECT * FROM states WHERE entity_id='binary_sensor.xyz789' GROUP BY state ORDER BY last_changed DESC LIMIT 1

Database size in Postgres

- platform: sql
    db_url: postgresql://user:password@host/dbname
    queries:
    - name: db_size
      query: "SELECT (pg_database_size('dsmrreader')/1024/1024) as db_size;"
      column: "db_size"
      unit_of_measurement: MB