Home Assistant Community Add-on: SQLite Web

WallyR · April 4, 2022, 1:07pm

No it is still there
Did you open the add-on store with the button in the lower right corner?

nikee · April 5, 2022, 6:20pm

This is what I see in the Add-on Store. I have Advanced Mode checked under my profile

boheme61 · April 5, 2022, 6:34pm

You need this:

Home Assistant Community Add-ons

Franck Nijhof [email protected]

*** ```

nikee · April 5, 2022, 6:37pm

Thanks. Now I have lots more add-on’s. Including SQLite Web

DirkStorck · July 27, 2022, 7:38am

Hi,

just have installed you plugin. Looks like it is hard to read the page in dark mode. Could that be adjusted a bit?

Thanks!

scOOb · October 7, 2022, 6:52am

I’m trying to add a database to query it, but sqlite crashes.
I’m just adding the path in the parameters as
/home/USER/smadata/SBFspot.db

in YAML it looks like
database: /home/robin/smadata/SBFspot.db

Restarted it, but HA tells it’ not running.
Next after starting it I only get a 502: Bad Gateway

Tips anyone ? Or simply impossible to add a local database ?
Thx-a-lot !

freefloris · October 10, 2022, 1:00am

Hello,
Same problem here.

[03:00:03] FATAL: The database file '/media/HA-USB/database/home-assistant_v2.db' is not found

Lakini · November 4, 2022, 1:11pm

Hey, quick question into the round before I break something: after looking into hardening my backup process a bit recently, I still stumble upon threads where people experience a corrupted sqlite file/db when trying to recover from a backup. As a minimal solution I’d like to manually export longterm stats via the sqlite web addon from time to time (query to csv/sql file), to at least have my energy data put aside somewhere.
What I’m a bit worried about: my HA instance is running on a Pi3, and I’ve had experiences before where the whole system crashed because e.g. compiling esphome stuff brought it to its knees.

Can I break something by executing a potentially long-running “select * …” query against the stats tables? Anyone good or bad experiences with this? Any feedback welcome and appreciated

Cheers!

WallyR · November 4, 2022, 1:16pm

If the host stays running, then you should be fine.
The issue mainly comes when it crash, which is often due to underrated or unstable powersupply.

e-raser · February 4, 2023, 4:20pm

grafik

Exactly what I was asking myself too just right now.

Unanswered in this thread.
Unanswered in 'Toggle helper tables' and Blank page · Issue #58 · hassio-addons/addon-sqlite-web · GitHub

So…

Is it “helpful”, for what?
Is it potentially harmful?
Working at all or for HA database a “frustration button” as nothing happens when clicking it?
Questions over questions.

Can someone please us.

MilesAheadToo · May 8, 2023, 9:33am

I’m not sure what is happening, but SQLite Web will not start. I get the following log when starting SQLite Web:

s6-rc: info: service s6rc-oneshot-runner: starting
s6-rc: info: service s6rc-oneshot-runner successfully started
s6-rc: info: service fix-attrs: starting
s6-rc: info: service fix-attrs successfully started
s6-rc: info: service legacy-cont-init: starting
s6-rc: info: service legacy-cont-init successfully started
s6-rc: info: service init-sqlite-web: starting
s6-rc: info: service init-nginx: starting
s6-rc: info: service init-sqlite-web successfully started
s6-rc: info: service sqlite-web: starting
s6-rc: info: service sqlite-web successfully started
s6-rc: info: service init-nginx successfully started
s6-rc: info: service nginx: starting
s6-rc: info: service nginx successfully started
s6-rc: info: service legacy-services: starting
s6-rc: info: service legacy-services successfully started
[10:21:34] INFO: Starting SQLite Web...
 * Serving Flask app 'sqlite_web.sqlite_web'
 * Debug mode: off
WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead.
 * Running on http://127.0.0.1:8080
Press CTRL+C to quit
[10:21:35] INFO: Starting NGINX...

But SQLIte Web has not started. This screenshot after I’ve clicked the Start button.

When I try to open the web UI I get an error massage that the SQLite Web addon has not been started.

What am I doing wrong?

MilesAheadToo · May 9, 2023, 11:10am

I found the issue.
The system running HA has to be rebooted.
In my case I pulled the power plug out of my x86 miniPC and restarted. SQLite_web worked perfectly.

erik7 · May 12, 2023, 10:28am

Can I also use this addon (SQLite web) for my MariaDB HA Database on my Synology NAS?

tom_l · May 25, 2023, 3:48pm

No. Use phpMyAdmin for MariaDB.

Mariusthvdb · June 4, 2023, 9:02am

as balloob posted I the current (2023.6) beta, on the topic of migrating MAriaDB to builtin SQLite:

Gist would be to let HA create an empty SQLite DB (which it does by default if it doesn't exist), then open SQLite browser add-on and import old data

before I’d even dare to try, I explored the docs on this add-on, but could not find info on doing just that: importing MAriaDB databases.

Would appreciate some guidance on the matter, before messing up my system

update

nevermind that, I managed to move the DB to SQLite using the other tool described here

aleco · October 19, 2023, 8:27am

I’m failing to run any queries from the SQLite Web interface, everything returns “400: Bad Request”, e.g. the queries listed here such as:

SELECT
  COUNT(*) AS cnt,
  COUNT(*) * 100 / (SELECT COUNT(*) FROM states) AS cnt_pct,
  states_meta.entity_id
FROM states
INNER JOIN states_meta ON states.metadata_id=states_meta.metadata_id
GROUP BY states_meta.entity_id
ORDER BY cnt DESC

The addon itself seems to be configured properly, there’s nothing in the addon log that looks like an error to me and the web console shows it found /config/home-assistant_v2.db with 13 tables, 20 indexes and 2GB size.

But the HA Core logs show:

Logger: homeassistant.components.http.security_filter
Source: components/http/security_filter.py:66
Integration: HTTP (documentation, issues)
First occurred: 10:02:23 (3 occurrences)
Last logged: 10:04:34

Filtered a request with unsafe byte query string: /query/?sql=SELECT%0D%0A++COUNT%28*%29+AS+cnt%2C%0D%0A++COUNT%28*%29+*+100+%2F+%28SELECT+COUNT%28*%29+FROM+states%29+AS+cnt_pct%2C%0D%0A++states_meta.entity_id%0D%0AFROM+states%0D%0AINNER+JOIN+states_meta+ON+states.metadata_id%3Dstates_meta.metadata_id%0D%0AGROUP+BY+states_meta.entity_id%0D%0AORDER+BY+cnt+DESC

I’m running HA 2023.10.3 and just installed the SQLite Web addon (so it’s the latest version).

Log:

s6-rc: info: service s6rc-oneshot-runner: starting
s6-rc: info: service s6rc-oneshot-runner successfully started
s6-rc: info: service base-addon-banner: starting

-----------------------------------------------------------
 Add-on: SQLite Web
 Explore your SQLite database
-----------------------------------------------------------
 Add-on version: 3.10.0
 You are running the latest version of this add-on.
 System: Home Assistant OS 11.0  (aarch64 / raspberrypi4-64)
 Home Assistant Core: 2023.10.3
 Home Assistant Supervisor: 2023.10.0
-----------------------------------------------------------
 Please, share the above information when looking for help
 or support in, e.g., GitHub, forums or the Discord chat.
-----------------------------------------------------------
s6-rc: info: service base-addon-banner successfully started
s6-rc: info: service fix-attrs: starting
s6-rc: info: service base-addon-log-level: starting
s6-rc: info: service fix-attrs successfully started
s6-rc: info: service base-addon-log-level successfully started
s6-rc: info: service legacy-cont-init: starting
s6-rc: info: service legacy-cont-init successfully started
s6-rc: info: service init-sqlite-web: starting
s6-rc: info: service init-nginx: starting
s6-rc: info: service init-sqlite-web successfully started
s6-rc: info: service sqlite-web: starting
s6-rc: info: service sqlite-web successfully started
s6-rc: info: service init-nginx successfully started
s6-rc: info: service nginx: starting
s6-rc: info: service nginx successfully started
s6-rc: info: service legacy-services: starting
[09:50:48] INFO: Starting SQLite Web...
s6-rc: info: service legacy-services successfully started
 * Serving Flask app 'sqlite_web.sqlite_web'
 * Debug mode: off
WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead.
 * Running on http://127.0.0.1:8080
Press CTRL+C to quit
[09:50:50] INFO: Starting NGINX...
127.0.0.1 - - [19/Oct/2023 09:50:54] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:51:00] "GET /event_data/ HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:51:22] "GET /states/ HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:51:27] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:51:31] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:52:02] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:52:14] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:52:37] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:52:47] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:54:28] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:54:38] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:59:34] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:59:36] "GET /states/ HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:59:37] "GET /event_data/ HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 09:59:38] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 10:02:19] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 10:02:33] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 10:04:31] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [19/Oct/2023 10:05:00] "GET / HTTP/1.1" 200 -

Thanks!

th.ch.mueller · October 30, 2023, 1:02pm

I can run simple queries like:

SELECT * FROM "states" WHERE  metadata_id = 98;

but all queries with a JOIN return “400: Bad Request”, eg.

SELECT *
FROM states
JOIN states_meta ON states.metadata_id = states_meta.metadata_id;

aleco · October 30, 2023, 2:52pm

Downgrading the addon to an older version helps, there’s a github issue where you can find an older version to download.

felafel · November 14, 2023, 3:54pm

Is there a way to open zigbee.db and other databases?

This seems very limited.

Thanks

P.S. just decided to download the file and read it on my PC.

droidgren · December 18, 2023, 6:35pm

How do I disable auto-update on this addon?