[PR #1611] [CLOSED] PostgreSQL support: web push subscription store (part 1/3) #1661

New issue

Closed

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

BreizhHardware commented

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

Owner

📋 Pull Request Information

Original PR: https://github.com/binwiederhier/ntfy/pull/1611
Author: @binwiederhier
Created: 2/16/2026
Status: ❌ Closed

Base: main ← Head: postgres-webpush

📝 Commits (9)

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
ceda5ec Move things in user package
b567b4e Merge branch 'main' into postgres-webpush

📊 Changes

23 files changed (+1336 additions, -931 deletions)

View changed files

📝 cmd/serve.go (+5 -2)
📝 docs/config.md (+23 -4)
📝 docs/releases.md (+7 -0)
📝 go.mod (+4 -0)
📝 go.sum (+9 -0)
📝 server/config.go (+2 -0)
📝 server/server.go (+9 -4)
📝 server/server.yml (+6 -0)
📝 server/server_webpush.go (+3 -2)
📝 server/server_webpush_test.go (+9 -9)
📝 server/types.go (+0 -16)
➖ server/webpush_store.go (+0 -285)
➖ server/webpush_store_test.go (+0 -199)
📝 user/manager.go (+7 -408)
➕ user/migrations.go (+342 -0)
📝 user/util.go (+70 -2)
➕ webpush/store.go (+188 -0)
➕ webpush/store_postgres.go (+126 -0)
➕ webpush/store_postgres_test.go (+91 -0)
➕ webpush/store_sqlite.go (+138 -0)

...and 3 more files

📄 Description

Adds PostgreSQL as an alternative database backend for the web push subscription store, towards #1114.

Parts

Part 1: web push (#1611)
Part 2: user manager (#1614)
Part 3: message cache (#1616)

Summary

Extract webpush/ package -- moved the web push store out of server/ into its own webpush/ package with a Store interface and separate implementations (sqlite.go, postgres.go)
PostgreSQL implementation -- full PostgresStore with webpush_-prefixed tables, $N placeholders, BIGINT types, ON CONFLICT upserts, and FK cascades
database-url config option -- new global --database-url flag (env: NTFY_DATABASE_URL) for PostgreSQL connection strings; when set, the web push store uses PostgreSQL instead of SQLite
Unified tests -- shared test logic in store_test.go runs against both SQLite and PostgreSQL backends; PostgreSQL tests create an isolated schema per test and skip when NTFY_TEST_DATABASE_URL is not set

Files changed

Area	Files
New package	`webpush/store.go`, `webpush/sqlite.go`, `webpush/postgres.go`
Shared tests	`webpush/store_test.go`, `webpush/sqlite_test.go`, `webpush/postgres_test.go`
Config/CLI	`server/config.go`, `cmd/serve.go`, `server/server.yml`
Server wiring	`server/server.go`, `server/server_webpush.go`, `server/types.go`
Server tests	`server/server_webpush_test.go`
Docs	`docs/config.md`, `docs/releases.md`
Removed	`server/webpush_store.go`, `server/webpush_store_test.go`

Next steps

This is the first store to get PostgreSQL support. The same pattern (interface + factory in server.go + database-url config) is designed to extend to the message cache and user manager.

Refs #1114

_{🔄 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/1611 **Author:** [@binwiederhier](https://github.com/binwiederhier) **Created:** 2/16/2026 **Status:** ❌ Closed **Base:** `main` ← **Head:** `postgres-webpush` --- ### 📝 Commits (9) - [`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 - [`ceda5ec`](https://github.com/binwiederhier/ntfy/commit/ceda5ec3d8d1300efff76d9ff8c41ca4dab6f585) Move things in user package - [`b567b4e`](https://github.com/binwiederhier/ntfy/commit/b567b4e904bac6e589cbbf86c04c9239ff45aac5) Merge branch 'main' into postgres-webpush ### 📊 Changes **23 files changed** (+1336 additions, -931 deletions) <details> <summary>View changed files</summary> 📝 `cmd/serve.go` (+5 -2) 📝 `docs/config.md` (+23 -4) 📝 `docs/releases.md` (+7 -0) 📝 `go.mod` (+4 -0) 📝 `go.sum` (+9 -0) 📝 `server/config.go` (+2 -0) 📝 `server/server.go` (+9 -4) 📝 `server/server.yml` (+6 -0) 📝 `server/server_webpush.go` (+3 -2) 📝 `server/server_webpush_test.go` (+9 -9) 📝 `server/types.go` (+0 -16) ➖ `server/webpush_store.go` (+0 -285) ➖ `server/webpush_store_test.go` (+0 -199) 📝 `user/manager.go` (+7 -408) ➕ `user/migrations.go` (+342 -0) 📝 `user/util.go` (+70 -2) ➕ `webpush/store.go` (+188 -0) ➕ `webpush/store_postgres.go` (+126 -0) ➕ `webpush/store_postgres_test.go` (+91 -0) ➕ `webpush/store_sqlite.go` (+138 -0) _...and 3 more files_ </details> ### 📄 Description Adds PostgreSQL as an alternative database backend for the web push subscription store, towards #1114. ## Parts - Part 1: web push (#1611) - Part 2: user manager (#1614) - Part 3: message cache (#1616) ## Summary - **Extract `webpush/` package** -- moved the web push store out of `server/` into its own `webpush/` package with a `Store` interface and separate implementations (`sqlite.go`, `postgres.go`) - **PostgreSQL implementation** -- full `PostgresStore` with `webpush_`-prefixed tables, `$N` placeholders, `BIGINT` types, `ON CONFLICT` upserts, and FK cascades - **`database-url` config option** -- new global `--database-url` flag (env: `NTFY_DATABASE_URL`) for PostgreSQL connection strings; when set, the web push store uses PostgreSQL instead of SQLite - **Unified tests** -- shared test logic in `store_test.go` runs against both SQLite and PostgreSQL backends; PostgreSQL tests create an isolated schema per test and skip when `NTFY_TEST_DATABASE_URL` is not set ## Files changed | Area | Files | |------|-------| | New package | `webpush/store.go`, `webpush/sqlite.go`, `webpush/postgres.go` | | Shared tests | `webpush/store_test.go`, `webpush/sqlite_test.go`, `webpush/postgres_test.go` | | Config/CLI | `server/config.go`, `cmd/serve.go`, `server/server.yml` | | Server wiring | `server/server.go`, `server/server_webpush.go`, `server/types.go` | | Server tests | `server/server_webpush_test.go` | | Docs | `docs/config.md`, `docs/releases.md` | | Removed | `server/webpush_store.go`, `server/webpush_store_test.go` | ## Next steps This is the first store to get PostgreSQL support. The same pattern (interface + factory in `server.go` + `database-url` config) is designed to extend to the message cache and user manager. Refs #1114 --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>