How to use SQLite in Blazor WebAssembly?

Question

For Blazor WebAssembly I came up with the idea of using SQLite. This question mentions it is not possible. Is it possible to use SQLite in Blazor WebAssembly and if so, how?

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:
   • Microsoft.EntityFrameworkCore.Sqlite
   • 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).