Shaunebu.Data.SQLite 1.1.0

Shaunebu.Data.SQLite

Shaunebu.Data.SQLite is a lightweight convenience layer over sqlite-net-pcl for .NET and .NET MAUI applications that need simple asynchronous CRUD, table management, fluent table queries, dependency injection, lifecycle management, interceptors, and structured operation logging.

It keeps sqlite-net close at hand, but wraps the repetitive production plumbing that application teams usually have to build themselves.

💡 Tip
Use SQLiteManagerService.GetInstance(...) for a shared path-based manager, or AddShaunebuSQLite(...) when your app is already composed with IServiceCollection.

✨ Features

🧭 Why Shaunebu.Data.SQLite?

sqlite-net-pcl is excellent. Shaunebu.Data.SQLite is for teams that want a focused, application-level manager around it.

🔍 Comparison

📦 Installation

Install-Package Shaunebu.Data.SQLite

The package currently targets:

ℹ️ Note
Version 1.3.0 is standardized on modern .NET target frameworks. Older net8.0 assets are not included in this package version.

🚀 Quick Start

using Microsoft.Extensions.Logging;
using Shaunebu.Data.SQLite;

var loggerFactory = LoggerFactory.Create(builder => builder.AddConsole());
var logger = loggerFactory.CreateLogger<SQLiteManagerService>();

var db = SQLiteManagerService.GetInstance("mydatabase.db", logger);

await db.EnsureTableExistsAsync<User>();

GetInstance keeps one live manager per normalized database path. Calls are safe for concurrent instance lookup, and the first logger supplied for a live path is the logger used by that shared instance. After CloseAsync, a later GetInstance call recreates a new manager for that path.

flowchart TD
    A["Application / .NET MAUI App"] --> B["SQLiteManagerService"]
    B --> C["sqlite-net-pcl"]
    C --> D["SQLite"]

⚙️ Configuration

Use SQLiteManagerOptions when you need to configure sqlite-net open flags or startup behavior:

var db = SQLiteManagerService.GetInstance(new SQLiteManagerOptions
{
    DatabasePath = "mydatabase.db",
    OpenFlags = SQLiteOpenFlags.ReadWrite |
                SQLiteOpenFlags.Create |
                SQLiteOpenFlags.SharedCache,
    EnableWriteAheadLogging = true,
    BusyTimeout = TimeSpan.FromSeconds(30),
    StoreDateTimeAsTicks = true,
    FullMutex = true,
    SharedCache = true
});

OpenFlags is the source of truth for connection flags. FullMutex and SharedCache are convenience accessors that add or remove their corresponding SQLiteOpenFlags values from OpenFlags.

Existing path-based APIs continue to use default options: ReadWrite, Create, FullMutex, and StoreDateTimeAsTicks = true. When using GetInstance(SQLiteManagerOptions), the live singleton is still keyed by normalized DatabasePath; changing options after the first live instance is created does not reconfigure that instance. Close it before recreating the manager with different options for the same path.

🔐 Lifecycle

Close pooled SQLite connections when the database is no longer needed and all database operations have completed:

await db.CloseAsync();

or:

await using var db = SQLiteManagerService.GetInstance("mydatabase.db");

CloseAsync() is idempotent, so repeated calls are safe. Treat close/dispose as an application shutdown boundary for that database manager. Do not close the manager while other threads are actively using it.

⚠️ Important
After close, using the manager, or any TableContext<T> or TableQueryBuilder<T> created by that manager, throws ObjectDisposedException. Closing does not cancel SQLite operations that were already in flight.

💡 Best Practices

1. Use GetInstance(dbPath) or singleton DI registration when one shared manager per database file is the right lifetime for your application.
2. Use scoped or transient DI registration when a manager must be isolated from the global singleton registry.
3. Use SQLiteManagerOptions rather than ad hoc setup calls when WAL, busy timeout, open flags, or observability should be applied during construction.
4. Use ResetTableAsync<T>() for tests, demos, and explicit table reset workflows; avoid it for user data unless destructive reset is intentional.
5. Use TableContext<T> for table-scoped CRUD workflows and TableQueryBuilder<T> when fluent query composition is the focus.
6. Keep CloseAsync() as a shutdown boundary after database work has completed.
7. Keep sensitive values out of log messages and let structured logging carry operation metadata.

🗄️ Table Management

await db.EnsureTableExistsAsync<User>();
await db.ResetTableAsync<User>();
await db.For<User>().DeleteAllAsync();

Custom sqlite-net mappings are honored by table creation, reset, query, and delete-all operations.

🔄 CRUD Operations

await db.Insert(new User { Name = "Alice" });
await db.InsertAsync(new User { Name = "Alice Async" });

await db.Insert(new List<User>
{
    new() { Name = "Bob" },
    new() { Name = "Charlie" }
});
await db.InsertAsync(new List<User>
{
    new() { Name = "Dana" },
    new() { Name = "Eve" }
});

var alice = await db.For<User>()
    .Where(user => user.Name == "Alice")
    .FirstOrDefaultAsync();

if (alice is not null)
{
    alice.Name = "Alice Updated";
    await db.Update(alice);
    await db.UpdateAsync(alice);
    await db.Delete(alice);
}

The SQLiteManagerService method names Insert, Update, and Delete are kept for compatibility. The InsertAsync, UpdateAsync, and DeleteAsync aliases are available for conventional .NET async naming and delegate to the existing methods.

ℹ️ Note
The compatibility method names are asynchronous even though their names do not end with Async. New code can use the Async aliases for clarity.

🔍 Query Builder

Use For<T>() for table-scoped operations:

var users = await db.For<User>()
    .Where(user => user.Name.Contains("Alice"))
    .OrderBy(user => user.Name)
    .ToListAsync();

Use Table<T>() when you want the wrapper query builder and its CountAsync helper:

var count = await db.Table<User>()
    .Where(user => user.Name.Contains("Alice"))
    .CountAsync();

🌐 TableContext Helper

Create a table-specific context from the manager when the surrounding code is focused on one model:

var usersTable = db.For<User>();
await usersTable.InsertAsync(new User { Name = "Diana" });

var allUsers = await usersTable.ToListAsync();

Supported table-context query methods:

💉 Dependency Injection

The core Shaunebu.Data.SQLite package includes dependency injection extensions. Register the manager in a .NET or .NET MAUI app with AddShaunebuSQLite():

using Microsoft.Extensions.DependencyInjection;

builder.Services.AddShaunebuSQLite(options =>
{
    options.DatabasePath = databasePath;
    options.EnableWriteAheadLogging = true;
    options.BusyTimeout = TimeSpan.FromSeconds(30);
});

The default lifetime is singleton. Scoped and transient registrations are supported:

builder.Services.AddShaunebuSQLite(
    options => options.DatabasePath = databasePath,
    ServiceLifetime.Scoped);

Singleton DI registration uses the same path-based shared manager as SQLiteManagerService.GetInstance. Scoped and transient registrations create independent managers. The DI container disposes resolved managers when their configured lifetime ends; manually calling CloseAsync is still valid, and the operation is idempotent.

Registered interceptors are resolved automatically by the core package DI extensions:

builder.Services.AddSingleton<ISQLiteInterceptor, MetricsInterceptor>();
builder.Services.AddSingleton<ISQLiteInterceptor, AuditInterceptor>();

Interceptors are resolved once per manager construction and preserve registration order. Scoped and transient SQLite managers do not use the global singleton registry. Scoped interceptors are rejected for singleton manager registration; use scoped or transient manager registration when interceptors need scoped dependencies.

📊 Observability

Enable operation logging through SQLiteManagerOptions:

builder.Services.AddShaunebuSQLite(options =>
{
    options.DatabasePath = databasePath;
    options.EnableObservability = true;
    options.LogLevel = LogLevel.Information;
    options.SlowOperationThreshold = TimeSpan.FromMilliseconds(500);
});

EnableObservability = true enables routine completion logs at LogLevel and slow-operation warnings at warning level. EnableObservability = false disables those routine operation logs, but it does not silently skip registered interceptors and it does not suppress error logs that are accepted by the configured ILogger filters.

The library logs through Microsoft.Extensions.Logging.ILogger. It does not implement a separate logging pipeline. If your application uses Shaunebu.Common.Logging, configure its existing Microsoft logging provider and SQLite logs will flow through that pipeline:

builder.Services
    .AddShaunebuLogging(options =>
    {
        options.ApplicationName = "MyMauiApp";
        options.Redaction.Enabled = true;
    })
    .AddShaunebuMicrosoftLogging();

flowchart TD
    A["Application"] --> B["ILogger / ILogger<T>"]
    B --> C["Shaunebu.Common.Logging Microsoft Adapter"]
    C --> D["Shaunebu.Common.Logging"]
    D --> E["Console Provider"]
    D --> F["File Provider"]
    D --> G["Custom Providers"]

Operation logs include operation type, table name when known, duration, slow-operation warnings, and exception details. Entity values, SQL parameter values, connection strings, encryption keys, tokens, and absolute database paths are not logged by default. SQLiteManagerOptions.LogLevel chooses the requested level for successful completion logs; normal Microsoft.Extensions.Logging filters still decide whether that level is emitted.

🧾 Sample Log Output

[Information] OperationCompleted
Table: Users
Duration: 14 ms

With Shaunebu.Common.Logging, SQLite logs flow through its provider pipeline:

INFORMATION [Shaunebu.Data.SQLite.SQLiteManagerService] SQLite operation Insert completed in 7.3 ms for table users
PROPERTIES: {"OperationType":4,"DurationMs":7.3,"TableName":"users","EventName":"OperationCompleted"}

SQLite logs use stable event names:

💡 Tip
Keep application-level redaction enabled in your logging stack when logs may leave the device or workstation.

🧩 Interceptors

Implement ISQLiteInterceptor to observe operations:

public sealed class MetricsInterceptor : ISQLiteInterceptor
{
    public ValueTask OnExecutingAsync(SQLiteOperationContext context)
        => ValueTask.CompletedTask;

    public ValueTask OnExecutedAsync(SQLiteOperationContext context)
    {
        var duration = context.Duration;
        return ValueTask.CompletedTask;
    }

    public ValueTask OnExceptionAsync(SQLiteOperationExceptionContext context)
    {
        var exception = context.OperationException;
        return ValueTask.CompletedTask;
    }
}

The operation context exposes a generated operation id, operation type, sanitized database identifier, table name, entity type, item count, start/completion timestamps, duration, exception, and sanitized metadata. It never exposes entity values or SQL parameters.

Interceptor failure behavior is deterministic:

Callbacks are invoked in registration order and exactly once for the applicable phase.

🧾 Operation Mapping

🧰 SQLite Options

The manager exposes common SQLite connection settings:

await db.SetBusyTimeoutAsync(TimeSpan.FromSeconds(5));
await db.EnableWriteAheadLoggingAsync();

The same settings can be applied at creation time through SQLiteManagerOptions:

var db = SQLiteManagerService.GetInstance(new SQLiteManagerOptions
{
    DatabasePath = "mydatabase.db",
    EnableWriteAheadLogging = true,
    BusyTimeout = TimeSpan.FromSeconds(5)
});

🧱 Example Model

using SQLite;

public sealed class User
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public string Name { get; set; } = "";
}

Custom sqlite-net mappings are honored:

[Table("app_users")]
public sealed class User
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public string Name { get; set; } = "";
}

🛠️ Full Flow Example

var userDb = SQLiteManagerService.GetInstance("users.db");
var productDb = SQLiteManagerService.GetInstance("products.db");

await userDb.EnsureTableExistsAsync<User>();
await productDb.EnsureTableExistsAsync<Product>();
await productDb.ResetTableAsync<Product>();

await userDb.Insert(new User { Name = "Alice" });
await productDb.Insert(new Product { Name = "Laptop", Price = 1200 });

var moreUsers = new List<User>
{
    new() { Name = "Bob" },
    new() { Name = "Charlie" }
};
await userDb.Insert(moreUsers);

var users = await userDb.For<User>().ToListAsync();
Console.WriteLine($"Users count: {users.Count}");

var userAlice = await userDb.For<User>()
    .Where(user => user.Name == "Alice")
    .FirstOrDefaultAsync();

if (userAlice is not null)
{
    userAlice.Name = "Alice Updated";
    await userDb.Update(userAlice);
    await userDb.Delete(userAlice);
}

var sortedProducts = await productDb.For<Product>()
    .OrderByDescending(product => product.Price)
    .ToListAsync();

foreach (var product in sortedProducts)
{
    Console.WriteLine($"{product.Name} - {product.Price}");
}

⚡ Performance

Shaunebu.Data.SQLite keeps the wrapper thin and delegates database execution to sqlite-net-pcl. Benchmark coverage is maintained for release validation across common operations.

ℹ️ Note
Benchmarks are intended to establish measurable baselines. They are not a substitute for testing your app's real database shape, device class, storage, and concurrency profile.

🧪 Console Client

The public samples include Shaunebu.Data.SQLite.Client, a net10.0 interactive console showcase for the current public API. It demonstrates manager CRUD, async aliases, batch operations, query builder, TableContext, connection options, dependency injection, lifecycle, WAL and busy timeout, interceptors, observability, Shaunebu.Common.Logging, concurrency, and expected failures.

Interactive mode and single-scenario mode display SQLite log output where relevant. Validation mode intentionally suppresses live console logging so automated output remains concise.

The client stores demo databases under the current user's local application data folder and prints a sanitized database identifier by default. It is a manual showcase and release-validation helper, not a replacement for automated unit tests.

📘 Public Assets

The public GitHub presence for Shaunebu.Data.SQLite is documentation- and sample-focused. Applications consume the compiled library through NuGet.

📚 References

• Shaunebu.Data.SQLite on NuGet
• Shaunebu.Data.SQLite documentation and samples
• sqlite-net-pcl
• .NET MAUI SQLite guidance

📄 License

Shaunebu.Data.SQLite is a closed-source commercial library distributed through NuGet. Review the license terms provided with the NuGet package or your commercial agreement.