Raycoon.Serilog.Sinks.SQLite 1.0.7

Raycoon.Serilog.Sinks.SQLite

A modern, high-performance Serilog sink for SQLite databases. Developed for .NET 8+ with full AnyCPU support.

Features

• AnyCPU compatible - Uses Microsoft.Data.Sqlite (no native SQLite required)
• .NET 8.0, .NET 9.0 & .NET 10.0 support
• Asynchronous batching - Optimal performance through batch writing
• Automatic retention - By time, count, or database size
• Custom columns - Store structured data in dedicated columns
• WAL mode - Optimized for high write load
• Thread-safe - Fully suitable for parallel logging
• Configurable - Extensive options for every use case

Installation

dotnet add package Raycoon.Serilog.Sinks.SQLite

Quick Start

Basic Usage

using Serilog;

var logger = new LoggerConfiguration()
    .WriteTo.SQLite("logs/app.db")
    .CreateLogger();

logger.Information("Hello, SQLite!");
logger.Error(new Exception("Oops!"), "An error occurred");

// Important: Dispose the logger at the end
await Log.CloseAndFlushAsync();

Advanced Configuration

using Serilog;
using Serilog.Events;
using Raycoon.Serilog.Sinks.SQLite.Options;

var logger = new LoggerConfiguration()
    .MinimumLevel.Debug()
    .Enrich.FromLogContext()
    .Enrich.WithThreadId()
    .WriteTo.SQLite("logs/app.db", options =>
    {
        // Table name
        options.TableName = "ApplicationLogs";

        // Retention: Delete logs older than 30 days
        options.RetentionPeriod = TimeSpan.FromDays(30);

        // Retention: Keep maximum 100,000 entries
        options.RetentionCount = 100_000;

        // Retention: Database max. 100 MB
        options.MaxDatabaseSize = 100 * 1024 * 1024;

        // Performance tuning
        options.BatchSizeLimit = 200;
        options.BatchPeriod = TimeSpan.FromSeconds(1);
        options.QueueLimit = 50000;

        // SQLite optimizations
        options.JournalMode = SQLiteJournalMode.Wal;
        options.SynchronousMode = SQLiteSynchronousMode.Normal;

        // Store timestamps in UTC
        options.StoreTimestampInUtc = true;

        // Minimum log level for this sink
        options.RestrictedToMinimumLevel = LogEventLevel.Information;
    })
    .CreateLogger();

Custom Columns

Store structured data in dedicated columns for better queries:

var logger = new LoggerConfiguration()
    .WriteTo.SQLite("logs/app.db", options =>
    {
        options.CustomColumns.Add(new CustomColumn
        {
            ColumnName = "UserId",
            DataType = "TEXT",
            PropertyName = "UserId",
            CreateIndex = true // Index for fast searches
        });

        options.CustomColumns.Add(new CustomColumn
        {
            ColumnName = "RequestId",
            DataType = "TEXT",
            PropertyName = "RequestId"
        });

        options.CustomColumns.Add(new CustomColumn
        {
            ColumnName = "Duration",
            DataType = "REAL",
            PropertyName = "DurationMs"
        });
    })
    .CreateLogger();

// Usage
logger
    .ForContext("UserId", "user123")
    .ForContext("RequestId", Guid.NewGuid())
    .ForContext("DurationMs", 42.5)
    .Information("Request processed");

Error Handling

var logger = new LoggerConfiguration()
    .WriteTo.SQLite("logs/app.db", options =>
    {
        options.OnError = ex =>
        {
            Console.WriteLine($"SQLite Error: {ex.Message}");
            // Or: Use a fallback logger
        };

        // Throw exception on critical errors
        options.ThrowOnError = false; // Default: false
    })
    .CreateLogger();

JSON Configuration (appsettings.json)

The sink supports full configuration via appsettings.json using Serilog.Settings.Configuration:

dotnet add package Serilog.Settings.Configuration
dotnet add package Microsoft.Extensions.Configuration.Json

Basic JSON Configuration

{
  "Serilog": {
    "Using": ["Raycoon.Serilog.Sinks.SQLite"],
    "MinimumLevel": "Information",
    "WriteTo": [
      {
        "Name": "SQLite",
        "Args": {
          "databasePath": "logs/app.db"
        }
      }
    ]
  }
}

Full JSON Configuration

{
  "Serilog": {
    "Using": ["Raycoon.Serilog.Sinks.SQLite"],
    "MinimumLevel": {
      "Default": "Information",
      "Override": {
        "Microsoft": "Warning",
        "System": "Warning"
      }
    },
    "WriteTo": [
      {
        "Name": "SQLite",
        "Args": {
          "databasePath": "logs/app.db",
          "tableName": "ApplicationLogs",
          "restrictedToMinimumLevel": "Information",
          "storeTimestampInUtc": true,
          "autoCreateDatabase": true,
          "storePropertiesAsJson": true,
          "storeExceptionDetails": true,
          "maxMessageLength": 10000,
          "maxExceptionLength": 20000,
          "maxPropertiesLength": 10000,
          "batchSizeLimit": 200,
          "batchPeriod": "00:00:01",
          "queueLimit": 50000,
          "retentionPeriod": "30.00:00:00",
          "retentionCount": 100000,
          "maxDatabaseSize": 104857600,
          "cleanupInterval": "01:00:00",
          "journalMode": "Wal",
          "synchronousMode": "Normal",
          "throwOnError": false,
          "customColumns": [
            {
              "columnName": "UserId",
              "dataType": "TEXT",
              "propertyName": "UserId",
              "allowNull": true,
              "createIndex": true
            },
            {
              "columnName": "RequestId",
              "dataType": "TEXT",
              "propertyName": "RequestId",
              "allowNull": true,
              "createIndex": false
            },
            {
              "columnName": "Duration",
              "dataType": "REAL",
              "propertyName": "DurationMs",
              "allowNull": true,
              "createIndex": false
            }
          ]
        }
      }
    ],
    "Enrich": ["FromLogContext"]
  }
}

C# Setup for JSON Configuration

using Microsoft.Extensions.Configuration;
using Serilog;

var configuration = new ConfigurationBuilder()
    .SetBasePath(Directory.GetCurrentDirectory())
    .AddJsonFile("appsettings.json")
    .Build();

var logger = new LoggerConfiguration()
    .ReadFrom.Configuration(configuration)
    .CreateLogger();

TimeSpan Format in JSON

Enum Values in JSON

Limitations

Note: This is a fundamental limitation of the Serilog ecosystem, not specific to this sink. All Serilog sinks face this constraint because Serilog.Settings.Configuration can only bind serializable types from JSON.

The following options are not available via JSON configuration:

• OnError callback (delegates cannot be serialized)
• AdditionalConnectionParameters (dictionary binding is complex)

Option 1: Full Programmatic Configuration (Recommended when callbacks are needed)

Use the action-based overload for complete control:

var logger = new LoggerConfiguration()
    .WriteTo.SQLite("logs/app.db", options =>
    {
        // All settings configured programmatically
        options.TableName = "ApplicationLogs";
        options.RetentionPeriod = TimeSpan.FromDays(30);
        options.BatchSizeLimit = 200;
        options.JournalMode = SQLiteJournalMode.Wal;

        // Callback options (not available via JSON)
        options.OnError = ex => Console.WriteLine($"SQLite error: {ex.Message}");
        options.AdditionalConnectionParameters["Password"] = "mySecurePassword";
    })
    .CreateLogger();

Option 2: Hybrid Approach (JSON for other sinks, programmatic for SQLite)

If you need JSON configuration for other parts of your logging pipeline:

var configuration = new ConfigurationBuilder()
    .AddJsonFile("appsettings.json")
    .Build();

var logger = new LoggerConfiguration()
    .ReadFrom.Configuration(configuration)  // Loads MinimumLevel, other sinks, enrichers
    .WriteTo.SQLite("logs/app.db", options =>
    {
        // SQLite-specific settings with callback support
        options.TableName = "ApplicationLogs";
        options.OnError = ex => MyErrorHandler(ex);
    })
    .CreateLogger();

Why can't I just add callbacks to a JSON-configured sink?

Serilog's architecture creates sinks during ReadFrom.Configuration() and doesn't expose them for modification afterwards. This is by design for thread-safety and immutability.

Database Schema

The sink automatically creates the following table:

Plus all configured custom columns.

Querying Logs

-- All errors from the last 24 hours
SELECT * FROM Logs
WHERE Level >= 4
AND Timestamp > datetime('now', '-1 day')
ORDER BY Timestamp DESC;

-- Logs by UserId (if custom column configured)
SELECT * FROM Logs
WHERE UserId = 'user123'
ORDER BY Timestamp DESC
LIMIT 100;

-- Aggregation by level
SELECT LevelName, COUNT(*) as Count
FROM Logs
GROUP BY Level;

-- Search through properties (JSON)
SELECT * FROM Logs
WHERE json_extract(Properties, '$.RequestId') = 'abc123';

Performance Tips

1. Optimize Batch Size

options.BatchSizeLimit = 500;  // For high-volume
options.BatchPeriod = TimeSpan.FromMilliseconds(100);

2. Use WAL Mode (Default)

options.JournalMode = SQLiteJournalMode.Wal;

3. Adjust Synchronous Mode

// Faster, but less safe in case of power failure
options.SynchronousMode = SQLiteSynchronousMode.Normal;

// Or for maximum performance (only if data loss is acceptable)
options.SynchronousMode = SQLiteSynchronousMode.Off;

4. Set Queue Limit

// Prevents memory overflow during burst traffic
options.QueueLimit = 100000;

Comparison to Other SQLite Sinks

API Reference

SQLiteSinkOptions

Database

Logging Behavior

Data Limits

Batching & Performance

Retention Policy

SQLite Configuration

License

Apache 2.0 - See LICENSE for details.