Logging using Serilog to SQLite sink

Question

I'm not able to make SeriLog work with SQLite sink.

My project setup is as follows:

Packages I have installed:	My SQLite database:

My appsettings.json file looks like this:

{
  "Serilog": {
    "MinimumLevel": {
      "Default": "Information",
      "Override": {
        "System": "Error",
        "Microsoft": "Error"
      }
    },
    "WriteTo": [
      {
        "Name": "SQLite",
        "Args": {
          "connectionString": "Data Source=WebApp.db",
          "tableName": "Log"
        }
      },
      {
        "Name": "Console",
        "Args": {
          "outputTemplate": "{Timestamp:yyyy-MM-dd HH:mm:ss} {MachineName} {EnvironmentUserName} [{Level:u4}] <{ThreadId}> [{SourceContext:l}] {Message:lj}{NewLine}{Exception}"
        },
        "theme": "AnsiConsoleTheme.Literate"
      }
    ],
    "Enrich": [ "FromLogContext", "WithMachineName", "WithThreadId", "WithEnvironmentUserName" ]
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "WebAppConnection": "Data Source=WebApp.db"
  }
}

My AppDbContext file looks like this (shows my Log table):

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { } // send DbContextOptions to base from constructor. Dbcontext provides information such as connection string, db provider etc.

    public DbSet<Log> Logs { get; set; }
}

Log model looks like this:

public class Log
{
    public int Id { get; set; }
    [Column(TypeName = "VARCHAR(500)")]
    public string Message { get; set; }
    [Column(TypeName = "VARCHAR(500)")]
    public string MessageTemplate { get; set; }
    [Column(TypeName = "VARCHAR(500)")]
    public string Level { get; set; }
    public DateTime TimeStamp { get; set; }
    [Column(TypeName = "VARCHAR(800)")]
    public string Exception { get; set; }
    [Column(TypeName = "VARCHAR(500)")]
    public string Properties { get; set; }
}

My Program.cs looks like this:

public class Program
{
    public static void Main(string[] args)
    {
        // I added this portion - start.
        var environmentName = Environment.GetEnvironmentVariable("DOTNET_ENVIRONMENT") ?? "EMPTYENVIRONMENTNAME";
        var config = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
            .AddJsonFile($"appsettings.{environmentName}.json", optional: true, reloadOnChange: true)
            .AddEnvironmentVariables()
            .Build();

        ConfigureLogger(config);
 
        // I added this portion - end.

        try
        {
            CreateHostBuilder(args).Build().Run(); // I wrapped this inside try catch.
        }
        catch (Exception ex)
        {
            Serilog.Log.Fatal(ex, "Host terminated unexpectedly");
        }
        finally
        {
            Serilog.Log.Information("Web host stopped.");
            Serilog.Log.CloseAndFlush(); // <-- I added this.
        }
    }

    public static IHostBuilder CreateHostBuilder(string[] args) =>
        Host.CreateDefaultBuilder(args)
            .UseSerilog() // <-- I added this.
            .ConfigureWebHostDefaults(webBuilder =>
            {
                webBuilder.UseStartup<Startup>();
            });

     // I added this.
    public static void ConfigureLogger(IConfiguration configuration)
    {
        Serilog.Log.Logger = new LoggerConfiguration()
                                .ReadFrom.Configuration(configuration)
                                .CreateLogger();

        Serilog.Debugging.SelfLog.Enable(msg => Console.WriteLine(msg));
    }

My Startup.cs file looks like this:

public class Startup
{
    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    public IConfiguration Configuration { get; }

    // This method gets called by the runtime. Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddRazorPages();

        // I added this portion - start.
        services.AddDbContextPool<AppDbContext>(options =>
        {
            options.UseSqlite(Configuration.GetConnectionString("WebAppConnection")); 
        });

        services.AddScoped<IAppRepository, EFAppRepository>();
     
        // I added this portion - end.
    }

    // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
    public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
    {
        if (env.IsDevelopment())
        {
            app.UseDeveloperExceptionPage();
        }
        else
        {
            app.UseExceptionHandler("/Error");
            // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
            app.UseHsts();
        }

        app.UseHttpsRedirection();
        app.UseStaticFiles();
        app.UseRouting();
        app.UseAuthorization();
        app.UseEndpoints(endpoints =>
        {
            endpoints.MapRazorPages();
        });

        SeedData.EnsurePopulated(app); // <-- I added this.
    }
}

SeedData class looks like this (this is where I apply my migrations):

public static class SeedData
{
    public static void EnsurePopulated(IApplicationBuilder app)
    {
        var context = app.ApplicationServices
                         .CreateScope()
                         .ServiceProvider
                         .GetRequiredService<AppDbContext>();

        // Applies migrations. For eg: 20220211005345_AddLogTable.cs created by running "dotnet ef migrations add AddLogTable"
        if (context.Database.GetPendingMigrations().Any())
        {
            context.Database.Migrate();
        }

        if (!context.Logs.Any())
        {
            context.Logs.AddRange(
                new Log
                {
                    Level = "ERROR",
                    Message = "Seed Message",
                    TimeStamp = DateTime.Now
                }
            );
        }

        context.SaveChanges();
    }
}

Now I try to log some message to this SQLite sink but it doesn't log anything. It doesn't even throw any errors. I can only see the logs to the console but not to the database.

_logger.LogInformation("Test message.");

Can someone please help me make this work?

Answer 1

I had similar problem today. When I tried to log a few test logs into two sinks (File and SQLite) - logs were inserted only to the file. SQLite db was created, but log table was empty. My logger configuration looked like this:

Log.Logger = new LoggerConfiguration()
    .WriteTo.File("logs.txt")
    .WriteTo.SQLite("logs.db")
    .CreateLogger();

Solution: I set batchSize parameter value in WriteTo.SQLite() method to 1 while I was testing. Then, logs were inserted into both sinks, just as expected. In first scenario table was empty, because default value of batchSize is 100. Logger configuration after change (just for a test scenario):

Log.Logger = new LoggerConfiguration()
    .WriteTo.File("logs.txt")
    .WriteTo.SQLite(sqliteDbPath: "logs.db", batchSize: 1)
    .CreateLogger();

Another option: invoke Log.CloseAndFlush() before closing application.

Answer 2

Had the same issue. SQLite sink is a bit different than MSSqlServer sink. You'll need to add public string RenderedMessage { get; set; } into your Log Entity class to process the SQLite message.

Also this Serilog.Debugging.SelfLog.Enable(msg => Console.WriteLine(msg)); should tell you the db connection error.

Below is how I would structure your sqliteDbPath:

var sqlPath = Environment.CurrentDirectory + @"/WebApp.db";
Log.Logger = new LoggerConfiguration()
    .WriteTo.SQLite(sqliteDbPath:sqlPath,tableName:"Log",batchSize:1)
    .CreateLogger();