[PR #1619] [MERGED] Add experimental PostgreSQL support as alternative database backend #1666

New issue

Closed

opened 2026-05-07 01:03:09 +02:00 by BreizhHardware · 0 comments

BreizhHardware commented

2026-05-07 01:03:09 +02:00

Owner

📋 Pull Request Information

Original PR: https://github.com/binwiederhier/ntfy/pull/1619
Author: @binwiederhier
Created: 2/20/2026
Status: ✅ Merged
Merged: 3/7/2026
Merged by: @binwiederhier

Base: main ← Head: postgres-support

📝 Commits (10+)

e432bf2 Rename PostgreSQL table prefix from wp_ to webpush_
5331437 Unify webpush store tests across SQLite and PostgreSQL backends
a8dcecd Refactor webpush store tests and add coverage
bdd2019 Manual refinements
869b972 Manual review
4e5f95b Refactor webpush store to eliminate code duplication
82e15d8 Manual changes
60fa50f Merge branch 'main' into postgres-webpush+user
b567b4e Merge branch 'main' into postgres-webpush
07c3e28 Refactor user package to Store interface with PostgreSQL support

📊 Changes

76 files changed (+15032 additions, -10219 deletions)

View changed files

📝 .github/workflows/release.yaml (+16 -0)
📝 .github/workflows/test.yaml (+17 -3)
📝 .gitignore (+2 -0)
📝 Makefile (+6 -4)
📝 cmd/serve.go (+22 -14)
📝 cmd/user.go (+18 -10)
➕ db/db.go (+38 -0)
➕ db/pg/pg.go (+93 -0)
➕ db/test/test.go (+63 -0)
📝 docs/config.md (+76 -13)
📝 docs/releases.md (+39 -17)
📝 go.mod (+6 -2)
📝 go.sum (+13 -4)
➕ message/cache.go (+590 -0)
➕ message/cache_postgres.go (+110 -0)
➕ message/cache_postgres_schema.go (+85 -0)
➕ message/cache_sqlite.go (+142 -0)
➕ message/cache_sqlite_schema.go (+453 -0)
➕ message/cache_sqlite_test.go (+292 -0)
➕ message/cache_test.go (+829 -0)

...and 56 more files

📄 Description

Summary

Add PostgreSQL as an alternative database backend to SQLite for all three database-backed stores: message cache, user/access control, and web push subscriptions. Configured via a single database-url option (connection string).

Resolves #1114.

What changed

New database-url config option — when set to a PostgreSQL connection string (e.g. postgres://user:pass@host:5432/ntfy), ntfy uses PostgreSQL for everything instead of SQLite. The cache-file, auth-file, and web-push-file options must not be set when using PostgreSQL. Setting database-url implicitly enables auth (equivalent to setting auth-file).

Architecture

Shared connection pool: A single *sql.DB PostgreSQL connection pool is created at startup and shared across all stores, rather than each store managing its own connection
New packages extracted from server internals:
- model/ — shared types (Message, Attachment, Action, etc.) used across packages
- message/ — message cache with SQLite and PostgreSQL backends
- webpush/ — web push subscription store with SQLite and PostgreSQL backends
- db/ — shared database utilities (ExecTx, QueryTx) and PostgreSQL connection helper (db/pg)
- user/ — refactored manager with separate SQLite and PostgreSQL backends (previously had SQLite-only migrations baked in)
Each store has a _sqlite.go / _postgres.go split for backend-specific SQL and schema, with shared logic in the main file
Schema management: PostgreSQL schemas are applied on startup (no incremental migrations yet — this is v1/experimental)

Tests

All server tests (server/server_test.go, server/server_*_test.go) run against both SQLite and PostgreSQL via forEachBackend() test helper
Individual store tests (message/cache_test.go, user/manager_test.go, webpush/store_test.go) also run against both backends
CI pipeline (test.yaml) spins up a PostgreSQL 17 service container and sets NTFY_TEST_DATABASE_URL

Migration tool

tools/pgimport/ — one-off CLI tool to import existing SQLite data (cache, users, web push) into PostgreSQL
Validates schema versions before import, verifies row counts after
Idempotent and safe to re-run

Docs

New Database options section documenting SQLite vs PostgreSQL setup
Connection pool tuning parameters (pool_max_conns, pool_max_idle_conns, pool_conn_max_lifetime, pool_conn_max_idle_time)
Updated message cache, access control, and web push sections to reference PostgreSQL alternative

Config example

base-url: "https://ntfy.example.com"
listen-http: ":2586"
database-url: "postgres://ntfy:mypassword@db.example.com:5432/ntfy?sslmode=require"
auth-default-access: "deny-all"

Test plan

All existing server tests pass against both SQLite and PostgreSQL
Store-level unit tests pass for both backends
CI runs tests with PostgreSQL 17 service container
Manual testing with pgimport migration tool
Manual testing of fresh PostgreSQL setup end-to-end

_{🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.}

## 📋 Pull Request Information **Original PR:** https://github.com/binwiederhier/ntfy/pull/1619 **Author:** [@binwiederhier](https://github.com/binwiederhier) **Created:** 2/20/2026 **Status:** ✅ Merged **Merged:** 3/7/2026 **Merged by:** [@binwiederhier](https://github.com/binwiederhier) **Base:** `main` ← **Head:** `postgres-support` --- ### 📝 Commits (10+) - [`e432bf2`](https://github.com/binwiederhier/ntfy/commit/e432bf2886631654d2203df5d84aa818c581fbb2) Rename PostgreSQL table prefix from wp_ to webpush_ - [`5331437`](https://github.com/binwiederhier/ntfy/commit/533143766434ed448a8688c709933018a255a4a6) Unify webpush store tests across SQLite and PostgreSQL backends - [`a8dcecd`](https://github.com/binwiederhier/ntfy/commit/a8dcecdb6d2e862782711f537edff5122c330020) Refactor webpush store tests and add coverage - [`bdd2019`](https://github.com/binwiederhier/ntfy/commit/bdd20197b39ad41ea091a2c89273a77522a9e128) Manual refinements - [`869b972`](https://github.com/binwiederhier/ntfy/commit/869b972a50894540aa570912bc1a1bd7cf73c290) Manual review - [`4e5f95b`](https://github.com/binwiederhier/ntfy/commit/4e5f95ba0c073bff35e2c0a748000718ff56d224) Refactor webpush store to eliminate code duplication - [`82e15d8`](https://github.com/binwiederhier/ntfy/commit/82e15d84bdb470665849a070d51019e5ff1d157f) Manual changes - [`60fa50f`](https://github.com/binwiederhier/ntfy/commit/60fa50f0d5115ba330a3fce07bce090ec7aeb97b) Merge branch 'main' into postgres-webpush+user - [`b567b4e`](https://github.com/binwiederhier/ntfy/commit/b567b4e904bac6e589cbbf86c04c9239ff45aac5) Merge branch 'main' into postgres-webpush - [`07c3e28`](https://github.com/binwiederhier/ntfy/commit/07c3e280bf670597dfb3cbc1f5ad678cef6faaf4) Refactor user package to Store interface with PostgreSQL support ### 📊 Changes **76 files changed** (+15032 additions, -10219 deletions) <details> <summary>View changed files</summary> 📝 `.github/workflows/release.yaml` (+16 -0) 📝 `.github/workflows/test.yaml` (+17 -3) 📝 `.gitignore` (+2 -0) 📝 `Makefile` (+6 -4) 📝 `cmd/serve.go` (+22 -14) 📝 `cmd/user.go` (+18 -10) ➕ `db/db.go` (+38 -0) ➕ `db/pg/pg.go` (+93 -0) ➕ `db/test/test.go` (+63 -0) 📝 `docs/config.md` (+76 -13) 📝 `docs/releases.md` (+39 -17) 📝 `go.mod` (+6 -2) 📝 `go.sum` (+13 -4) ➕ `message/cache.go` (+590 -0) ➕ `message/cache_postgres.go` (+110 -0) ➕ `message/cache_postgres_schema.go` (+85 -0) ➕ `message/cache_sqlite.go` (+142 -0) ➕ `message/cache_sqlite_schema.go` (+453 -0) ➕ `message/cache_sqlite_test.go` (+292 -0) ➕ `message/cache_test.go` (+829 -0) _...and 56 more files_ </details> ### 📄 Description ## Summary Add PostgreSQL as an alternative database backend to SQLite for all three database-backed stores: message cache, user/access control, and web push subscriptions. Configured via a single `database-url` option (connection string). Resolves #1114. ## What changed **New `database-url` config option** — when set to a PostgreSQL connection string (e.g. `postgres://user:pass@host:5432/ntfy`), ntfy uses PostgreSQL for everything instead of SQLite. The `cache-file`, `auth-file`, and `web-push-file` options must not be set when using PostgreSQL. Setting `database-url` implicitly enables auth (equivalent to setting `auth-file`). ### Architecture - **Shared connection pool**: A single `*sql.DB` PostgreSQL connection pool is created at startup and shared across all stores, rather than each store managing its own connection - **New packages** extracted from server internals: - `model/` — shared types (`Message`, `Attachment`, `Action`, etc.) used across packages - `message/` — message cache with SQLite and PostgreSQL backends - `webpush/` — web push subscription store with SQLite and PostgreSQL backends - `db/` — shared database utilities (`ExecTx`, `QueryTx`) and PostgreSQL connection helper (`db/pg`) - `user/` — refactored manager with separate SQLite and PostgreSQL backends (previously had SQLite-only migrations baked in) - **Each store** has a `_sqlite.go` / `_postgres.go` split for backend-specific SQL and schema, with shared logic in the main file - **Schema management**: PostgreSQL schemas are applied on startup (no incremental migrations yet — this is v1/experimental) ### Tests - All server tests (`server/server_test.go`, `server/server_*_test.go`) run against **both SQLite and PostgreSQL** via `forEachBackend()` test helper - Individual store tests (`message/cache_test.go`, `user/manager_test.go`, `webpush/store_test.go`) also run against both backends - CI pipeline (`test.yaml`) spins up a PostgreSQL 17 service container and sets `NTFY_TEST_DATABASE_URL` ### Migration tool - `tools/pgimport/` — one-off CLI tool to import existing SQLite data (cache, users, web push) into PostgreSQL - Validates schema versions before import, verifies row counts after - Idempotent and safe to re-run ### Docs - New [Database options](https://docs.ntfy.sh/config/#database-options) section documenting SQLite vs PostgreSQL setup - Connection pool tuning parameters (`pool_max_conns`, `pool_max_idle_conns`, `pool_conn_max_lifetime`, `pool_conn_max_idle_time`) - Updated message cache, access control, and web push sections to reference PostgreSQL alternative ## Config example ```yaml base-url: "https://ntfy.example.com" listen-http: ":2586" database-url: "postgres://ntfy:mypassword@db.example.com:5432/ntfy?sslmode=require" auth-default-access: "deny-all" ``` ## Test plan - [x] All existing server tests pass against both SQLite and PostgreSQL - [x] Store-level unit tests pass for both backends - [x] CI runs tests with PostgreSQL 17 service container - [x] Manual testing with `pgimport` migration tool - [x] Manual testing of fresh PostgreSQL setup end-to-end --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>