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

[Enhancement]: Postgres support #3211

New Issue
Open
opened 2026-04-25 00:14:20 +02:00 by adam · 1 comment
adam commented 2026-04-25 00:14:20 +02:00
Owner
Copy Link
Copy Source

Originally created by @twsouthwick on GitHub (Jan 31, 2026).

Type of Enhancement

Server Backend

Describe the Feature/Enhancement

Add PostgreSQL as an alternative database backend while keeping SQLite as the default.

#2046 already exists, but this a proposal that I wanted to discuss how to actually accomplish this.

Why would this be helpful?

In some hosting setups (such as kubernetes) it's easier to separate out the data concerns from the app so there's more control over the storage setup.

Future Implementation (Screenshot)

Approach

This breaks the addition of PostgreSQL support to two steps: prepatory work and enablign postgres.

The existing codebase has some existing sqlite-specific code. To prepare for postgres, there are two approaches that could be taken to address this:

Phase 1A: Dialect Abstraction (no behavior change)

  • Create dialect helper module for SQL generation
  • Refactor raw queries to use helpers instead of inline SQLite syntax
  • Make migrations dialect-aware (conditionally skip SQLite-only operations)

Example dialect specific changes:

Current (SQLite) PostgreSQL equivalent
json_each(column) jsonb_array_elements_text(column)
GROUP_CONCAT(col, ', ') STRING_AGG(col, ', ')
COLLATE NOCASE LOWER(column)
IFNULL(a, b) COALESCE(a, b)

Phase 1B: Schema Normalization (alternate approach)

Convert JSON blob columns to proper relational tables, eliminating dialect-specific JSON functions entirely.

  • Create lookup tables for shared entities
  • Create junction tables for many-to-many relationships
  • Write migration to extract JSON data into new tables
  • Update Sequelize models with proper associations
  • Refactor queries to use JOINs instead of JSON functions

JSON columns actively queried (require normalization):

Model Column New Tables
books narrators narrators + book_narrators
books tags tags + book_tags
books genres genres + book_genres
podcasts tags tags + podcast_tags
podcasts genres genres + podcast_genres

JSON columns NOT queried (keep as-is):

These columns store metadata blobs read/written as a whole and don't need SQL-level filtering:

Model Columns
books audioFiles, ebookFile, chapters
podcastEpisodes audioFile, chapters, extraData
libraryItems libraryFiles, extraData
users permissions, bookmarks, extraData
libraries settings, extraData
playbackSessions mediaMetadata, extraData
settings value
mediaProgress extraData
devices, apiKeys, mediaItemShares, customMetadataProviders extraData / permissions

Phase 2: Enable PostgreSQL

  • Add pg and pg-hstore dependencies
  • Add environment variable configuration
  • Hook up postgres connection

Configuration:

# For PostgreSQL, set a connection string:
DATABASE_URL=postgres://abs_user:secret@localhost:5432/audiobookshelf

# If not set, defaults to SQLite at the standard data path

Out of Scope

  • Automatic SQLite → PostgreSQL migration tool
  • MySQL/MariaDB support

Audiobookshelf Server Version

2.32.1

Current Implementation (Screenshot)

No response

Originally created by @twsouthwick on GitHub (Jan 31, 2026). ### Type of Enhancement Server Backend ### Describe the Feature/Enhancement Add PostgreSQL as an alternative database backend while keeping SQLite as the default. #2046 already exists, but this a proposal that I wanted to discuss how to actually accomplish this. ### Why would this be helpful? In some hosting setups (such as kubernetes) it's easier to separate out the data concerns from the app so there's more control over the storage setup. ### Future Implementation (Screenshot) ## Approach This breaks the addition of PostgreSQL support to two steps: prepatory work and enablign postgres. The existing codebase has some existing sqlite-specific code. To prepare for postgres, there are two approaches that could be taken to address this: ### Phase 1A: Dialect Abstraction (no behavior change) - [ ] Create dialect helper module for SQL generation - [ ] Refactor raw queries to use helpers instead of inline SQLite syntax - [ ] Make migrations dialect-aware (conditionally skip SQLite-only operations) **Example dialect specific changes:** | Current (SQLite) | PostgreSQL equivalent | |------------------|----------------------| | `json_each(column)` | `jsonb_array_elements_text(column)` | | `GROUP_CONCAT(col, ', ')` | `STRING_AGG(col, ', ')` | | `COLLATE NOCASE` | `LOWER(column)` | | `IFNULL(a, b)` | `COALESCE(a, b)` | ### Phase 1B: Schema Normalization (alternate approach) Convert JSON blob columns to proper relational tables, eliminating dialect-specific JSON functions entirely. - [ ] Create lookup tables for shared entities - [ ] Create junction tables for many-to-many relationships - [ ] Write migration to extract JSON data into new tables - [ ] Update Sequelize models with proper associations - [ ] Refactor queries to use JOINs instead of JSON functions **JSON columns actively queried (require normalization):** | Model | Column | New Tables | |-------|--------|------------| | `books` | `narrators` | `narrators` + `book_narrators` | | `books` | `tags` | `tags` + `book_tags` | | `books` | `genres` | `genres` + `book_genres` | | `podcasts` | `tags` | `tags` + `podcast_tags` | | `podcasts` | `genres` | `genres` + `podcast_genres` | **JSON columns NOT queried (keep as-is):** These columns store metadata blobs read/written as a whole and don't need SQL-level filtering: | Model | Columns | |-------|---------| | `books` | `audioFiles`, `ebookFile`, `chapters` | | `podcastEpisodes` | `audioFile`, `chapters`, `extraData` | | `libraryItems` | `libraryFiles`, `extraData` | | `users` | `permissions`, `bookmarks`, `extraData` | | `libraries` | `settings`, `extraData` | | `playbackSessions` | `mediaMetadata`, `extraData` | | `settings` | `value` | | `mediaProgress` | `extraData` | | `devices`, `apiKeys`, `mediaItemShares`, `customMetadataProviders` | `extraData` / `permissions` | --- ### Phase 2: Enable PostgreSQL - [ ] Add `pg` and `pg-hstore` dependencies - [ ] Add environment variable configuration - [ ] Hook up postgres connection **Configuration:** ```bash # For PostgreSQL, set a connection string: DATABASE_URL=postgres://abs_user:secret@localhost:5432/audiobookshelf # If not set, defaults to SQLite at the standard data path ``` ## Out of Scope - Automatic SQLite → PostgreSQL migration tool - MySQL/MariaDB support ### Audiobookshelf Server Version 2.32.1 ### Current Implementation (Screenshot) _No response_
adam added the enhancement label 2026-04-25 00:14:20 +02:00
adam commented 2026-04-25 00:14:20 +02:00
Author
Owner
Copy Link
Copy Source

@nichwall commented on GitHub (Feb 1, 2026):

This probably makes more sense as a discussion since there is already a Postgres issue like you mentioned.

@nichwall commented on GitHub (Feb 1, 2026): This probably makes more sense as a discussion since there is already a Postgres issue like you mentioned.
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 enhancement
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#3211
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?