Besql deletes the offline database on page refresh

[waleed-alharthi]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the bug

[Dev Mode] When navigating between pages and returning to a page with populated data, it successfully retrieves data from Besql. However, when refreshing the page, all the data is gone.

For some context, I tried playing with the default service-worker.js just in case it's the culprit but without success.

Expected Behavior

Data to come back after page refresh.

Steps To Reproduce

1. Created a .Net 7 (WASM+ASP.Net Core hosted) app.
2. Upgraded solution to .Net 8
3. Installed Besql
4. Pulled data from httpclient to Besql
5. Refreshed page

Exceptions (if any)

No response

.NET Version

.Net 8.0.5

Anything else?

service-worker.js

// In development, always fetch from the network and do not enable offline support.
// This is because caching would make development more difficult (changes would not
// be reflected on the first load after each change).
self.addEventListener('fetch', () => { });

Program.cs (I've had the issue from before adding BlazorJS.WebWorkers)

using Microsoft.AspNetCore.Components.Web;
using Microsoft.AspNetCore.Components.WebAssembly.Hosting;
using Microsoft.EntityFrameworkCore;
using MudBlazor;
using MudBlazor.Services;
using RihalDotnetTemplateWasm.Client;
using RihalDotnetTemplateWasm.Client.Data;
using RihalDotnetTemplateWasm.Client.Services;
using System.Text.Json;
using System.Text.Json.Serialization;
using SpawnDev.BlazorJS;
using SpawnDev.BlazorJS.WebWorkers;
using Bit.Besql;

var builder = WebAssemblyHostBuilder.CreateDefault(args);
builder.RootComponents.Add<App>("#app");
builder.RootComponents.Add<HeadOutlet>("head::after");

// Configure EF Core logging
builder.Logging.SetMinimumLevel(LogLevel.Warning); // Set default minimum log level to Warning
builder.Logging.AddFilter("Microsoft.EntityFrameworkCore.Database.Command", LogLevel.Warning); // Filter out EF Core command logs
builder.Logging.AddFilter("Microsoft.EntityFrameworkCore.Infrastructure", LogLevel.Warning); // Filter out EF Core infrastructure logs

builder.Services.AddBlazorJSRuntime();
builder.Services.AddWebWorkerService(webWorkerService =>
{
    // Optionally configure the WebWorkerService service before it is used
    // Default WebWorkerService.TaskPool settings: PoolSize = 0, MaxPoolSize = 1, AutoGrow = true
    // Below sets TaskPool max size to 2. By default the TaskPool size will grow as needed up to the max pool size.
    // Setting max pool size to -1 will set it to the value of navigator.hardwareConcurrency
    webWorkerService.TaskPool.MaxPoolSize = 2;
    // Below is telling the WebWorkerService TaskPool to set the initial size to 2 if running in a Window scope and 0 otherwise
    // This starts up 2 WebWorkers to handle TaskPool tasks as needed
    // Setting this to -1 will set the initial pool size to max pool size
    //webWorkerService.TaskPool.PoolSize = webWorkerService.GlobalScope == GlobalScope.Window ? 2 : 0;
    webWorkerService.TaskPool.PoolSize = webWorkerService.GlobalScope == GlobalScope.Window ? 2 : 0;
});
//builder.Services.RegisterServiceWorker<SyncServiceWorker>();

//builder.Services.AddBesqlDbContextFactory<AppDbContext>(options =>
//{
//    options.UseSqlite("Data Source=app.db");
//    options.UseSnakeCaseNamingConvention();
//    options.EnableDetailedErrors();
//    options.EnableSensitiveDataLogging(); // This enables logging parameter values
//    options.LogTo(Console.WriteLine, LogLevel.Error); // Add logging to console
//});

//add besql as singleton
builder.Services.AddSingleton<IBesqlStorage, BrowserCacheBesqlStorage>();
builder.Services.AddDbContextFactory<AppDbContext, BesqlDbContextFactory<AppDbContext>>(options =>
{
    options.UseSqlite("Data Source=app.db");
    options.UseSnakeCaseNamingConvention();
    options.EnableDetailedErrors();
    options.EnableSensitiveDataLogging(); // This enables logging parameter values
    options.LogTo(Console.WriteLine, LogLevel.Error); // Add logging to console
}, ServiceLifetime.Singleton);

//AddBesqlDbContextFactory as a singleton with options options.UseSqlite("Data Source=app.db");
builder.Services.AddSingleton<IUnitOfWork, UnitOfWork>();
builder.Services.AddSingleton<DynamicRepository>();

// Configure JSON serialization options
builder.Services.AddOptions<JsonSerializerOptions>().Configure(options =>
{
    options.ReferenceHandler = ReferenceHandler.Preserve; // Use ReferenceHandler.Preserve to handle object cycles
});

builder.Services.AddSingleton(sp => new HttpClient { BaseAddress = new Uri(builder.HostEnvironment.BaseAddress) });
builder.Services.AddSingleton<GenericHttpClient>();

//MudBlazor
builder.Services.AddMudServices(config =>
{
    config.SnackbarConfiguration.PositionClass = Defaults.Classes.Position.BottomRight;
});
builder.Services.AddSingleton<MudThemeService>();

//Listeners to trigger UI updates from background services
builder.Services.AddSingleton<ISyncListenerService, SyncListenerService>();
builder.Services.AddSingleton<ISyncService, SyncService>();

var app = builder.Build();

await using (var scope = app.Services.CreateAsyncScope())
{
    // Create db context
    await using var dbContext = await scope.ServiceProvider
        .GetRequiredService<IDbContextFactory<AppDbContext>>()
        .CreateDbContextAsync();

    // migrate database
    await dbContext.Database.MigrateAsync();
}

//await app.RunAsync();
await app.BlazorJSRunAsync();

AppDbContext.cs

using Microsoft.EntityFrameworkCore;
using RihalDotnetTemplateWasm.Shared.Data;
using RihalDotnetTemplateWasm.Shared.Models;

namespace RihalDotnetTemplateWasm.Client.Data
{
    public class AppDbContext : SharedDbContext
    {
        public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
        {
            if (OperatingSystem.IsBrowser())
            {
                // To make optimized db context work in blazor wasm: https://github.com/dotnet/efcore/issues/31751
                // https://learn.microsoft.com/en-us/ef/core/performance/advanced-performance-topics?tabs=with-di%2Cexpression-api-with-constant#compiled-models
                AppContext.SetSwitch("Microsoft.EntityFrameworkCore.Issue31751", true);
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Use configuration from files
            modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
            base.OnModelCreating(modelBuilder);
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Data Source=app.db");
            optionsBuilder.UseSnakeCaseNamingConvention();
            optionsBuilder.EnableDetailedErrors();
            optionsBuilder.LogTo(Console.WriteLine, LogLevel.Error);
            optionsBuilder.EnableSensitiveDataLogging(); // This enables logging parameter values
            base.OnConfiguring(optionsBuilder);
        }
    }
}

[ysmoradi]

Could you plesse check if this sample is working fine or not on your system?

https://github.com/bitfoundation/bitplatform-samples/tree/develop/videos/Bit.Besql

[waleed-alharthi]

The sample does work fine on my system.

• The data remains when refreshing the weather forecast page.
• The data goes away when doing a hard-reload plus refresh. Which is normal.

It appears to be a newer Blazor web app (not WASM+ASP.Net Core hosted). Also, there's an "Offline-ClientDb.db" file showing up in the server?

In my setup I have Postgres in the server, SQLite in the client, and a semi-inherited db context to share the dbsets between the two.

The ClientData project is simply because I need a console app to add migrations to my WASM app.

[ysmoradi]

There's a simple web assembly project working with index.html

https://github.com/bitfoundation/bitplatform/tree/develop/src/Butil

Could you plesse check that sample as well?

[waleed-alharthi]

I can't get that sample to compile. Maui platforms mix-up and other errors popping up. Is there something I could be missing which causes the database to reset when I do a normal page refresh?

[ysmoradi]

I'll check your project details soon. I that sample project, you can easily unload blazor maui hybrid project

[waleed-alharthi]

I found this project on GitHub, uses Besql, purely WASM with no backend: https://github.com/ravi66/Book

It compiles and runs fine, the database persists between refreshes and completely separate runs! I'm trying to pin the differences in project config, index.html, dbcontext, program.cs, etc... can't find the culprit! 😔

[waleed-alharthi]

One thing I've notice in the Book app is the cache has an actual entry for Besql:

My app:

It's very odd though. Because while my app is running, I can actually read and write to sqlite, I can navigate between SPA pages and see my data come back. However, I can't refresh or restart the app (all data gets lost).

[ysmoradi]

It's hard to find the root cause in your issue, could you please send me a the most minimal possible sample project that reproduces the issue?

[waleed-alharthi]

Apologies taking forever to respond. Is it alright if I share the actual project? It's an ongoing boilerplate project. It's self-contained in a docker-compose project runs with one click.

[ysmoradi]

As far as we know, Bit.Besql is working properly, so there's something different in your project. We ask developers to create most simple sample that demonstrates the issue, because the time they're creating that simple sample, they found the root cause of the issue themselves!

We don't offer reviewing/test real world complex projects in our free community support to find the root cause on our own, but we do offer paid license that you can easily purchase by paying the specified amount in our GitHub sponsor page for at least one month for one developer. We not only check your project in a live meeting through desktop sharing to solve the issue, we'll improve the project's performance through project review, and you'll receive benefit on not only bitplatform related parts, but also aspnetcore, ef core and other technologies that you're using.

Let me know if you've any questions. Regards

[waleed-alharthi]

That's fair. I'll keep trying and come around if there's no way out. Many thanks!