Skip to content

Details about the HA DB #1202

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 2 commits into from
Oct 11, 2016
Merged

Details about the HA DB #1202

Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
f7149db
Add database documentation
fabaff Oct 10, 2016
812390a
Add titles
fabaff Oct 10, 2016
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
125 changes: 125 additions & 0 deletions source/_topics/database.markdown
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,125 @@
---
layout: page
title: "Home Assistant Database"
description: "Details about the database which Home Assistant is using."
date: 2016-10-10 10:00
sidebar: true
comments: false
sharing: true
footer: true
---

The default database that is used for Home Assistant is [SQLite](https://www.sqlite.org/) and is stored in your [configuration directory](/getting-started/configuration/), eg. `<path to config dir>/.homeassistant/home-assistant_v2.db`. You will need an installation of `sqlite3`, the command-line for SQLite database, or [DB Browser for SQLite](http://sqlitebrowser.org/) which provide an editor for executing SQL commands.
First load your database with `sqlite3`.

```bash
$ 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 better readable.

```bash
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.

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

### {% linkable_title Schema %}

Get all available tables from your current Home Assistant database.

```bash
sqlite> SELECT sql FROM sqlite_master;

-------------------------------------------------------------------------------------
CREATE TABLE events (
event_id INTEGER NOT NULL,
event_type VARCHAR(32),
event_data TEXT,
origin VARCHAR(32),
time_fired DATETIME,
created DATETIME,
PRIMARY KEY (event_id)
)
CREATE INDEX ix_events_event_type ON events (event_type)
CREATE TABLE recorder_runs (
run_id INTEGER NOT NULL,
start DATETIME,
"end" DATETIME,
closed_incorrect BOOLEAN,
created DATETIME,
PRIMARY KEY (run_id),
CHECK (closed_incorrect IN (0, 1))
)
CREATE TABLE states (
state_id INTEGER NOT NULL,
domain VARCHAR(64),
entity_id VARCHAR(64),
state VARCHAR(255),
attributes TEXT,
event_id INTEGER,
last_changed DATETIME,
last_updated DATETIME,
created DATETIME,
PRIMARY KEY (state_id),
FOREIGN KEY(event_id) REFERENCES events (event_id)
)
CREATE INDEX states__significant_changes ON states (domain, last_updated, entity_id)
CREATE INDEX states__state_changes ON states (last_changed, last_updated, entity_id)
CREATE TABLE sqlite_stat1(tbl,idx,stat)
```

To only show the details about the `states` table as we are using that one in the next examples.

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

### {% linkable_title Query %}

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

```bash
sqlite> .width 30, 10,
sqlite> SELECT entity_id, COUNT(*) as count FROM states GROUP BY 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
```

### {% linkable_title Delete %}

If you don't want to keep certain entities, you can delete them permanently.

```bash
sqlite> DELETE FROM states WHERE entity_id="sensor.cpu";
```

The `VACUUM` command cleans the your database.

```bash
sqlite> VACUUM;
```

For a more interactive way to work with the database or the create statistics, checkout our [Jupyther notebooks](http://nbviewer.jupyter.org/github/home-assistant/home-assistant-notebooks/blob/master/).