[Feature] SQLite support

[CritLoren]

Problem

Postgres is yet another container that needs to run at all times, using up resources.

Proposed solution

SQLite is a decent solution that I've seen many selfhosted software include, probably more than needed for 1-2 users. https://github.com/EzekielTheMad/healthtrack seems to use it too already.

Alternatives considered

No response

Affected surface

Self-hosting / Docker

Additional context

No response

Pre-flight checks

• I confirmed this is not a duplicate of an existing feature request.
• I'm proposing a feature, not asking a usage question (those go to Discussions or the docs).

[MBombeck]

Hi @CritLoren,

thank you for taking the time to write this up, and for all your other reports over the last few days. They have been genuinely helpful.

I completely understand the wish for one container less. SQLite is a great database, and for a lot of self-hosted tools it is exactly the right choice. For HealthLog I want to stay with PostgreSQL, though, and I would like to explain why instead of just saying no.

The data grows a lot. Many people connect several sources, for example Apple Health, Withings, Fitbit or Google Health, and import years of history. That can grow into millions of rows, and the insights and pattern discovery run large aggregations over all of it. PostgreSQL handles that comfortably.

Several things write at the same time. Background syncs, a large Apple Health import, reminders and the web app regularly write concurrently. SQLite allows only one writer at a time, so under that kind of load writes would start queueing behind each other.

The job queue lives in the database. Background work runs on pg-boss, which keeps its queue inside PostgreSQL. That is also what lets you split HealthLog into separate web and worker containers later on.

The code is written for PostgreSQL. HealthLog has a large number of hand-written SQL queries that use PostgreSQL features, and every release is tested against PostgreSQL 16. Supporting a second database properly would mean maintaining and testing two variants of all of that, and I would rather spend that time on features.

The good news for the "yet another container" part: you do not have to run the bundled one. If you already have a PostgreSQL server for other apps, HealthLog can use that instead. It needs no superuser and installs no extensions, just a role that owns its database:
https://docs.healthlog.dev/self-hosting/external-postgres/

Thanks again for the suggestion. Even though I am not planning to add SQLite, it really helps to hear what matters to people running HealthLog at home.