There are several ways to access the SQLite database of Home Assistant directly. The easiest way is to use SQLite Web.

Why?

Direct access to the Home Assistant database enables detailed analyses and queries of the stored data. In addition to creating your own sensors based on special queries, the values of certain sensors can be added, deleted or changed. As an example, I was able to import historical values from an old Influx database directly via the database into the Home Assistant long-term statistics and display them as Table.

Variant 1: SQLite Web - Simple and for all installation variants

Depending on the installation variant, the SQLite Web add-on can simply be installed and used when using HAOS :

Without an add-on store, SQLite Web can be started directly via Docker :

Start SQLite Web in Docker

When using without HAOS with Docker setup, SQLite can be started from the HA folder with the following command:

docker run -it --rm -p "9999:8080" -v .:/data -e SQLITE_DATABASE="home-assistant_v2.db" coleifer/sqlite-web

SQLite Web is then simply called up via http://localost:9999 or, if Home Assistant is running on another PC / server, with: http://ServerIP:9999

Information about my Docker setup, see : Home Assistant: Docker commissioning + SSL Internet access

If you use Docker as the installation variant, you can send SQL queries directly in the Home Assistant container:

Variant 2: Alternatively: direct call via the existing Docker container

The SQLite database can be read and edited via Linux board means using the sqlite command.

docker exec -it home-assistant /bin/bash

In the Docker container, sqlite can be installed and the database connected using the following commands:

bash-5.1# apk add sqlite
bash-5.1# sqlite3 /config/home-assistant_v2.db

The connection to the database allows you to execute any database queries,see also: Home Assistant SQlite - Change statistical data

Variant 3: VSCode and SQLite3 Editor

Direct access to the host file system via VSCode and the VSCode extension SQLite Editor is somewhat more convenient:

For editing, I did not use the studio code server in Home-Assistant, but installed VSCode locally and established a remote connection to the host - on which the HA Docker container is running. For information on VSCode, see:"The 4 best tools for comparing text files" and"PowerShell editors in comparison: ISE, Visual Studio Code"

With the VSCode extension SQLite3 Editor, the database files can be easily opened in the editor:

The individual tables of the database can be selected and displayed via dropdown:

In addition, a custom SQL query can be used for the query.

The queries are executed by pressing Shift + ENTER: