Raycoon.Serilog.Sinks.SQLite 1.0.0

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();

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.

Contributing

Pull requests are welcome! Please open an issue first to discuss proposed changes.

Changelog

1.0.0

• Initial Release
• .NET 8.0, .NET 9.0 and .NET 10.0 support
• AnyCPU compatibility with Microsoft.Data.Sqlite
• Async batching
• Retention policies (time, count, size)
• Custom columns
• WAL mode support