starred/audiobookshelf
1
0
Fork 0
mirror of https://github.com/advplyr/audiobookshelf.git synced 2026-05-30 23:40:40 +02:00
Code Issues 1.0k Packages Projects Releases 10 Wiki Activity

[PR #5098] Add optional PostgreSQL backend support with migration safeguards and regression coverage #4425

New Issue
Open
opened 2026-04-25 00:19:42 +02:00 by adam · 0 comments
adam commented 2026-04-25 00:19:42 +02:00
Owner
Copy Link
Copy Source

📋 Pull Request Information

Original PR: https://github.com/advplyr/audiobookshelf/pull/5098
Author: @kevingatera
Created: 3/3/2026
Status: 🔄 Open

Base: master ← Head: postgres-upstream-ready

📝 Commits (10+)

  • 453cd25 add postgres dialect support for homelab migration
  • f31826c fix migration metadata table detection on postgres
  • 920a743 handle postgres alias casing in migration metadata
  • c7d14f7 disable identifier quoting for postgres compatibility
  • c0a4ce1 make sqlite to postgres migrator case-insensitive
  • 8593721 harden json normalization in postgres migration script
  • cb31fd3 prevent lossy postgres migration edge cases
  • 6227a60 fix postgres legacy user id lookup
  • 15d2715 fix postgres old user id json lookup
  • 8b3f67b guard numeric casts for postgres text fields

📊 Changes

41 files changed (+2132 additions, -192 deletions)

View changed files

📝 package-lock.json (+166 -5)
📝 package.json (+3 -0)
📝 server/Database.js (+119 -41)
📝 server/Server.js (+19 -6)
📝 server/controllers/LibraryController.js (+12 -4)
📝 server/managers/MigrationManager.js (+46 -26)
📝 server/migrations/v2.15.0-series-column-unique.js (+6 -2)
📝 server/migrations/v2.15.1-reindex-nocase.js (+6 -0)
📝 server/migrations/v2.17.3-fk-constraints.js (+6 -0)
📝 server/migrations/v2.19.1-copy-title-to-library-items.js (+12 -0)
📝 server/migrations/v2.19.4-improve-podcast-queries.js (+12 -0)
📝 server/migrations/v2.20.0-improve-author-sort-queries.js (+10 -0)
📝 server/models/Author.js (+3 -6)
📝 server/models/Book.js (+4 -7)
📝 server/models/LibraryItem.js (+9 -4)
📝 server/models/MediaProgress.js (+7 -1)
📝 server/models/PlaybackSession.js (+1 -1)
📝 server/models/Series.js (+5 -7)
📝 server/models/User.js (+17 -3)
📝 server/scanner/LibraryScanner.js (+6 -1)

...and 21 more files

📄 Description

Why

Audiobookshelf currently defaults to SQLite, which is simple for most installs, but some deployments need an external DB backend for reliability and infrastructure fit.

This PR adds PostgreSQL as an optional backend while preserving SQLite as the default and maintaining backward compatibility.
It also includes targeted safety fixes for real-world PostgreSQL edge cases discovered during migration and testing.

What this changes

1) Optional PostgreSQL backend support (SQLite remains default)

  • Add dialect and config selection:
    • DB_DIALECT=sqlite|postgres (optional)
    • DATABASE_URL for PostgreSQL
  • Keep SQLite behavior unchanged when PostgreSQL is not configured.
  • Add PostgreSQL dependencies (pg, pg-hstore).

2) Dialect-aware query compatibility

  • Introduce server/utils/sqlDialectHelpers.js for cross-dialect SQL generation.
  • Refactor key query paths to avoid SQLite-specific syntax in PostgreSQL, including:
    • case-insensitive sort behavior
    • JSON array and value operations
    • numeric casting behavior for text-backed fields
  • Guard sequence and year sorting with safe casts to avoid runtime failures from non-numeric legacy values.

3) Migration and schema-safety hardening

  • Add server/scripts/migrateSqliteToPostgres.js for SQLite to PostgreSQL data migration.
  • Add preflight checks to prevent data loss:
    • detect overlong varchar source values
    • detect non-integer source values for integer target columns
  • Harden JSON normalization for malformed and double-encoded payloads.
  • Improve migration metadata handling and case-insensitive table and column mapping for PostgreSQL.

4) PostgreSQL runtime bug fixes found during validation

  • Fix legacy user lookup behavior for PostgreSQL (oldUserId path).
  • Fix media progress updatedAt sync path to avoid raw SQL table-name mismatches.
  • Fix collapse-series edge cases:
    • empty include list (IN ()) generation
    • aliasing issues in PostgreSQL SQL
    • display title fallback alias compatibility

5) Focused regression tests (high signal)

Added tests for the exact risky paths that caused failures:

  • test/server/utils/queries/libraryItemsBookFilters.test.js
    • collapse-series empty ID set
    • safe ID quoting
    • PostgreSQL join alias safety
    • failure-path query diagnostics
  • test/server/models/MediaProgress.test.js
    • valid and invalid lastUpdate handling
  • test/server/models/User.test.js
    • UUID and legacy old-id lookup behavior on PostgreSQL and SQLite
  • test/server/scripts/migrateSqliteToPostgres.test.js
    • JSON normalization and integer and varchar preflight checks
  • test/server/utils/sqlDialectHelpers.test.js
    • dialect helper correctness

Backward compatibility

  • SQLite remains the default behavior.
  • Existing SQLite users are unaffected unless PostgreSQL is explicitly configured.
  • PostgreSQL code paths are opt-in and covered by targeted regression tests.

Validation

Local:

  • npm test passed (356 passing)
  • targeted compatibility suite passed (69 passing)

CI (branch):

  • Unit tests passed
  • Integration tests passed

Observed runtime impact during migration validation (same host, from app logs):

  • Discover load time improved from about 5.23s (SQLite period average) to about 0.08s (PostgreSQL period average).
  • Personalized shelves load time improved from about 7.35s to about 0.12s.

Notes

  • This PR intentionally focuses on safe, minimal compatibility and migration behavior.
  • It does not force external DB usage. PostgreSQL is optional.

Beta image tag for testing:

  • ghcr.io/kevingatera/audiobookshelf:homelab-postgres-5871b01f6dd9f1cd690b9a70bdc227a2d5db2643

🔄 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/advplyr/audiobookshelf/pull/5098 **Author:** [@kevingatera](https://github.com/kevingatera) **Created:** 3/3/2026 **Status:** 🔄 Open **Base:** `master` ← **Head:** `postgres-upstream-ready` --- ### 📝 Commits (10+) - [`453cd25`](https://github.com/advplyr/audiobookshelf/commit/453cd2587bf014e9b799deab593d4c8d9dee7347) add postgres dialect support for homelab migration - [`f31826c`](https://github.com/advplyr/audiobookshelf/commit/f31826c256e0f01e3e0b1a10f79b79c43bbdc847) fix migration metadata table detection on postgres - [`920a743`](https://github.com/advplyr/audiobookshelf/commit/920a7434f1f8d36fff3f79fd985da7f79641b51e) handle postgres alias casing in migration metadata - [`c7d14f7`](https://github.com/advplyr/audiobookshelf/commit/c7d14f7d303bf4bc355c923409b61a33c79df6cf) disable identifier quoting for postgres compatibility - [`c0a4ce1`](https://github.com/advplyr/audiobookshelf/commit/c0a4ce139690ccfc0d2afc4a7c30162a3f79466b) make sqlite to postgres migrator case-insensitive - [`8593721`](https://github.com/advplyr/audiobookshelf/commit/8593721c3558e3f0e2f998c264cb250be8fd2394) harden json normalization in postgres migration script - [`cb31fd3`](https://github.com/advplyr/audiobookshelf/commit/cb31fd34d56723ba09573b30753bd9d0ed14e8ab) prevent lossy postgres migration edge cases - [`6227a60`](https://github.com/advplyr/audiobookshelf/commit/6227a60fed3a09340b21596ee8864044e5414dcd) fix postgres legacy user id lookup - [`15d2715`](https://github.com/advplyr/audiobookshelf/commit/15d2715a7408e888c1cd1b5f781b06ea49b4f5da) fix postgres old user id json lookup - [`8b3f67b`](https://github.com/advplyr/audiobookshelf/commit/8b3f67b7c942c8f3bd8bcbc7e03a52fb0ed9dd8a) guard numeric casts for postgres text fields ### 📊 Changes **41 files changed** (+2132 additions, -192 deletions) <details> <summary>View changed files</summary> 📝 `package-lock.json` (+166 -5) 📝 `package.json` (+3 -0) 📝 `server/Database.js` (+119 -41) 📝 `server/Server.js` (+19 -6) 📝 `server/controllers/LibraryController.js` (+12 -4) 📝 `server/managers/MigrationManager.js` (+46 -26) 📝 `server/migrations/v2.15.0-series-column-unique.js` (+6 -2) 📝 `server/migrations/v2.15.1-reindex-nocase.js` (+6 -0) 📝 `server/migrations/v2.17.3-fk-constraints.js` (+6 -0) 📝 `server/migrations/v2.19.1-copy-title-to-library-items.js` (+12 -0) 📝 `server/migrations/v2.19.4-improve-podcast-queries.js` (+12 -0) 📝 `server/migrations/v2.20.0-improve-author-sort-queries.js` (+10 -0) 📝 `server/models/Author.js` (+3 -6) 📝 `server/models/Book.js` (+4 -7) 📝 `server/models/LibraryItem.js` (+9 -4) 📝 `server/models/MediaProgress.js` (+7 -1) 📝 `server/models/PlaybackSession.js` (+1 -1) 📝 `server/models/Series.js` (+5 -7) 📝 `server/models/User.js` (+17 -3) 📝 `server/scanner/LibraryScanner.js` (+6 -1) _...and 21 more files_ </details> ### 📄 Description ## Why Audiobookshelf currently defaults to SQLite, which is simple for most installs, but some deployments need an external DB backend for reliability and infrastructure fit. This PR adds PostgreSQL as an optional backend while preserving SQLite as the default and maintaining backward compatibility. It also includes targeted safety fixes for real-world PostgreSQL edge cases discovered during migration and testing. ## What this changes ### 1) Optional PostgreSQL backend support (SQLite remains default) - Add dialect and config selection: - `DB_DIALECT=sqlite|postgres` (optional) - `DATABASE_URL` for PostgreSQL - Keep SQLite behavior unchanged when PostgreSQL is not configured. - Add PostgreSQL dependencies (`pg`, `pg-hstore`). ### 2) Dialect-aware query compatibility - Introduce `server/utils/sqlDialectHelpers.js` for cross-dialect SQL generation. - Refactor key query paths to avoid SQLite-specific syntax in PostgreSQL, including: - case-insensitive sort behavior - JSON array and value operations - numeric casting behavior for text-backed fields - Guard sequence and year sorting with safe casts to avoid runtime failures from non-numeric legacy values. ### 3) Migration and schema-safety hardening - Add `server/scripts/migrateSqliteToPostgres.js` for SQLite to PostgreSQL data migration. - Add preflight checks to prevent data loss: - detect overlong varchar source values - detect non-integer source values for integer target columns - Harden JSON normalization for malformed and double-encoded payloads. - Improve migration metadata handling and case-insensitive table and column mapping for PostgreSQL. ### 4) PostgreSQL runtime bug fixes found during validation - Fix legacy user lookup behavior for PostgreSQL (`oldUserId` path). - Fix media progress `updatedAt` sync path to avoid raw SQL table-name mismatches. - Fix collapse-series edge cases: - empty include list (`IN ()`) generation - aliasing issues in PostgreSQL SQL - display title fallback alias compatibility ### 5) Focused regression tests (high signal) Added tests for the exact risky paths that caused failures: - `test/server/utils/queries/libraryItemsBookFilters.test.js` - collapse-series empty ID set - safe ID quoting - PostgreSQL join alias safety - failure-path query diagnostics - `test/server/models/MediaProgress.test.js` - valid and invalid `lastUpdate` handling - `test/server/models/User.test.js` - UUID and legacy old-id lookup behavior on PostgreSQL and SQLite - `test/server/scripts/migrateSqliteToPostgres.test.js` - JSON normalization and integer and varchar preflight checks - `test/server/utils/sqlDialectHelpers.test.js` - dialect helper correctness ## Backward compatibility - SQLite remains the default behavior. - Existing SQLite users are unaffected unless PostgreSQL is explicitly configured. - PostgreSQL code paths are opt-in and covered by targeted regression tests. ## Validation Local: - `npm test` passed (`356 passing`) - targeted compatibility suite passed (`69 passing`) CI (branch): - Unit tests passed - Integration tests passed Observed runtime impact during migration validation (same host, from app logs): - Discover load time improved from about `5.23s` (SQLite period average) to about `0.08s` (PostgreSQL period average). - Personalized shelves load time improved from about `7.35s` to about `0.12s`. ## Notes - This PR intentionally focuses on safe, minimal compatibility and migration behavior. - It does not force external DB usage. PostgreSQL is optional. Beta image tag for testing: - `ghcr.io/kevingatera/audiobookshelf:homelab-postgres-5871b01f6dd9f1cd690b9a70bdc227a2d5db2643` --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>
adam added the pull-request label 2026-04-25 00:19:42 +02:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
authentication
backlog
bug
chapter editor
config-issue
ebooks
encoding/embedding
enhancement
help wanted
listening sessions & progress
planned
possible plugin
progress sync
pull-request
Mirrored from GitHub Pull Request
 sorting/filtering/searching
unable to reproduce
upload
users & permissions
waiting
No Label pull-request
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
adam (Adam Melkus)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/audiobookshelf#4425
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?