How to use SQL Sensor query with HA db running in memory?

Configuration
1

When I try to execute a query to get the HA database size using the SQL sensor against my SQLLite database that is running in memory, the query returns 0 (zero).

In the SQL sensor definition I use the same URL that I’m using in the Recorder. If I omit this query URL then the query is executed against the “home-assistant_v2.db” in the …/homeassistant directory and the same value is returned as when I execute the query when I connect to this database directly (using: sqlite3 home-assistant_v2.db), which suggests that the rest of the sensor configuration is fine.

Below: the Recorder database definition in configuration.yaml:

recorder:
  db_url: 'sqlite:///:memory:'

And the SQL sensor definition:

sensor:
  - platform: sql
    db_url: 'sqlite:///:memory:'
    queries: 
        query: "SELECT ROUND(page_count * page_size / 1024 / 1024, 1) as size FROM pragma_page_count(), pragma_page_size();"
        column: "size"
        unit_of_measurement: MB

I don’t get any error in the logs, so not sure what I’m doing wrong.
Any ideas how to get this to work?

2

I agree, this doesn’t seem to work as expected. I’d also love to know a solution - especially since so many people are starting to run this way with the rpi’s.

3

I suspect that, with this url, 2 distinct databases are used by the recorder and the sqlquery, so it’d make sense that the sql query returns 0 as the db is actually empty.

Looks like you can use urls like sqlite:///file:memdb1?mode=memory&cache=shared to have named, shareable, memory dbs (from In-Memory Databases)