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. feed_checked_at (datetime, nullable): when the hourly feed check last confirmed the listing had every upload in the channel's YouTube RSS feeds (public and members-only); a tab with a recent value is not refreshed when its channel page opens. Non-unique index on channel_id (some installs carry duplicate rows).
videosVideoDownloaded video metadata. removed (boolean): no file on disk. removed_reason (VARCHAR(32), nullable) says why: user_deleted (deleted in the app), auto_removal (automatic cleanup; removed_detail VARCHAR(255) holds the rule, e.g. "older than 30 days"), not_found (a file was expected on disk and not found: the video is missing), or marked_deleted (the user marked a missing video as deleted). Every other removed row, including NULL (removed before the reason was recorded), is deleted. removed_at (DATETIME, nullable): when it was removed. Deleted videos are hidden on the Videos page by default; a file that reappears clears all three columns. Index on (removed, removed_reason). 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). External API policy fields store the key role, request permissions and auto-approval settings, allowed media types, rating limits, revocation time, and write quotas. Existing keys are assigned the legacy_download role.
api_key_channel_grantsApiKeyChannelGrantChannels granted to an API key. Unique index on (api_key_id, channel_id).
external_requestsExternalRequestExternal API request lifecycle records, including the requesting key, optional channel and job, status, idempotency hash, and completion timestamps. approval_source records how an approved request was approved (auto for key auto-approval, admin for an administrator decision); the background executor rechecks auto-approval only for auto rows.
external_api_usage_bucketsExternalApiUsageBucketPer-key hourly and daily accepted-write counters. Unique index on (api_key_id, window_type, window_start).
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.
video_members_accessVideoMembersAccessOne row per members-only video Youtarr has seen (a row marks the video members-only even when its channel is not tracked): youtube_id (primary key), access (confirmed/denied, or NULL when no verdict was recorded), cookie_key (SHA-256 of the cookie file the verdict was made with), checked_at. A verdict counts for 24 hours and only while the same cookie file is configured. Index on (access, checked_at). Feeds the members-only match filter for downloads and members_access on the channel and playlist listings.
arr_bridge_grabsArrBridgeGrabOne row per release Sonarr or Radarr grabbed through the Sonarr/Radarr bridge: id (UUID; also the SABnzbd nzo_id), youtube_id, kind (tv/movie), category (the SABnzbd category the app sent), release_title (also the staging folder and file name), status (pending/queued/completed/failed/removed), job_id (the Youtarr download job of the current attempt; no foreign key; follows automatic retries), staged_path, bytes, estimated_bytes, fail_message (kept after removal, so a failed video is not offered again for 7 days), library_copy (for the "remove after import" setting: owned when Youtarr downloaded the video for the grab, remove while the import is being confirmed, then removed or kept; NULL when the video was already in the library), blocked_at (when YouTube first blocked this grab's download; set while the grab waits to be tried again, and the grab fails once it is a day old and the download is blocked still), created_at, updated_at, completed_at, removed_at. Indexes on (status, updated_at) and youtube_id. Empty unless the bridge is switched on.
arr_bridge_searchesArrBridgeSearchOne row per search Sonarr or Radarr sent through the Sonarr/Radarr bridge, for the Settings page: id, at (when the request arrived), first_at and count (the no-query requests of an RSS sync share one row per kind, counted), kind (tv/movie), shape (episode/season/daily/movie/rss/empty), outcome (offered/nothing/not-searched/failed), offered, note, title_stage_skipped, search_text (what the list's filter box matches), asked and stages (JSON stored as text: what was asked, and every YouTube search with what became of each result). Index on at. Rows older than 30 days are never listed; they, and rows past the newest 2,000, are removed after a later search is stored. It never holds a key or a feed link. Empty unless the bridge is switched on.
SequelizeMetaNASequelize ORM migration tracking

External API Policy Foundation​

The external API policy fields and supporting tables are enforced when EXTERNAL_API_ENABLED=true; the runtime is disabled by default. Existing keys retain their single-video download behavior and are backfilled with the legacy_download role. API-key values remain hashed and cannot be recovered from the database. Revocation is represented by revoked_at together with the key's active status. The runtime adds two request-history indexes for catalog status and administrator queue queries; see External API query plans for measured plans and rollback behavior.

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.

If you use docker-compose.override.yml (for example, to mount a NAS share): once COMPOSE_FILE is pinned, Compose no longer loads the override automatically, and the script does not add it. Add it to COMPOSE_FILE before docker-compose.arm.yml right after the migration, or whatever the override configured (such as your video share) disappears from the containers. See Keeping docker-compose.override.yml.

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