SQL
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
integration database. It can also be used with an external data source.
This integration can be configured using both config flow and by YAML.
Configuration
To add the SQL integration to your Home Assistant instance, use this My button:
If the above My button doesn’t work, you can also perform the following steps manually:
-
Browse to your Home Assistant instance.
-
In the bottom right corner, select the Add Integration button.
-
From the list, select SQL.
-
Follow the instructions on screen to complete the setup.
Configuration by YAML
To configure this sensor, 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
file (example by required fields):
# Example configuration.yaml
sql:
- name: Sun state
query: >
SELECT
states.state
FROM
states
LEFT JOIN state_attributes ON (
states.attributes_id = state_attributes.attributes_id
)
WHERE
metadata_id = (
SELECT
metadata_id
FROM
states_meta
where
entity_id = 'sun.sun'
)
ORDER BY
state_id DESC
LIMIT
1;
column: "state"
Configuration Variables
Integration.
The URL which points to your database. See supported engines.
Defaults to the recorder db_url
.
The name of the sensor.
Defines a template to extract a value from the payload.
Provide device class for this sensor.
Provide state class for this sensor.
Defines a template for the icon of the entity.
Defines a template for the entity picture of the entity.
Defines a template if the entity state is available or not.
Information
See supported engines for which you can connect with this integration.
The SQL integration will connect to the Home Assistant Recorder database if “Database URL” has not been specified.
There is no explicit configuration required for attributes. The integration will set all additional columns returned by the query as attributes.
Note that in all cases only the first row returned will be used.
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 the sensor sensor.temperature_in
.
sensor:
- platform: random
name: Temperature in
unit_of_measurement: "°C"
The query will look like this:
SELECT
states.state
FROM
states
WHERE
metadata_id = (
SELECT
metadata_id
FROM
states_meta
WHERE
entity_id = 'sensor.temperature_in'
)
ORDER BY
state_id DESC
LIMIT
1;
Use state
as column for value.
Previous state of an entity
Based on previous example with temperature, the query to get the former state is :
SELECT
states.state
FROM
states
WHERE
state_id = (
SELECT
states.old_state_id
FROM
states
WHERE
metadata_id = (
SELECT
metadata_id
FROM
states_meta
WHERE
entity_id = 'sensor.temperature_in'
)
AND old_state_id IS NOT NULL
ORDER BY
last_updated_ts DESC
LIMIT
1
);
Use state
as column for value.
State of an entity x time ago
If you want to extract the state of an entity from a day, hour, or minute ago, the query is:
SELECT
states.state
FROM
states
INNER JOIN states_meta ON
states.metadata_id = states_meta.metadata_id
WHERE
states_meta.entity_id = 'sensor.temperature_in'
AND last_updated_ts <= strftime('%s', 'now', '-1 day')
ORDER BY
last_updated_ts DESC
LIMIT
1;
Replace -1 day
with the target offset, for example, -1 hour
.
Use state
as column for value.
Keep in mind that, depending on the update frequency of your sensor and other factors, this may not be a 100% accurate reflection of the actual situation you are measuring. Since your database won’t necessarily have a value saved exactly 24 hours ago, use “>=” or “<=” to get one of the closest values.
Database size
Postgres
SELECT pg_database_size('dsmrreader')/1024/1024 as db_size;
Use db_size
as column for value.
Replace dsmrreader
with the correct name of your database.
MariaDB/MySQL
Change table_schema="homeassistant"
to the name that you use as the database name, to ensure that your sensor will work properly.
SELECT table_schema "database", Round(Sum(data_length + index_length) / POWER(1024,2), 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;
Use value
as column for value.
SQLite
If you are using the recorder
integration then you don’t need to specify the location of the database. For all other cases, add sqlite:////path/to/database.db
as Database URL.
SELECT ROUND(page_count * page_size / 1024 / 1024, 1) as size FROM pragma_page_count(), pragma_page_size();
Use size
as column for value.
MS SQL
Use the same Database URL as for the recorder
integration. Change DB_NAME
to the name that you use as the database name, to ensure that your sensor will work properly. Be sure username
has enough rights to access the sys tables.
Example Database URL: "mssql+pyodbc://username:password@SERVER_IP:1433/DB_NAME?charset=utf8&driver=FreeTDS"
“pyodbc” has special requirements which need to be pre-installed before installation, see the “pyodbc” wiki for installation instructions
SELECT TOP 1 SUM(m.size) * 8 / 1024 as size FROM sys.master_files m INNER JOIN sys.databases d ON d.database_id=m.database_id WHERE d.name='DB_NAME';
Use size
as column for value.