Skip to main content

Database Configuration and Management

This document provides comprehensive information about Youtarr's database setup, configuration, troubleshooting, and management.

Table of Contents​

Overview​

Youtarr uses MariaDB/MySQL for storing:

  • Channel subscriptions and metadata
  • Playlist subscriptions and per-server sync state
  • Video information and download history
  • Job queues and processing state
  • Session data for authentication

Database Tables​

TableModelDescription
channelsChannelYouTube channel information. m3u_enabled (boolean, default false): generate a .m3u playlist file in the channel folder. m3u_sort_order (string, default oldest_first): .m3u entry order, oldest_first or newest_first. auto_removal_protected (boolean, default false): exclude every video of this channel from auto-removal; only applies while the channel is subscribed (enabled), and enabling it clears auto_removal_keep_recent_count. auto_removal_keep_recent_count (int, nullable): auto-removal always keeps this many of the channel's most recently downloaded videos; mutually exclusive with auto_removal_protected, dormant while the channel is unsubscribed. additional_tags (text, nullable, default null): adds additional, custom tags to videos downloaded from the channel. tab_video_counts (text, nullable): JSON of YouTube's public video totals per tab, keyed by media type ({"video":{"total":449,"fetchedAt":"..."}}), used for the download percentage (refreshed daily with a YouTube API key, every three days or more in capped batches through yt-dlp without one). tab_counts_attempted_at (datetime, nullable): when a tab count lookup last started for the channel, successful or not; yt-dlp bulk refreshes take the oldest attempts first and skip channels attempted within three days, and opening a channel page waits an hour after an attempt. Non-unique index on channel_id (some installs carry duplicate rows).
videosVideoDownloaded video metadata. video_resolution (VARCHAR(20), nullable): actual pixel dimensions of the downloaded file, e.g. "1920x1080", measured by ffprobe at download time and backfilled by the filesystem rescan; the displayed tier label (e.g. "1080p") is derived client-side (client/src/utils/videoResolution.ts), so labeling rules can change without re-probing; NULL = not yet checked or audio-only, "0x0" = probe failed (never shown in the UI). Index on channel_id.
channelvideosChannelVideoChannel <-> video associations. published_at_source tracks published_at provenance: exact (.info.json), approximate (yt-dlp flat-playlist date), estimated (ordering-only placeholder assigned when YouTube returns a listing with no dates; never displayed), NULL (legacy, treated as approximate). Non-unique indexes on (channel_id, youtube_id) and youtube_id.
jobsJobDownload job queue. aux_data (MEDIUMTEXT, nullable): JSON snapshot of job data other than videos (failed downloads, diagnoses, skip counts, terminated channels), written on job save and merged back on startup load; NULL for jobs recorded before the column existed.
jobvideosJobVideoJob <-> video associations
jobvideodownloadsJobVideoDownloadDownload progress tracking
sessionsSessionUser authentication sessions
apikeysApiKeyAPI key credentials for external integrations (bookmarklets, shortcuts, automation)
playlistsPlaylistSubscribed YouTube playlists with per-playlist sync targets and seeded settings. auto_download_baseline_at (DATETIME, nullable): saved starting time for automatic following. auto_download_baseline_id (INTEGER, nullable): last known playlist entry id for new setups, avoiding same-second timestamp ambiguity. Existing timestamp-only cutoffs are preserved on upgrade. auto_download_setup_error (STRING, nullable): persisted first-setup failure (PLAYLIST_TOO_LARGE or PLAYLIST_REFRESH_INCOMPLETE), cleared when a baseline is successfully established. sort_order (STRING NOT NULL, default 'default'): saved output order for the .m3u file and media server sync; 'reversed' flips the YouTube playlist order. title_filter_regex (TEXT, nullable): matched against video titles on refresh as a case-insensitive JavaScript regex; the settings endpoint rejects a pattern that does not compile. It also seeds auto-created source channels, whose title filter runs through yt-dlp's Python matching (case sensitive).
playlistvideosPlaylistVideoOne row per (playlist, video) with the YouTube playlist position. first_seen_at: immutable local discovery time, backfilled from row creation on upgrade. downloaded_at: recorded download/job time, independently updated. auto_download_requested: explicitly selected existing videos pending successful download, saved only with auto-download enabled and an established baseline. auto_download_last_attempt_at (DATETIME(3), nullable): scheduling time for an explicit saved batch or a selected scheduled retry, written before queue submission. Used to rotate older saved requests, independently of discovery and download dates; legacy requests with no known attempt remain null and join the bounded retry pool. First initialization preserves pending requests; successful downloads clear their own flags, and an explicit starting-point reset clears the remaining requests. Legacy added_at is retained for pre-upgrade timestamp cutoffs; it is no longer overwritten on download.
playlist_sync_statePlaylistSyncStatePer-(playlist, server) sync state: server playlist id, last_synced_at, last_error
subfoldersSubfolderDurable registry of known subfolder names (id, name unique, created_at, updated_at). Backfilled from channels, playlists, and video file paths by the add-subfolders-table migration; kept current by register-on-create and register-on-download-override.
video_watch_statusVideoWatchStatusPer-video, per-media-server, per-user watch state pulled by the watch status sync. Absence of a row means never synced/unknown, not unwatched. Columns: video_id, server_type (plex/jellyfin/emby), server_user_id (Plex owner is '1'), played, play_count, position_ms, percent_watched, last_watched_at, last_synced_at. Unique index on (video_id, server_type, server_user_id).
media_server_usersMediaServerUserMedia-server account directory populated during watch status sync: server_type, server_user_id, server_user_name. Unique index on (server_type, server_user_id). Used to display which users watched a video.
watch_status_sync_cursorsWatchStatusSyncCursorDurable per-server watch-status sync cursor (unique server_type, cursor DATETIME). Today only Plex uses it: the newest play-history event scanned, so incremental pulls never permanently skip events. Deleting a row forces a full history re-scan on the next sync.
scheduled_task_runsScheduledTaskRunBounded run history (the last 20 runs per task, plus any row still running and the newest row of each outcome) for the configurable schedules: task_key (the schedule's config key), trigger_type (scheduled/manual/startup), status (running/success/error/skipped/interrupted), task-specific outcome, message, JSON details, started_at, finished_at. Index on (task_key, started_at). Rows still running when the server starts are marked interrupted. Feeds GET /api/schedules and the last-run displays on the Scheduling, Maintenance, and YT-DLP settings pages.
SequelizeMetaNASequelize ORM migration tracking

Internal Database (Default)​

Container Details​

  • Image: mariadb:10.3
  • Container Name: youtarr-db
  • Port: 3321 inside the Docker network only; the bundled database is not published to the host
  • Character Set: utf8mb4 (full Unicode/emoji support), collation utf8mb4_unicode_ci on the database default and every table. The 20260907000000-normalize-utf8mb4-unicode-collation migration converts older installs that still had utf8mb4_general_ci or three-byte utf8 tables.
  • Default Credentials:
    • User: root
    • Password: 123qweasd (change in production!)
    • Database: youtarr

Storage Options​

Option 1: Bind Mount (legacy / pre-existing installs)​

volumes:
  - ./database:/var/lib/mysql
  • Data stored in ./database directory on the host
  • Kept for backwards compatibility with existing bind-mounted installs and plain docker compose up -d users
  • Works well on native Linux Docker hosts
  • Can have permission issues on Synology/QNAP
  • Can corrupt during MariaDB schema migrations on Docker Desktop for Windows/macOS, ARM hosts, and some virtualized filesystems
volumes:
  - youtarr-db-data:/var/lib/mysql
  • Better compatibility with Synology/QNAP
  • Avoids the virtualized-filesystem write semantics problem that can affect bind-mounted MariaDB
  • Used automatically for fresh installs started with ./start.sh on every platform (Linux included, since v1.69)
  • Recommended for Docker Desktop on Windows/macOS, ARM systems, and NAS setups
  • Not easily visible on host: data lives under /var/lib/docker/volumes/<project>_youtarr-db-data/_data rather than ./database/. To back it up, stop Youtarr with docker stop youtarr, then run ./scripts/backup.sh. Keep the database container in place; the script can start it if needed. Use docker stop rather than ./stop.sh, which removes containers. See Backup and Restore.

Migrating from Bind Mount to Named Volume​

If you already have Youtarr data in ./database/, do not switch the compose mount by hand unless you intentionally want to start with an empty database. Use the migration helper instead:

./scripts/migrate-to-named-volume.sh

What the script does (in this order, so any failure leaves the simplest possible recovery state):

  1. Runs a pre-flight permissions check so it fails fast (instead of stalling on an interactive sudo prompt) if it cannot write to the project directory.
  2. Stops Youtarr.
  3. Starts the existing bind-mounted MariaDB long enough to run mysqldump and to capture per-table row counts.
  4. Renames ./database/ to ./database.bind-mount-backup.<timestamp>/ so the original files are preserved.
  5. Starts a fresh named-volume MariaDB and imports the dump.
  6. Verifies that the table set matches the source and that every table has the same row count as the source.
  7. Only after verification succeeds, snapshots .env to ./.env.bak.<timestamp> and pins COMPOSE_PATH_SEPARATOR=: and COMPOSE_FILE=docker-compose.yml:docker-compose.arm.yml in .env. This means a failure during step 5 or 6 leaves .env untouched, and recovery is just mv ./database.bind-mount-backup.<timestamp> ./database plus removing the partial named volume.
  8. Brings the full stack (app + database) back up so Youtarr is immediately usable.

What the migration does not copy: mysqldump runs with --single-transaction --routines --triggers --events. Schema, data, stored routines, triggers, and events all migrate. MariaDB users and GRANT statements (anything in mysql.user / mysql.db) do not. The default Youtarr install only uses the bundled root user, so this is a no-op for almost everyone. If you have created additional database users on the bundled MariaDB, recreate them after the migration completes.

Password note: for the bundled root database user, DB_ROOT_PASSWORD seeds the root password when a fresh MariaDB data directory is initialized, while Youtarr connects with DB_PASSWORD. The migration requires those two values to match before it creates the new named-volume database.

After it completes, the stack is already running. Subsequent restarts can use any of:

./start.sh                                                       # recommended
docker compose up -d                                             # the script pins COMPOSE_FILE in .env
docker compose -f docker-compose.yml -f docker-compose.arm.yml up -d  # explicit override

Reverting to Bind Mount​

The migration is reversible:

  1. Stop the stack: 
    ./stop.sh
  2. Restore the .env snapshot: 
    mv ./.env.bak.<timestamp> .env
  3. Remove the named volume for this install. The name is usually <project>_youtarr-db-data: 
    docker volume ls --format '{{.Name}}' | grep -E '(^|_)youtarr-db-data$'
    docker volume rm <volume-name>
  4. Restore the original bind-mounted database directory: 
    mv ./database.bind-mount-backup.<timestamp> ./database
  5. Start Youtarr: 
    ./start.sh

Changes made while running on the named volume are not present in the old bind-mounted backup. If you have used the named volume for a while and want to keep those newer changes, take a backup first with ./scripts/backup.sh.

Fresh Installs with Named Volume​

For a new install with no data to preserve, you can start directly with the named-volume override:

docker compose -f docker-compose.yml -f docker-compose.arm.yml up -d

Or pin the override in .env so plain docker compose up -d uses it:

COMPOSE_PATH_SEPARATOR=:
COMPOSE_FILE=docker-compose.yml:docker-compose.arm.yml

COMPOSE_PATH_SEPARATOR=: is important on Windows so Compose parses the file list consistently.

Security Considerations​

Changing Default Credentials​

  1. Edit .env file:

    DB_USER=youtarr
    DB_PASSWORD=secure-password-here
    DB_ROOT_PASSWORD=different-secure-password

  2. If using non-root user, uncomment in docker-compose.yml:

    environment:
      - MYSQL_USER=${DB_USER}
      - MYSQL_PASSWORD=${DB_PASSWORD}

  3. Restart containers for changes to take effect

Warning: The bundled database is not exposed to the host by default. If you manually publish port 3321, keep it restricted to trusted hosts only.

External Database Setup​

Requirements​

  • MariaDB 10.3+ or MySQL 8.0+
  • Database with utf8mb4 character set
  • User with full privileges on the database
  • Network connectivity from Youtarr container

Step 1: Prepare External Database​

Run on your database server:

Note: The example below assumes you are using youtarr for your DB name and youtarr for your DB user.

CREATE DATABASE youtarr
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

CREATE USER 'youtarr'@'%'
  IDENTIFIED BY 'your-secure-password';

GRANT ALL PRIVILEGES ON youtarr.*
  TO 'youtarr'@'%';

FLUSH PRIVILEGES;

Replace '%' with specific IP/network if restricting access.

Step 2: Configure Youtarr​

Edit .env file:

DB_HOST=192.168.1.100  # Your database server IP
DB_PORT=3306           # Your database port
DB_USER=youtarr        # Database username
DB_PASSWORD=your-secure-password
DB_NAME=youtarr        # Database name

Step 3: Start with External Database​

Using convenience script:

./start-with-external-db.sh

Or manually:

docker compose -f docker-compose.external-db.yml up -d

Reverting to Internal Database​

Simply run the normal start script:

./start.sh

Database Migrations​

How Migrations Work​

  • Migrations run automatically on container startup
  • Tracked in SequelizeMeta table
  • Located in /app/migrations/ inside container
  • Idempotent - safe to run multiple times

Creating New Migrations​

# Use the provided script
./scripts/db-create-migration.sh my-migration-name

# Or use npm directly
npm run db:create-migration -- --name my-migration-name

Migration Best Practices​

  1. Always use helpers for idempotent operations:

    const { tableExists, columnExists, createTableIfNotExists } = require('./helpers');

    if (!await columnExists(queryInterface, 'videos', 'duration')) {
      await queryInterface.addColumn('videos', 'duration', {...});
    }

  2. Test migrations in development first

  3. Never modify existing migration files

  4. Create new migrations for schema changes

Troubleshooting Database Issues​

Permission Failures​

Symptoms​

  • InnoDB: Operating system error number 13
  • MariaDB container fails to start
  • Permission denied errors in logs

Common Causes​

  1. Synology/QNAP NAS: MariaDB runs as UID 999, which may not exist
  2. Docker Desktop/ARM/NAS: virtualized filesystem or permission issues with bind-mounted MariaDB data
  3. Wrong ownership: Database files owned by incorrect user

Solutions​

  1. Migrate to named volume (see above)
  2. Fix permissions: 
    # Check current ownership
    ls -la ./database

    # Fix ownership (adjust UID:GID as needed)
    sudo chown -R 999:999 ./database

Duplicate Column Errors​

Symptoms​

  • Duplicate column name 'duration'
  • Table 'channelvideos' already exists
  • Migration errors after crash/restore

Cause​

Lost or corrupted SequelizeMeta table causing migrations to re-run

Solution​

With recent updates, migrations are idempotent and self-healing:

  1. Simply restart the container:

    docker compose down
    docker compose up -d

  2. If errors persist, manually check:

    # Connect to database
    docker exec -it youtarr-db mysql -u root -p123qweasd youtarr

    # Check SequelizeMeta
    SELECT * FROM SequelizeMeta;

    # If missing, migrations will re-run safely

Connection Issues​

Cannot Connect to Database​

  1. Check container status:

    docker ps | grep youtarr-db

  2. Test connection:

    # From inside the database container
    docker compose exec youtarr-db mysql -u root -p123qweasd youtarr

  3. Check logs:

    docker logs youtarr-db

Authentication Failures​

  • Verify credentials match in .env and database
  • Check user permissions: SHOW GRANTS FOR 'youtarr'@'%';
  • Ensure user can connect from container IP

Character Set Issues​

Symptoms​

  • Emoji not saving correctly
  • UTF-8 encoding errors
  • Question marks in text

Solution​

Ensure database uses utf8mb4:

-- Check database charset
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'youtarr';

-- Convert if needed
ALTER DATABASE youtarr
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Tables on mixed collations (utf8mb4_general_ci beside utf8mb4_unicode_ci) make any query that compares string columns across them fail with Illegal mix of collations. The normalize-utf8mb4-unicode-collation migration fixes this automatically on startup; see Troubleshooting for the manual steps.

Storage Considerations​

Database Size Estimates​

  • Per Channel: ~1-2 KB metadata
  • Per Video: ~5-10 KB metadata
  • Growth Rate: ~10 MB per 1000 videos