[Enhancement]: Postgres support #3211

New issue

Open

opened 2026-02-20 11:01:29 -05:00 by deekerman · 1 comment

deekerman commented

2026-02-20 11:01:29 -05:00

Owner

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_

deekerman added the

enhancement

label

2026-02-20 11:01:29 -05:00

deekerman commented

2026-02-20 11:01:30 -05:00

Author

Owner

@nichwall commented on GitHub (Jan 31, 2026):

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

@nichwall commented on GitHub (Jan 31, 2026): This probably makes more sense as a discussion since there is already a Postgres issue like you mentioned.