Why is it not possible to use Sqlite in Blazor WebAssembly?

Question

For a platform solution, built with blazor wasm, I'm searching for a light weight storage solution, that is capable of storing big datasets on client side. We came up with the idea of using Sqlite. I literally couldn't find any information about doing so, expect this Stackoverflow question from today, which mentions that it is not possible. Any explanations on this?

Thanks a lot!

Answer 1

As of .NET 6, you can use include native dependencies in Blazor WebAssembly, one example in fact being SQLite. See example code here: https://github.com/SteveSandersonMS/BlazeOrbital/blob/6b5f7892afbdc96871c974eb2d30454df4febb2c/BlazeOrbital/ManufacturingHub/Properties/NativeMethods.cs#L6

Answer 2

Starting .Net 6, it is now possible to use SQLite with Blazor Web Assembly .

Here are the steps,

1. Add reference to following Nuget packages. a. Microsoft.EntityFrameworkCore.Sqlite b. SQLitePCLRaw.bundle_e_sqlite3 - Currently in preview as of posting this answer. This package is to avoid NativeFileReference of e_sqlite3.o.
2. Add the following in .csproj to avoid unwanted warning from popping out.

<AllowUnsafeBlocks>true</AllowUnsafeBlocks>
<EmccExtraLDFlags>-s WARN_ON_UNDEFINED_SYMBOLS=0</EmccExtraLDFlags>

3. Add the following code in Program.cs. This is required to avoid runtime exception - Could not find method 'AddYears' on type 'System.DateOnly'

public partial class Program 
{
    /// <summary>
    /// FIXME: This is required for EF Core 6.0 as it is not compatible with trimming.
    /// </summary>
    [DynamicallyAccessedMembers(DynamicallyAccessedMemberTypes.All)]
    private static Type _keepDateOnly = typeof(DateOnly);
}

4. I'm using sqlite inmemory store. Here is the Code Example.

Database Model:

public class Name
{
    public int Id { get; set; }
    public string FullName { get; set; }
}

Database Context:

public class TestDbCOntext : DbContext
{
    public DbSet<Name> Names { get; set; } = default!;

    public TestDbCOntext(DbContextOptions<TestDbCOntext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Name>().ToTable("Names");
        modelBuilder.Entity<Name>().HasIndex(x => x.FullName);
        modelBuilder.Entity<Name>().Property(x => x.FullName).UseCollation("nocase");
    }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.LogTo(Console.WriteLine, LogLevel.Warning)
                .EnableDetailedErrors()
                .EnableSensitiveDataLogging(true);
    }
}

Page/Component:

<button @onclick="RunEfCore">Run Ef Core</button>

@code {
private async Task RunEfCore()
{
    var connectionStringBuilder = new SqliteConnectionStringBuilder { DataSource = ":memory:" };
    var connection = new SqliteConnection(connectionStringBuilder.ToString());

    var options = new DbContextOptionsBuilder<TestDbCOntext>()
        .UseSqlite(connection)
        .Options;

    using var db = new TestDbCOntext(options);
    db.Database.OpenConnection();
    await db.Database.EnsureCreatedAsync();
    var nextId = db.Names!.Count() + 1;
    db.Names.Add(new Name { Id = nextId, FullName = "Abdul Rahman" });
    await db.SaveChangesAsync();

    Console.WriteLine();
    await foreach (var name in db.Names.AsAsyncEnumerable())
    {
        Console.WriteLine(name.FullName);
    }
    db.Database.CloseConnection();
}
}

4. For persistance, you can make use of IndexedDB from browser and sync to save in your server.

Sample Working Demo can found in my Github Repo - BlazorWasmEfCore

Live Demo

Refer the github issue for complete history.

Steve Sanderson Video Reference

Screenshot:

Points to consider:

The below details are taken from following stackoverflow answer.

A good rule of programming is KISS - Keep it Simple. So if the requirement of your app is satisfied by Linq to Objects, then complicating it with SQLite would seem to be the wrong thing to do.

However, in the video Steve S. does come up with requirement parameters that lend themselves to using SQLite - the application needs to process:

• lots of data
• in the client
• offline
• quickly
• The use of SQLite here is not for persisting beyond client app memory.

So the answer to your question on the advantage of a Blazor app using SQLite is simply:

• If an app needs the functionality of an in-memory relational database but is trying to avoid using one, it will either be reinventing the wheel in its implementation, or missing necessary functionality.
• If an app doesn't need the functionality of an in-memory database but is using one, it will be introducing unnecessary complexity (cost).

Answer 3

Your Blazor WebAssembly C# code still runs in the sandbox of the browser, that means it is not allowed to open files on the local drive.

Blazor WebAssembly has the same access to the machine as any regular website.

Even if someone was to port SQLite to WebAssembly you would not be able to open a database file.

For storage on the client computer you are limited to Local Storage, it is limited to 5 MB (might be different per browser brand) and can only contain strings. But it is not a reliable option as the data will be removed when the users clears the cache, browser history etc..

The only option you have is storing data on the server.