Home ▸ Documentation ▸ Backend ▸

Database

Home Assistant uses databases to store eventsEvery time something happens in Home Assistant, an event is fired. There are different types of events, such as state change events, when an action was triggered, or the time changed. All entities produce state change events. Every time a state changes, a state change event is produced. Events can be used to trigger automations or scripts. For example, you can trigger an automation when a light is turned on, then a speaker turns on in that room. Events can also be used to trigger actions in the frontend. For example, you can trigger an action when a button is pressed. [Learn more] and parameters for history and tracking. The default database used is SQLite.

The database file is stored in your configuration directory (e.g., <path to config dir>/home-assistant_v2.db); however, other databases can be used. If you prefer to run a database server (e.g., PostgreSQL), use the recorder integration.

To work with SQLite database manually from the command-line, you will need an installation of sqlite3. Alternatively DB Browser for SQLite provides a viewer for exploring the database data and an editor for executing SQL commands. First load your database with sqlite3:

$ sqlite3 home-assistant_v2.db
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite>

It helps to set some options to make the output more readable:

sqlite> .header on
sqlite> .mode column

You could also start sqlite3 and attach the database later. Not sure what database you are working with? Check it, especially if you are going to delete data.

sqlite> .databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /home/fab/.homeassistant/home-assistant_v2.db

Schema

Get all available tables from your current Home Assistant database:

sqlite> SELECT sql FROM sqlite_master;

-------------------------------------------------------------------------------------
CREATE TABLE event_data (
        data_id INTEGER NOT NULL,
        hash BIGINT,
        shared_data TEXT,
        PRIMARY KEY (data_id)
)

CREATE TABLE event_types (
        event_type_id INTEGER NOT NULL,
        event_type VARCHAR(64),
        PRIMARY KEY (event_type_id)
)

CREATE TABLE state_attributes (
        attributes_id INTEGER NOT NULL,
        hash BIGINT,
        shared_attrs TEXT,
        PRIMARY KEY (attributes_id)
)

CREATE TABLE states_meta (
        metadata_id INTEGER NOT NULL,
        entity_id VARCHAR(255),
        PRIMARY KEY (metadata_id)
)

CREATE TABLE statistics_meta (
        id INTEGER NOT NULL,
        statistic_id VARCHAR(255),
        source VARCHAR(32),
        unit_of_measurement VARCHAR(255),
        has_mean BOOLEAN,
        has_sum BOOLEAN,
        name VARCHAR(255),
        PRIMARY KEY (id)
)

CREATE TABLE recorder_runs (
        run_id INTEGER NOT NULL,
        start DATETIME NOT NULL,
        "end" DATETIME,
        closed_incorrect BOOLEAN NOT NULL,
        created DATETIME NOT NULL,
        PRIMARY KEY (run_id)
)

CREATE TABLE migration_changes (
        migration_id VARCHAR(255) NOT NULL,
        version SMALLINT NOT NULL,
        PRIMARY KEY (migration_id)
)



CREATE TABLE schema_changes (
        change_id INTEGER NOT NULL,
        schema_version INTEGER,
        changed DATETIME NOT NULL,
        PRIMARY KEY (change_id)
)

CREATE TABLE statistics_runs (
        run_id INTEGER NOT NULL,
        start DATETIME NOT NULL,
        PRIMARY KEY (run_id)
)

CREATE TABLE events (
        event_id INTEGER NOT NULL,
        event_type CHAR(0),
        event_data CHAR(0),
        origin CHAR(0),
        origin_idx SMALLINT,
        time_fired CHAR(0),
        time_fired_ts FLOAT,
        context_id CHAR(0),
        context_user_id CHAR(0),
        context_parent_id CHAR(0),
        data_id INTEGER,
        context_id_bin BLOB,
        context_user_id_bin BLOB,
        context_parent_id_bin BLOB,
        event_type_id INTEGER,
        PRIMARY KEY (event_id),
        FOREIGN KEY(data_id) REFERENCES event_data (data_id),
        FOREIGN KEY(event_type_id) REFERENCES event_types (event_type_id)
)

CREATE TABLE states (
        state_id INTEGER NOT NULL,
        entity_id CHAR(0),
        state VARCHAR(255),
        attributes CHAR(0),
        event_id SMALLINT,
        last_changed CHAR(0),
        last_changed_ts FLOAT,
        last_reported_ts FLOAT,
        last_updated CHAR(0),
        last_updated_ts FLOAT,
        old_state_id INTEGER,
        attributes_id INTEGER,
        context_id CHAR(0),
        context_user_id CHAR(0),
        context_parent_id CHAR(0),
        origin_idx SMALLINT,
        context_id_bin BLOB,
        context_user_id_bin BLOB,
        context_parent_id_bin BLOB,
        metadata_id INTEGER,
        PRIMARY KEY (state_id),
        FOREIGN KEY(old_state_id) REFERENCES states (state_id),
        FOREIGN KEY(attributes_id) REFERENCES state_attributes (attributes_id),
        FOREIGN KEY(metadata_id) REFERENCES states_meta (metadata_id)
)

CREATE TABLE statistics (
        id INTEGER NOT NULL,
        created CHAR(0),
        created_ts FLOAT,
        metadata_id INTEGER,
        start CHAR(0),
        start_ts FLOAT,
        mean FLOAT,
        min FLOAT,
        max FLOAT,
        last_reset CHAR(0),
        last_reset_ts FLOAT,
        state FLOAT,
        sum FLOAT,
        PRIMARY KEY (id),
        FOREIGN KEY(metadata_id) REFERENCES statistics_meta (id) ON DELETE CASCADE
)

CREATE TABLE statistics_short_term (
        id INTEGER NOT NULL,
        created CHAR(0),
        created_ts FLOAT,
        metadata_id INTEGER,
        start CHAR(0),
        start_ts FLOAT,
        mean FLOAT,
        min FLOAT,
        max FLOAT,
        last_reset CHAR(0),
        last_reset_ts FLOAT,
        state FLOAT,
        sum FLOAT,
        PRIMARY KEY (id),
        FOREIGN KEY(metadata_id) REFERENCES statistics_meta (id) ON DELETE CASCADE
)

CREATE TABLE sqlite_stat1(tbl,idx,stat)

CREATE INDEX ix_event_data_hash ON event_data (hash)

CREATE UNIQUE INDEX ix_event_types_event_type ON event_types (event_type)

CREATE INDEX ix_state_attributes_hash ON state_attributes (hash)

CREATE UNIQUE INDEX ix_states_meta_entity_id ON states_meta (entity_id)

CREATE UNIQUE INDEX ix_statistics_meta_statistic_id ON statistics_meta (statistic_id)

CREATE INDEX ix_recorder_runs_start_end ON recorder_runs (start, "end")

CREATE INDEX ix_statistics_runs_start ON statistics_runs (start)

CREATE INDEX ix_events_data_id ON events (data_id)

CREATE INDEX ix_events_event_type_id_time_fired_ts ON events (event_type_id, time_fired_ts)

CREATE INDEX ix_events_context_id_bin ON events (context_id_bin)

CREATE INDEX ix_events_time_fired_ts ON events (time_fired_ts)

CREATE INDEX ix_states_attributes_id ON states (attributes_id)

CREATE INDEX ix_states_metadata_id_last_updated_ts ON states (metadata_id, last_updated_ts)

CREATE INDEX ix_states_old_state_id ON states (old_state_id)

CREATE INDEX ix_states_context_id_bin ON states (context_id_bin)

CREATE INDEX ix_states_last_updated_ts ON states (last_updated_ts)

CREATE UNIQUE INDEX ix_statistics_statistic_id_start_ts ON statistics (metadata_id, start_ts)

CREATE INDEX ix_statistics_start_ts ON statistics (start_ts)

CREATE INDEX ix_statistics_short_term_start_ts ON statistics_short_term (start_ts)

CREATE UNIQUE INDEX ix_statistics_short_term_statistic_id_start_ts ON statistics_short_term (metadata_id, start_ts)

To only show the details about the states table (since we are using that one in the next examples):

sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'states';

Query

The identification of the available columns in the table is done and we are now able to create a query. Let’s list your Top 10 entities:

sqlite> .width 30, 10,
sqlite> SELECT states_meta.entity_id, COUNT(*) as count FROM states INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id GROUP BY states_meta.entity_id ORDER BY count DESC LIMIT 10;
entity_id                       count
------------------------------  ----------
sensor.cpu                      28874
sun.sun                         21238
sensor.time                     18415
sensor.new_york                 18393
cover.kitchen_cover             17811
switch.mystrom_switch           14101
sensor.internet_time            12963
sensor.solar_angle1             11397
sensor.solar_angle              10440
group.all_switches              8018

Delete

If you don’t want to keep certain entities, you can delete them permanently by using the actions provided by the recorder.

For a more interactive way of working with the database, check the Data Science Portal.

Help us improve our documentation

Suggest an edit to this page, or provide/view feedback for this page.

Edit Provide feedback View given feedback