Backend DB To use PostgreSQL instead of SQLite #18

New Issue

rays · 2026-09-16T07:32:43-07:00

rays commented

2026-09-16 07:32:43 -07:00

Currently everything is in either a toml config or a single sqlite database. Move everything into a PostgreSQL database instead.

rays added the enhancement label 2026-09-17 13:28:13 -07:00

rays changed title from ~~DB Migration~~ to Backend DB To use PostgreSQL instead of SQLite

2026-09-17 13:40:28 -07:00

rays commented

2026-09-18 11:00:47 -07:00

Plan

Three phases, each deployed and checked before the next.

Today: one SQLite file (290 MB; 7 tables, about 115,000 rows, mostly item text) behind one global lock; 70 queries in db.rs, called from about 140 places in main.rs and web.rs, all from async code. config.toml holds both the start-up settings and data the app edits at runtime: 130 feeds and the server-wide settings.

Decisions

ORM: SeaORM 2 with sea-orm-migration. Async, and one set of code runs on SQLite and Postgres, so porting the code and switching the database happen separately.
Postgres: a dedicated container in the content project, not the shared Immich server. ipx gets its own login role and database; the test database lives on the same container.
Start-up settings stay in config.toml: bind address, token, trusted proxies, torrent, paths. They decide who gets in and are needed before any database connection. The feed catalogue and the server-wide settings move into the database.

Phase 1: port to SeaORM, still on SQLite

Entities for the seven tables; the schema moves into Rust migrations, the first matching today's exactly, so the existing state.db opens as it is.
db.rs keeps its functions but runs them through the ORM; the call sites gain .await. The item list's dynamic sorts, its size and type subqueries, and the duplicate-enclosure merge stay hand-written SQL, written to run on both databases.
Unit tests keep their in-memory SQLite. Deploy and let it run; rollback is the previous image, same file.

Phase 2: switch to Postgres

IPX_DATABASE_URL picks sqlite://… or postgres://….
ipx import copies SQLite into an empty Postgres database through the same entities, then resets the id counters and checks the row counts.
The tests also run against a Postgres test database.
Cut over: tag the current image, stop ipx, copy state.db and config.toml aside, import, deploy, check counts and a scan. Rollback: point the URL back at the untouched file.

Phase 3: feeds and server settings out of `config.toml`

The catalogue's fields become columns of feeds (OPML children become ordinary rows); the server settings get a one-row settings table.
Web UI and CLI edits write to the database and refresh the daemon; a CLI run outside it asks it to reload over the control socket.
ipx import-config loads the current file. Update the docs.

Risks

A pool allows concurrency the global lock never did: re-check the two transactions and the claim-the-next-download path.
Feed passwords move from a file to the database, still in plain text as now; password_env stays available.
The dedicated container needs its own backups (pg_dump), and ipx has to reconnect when it restarts.
SeaORM adds noticeably to build time.

## Plan Three phases, each deployed and checked before the next. **Today:** one SQLite file (290 MB; 7 tables, about 115,000 rows, mostly item text) behind one global lock; 70 queries in `db.rs`, called from about 140 places in `main.rs` and `web.rs`, all from async code. `config.toml` holds both the start-up settings and data the app edits at runtime: 130 feeds and the server-wide settings. **Decisions** - **ORM:** SeaORM 2 with `sea-orm-migration`. Async, and one set of code runs on SQLite and Postgres, so porting the code and switching the database happen separately. - **Postgres:** a dedicated container in the `content` project, not the shared Immich server. ipx gets its own login role and database; the test database lives on the same container. - **Start-up settings stay in `config.toml`:** bind address, token, trusted proxies, torrent, paths. They decide who gets in and are needed before any database connection. The feed catalogue and the server-wide settings move into the database. ### Phase 1: port to SeaORM, still on SQLite - Entities for the seven tables; the schema moves into Rust migrations, the first matching today's exactly, so the existing `state.db` opens as it is. - `db.rs` keeps its functions but runs them through the ORM; the call sites gain `.await`. The item list's dynamic sorts, its size and type subqueries, and the duplicate-enclosure merge stay hand-written SQL, written to run on both databases. - Unit tests keep their in-memory SQLite. Deploy and let it run; rollback is the previous image, same file. ### Phase 2: switch to Postgres - `IPX_DATABASE_URL` picks `sqlite://…` or `postgres://…`. - `ipx import` copies SQLite into an empty Postgres database through the same entities, then resets the id counters and checks the row counts. - The tests also run against a Postgres test database. - Cut over: tag the current image, stop ipx, copy `state.db` and `config.toml` aside, import, deploy, check counts and a scan. Rollback: point the URL back at the untouched file. ### Phase 3: feeds and server settings out of `config.toml` - The catalogue's fields become columns of `feeds` (OPML children become ordinary rows); the server settings get a one-row `settings` table. - Web UI and CLI edits write to the database and refresh the daemon; a CLI run outside it asks it to reload over the control socket. - `ipx import-config` loads the current file. Update the docs. ### Risks - A pool allows concurrency the global lock never did: re-check the two transactions and the claim-the-next-download path. - Feed passwords move from a file to the database, still in plain text as now; `password_env` stays available. - The dedicated container needs its own backups (`pg_dump`), and ipx has to reconnect when it restarts. - SeaORM adds noticeably to build time.

rays referenced this issue from a commit

2026-09-18 12:11:26 -07:00

SeaORM beside rusqlite: entities, schema sync, a second connection

rays commented

2026-09-18 13:28:40 -07:00

Progress update

Done

Phase 1: SeaORM, still on SQLite. On the seaorm branch (pushed, not merged or deployed).

src/entity.rs: the seven tables as SeaORM entities, now the schema. On open, ipx creates whatever table or index is missing (CREATE ... IF NOT EXISTS) and never alters or drops anything.
All 70 database functions moved to SeaORM and made async. Joins, sums and upserts are hand-written SQL that runs on both databases. rusqlite, the SQL schema and migrate() are gone.
Checked on copies of production against the old code on the same data: a forced scan of all 162 real feeds, the item lists, every filter's count, and the reaper's 2,195 delete candidates (identical, same order).
Bug found and fixed: SeaORM's experimental schema sync drops unique indexes it doesn't know about, despite its docs. It dropped users_name_lower on every open, so every ipx command took a write lock. Under load ipx status (the healthcheck) then failed 7 times in 15; the old code failed none. Replaced with create-if-missing: 15 in 15 again.

Phase 2: Postgres. Same branch, not deployed.

On the new postgres:18 server (port 5433): an ipodderx login role owning ipodderx (empty, for the real data) and ipodderx_test. Connection strings are in /src/.env as IPX_DATABASE_URL and IPX_TEST_DATABASE_URL; the app never uses the superuser.
IPX_DATABASE_URL picks the database: Postgres when set, the SQLite file otherwise. Passwords are kept out of error messages.
All 79 unit tests pass on both databases. With IPX_TEST_DATABASE_URL set, each test gets its own Postgres schema.
ipx copy-db <state.db> copies SQLite into an empty Postgres database in one transaction, then resets the id counters. A production copy went across in 14 seconds, every row count and column fingerprint identical (text lengths, timestamps, sizes, ids, every yes/no flag).
The app on Postgres against the same data: identical feed list and every count identical.
Second bug found and fixed: SQLite sorts a missing value (an item with no file) as the smallest and Postgres as the largest, so "largest first" on Postgres opened with every item that has no file. The sorts now say NULLS FIRST/NULLS LAST, matching SQLite; a new test fails on Postgres without it.

Worth knowing

Title and feed-name sorts differ on Postgres: it sorts by the en_US locale ("émile" next to "e"), where SQLite used byte order ("émile" after "zebra"). An improvement, but the browser test that asserts byte order would fail on Postgres; the browser suite still runs on SQLite.
The postgres container has restart policy no: after a reboot it won't come back, and ipx won't start without it. Setting restart: unless-stopped in the databases project would fix that.
Backups: the new server needs its own pg_dump of ipodderx.
One loose end: two forced scans run minutes apart on copies of production left 210 and 222 feeds (old and new code). Run at the same moment they matched exactly (312 each), so it's most likely OPML contents changing between fetches. I'll confirm before cutover.

Confirm the loose end above.
Merge seaorm to main.
Cut over:
- tag the current image as the rollback;
- stop ipx and copy state.db aside;
- ipx copy-db into ipodderx;
- add IPX_DATABASE_URL to the ipodderx service in the content compose file;
- deploy, then check row counts, a scan and the healthcheck.
Rollback: remove the variable and redeploy the old image; the SQLite file is untouched.
Phase 3 later: the feed catalogue and server settings out of config.toml into the database.

## Progress update ### Done **Phase 1: SeaORM, still on SQLite.** On the `seaorm` branch (pushed, not merged or deployed). - `src/entity.rs`: the seven tables as SeaORM entities, now the schema. On open, ipx creates whatever table or index is missing (`CREATE ... IF NOT EXISTS`) and never alters or drops anything. - All 70 database functions moved to SeaORM and made async. Joins, sums and upserts are hand-written SQL that runs on both databases. `rusqlite`, the SQL schema and `migrate()` are gone. - Checked on copies of production against the old code on the same data: a forced scan of all 162 real feeds, the item lists, every filter's count, and the reaper's 2,195 delete candidates (identical, same order). - Bug found and fixed: SeaORM's experimental schema sync drops unique indexes it doesn't know about, despite its docs. It dropped `users_name_lower` on every open, so every `ipx` command took a write lock. Under load `ipx status` (the healthcheck) then failed 7 times in 15; the old code failed none. Replaced with create-if-missing: 15 in 15 again. **Phase 2: Postgres.** Same branch, not deployed. - On the new `postgres:18` server (port 5433): an `ipodderx` login role owning `ipodderx` (empty, for the real data) and `ipodderx_test`. Connection strings are in `/src/.env` as `IPX_DATABASE_URL` and `IPX_TEST_DATABASE_URL`; the app never uses the superuser. - `IPX_DATABASE_URL` picks the database: Postgres when set, the SQLite file otherwise. Passwords are kept out of error messages. - All 79 unit tests pass on **both** databases. With `IPX_TEST_DATABASE_URL` set, each test gets its own Postgres schema. - `ipx copy-db <state.db>` copies SQLite into an empty Postgres database in one transaction, then resets the id counters. A production copy went across in 14 seconds, every row count and column fingerprint identical (text lengths, timestamps, sizes, ids, every yes/no flag). - The app on Postgres against the same data: identical feed list and every count identical. - Second bug found and fixed: SQLite sorts a missing value (an item with no file) as the smallest and Postgres as the largest, so "largest first" on Postgres opened with every item that has no file. The sorts now say `NULLS FIRST`/`NULLS LAST`, matching SQLite; a new test fails on Postgres without it. ### Worth knowing - **Title and feed-name sorts differ on Postgres**: it sorts by the `en_US` locale ("émile" next to "e"), where SQLite used byte order ("émile" after "zebra"). An improvement, but the browser test that asserts byte order would fail on Postgres; the browser suite still runs on SQLite. - **The `postgres` container has restart policy `no`**: after a reboot it won't come back, and ipx won't start without it. Setting `restart: unless-stopped` in the `databases` project would fix that. - **Backups**: the new server needs its own `pg_dump` of `ipodderx`. - One loose end: two forced scans run minutes apart on copies of production left 210 and 222 feeds (old and new code). Run at the same moment they matched exactly (312 each), so it's most likely OPML contents changing between fetches. I'll confirm before cutover. ### Next 1. Confirm the loose end above. 2. Merge `seaorm` to `main`. 3. Cut over: - tag the current image as the rollback; - stop ipx and copy `state.db` aside; - `ipx copy-db` into `ipodderx`; - add `IPX_DATABASE_URL` to the ipodderx service in the `content` compose file; - deploy, then check row counts, a scan and the healthcheck. Rollback: remove the variable and redeploy the old image; the SQLite file is untouched. 4. Phase 3 later: the feed catalogue and server settings out of `config.toml` into the database.

rays commented

2026-09-18 13:37:25 -07:00

Loose end settled: the 210-versus-222 feed count was my test, not the code. At start-up ipx retires the feeds of an OPML that has left config.toml: kept if they have downloaded files, dropped if not. On my test machine none of production's files exist, so the reaper marks them missing and the next start-up drops those feeds. My runs were cut off by timeouts at different points in that clean-up. Run to completion on fresh copies, the old and new code give the same result, identical row for row: 193 feeds, the same 119 davewiner feeds retired. In production the files exist, so nothing changes there.

It did show old removed feeds still in the database, which the copy would carry into Postgres unless cleared first:

119 feeds from the old davewiner OPML, kept as orphans for their downloads: no subscribers, 31,153 items, 1,610 files (86 GB), 4,499 read-state rows, nothing pinned.
31 other leftovers in neither the config nor any OPML: no subscribers, 5,059 items, 5 files (daily-meditation-podcast, 0.1 GB).
Waiting on a decision about whether to clear them before the copy.

Loose end settled: the 210-versus-222 feed count was my test, not the code. At start-up ipx retires the feeds of an OPML that has left config.toml: kept if they have downloaded files, dropped if not. On my test machine none of production's files exist, so the reaper marks them missing and the next start-up drops those feeds. My runs were cut off by timeouts at different points in that clean-up. Run to completion on fresh copies, the old and new code give the same result, identical row for row: 193 feeds, the same 119 davewiner feeds retired. In production the files exist, so nothing changes there. It did show old removed feeds still in the database, which the copy would carry into Postgres unless cleared first: - 119 feeds from the old davewiner OPML, kept as orphans for their downloads: no subscribers, 31,153 items, 1,610 files (86 GB), 4,499 read-state rows, nothing pinned. - 31 other leftovers in neither the config nor any OPML: no subscribers, 5,059 items, 5 files (daily-meditation-podcast, 0.1 GB). Waiting on a decision about whether to clear them before the copy.

rays referenced this issue from a commit

2026-09-18 14:42:28 -07:00

Merge seaorm: the database through SeaORM, on SQLite or Postgres (#18)

rays commented

2026-09-18 14:45:53 -07:00

Phase 2 done: production runs on Postgres as of 2026-09-18 21:44 (Toronto), merged to main in c8df148.

Clean-up first: removed the 149 unused feeds and their 1,615 files (86 GB) with the reviewed script in /mnt/user/ipodderx/cleanup-20260918/. The database backup from just before is state.db.pre-cleanup-20260918.
Tests: 79 unit tests on SQLite and on Postgres, and the 52 browser tests, all passing on the final code.
Cutover:
- stopped ipx, backed up state.db to state.db.pre-postgres-20260918, and ran ipx copy-db with the new image (5 s);
- every table and every column fingerprint identical between SQLite and Postgres;
- ipx started on Postgres and is healthy. ipx status answers from it (162 feeds, 583 downloaded), a forced scan wrote to it, the web UI reads from it, and state.db has not been written since the copy.
Configuration:
- the ipodderx login has a new password, kept in /mnt/fast/appdata/ipodderx/database.env (root-only) and passed in with env_file in the content compose file (backup: compose.yaml.bak-pre-postgres-20260918);
- the POSTGRES_* variables added to that service are not read by ipx and can go.
Rollback: remove the env_file lines and redeploy 192.168.1.130:5000/ipodderx:pre-postgres. state.db is untouched.

Still to do: the postgres container (project databases) has restart policy no, so it won't come back after a reboot, and ipx won't start without it; it also needs a pg_dump backup. Phase 3 (feeds and server settings out of config.toml) keeps this issue open.

Phase 2 done: production runs on Postgres as of 2026-09-18 21:44 (Toronto), merged to main in c8df148. - **Clean-up first:** removed the 149 unused feeds and their 1,615 files (86 GB) with the reviewed script in /mnt/user/ipodderx/cleanup-20260918/. The database backup from just before is state.db.pre-cleanup-20260918. - **Tests:** 79 unit tests on SQLite and on Postgres, and the 52 browser tests, all passing on the final code. - **Cutover:** - stopped ipx, backed up state.db to state.db.pre-postgres-20260918, and ran `ipx copy-db` with the new image (5 s); - every table and every column fingerprint identical between SQLite and Postgres; - ipx started on Postgres and is healthy. `ipx status` answers from it (162 feeds, 583 downloaded), a forced scan wrote to it, the web UI reads from it, and state.db has not been written since the copy. - **Configuration:** - the `ipodderx` login has a new password, kept in /mnt/fast/appdata/ipodderx/database.env (root-only) and passed in with `env_file` in the content compose file (backup: compose.yaml.bak-pre-postgres-20260918); - the POSTGRES_* variables added to that service are not read by ipx and can go. - **Rollback:** remove the env_file lines and redeploy `192.168.1.130:5000/ipodderx:pre-postgres`. state.db is untouched. Still to do: the `postgres` container (project databases) has restart policy `no`, so it won't come back after a reboot, and ipx won't start without it; it also needs a pg_dump backup. Phase 3 (feeds and server settings out of config.toml) keeps this issue open.

rays referenced this issue from a commit

2026-09-18 15:29:17 -07:00

Keep the feed catalogue and server settings in the database

rays referenced this issue from a commit

2026-09-18 15:29:17 -07:00

Merge config-db: the feed catalogue and server settings in the database (#18)

rays commented

2026-09-18 15:31:03 -07:00

Phase 3 done, deployed (8849ba6); this finishes the issue.

The feed catalogue and the server settings the admin page edits (schedule, disk quota, age limit, max new downloads per scan, media types) are now in Postgres, in two new tables, catalogue and settings.
config.toml keeps only what ipx needs before it reaches the database, or what decides who gets in: paths, [torrent] and [web].
Every change the web UI or ipx add/rm/import makes goes to the database.
On first start production imported all 130 feeds and trimmed config.toml; the original is kept as config.toml.pre-database.
The feed list, settings and directory served afterwards are identical to a snapshot taken just before the deploy.
Feeds written into config.toml from now on are ignored, with a warning in the log.

Found along the way: Postgres sends a notice for every CREATE ... IF NOT EXISTS on something existing (11 on each open). sqlx logs them, and tracing-subscriber's per-layer filters then silently dropped the next line ipx logged. ipx's connections now ask for warnings and above only.

Before the deploy: a pg_dump through db-backup, and config.toml.bak-pre-phase3-20260918.

Phase 3 done, deployed (8849ba6); this finishes the issue. - The feed catalogue and the server settings the admin page edits (schedule, disk quota, age limit, max new downloads per scan, media types) are now in Postgres, in two new tables, `catalogue` and `settings`. - config.toml keeps only what ipx needs before it reaches the database, or what decides who gets in: paths, [torrent] and [web]. - Every change the web UI or `ipx add`/`rm`/`import` makes goes to the database. - On first start production imported all 130 feeds and trimmed config.toml; the original is kept as config.toml.pre-database. - The feed list, settings and directory served afterwards are identical to a snapshot taken just before the deploy. - Feeds written into config.toml from now on are ignored, with a warning in the log. Found along the way: Postgres sends a notice for every CREATE ... IF NOT EXISTS on something existing (11 on each open). sqlx logs them, and tracing-subscriber's per-layer filters then silently dropped the next line ipx logged. ipx's connections now ask for warnings and above only. Before the deploy: a pg_dump through db-backup, and config.toml.bak-pre-phase3-20260918.

rays closed this issue

2026-09-18 15:31:03 -07:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: rays/ipodderx-rs#18

Backend DB To use PostgreSQL instead of SQLite #18

Plan

Phase 1: port to SeaORM, still on SQLite

Phase 2: switch to Postgres

Phase 3: feeds and server settings out of config.toml

Risks

Progress update

Done

Worth knowing

Next

Phase 3: feeds and server settings out of `config.toml`