< Summary - Jellyfin

Information
Class: Jellyfin.Database.Providers.Sqlite.SqliteDatabaseProvider
Assembly: Jellyfin.Database.Providers.Sqlite
File(s): /srv/git/jellyfin/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs
Line coverage
50%
Covered lines: 91
Uncovered lines: 88
Coverable lines: 179
Total lines: 424
Line coverage: 50.8%
Branch coverage
38%
Covered branches: 19
Total branches: 50
Branch coverage: 38%
Method coverage

Feature is only available for sponsors

Upgrade to PRO version

Coverage history

Coverage history 0 25 50 75 100 5/11/2026 - 12:15:59 AM Line coverage: 41.8% (36/86) Branch coverage: 31.2% (5/16) Total lines: 2115/20/2026 - 12:15:44 AM Line coverage: 41.8% (36/86) Branch coverage: 25% (4/16) Total lines: 2118/9/2026 - 12:16:58 AM Line coverage: 44.4% (40/90) Branch coverage: 25% (4/16) Total lines: 21510/29/2026 - 12:17:11 AM Line coverage: 50.8% (91/179) Branch coverage: 38% (19/50) Total lines: 424 5/11/2026 - 12:15:59 AM Line coverage: 41.8% (36/86) Branch coverage: 31.2% (5/16) Total lines: 2115/20/2026 - 12:15:44 AM Line coverage: 41.8% (36/86) Branch coverage: 25% (4/16) Total lines: 2118/9/2026 - 12:16:58 AM Line coverage: 44.4% (40/90) Branch coverage: 25% (4/16) Total lines: 21510/29/2026 - 12:17:11 AM Line coverage: 50.8% (91/179) Branch coverage: 38% (19/50) Total lines: 424

Coverage delta

Coverage delta 13 -13

Metrics

MethodBranch coverage Crap Score Cyclomatic complexity Line coverage
.ctor(...)100%11100%
Initialise(...)50%8894.28%
RunScheduledOptimisation(...)100%11100%
RefreshStatistics()75%4490%
OnModelCreating(...)100%11100%
RunShutdownTask()0%620%
OptimizeAsync()87.5%8896.15%
HasLibraryItemsAsync(...)100%11100%
AnalyzeAsync()100%22100%
ReadPragmaValueAsync()50%66100%
ConfigureConventions(...)100%11100%
MigrationBackupFast()0%4260%
RestoreBackupFast(...)0%2040%
TryRetireWriteAheadLog(...)0%2040%
DeleteBackup(...)0%620%
GetDatabasePath()0%620%
PurgeDatabase()0%620%

File(s)

/srv/git/jellyfin/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs

#LineLine coverage
 1using System;
 2using System.Collections.Generic;
 3using System.Data.Common;
 4using System.Globalization;
 5using System.IO;
 6using System.Linq;
 7using System.Threading;
 8using System.Threading.Tasks;
 9using Jellyfin.Database.Implementations;
 10using Jellyfin.Database.Implementations.DbConfiguration;
 11using MediaBrowser.Common.Configuration;
 12using Microsoft.Data.Sqlite;
 13using Microsoft.EntityFrameworkCore;
 14using Microsoft.EntityFrameworkCore.Diagnostics;
 15using Microsoft.Extensions.Logging;
 16
 17namespace Jellyfin.Database.Providers.Sqlite;
 18
 19/// <summary>
 20/// Configures jellyfin to use an SQLite database.
 21/// </summary>
 22[JellyfinDatabaseProviderKey("Jellyfin-SQLite")]
 23public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider
 24{
 25    private const string BackupFolderName = "SQLiteBackups";
 26    private readonly IApplicationPaths _applicationPaths;
 27    private readonly ILogger<SqliteDatabaseProvider> _logger;
 28
 29    /// <summary>
 30    /// Initializes a new instance of the <see cref="SqliteDatabaseProvider"/> class.
 31    /// </summary>
 32    /// <param name="applicationPaths">Service to construct the fallback when the old data path configuration is used.</
 33    /// <param name="logger">A logger.</param>
 34    public SqliteDatabaseProvider(IApplicationPaths applicationPaths, ILogger<SqliteDatabaseProvider> logger)
 35    {
 95336        _applicationPaths = applicationPaths;
 95337        _logger = logger;
 95338    }
 39
 40    /// <inheritdoc/>
 41    public IDbContextFactory<JellyfinDbContext>? DbContextFactory { get; set; }
 42
 43    /// <inheritdoc/>
 44    public void Initialise(DbContextOptionsBuilder options, DatabaseConfigurationOptions databaseConfiguration)
 45    {
 46        static T? GetOption<T>(ICollection<CustomDatabaseOption>? options, string key, Func<string, T> converter, Func<T
 47        {
 48            if (options is null)
 49            {
 50                return defaultValue is not null ? defaultValue() : default;
 51            }
 52
 53            var value = options.FirstOrDefault(e => e.Key.Equals(key, StringComparison.OrdinalIgnoreCase));
 54            if (value is null)
 55            {
 56                return defaultValue is not null ? defaultValue() : default;
 57            }
 58
 59            return converter(value.Value);
 60        }
 61
 6362        var customOptions = databaseConfiguration.CustomProviderOptions?.Options;
 63
 6364        var sqliteConnectionBuilder = new SqliteConnectionStringBuilder
 6365        {
 6366            DataSource = GetOption(customOptions, "path", e => e, () => Path.Combine(_applicationPaths.DataPath, "jellyf
 6367            // Private, not Default: sqlite3_enable_shared_cache is process-global, so a plugin
 6368            // enabling it makes these connections share a cache too. Contention then surfaces as
 6369            // SQLITE_LOCKED ("database table is locked"), which the busy handler does not cover,
 6370            // so busy_timeout is skipped and the command fails at CommandTimeout instead.
 6371            Cache = GetOption(customOptions, "cache", Enum.Parse<SqliteCacheMode>, () => SqliteCacheMode.Private),
 6372            Pooling = GetOption(customOptions, "pooling", e => e.Equals(bool.TrueString, StringComparison.OrdinalIgnoreC
 6373            DefaultTimeout = GetOption(customOptions, "command-timeout", int.Parse, () => 60)
 6374        };
 75
 6376        var connectionString = sqliteConnectionBuilder.ToString();
 77
 78        // Log SQLite connection parameters
 6379        _logger.LogInformation("SQLite connection string: {ConnectionString}", connectionString);
 80
 6381        options
 6382            .UseSqlite(
 6383                connectionString,
 6384                sqLiteOptions => sqLiteOptions.MigrationsAssembly(GetType().Assembly))
 6385            // TODO: Remove when https://github.com/dotnet/efcore/pull/35873 is merged & released
 6386            .ConfigureWarnings(warnings =>
 6387                warnings.Ignore(RelationalEventId.NonTransactionalMigrationOperationWarning)
 6388                    .Ignore(RelationalEventId.MultipleCollectionIncludeWarning))
 6389            .AddInterceptors(new PragmaConnectionInterceptor(
 6390                _logger,
 6391                GetOption<int?>(customOptions, "cacheSize", e => int.Parse(e, CultureInfo.InvariantCulture)),
 6392                GetOption(customOptions, "lockingmode", e => e, () => "NORMAL")!,
 6393                GetOption(customOptions, "journalsizelimit", int.Parse, () => 134_217_728),
 6394                GetOption(customOptions, "tempstoremode", int.Parse, () => 2),
 6395                GetOption(customOptions, "syncmode", int.Parse, () => 1),
 6396                customOptions?.Where(e => e.Key.StartsWith("#PRAGMA:", StringComparison.OrdinalIgnoreCase)).ToDictionary
 97
 6398        var enableSensitiveDataLogging = GetOption(customOptions, "EnableSensitiveDataLogging", e => e.Equals(bool.TrueS
 6399        if (enableSensitiveDataLogging)
 100        {
 0101            options.EnableSensitiveDataLogging(enableSensitiveDataLogging);
 0102            _logger.LogInformation("EnableSensitiveDataLogging is enabled on SQLite connection");
 103        }
 63104    }
 105
 106    /// <inheritdoc/>
 107    public Task RunScheduledOptimisation(CancellationToken cancellationToken)
 108    {
 2109        return OptimizeAsync(cancellationToken);
 110    }
 111
 112    /// <inheritdoc/>
 113    public async Task RefreshStatistics(CancellationToken cancellationToken)
 114    {
 23115        if (DbContextFactory is null)
 116        {
 20117            return;
 118        }
 119
 3120        var context = await DbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
 3121        await using (context.ConfigureAwait(false))
 122        {
 3123            await context.Database.OpenConnectionAsync(cancellationToken).ConfigureAwait(false);
 124            try
 125            {
 3126                if (!await HasLibraryItemsAsync(context, cancellationToken).ConfigureAwait(false))
 127                {
 128                    return;
 129                }
 130
 2131                _logger.LogInformation("Analyzing jellyfin.db");
 2132                await AnalyzeAsync(context, cancellationToken).ConfigureAwait(false);
 133            }
 134            finally
 135            {
 3136                await context.Database.CloseConnectionAsync().ConfigureAwait(false);
 137            }
 138        }
 23139    }
 140
 141    /// <inheritdoc/>
 142    public void OnModelCreating(ModelBuilder modelBuilder)
 143    {
 4144        modelBuilder.SetDefaultDateTimeKind(DateTimeKind.Utc);
 4145    }
 146
 147    /// <inheritdoc/>
 148    public async Task RunShutdownTask(CancellationToken cancellationToken)
 149    {
 150        // Run before disposing the application. Only a checkpoint: stopping is on a deadline.
 151
 152        // Empty the pool first. Anything still parked in it can start reading again between here and the
 153        // checkpoint, and a reader that holds the write-ahead log open is exactly what makes the truncation
 154        // fail. Connections handed out already cannot be taken away, but they get disposed on return.
 0155        SqliteConnection.ClearAllPools();
 156
 157        try
 158        {
 0159            if (DbContextFactory is not null)
 160            {
 0161                var context = await DbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
 0162                await using (context.ConfigureAwait(false))
 163                {
 0164                    await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).Conf
 165                }
 166            }
 0167        }
 0168        catch (Exception ex)
 169        {
 170            // A missed checkpoint only leaves a write-ahead log for the next start to replay, so never fail the
 171            // shutdown over this.
 0172            _logger.LogError(ex, "Error while checkpointing jellyfin.db");
 0173        }
 174
 175        // The checkpointing connection went back into the pool, so retire that one as well.
 0176        SqliteConnection.ClearAllPools();
 0177    }
 178
 179    private async Task OptimizeAsync(CancellationToken cancellationToken)
 180    {
 2181        if (DbContextFactory is null)
 182        {
 0183            return;
 184        }
 185
 2186        var context = await DbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
 2187        await using (context.ConfigureAwait(false))
 188        {
 2189            await context.Database.OpenConnectionAsync(cancellationToken).ConfigureAwait(false);
 190            try
 191            {
 192                long? tempStore;
 2193                var pragmaCommand = context.Database.GetDbConnection().CreateCommand();
 2194                await using (pragmaCommand.ConfigureAwait(false))
 195                {
 2196                    pragmaCommand.CommandText = "PRAGMA temp_store";
 2197                    tempStore = await ReadPragmaValueAsync(pragmaCommand, cancellationToken).ConfigureAwait(false);
 198                }
 199
 2200                await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).Configur
 201
 2202                _logger.LogDebug(
 2203                    "Rebuilding jellyfin.db on disk, scratch space goes to {TempDirectory}",
 2204                    Environment.GetEnvironmentVariable("SQLITE_TMPDIR") ?? "SQLite's default temporary directory");
 2205                await context.Database.ExecuteSqlRawAsync("PRAGMA temp_store=1", cancellationToken).ConfigureAwait(false
 206                try
 207                {
 2208                    await context.Database.ExecuteSqlRawAsync("VACUUM", cancellationToken).ConfigureAwait(false);
 209                }
 210                finally
 211                {
 212                    // The connection goes back to the pool, so hand it over the way it was handed to us.
 2213                    if (tempStore is not null)
 214                    {
 2215                        await context.Database.ExecuteSqlRawAsync(
 2216                            FormattableString.Invariant($"PRAGMA temp_store={tempStore.Value}"),
 2217                            CancellationToken.None).ConfigureAwait(false);
 218                    }
 219                }
 220
 221                // Statistics taken while the library is empty make the planner treat every table as one row and
 222                // pick full scans once it fills up; no statistics at all plan far better until there is data.
 2223                if (await HasLibraryItemsAsync(context, cancellationToken).ConfigureAwait(false))
 224                {
 1225                    await AnalyzeAsync(context, cancellationToken).ConfigureAwait(false);
 226                }
 227                else
 228                {
 1229                    _logger.LogInformation("Not analyzing jellyfin.db, the library holds no items yet");
 230                }
 231
 2232                await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).Configur
 2233                _logger.LogInformation("jellyfin.db optimized successfully!");
 234            }
 235            finally
 236            {
 2237                await context.Database.CloseConnectionAsync().ConfigureAwait(false);
 238            }
 239        }
 2240    }
 241
 242    private static Task<bool> HasLibraryItemsAsync(JellyfinDbContext context, CancellationToken cancellationToken)
 243    {
 244        // Folders and the seeded placeholder exist before any library has been scanned.
 5245        return context.BaseItems.AnyAsync(e => !e.IsFolder && e.Type != "PLACEHOLDER", cancellationToken);
 246    }
 247
 248    private static async Task AnalyzeAsync(JellyfinDbContext context, CancellationToken cancellationToken)
 249    {
 250        long? analysisLimit;
 3251        var pragmaCommand = context.Database.GetDbConnection().CreateCommand();
 3252        await using (pragmaCommand.ConfigureAwait(false))
 253        {
 3254            pragmaCommand.CommandText = "PRAGMA analysis_limit";
 3255            analysisLimit = await ReadPragmaValueAsync(pragmaCommand, cancellationToken).ConfigureAwait(false);
 256        }
 257
 3258        await context.Database.ExecuteSqlRawAsync("PRAGMA analysis_limit=0", cancellationToken).ConfigureAwait(false);
 259        try
 260        {
 3261            await context.Database.ExecuteSqlRawAsync("ANALYZE", cancellationToken).ConfigureAwait(false);
 262        }
 263        finally
 264        {
 265            // The connection goes back to the pool, so hand it over the way it was handed to us.
 3266            if (analysisLimit is not null)
 267            {
 3268                await context.Database.ExecuteSqlRawAsync(
 3269                    FormattableString.Invariant($"PRAGMA analysis_limit={analysisLimit.Value}"),
 3270                    CancellationToken.None).ConfigureAwait(false);
 271            }
 272        }
 3273    }
 274
 275    private static async Task<long?> ReadPragmaValueAsync(DbCommand command, CancellationToken cancellationToken)
 276    {
 5277        var value = await command.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false);
 5278        return value is null or DBNull ? null : Convert.ToInt64(value, CultureInfo.InvariantCulture);
 5279    }
 280
 281    /// <inheritdoc/>
 282    public void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
 283    {
 4284        configurationBuilder.Conventions.Add(_ => new DoNotUseReturningClauseConvention());
 4285    }
 286
 287    /// <inheritdoc />
 288    public async Task<string> MigrationBackupFast(CancellationToken cancellationToken)
 289    {
 0290        var path = GetDatabasePath();
 0291        var backupFolder = Path.Combine(_applicationPaths.DataPath, BackupFolderName);
 0292        Directory.CreateDirectory(backupFolder);
 293
 0294        if (DbContextFactory is not null && File.Exists(path))
 295        {
 0296            var context = await DbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
 0297            await using (context.ConfigureAwait(false))
 298            {
 0299                await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).Configur
 300            }
 301        }
 302
 0303        var key = DateTime.UtcNow.ToString("yyyyMMddHHmmss", CultureInfo.InvariantCulture);
 0304        var backupFile = Path.Combine(backupFolder, $"{key}_jellyfin.db");
 0305        for (var attempt = 1; File.Exists(backupFile); attempt++)
 306        {
 0307            key = string.Create(CultureInfo.InvariantCulture, $"{DateTime.UtcNow:yyyyMMddHHmmss}_{attempt}");
 0308            backupFile = Path.Combine(backupFolder, $"{key}_jellyfin.db");
 309        }
 310
 0311        File.Copy(path, backupFile);
 0312        return key;
 0313    }
 314
 315    /// <inheritdoc />
 316    public Task RestoreBackupFast(string key, CancellationToken cancellationToken)
 317    {
 318        // ensure there are absolutely no dangling Sqlite connections.
 0319        SqliteConnection.ClearAllPools();
 0320        var path = GetDatabasePath();
 0321        var backupFile = Path.Combine(_applicationPaths.DataPath, BackupFolderName, $"{key}_jellyfin.db");
 322
 0323        if (!File.Exists(backupFile))
 324        {
 0325            _logger.LogCritical("Tried to restore a backup that does not exist: {Key}", key);
 0326            return Task.CompletedTask;
 327        }
 328
 0329        if (!TryRetireWriteAheadLog(path))
 330        {
 0331            _logger.LogCritical(
 0332                "Refusing to restore jellyfin.db: the write-ahead log at {WriteAheadLog} could not be retired, which "
 0333                + "means the database is still open and replacing it now would silently bring back the data this "
 0334                + "rollback is undoing. Stop the server and copy {Backup} over {Path} by hand.",
 0335                path + "-wal",
 0336                backupFile,
 0337                path);
 0338            return Task.CompletedTask;
 339        }
 340
 0341        File.Copy(backupFile, path, true);
 342
 0343        return Task.CompletedTask;
 344    }
 345
 346    private bool TryRetireWriteAheadLog(string path)
 347    {
 0348        var writeAheadLogPath = path + "-wal";
 0349        if (!File.Exists(path) || !File.Exists(writeAheadLogPath))
 350        {
 0351            return true;
 352        }
 353
 354        try
 355        {
 0356            var connectionString = new SqliteConnectionStringBuilder
 0357            {
 0358                DataSource = path,
 0359                Mode = SqliteOpenMode.ReadWrite,
 0360                Pooling = false
 0361            }.ToString();
 362
 0363            using var connection = new SqliteConnection(connectionString);
 0364            connection.Open();
 0365            using var command = connection.CreateCommand();
 0366            command.CommandText = "PRAGMA wal_checkpoint(TRUNCATE)";
 0367            command.ExecuteNonQuery();
 0368        }
 0369        catch (SqliteException ex)
 370        {
 371            // Either something else holds the database or it is too damaged to open. The check below covers both.
 0372            _logger.LogError(ex, "Could not open jellyfin.db to retire its write-ahead log");
 0373        }
 374
 0375        return !File.Exists(writeAheadLogPath);
 376    }
 377
 378    /// <inheritdoc />
 379    public Task DeleteBackup(string key)
 380    {
 0381        var backupFile = Path.Combine(_applicationPaths.DataPath, BackupFolderName, $"{key}_jellyfin.db");
 382
 0383        if (!File.Exists(backupFile))
 384        {
 0385            _logger.LogCritical("Tried to delete a backup that does not exist: {Key}", key);
 0386            return Task.CompletedTask;
 387        }
 388
 0389        File.Delete(backupFile);
 0390        return Task.CompletedTask;
 391    }
 392
 393    private string GetDatabasePath()
 394    {
 0395        if (DbContextFactory is null)
 396        {
 0397            throw new InvalidOperationException("The database provider has not been initialized.");
 398        }
 399
 0400        using var dbContext = DbContextFactory.CreateDbContext();
 0401        return dbContext.Database.GetDbConnection().DataSource;
 0402    }
 403
 404    /// <inheritdoc/>
 405    public async Task PurgeDatabase(JellyfinDbContext dbContext, IEnumerable<string>? tableNames)
 406    {
 0407        ArgumentNullException.ThrowIfNull(tableNames);
 408
 0409        var deleteQueries = new List<string>();
 0410        foreach (var tableName in tableNames)
 411        {
 0412            deleteQueries.Add($"DELETE FROM \"{tableName}\";");
 413        }
 414
 0415        var deleteAllQuery =
 0416        $"""
 0417        PRAGMA foreign_keys = OFF;
 0418        {string.Join('\n', deleteQueries)}
 0419        PRAGMA foreign_keys = ON;
 0420        """;
 421
 0422        await dbContext.Database.ExecuteSqlRawAsync(deleteAllQuery).ConfigureAwait(false);
 0423    }
 424}
Generated by: ReportGenerator 5.5.10.0
10/31/2026 - 12:17:48 AM
GitHub | reportgenerator.io

Methods/Properties

.ctor(MediaBrowser.Common.Configuration.IApplicationPaths,Microsoft.Extensions.Logging.ILogger`1<Jellyfin.Database.Providers.Sqlite.SqliteDatabaseProvider>)
Initialise(Microsoft.EntityFrameworkCore.DbContextOptionsBuilder,Jellyfin.Database.Implementations.DbConfiguration.DatabaseConfigurationOptions)
RunScheduledOptimisation(System.Threading.CancellationToken)
RefreshStatistics()
OnModelCreating(Microsoft.EntityFrameworkCore.ModelBuilder)
RunShutdownTask()
OptimizeAsync()
HasLibraryItemsAsync(Jellyfin.Database.Implementations.JellyfinDbContext,System.Threading.CancellationToken)
AnalyzeAsync()
ReadPragmaValueAsync()
ConfigureConventions(Microsoft.EntityFrameworkCore.ModelConfigurationBuilder)
MigrationBackupFast()
RestoreBackupFast(System.String,System.Threading.CancellationToken)
TryRetireWriteAheadLog(System.String)
DeleteBackup(System.String)
GetDatabasePath()
PurgeDatabase()