This is what I see in the Add-on Store. I have Advanced Mode checked under my profile
Thanks. Now I have lots more add-on’s. Including SQLite Web
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!
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 !
Hello,
Same problem here.
[03:00:03] FATAL: The database file '/media/HA-USB/database/home-assistant_v2.db' is not found
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!
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.
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.
1 Like
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?
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.
Can I also use this addon (SQLite web) for my MariaDB HA Database on my Synology NAS?
No. Use phpMyAdmin for MariaDB.
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
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!
2 Likes
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;
1 Like
Downgrading the addon to an older version helps, there’s a github issue where you can find an older version to download.
1 Like
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.
How do I disable auto-update on this addon?
