Database Architecture

This page describes how Tune stores its data: supported engines, migrations, the main families of tables, full-text search and backups. The file location and how to turn on PostgreSQL are covered in Configuration.

Two engines, one active

SQLite is the default engine. It is compiled into the program: nothing to install, nothing to set up. The database is a single file, tune.db.
PostgreSQL is an option, available in the published Windows, macOS and Linux builds (except the Docker image and the aarch64-musl build).
The engine is chosen at startup: a postgres:// or postgresql:// URL selects PostgreSQL, otherwise Tune uses SQLite. Only one engine is open at a time.
The rest of the server goes through an interface shared by both engines.

Migrations

The schema evolves through numbered migrations, applied automatically at startup. Meanwhile, the server already answers on its port and says so.
SQLite: applied migrations are recorded in a _migrations table (version, name, date). Tune only applies the missing ones. A check pass, which changes nothing when everything is in place, then runs at every startup.
PostgreSQL has its own series of SQL migrations, written for it and tracked in a schema_version table.
There are no down migrations. If the database was created by a newer version of Tune, the server writes it to the log.

Main tables

The SQLite database has about sixty tables. The domains are the same with PostgreSQL.

Domain	Tables	Contents
Library	`artists`, `albums`, `tracks`, `track_credits`	Artists, albums (year, genre, cover, format, sample rate, bit depth), tracks (file path, duration, format, ISRC, audio fingerprint, CUE markers), credits by role
Metadata	`track_metadata`, `album_metadata`, `metadata_proposals`, `lyrics_cache`	Enriched metadata, correction proposals, cached lyrics
Organisation	`tags`, `item_tags`, `favorites`, `album_ratings`, `hidden_items`	Tags, favourites (per profile), ratings, hidden items
Playback	`zones`, `queue_items`	Zones (output type, device, volume, group, sync delay, DSD mode), each zone's queue
Playlists	`playlists`, `playlist_tracks`, `smart_playlists`, `smart_collections`	Playlists (local or service tracks), smart playlists and collections
Radio and podcasts	`radio_stations`, `radio_favorites`, `podcast_subscriptions`	Stations, favourites, subscriptions
Services	`streaming_auth`, `streaming_favorites`, `offline_cache`	Service connections, service favourites, offline cache
Profiles and settings	`profiles`, `settings`	Profiles, key-value settings
History	`listen_history`, `playback_history`	Listens
Network	`network_mounts`, `media_servers`, `ignored_devices`	Network shares, UPnP servers, ignored devices

SQLite in detail

WAL journal mode: reads and writes happen in parallel. Foreign keys are enforced.
One write connection and three read-only connections.
If the database sits on a network or FUSE file system (NFS, SMB, exFAT, NTFS…), Tune falls back to the classic journal and says so in the log, because WAL is not reliable there. Prefer a local disk.
Full-text search: three FTS5 indexes (artists, albums, tracks), kept up to date automatically. They ignore accents. An unaccent() function added by Tune does the same for other searches. The indexes are rebuilt with POST /api/v1/system/database/rebuild-fts.

PostgreSQL in detail

Recommended version: PostgreSQL 16. Version 15 also works; 14 has not been tested.
The unaccent extension is required. It ships with PostgreSQL 16; on PostgreSQL 15 it is part of the contrib package.
Full-text search uses tsvector columns and GIN indexes.
The server opens up to 16 connections.

Moving from SQLite to PostgreSQL

In the interface: Settings › System › Database › Migrate to PostgreSQL (Expert level). Tune creates the PostgreSQL database if it does not exist, copies every table, applies the PostgreSQL migrations, saves the URL, then restarts on PostgreSQL.
Tune does not delete the original SQLite database.
The command-line tool also offers tune db migrate-to-postgres, but it only copies the base tables: prefer the interface.

Backups

With SQLite, a backup is made on demand, from Settings › System › Database or with POST /api/v1/system/backups. Tune stores it in a backups folder next to the database and keeps the five most recent.
Tune also makes a safety copy before importing a database.
With PostgreSQL, use PostgreSQL's own tools, such as pg_dump.