SQL Access¶
Home Assistant has an internal SQL database front-ended by the Recorder integration. It holds, history, activity, long term statistics and more. Underneath it can by Sqlite (most common), MariaDB, MySQL or PostgreSQL.
When in live mode, the ha-repl exposes an sql variable, which allows access to this database, via the Recorder interface. It also adds helpful virtual tables for common joins, and auto-complete on table and column names.
sql and its results are local objects. A query is sent to Home Assistant, the rows come back as one download, and everything after that - show(), slicing, dataframes, CSV export - works on that local copy, with whatever libraries you have installed locally. Nothing extra needs installing in Home Assistant.
Info
Data is serialized as Apache Arrow using the nanofeather library - this provides column-oriented data for good compression, zero-copy reuse into dataframes and good compatibility with popular data libraries like polars and pandas.
sql Object¶
| Operation | Description |
|---|---|
sql("<query>") |
Send a query to the database and wait for results |
sql("<query>", legacy=True) |
The same, with legacy columns in view |
sql("<query>", raw=True) |
The same, with every value as the database has it |
sql.max_rows |
Read/write property to set the default row limit for results in this session |
sql.table("<name>") |
Returns the Table object that describes the named table and its contents, or raises KeyError if there is no such table |
sql.table("<name>", legacy=True) |
The same, with the table's legacy columns included |
sql.tables |
Returns a list of Table objects that describe each table and its contents, with a VirtualTable for each virtual table |
In the shell, Tab completes inside the query: table names after from and join, a table's columns after its name or alias and a ., and elsewhere the columns of the tables named so far - see Completion.


For example this will show the size of the statistics table.

Result Objects¶
On the result object, show() will display data in tabular form at the command, using the rich library's table support.
The result will have its rows automatically limited, so it doesn't lock up the console - use max_rows to override this. The columns shown are the same ones a row of the result has, so show() and [0] always agree; use max_cols to cap how many, or columns to set the list of columns specifically.
A single row can be picked out by position, so [0] for the first row or [-1] for the last - see Row Objects. The results can also be sliced like a Python list, so [:10] for the first 10 rows or [-10:] for the last 10. Slices share the downloaded data rather than copying it. For anything more advanced - a step, filtering, sorting, joins - turn the result into a dataframe with to_polars(), which also uses the downloaded Arrow data directly. You can also call len() on the results to get the number of rows.
| Operation. | Description |
|---|---|
rowcount |
Size of result set, may not be size of table due to row limits |
truncated |
True if results were truncated due to row limits |
query |
The query this is the result of, as it was given to sql |
legacy |
Read/write flag, set to True to bring legacy columns into view |
table |
The Table object describing this table, or None if the columns don't identify a single table |
column_names |
List of the column names in the table, in order |
show() |
Dump the table data to the console |
project() |
Create a new result object with a limited set of columns based on this one |
sample() |
Randomly sample selected quantity of rows out of the result set |
export_csv() |
Write the results to a local CSV file, by default named after the table (result.csv if there isn't one) |
arrow() |
The result as an Arrow array, for Arrow-aware libraries, e.g. polars.DataFrame(r.arrow()) or pyarrow.table(r.arrow()) |
arrow_ipc() |
The result as Arrow IPC stream bytes, e.g. to save to a file |
to_json() |
The results as a JSON string of columns, rows, rowcount and truncated |
to_dicts() |
Extract a list of dict objects from the results |
to_pandas() |
Turn the results into a pandas dataframe, if pandas installed |
to_polars() |
Turn the results into a polars dataframe, if polars installed |
Column Values¶
A column of a Recorder table comes back as what it holds, not as how the database stores it. This goes by how Home Assistant declares the column in db_schema.
Declared in db_schema as |
Columns | Stored as | In a result |
|---|---|---|---|
TIMESTAMP_TYPE |
Those ending _ts, such as last_updated_ts, time_fired_ts, start_ts |
Seconds since the epoch, as a float | A datetime in UTC |
DATETIME_TYPE |
start, end and created of recorder_runs, start of statistics_runs, changed of schema_changes |
A date and time, as text in SQLite | A datetime in UTC |
CONTEXT_BINARY_TYPE |
context_id_bin, context_parent_id_bin |
16 bytes | The ULID as text, as context.id has it |
CONTEXT_BINARY_TYPE |
context_user_id_bin |
16 bytes | The user's id as text, as context.user_id has it |
Boolean |
has_sum, closed_incorrect |
0 or 1 in SQLite |
True or False |
The times are Arrow timestamps, so they are also datetimes in a polars or pandas dataframe, and ISO 8601 text in to_json().
Pass raw=True for the values as the database has them instead.
>>> sql("select entity_id, last_updated_ts, context_id_bin from state_history")[0]
>>> sql("select entity_id, last_updated_ts, context_id_bin from state_history", raw=True)[0]
- A column is recognised by its name in the result, so
max(last_updated_ts)is a plain number unless it is given the name back, as inmax(last_updated_ts) as last_updated_ts - This only changes what comes back. Inside the query the columns are still as stored, so a
whereon a_tscolumn compares it to seconds since the epoch stateis text instatesand a number instatistics, so it is left as it comes
Row Objects¶
Indexing a result with a row number gives a Row object. It reads like a list of the row's values, in column order, and can also be indexed by column name.
| Operation | Description |
|---|---|
values |
List of the values in the row, in column order |
column_names |
List of the column names, in the same order |
table |
The Table object of the result the row came from, or None if it had none |
to_dict() |
The row as a dict of column name to value |
>>> sql("select count(*) from events")[0][0]
>>> row = sql("select * from states_meta")[0]
>>> row["entity_id"]
>>> {col: sql(f"select count(distinct {col}) from events")[0][0] for col in sql.table("events").column_names}
Table Objects¶
A Table object describes one table, using Home Assistant's own definition of it in homeassistant.components.recorder.db_schema.
| Operation | Description |
|---|---|
name |
The table's name in the database, e.g. states |
class_name |
The name of the class the Recorder uses for this table, e.g. States, to look up in db_schema. Empty for a virtual table |
description |
That class's docstring, e.g. State change history. |
columns |
List of Column objects, each with a name, type and legacy flag |
column_names |
List of the column names, in order |
virtual |
True if this is a virtual table rather than a real table in the database |
Both leave out the legacy columns, unless the table came from sql.table("<name>", legacy=True).
Virtual Tables¶
A virtual table is a ready-made join, to query as if it were a table. It saves writing out a join that almost every query on that table needs, because the Recorder keeps the name of a thing - an entity id, an event type, a statistic id - in a different table from its rows.
Each virtual table has that name as its first column, then the columns of its table that are in use, without the legacy columns or the key the two tables are joined on.
| Virtual table | What it holds |
|---|---|
state_history |
One row per recorded state, with its entity_id: states joined to states_meta |
event_history |
One row per recorded event, with its event_type: events joined to event_types |
statistics_history |
One row an hour per long term statistic, with its statistic_id: statistics joined to statistics_meta |
statistics_short_term_history |
One row every 5 minutes per statistic, with its statistic_id: statistics_short_term joined to statistics_meta |
>>> sql("select * from state_history where entity_id = 'sensor.outside_temperature'").show()
>>> sql("select entity_id, count(*) from state_history group by entity_id").show()
>>> sql("select event_type, count(*) from event_history group by event_type").show()
>>> sql("select start_ts, mean from statistics_history where statistic_id = 'sensor.outside_temperature'").show()
>>> sql.table("state_history").column_names
A virtual table can be used wherever a real one can in a query - filtered, grouped, joined to other tables, real or virtual, or to itself - and the database does all of that work, as it would for the join written out.
sql.tables lists them among the real tables. Each is a VirtualTable object, which shows as VirtualTable(...) rather than Table(...), so the two can't be mistaken for each other. It has everything a Table has, with virtual set to True, an empty class_name, and one thing more:
| Operation | Description |
|---|---|
definition |
The query the virtual table stands for |
Info
Nothing is created in the Home Assistant database. When a query mentions a virtual table by name, its definition is added to the query as a common table expression (a with clause) before it is run. If the query has a with clause of its own, the definition joins that one.
Legacy Columns¶
Some Recorder tables still have columns that Home Assistant no longer writes to, because the data moved elsewhere - entity_id in states, for example, is now in states_meta. Home Assistant marks these in db_schema as UNUSED_LEGACY_COLUMN, and they are empty for anything recorded recently.
They are kept out of view by default, both in a Table and in a query's result. A query still downloads them, but the result leaves them out of everything it does - show(), rows, column_names, dataframes, exports - so select * from states shows only the columns in use.
A legacy column is in view when:
- the query names it
- the query is made with
legacy=True legacyis set toTrueon the result afterwards, which needs no new query
>>> r = sql("select * from states") # columns in use
>>> r.legacy = True # now every column
>>> sql("select * from states", legacy=True) # every column from the start
>>> sql("select state_id, entity_id from states") # named, so in view
>>> sql.table("states").column_names # columns in use
>>> sql.table("states", legacy=True).column_names # every column
A query is checked by its words rather than parsed, so a legacy column mentioned anywhere in it, such as in a where, is in view.
Dataframe Portability¶
Since internally pyarrow is used for moving and holding data, and the arrow() method returns a pycapsule compliant interface, the data can be used in other tools that accept Arrow.
In this example the dataframe-neutral narwhals library is used to construct a dataframe, which can then have implementation neutral manipulation applied, or re-exported to another dataframe type.
import narwhals as nw
import polars as pl
>>> df=nw.from_arrow(sql('select * from events').arrow(),backend=pl)
Polars 2
arrow() returns a single Arrow struct array, one field per column. Since polars 2.0, pl.from_arrow() turns a struct array into a struct Series rather than a DataFrame, so go via pl.DataFrame(r.arrow()) (or r.to_polars()) instead. narwhals' from_arrow is unaffected, and still gives a dataframe with one column per field.
More Help¶
Use help(sql), or call help(..) on a result, row or table object, to get reminded of the API. It gives a short summary of the methods, properties and attributes; add full=True for Python's own full help page.