Devspace.Repositories.SQLite 0.1.4

SQLite repository library

Sqlite provides the embedded relational counterpart to the sibling MongoDB and PostgreSQL repositories. It uses Microsoft.Data.Sqlite and ADO.NET commands to expose strongly typed CRUD, audit fields, soft deletion, projection, pagination, collection-value removal, index management, and table lifecycle helpers against a zero-configuration, file- or memory-backed database.

For the side-by-side database comparison and solution-wide guidance, see the root documentation.

Technical specification

When to use it

Choose this library when the application needs an embedded, serverless relational store: desktop and mobile apps, CLI tools, local caches, offline-first workloads, integration tests, and small services with a single writer. PostgreSQL is the better fit for concurrent multi-writer server workloads; MongoDB fits document-shaped data with heavy nested-array operations.

Advantages

• Zero infrastructure: no server, no container, no credentials — a single file (or in-memory database).
• Low-overhead, parameterized SQLite commands with the same expression-based API as PostgreSQL.
• Consistent CRUD workflow with the MongoDB and PostgreSQL repositories.
• Built-in audit timestamps, soft delete, restore, projection, and pagination.
• Simple, unique, compound, and directional index helpers.
• Runtime table inspection, creation, and reset helpers for controlled scenarios.
• Excellent for tests and demos: the full repository behavior runs in-process.

Tradeoffs

• Single-writer concurrency model; heavy concurrent writes serialize at the database level.
• No network access layer: all readers and writers need access to the database file.
• SQLite has no TRUNCATE; TruncateTableAsync issues an unfiltered DELETE (and resets sqlite_sequence when asked).
• The cascade truncate argument is accepted for API parity but ignored; cascades follow the schema's ON DELETE rules.
• Each repository maps one entity type, so this abstraction is not intended for relationship-rich aggregate graphs.
• A repository owns one connection and is not intended for concurrent operations.
• PullAsync loads matching entities, modifies their collection values in memory, and persists them.
• SQLite has no MongoDB-style TTL index in this API.
• [SensitiveData] is metadata only; it does not encrypt, redact, or omit a value automatically.

Installation

Reference the project while developing in this monorepo:

<ProjectReference Include="..\path\to\Sqlite\Sqlite\Sqlite.csproj" />

When consuming a packed release, reference the package version produced by the repository:

<PackageReference Include="Sqlite" Version="VERSION" />

Define an entity

Entities must inherit SqliteEntity or implement ISqliteEntity.

using Sqlite;
using Sqlite.Helpers;

[TableName("ApplicationUsers")]
public sealed class UserEntity : SqliteEntity
{
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public List<string> Roles { get; set; } = [];

    [SensitiveData]
    public string PasswordHash { get; set; } = string.Empty;
}

This example maps to application_users. SqliteEntity supplies Id, CreatedDateTime, UpdatedDateTime, DeletedDateTime, and IsDeleted.

Create and register a repository

Create a repository directly:

var repository = new SqliteRepository<UserEntity>("Data Source=application.db");

For dependency injection, register a scoped repository:

using Microsoft.Extensions.DependencyInjection;
using Sqlite;

services.AddScoped<ISqliteRepository<UserEntity>>(_ =>
    new SqliteRepository<UserEntity>(connectionString));

The repository implements IAsyncDisposable; use a scope or await using to release the database file deterministically. Data Source=:memory: databases live only as long as their connection, so keep the repository alive for the database lifetime or supply an externally managed connection.

CRUD operations

var user = new UserEntity
{
    Name = "Ada Lovelace",
    Email = "ada@example.test",
    Roles = ["Admin", "Temporary"]
};

await repository.InsertAsync(user);

UserEntity? stored = await repository.GetByIdAsync(user.Id);

stored!.Name = "Augusta Ada King";
await repository.UpdateAsync(stored);

await repository.DeleteAsync(stored);                  // soft delete
await repository.RestoreAsync(stored);                 // restore
await repository.DeleteAsync(stored, hardDelete: true); // physical delete

Bulk insert and shared-field updates are also supported:

await repository.InsertAsync(users);

await repository.UpdateManyAsync(users, new Dictionary<string, object>
{
    [nameof(UserEntity.Email)] = "archived@example.test"
});

The update dictionary uses CLR property names. Invalid, unmapped, or incompatible values fail at runtime, so prefer nameof over string literals.

Query, sort, and page

using Sqlite.Helpers;

var admins = await repository.GetAllAsync(
    predicate: user => user.Email.EndsWith("@example.test"),
    orderBy: user => user.Name,
    sortDirection: SortDirection.Ascending);

var page = await repository.GetPagedListAsync(
    pageIndex: 1,
    pageSize: 25,
    predicate: user => user.Email.EndsWith("@example.test"),
    orderBy: user => user.Name,
    sortDirection: SortDirection.Ascending);

Console.WriteLine($"{page.Items.Count} of {page.TotalCount}");
Console.WriteLine($"Page {page.PageIndex} of {page.TotalPages}");

Use object initializers for multi-column sorting:

var ordering = new[]
{
    new SortExpression<UserEntity>
    {
        Expression = user => user.Name,
        SortDirection = SortDirection.Ascending
    },
    new SortExpression<UserEntity>
    {
        Expression = user => user.CreatedDateTime,
        SortDirection = SortDirection.Descending
    }
};

var page = await repository.GetPagedListAsync(1, 25, null, ordering);

Page indexes are one-based. pageIndex and pageSize must be positive.

Projection

SQLite projections are standard LINQ expressions applied after matching rows are materialized:

var summaries = await repository.GetAllAsync(
    projection: user => new
    {
        user.Id,
        user.Name,
        user.Email
    },
    predicate: user => user.Email.EndsWith("@example.test"));

Predicates are translated to parameterized SQL. Entity property comparisons, Boolean composition, null checks, string StartsWith/EndsWith/Contains, and constant-collection Contains are supported. Unsupported predicates throw NotSupportedException.

Remove collection values

await repository.PullAsync(
    field: user => user.Roles,
    fieldFilter: role => role == "Temporary",
    documentPredicate: user => user.Email.EndsWith("@example.test"));

Unlike MongoDB's atomic array operator, this implementation reads matching rows, updates the mapped collection in memory, and writes changes with parameterized commands. Keep the predicate selective for large tables.

Table lifecycle

bool exists = await repository.TableExistsAsync();
bool auditExists = await repository.TableExistsAsync("audit_events");

await repository.EnsureTableAsync();
await repository.TruncateTableAsync(restartIdentity: true);

TableExistsAsync queries sqlite_master and compares names case-insensitively; the optional schema argument exists for API parity with the relational siblings and is ignored. EnsureTableAsync is convenient for demonstrations, tests, and runtime-owned tables. TruncateTableAsync issues an unfiltered DELETE (SQLite has no TRUNCATE), optionally resets the table's AUTOINCREMENT counter in sqlite_sequence, bypasses soft deletion, and should be restricted to intentional administrative or test workflows.

Indexes

await repository.CreateIndexAsync(user => user.Email);

await repository.CreateIndexAsync(
    fields: new Expression<Func<UserEntity, object>>[]
    {
        user => user.Email,
        user => user.Name
    },
    unique: true);

await repository.RemoveIndexAsync(user => user.Email);

The directional overload accepts (property expression, SortDirection) tuples. The expiresAfter overload creates a normal index because SQLite has no native TTL index. Automatic retention requires application cleanup or a scheduled job. The current filter argument is reserved and does not create a partial WHERE index.

Soft-delete behavior

List, paging, first, projection, and existence operations exclude soft-deleted rows by default. Pass the applicable includeDeletes or includeDeleted argument when an API provides it and deleted rows must be returned. Restore clears the deletion state; hard delete permanently removes a row.

CountAsync and GetSingleOrDefaultAsync execute their supplied predicates without the default soft-delete filter, so include entity.DeletedDateTime == null when selecting only active records.

Console demonstration

The sibling console app is a self-checking executable specification. Because SQLite is embedded, it needs no Docker, container, or server: it creates an ephemeral database file in the system temp directory and performs a complete deterministic workflow:

1. Resolve table-name and sensitive-data attributes without printing sensitive values.
2. Ensure feature_examples, verify mapped and named existence checks, and reset it with the DELETE-based truncate.
3. Create simple, TTL-overload, compound unique, and directional indexes.
4. Explicitly report that TTL produces a normal SQLite index and partial-filter translation is reserved.
5. Insert one row and bulk-insert five rows with audit timestamps.
6. Count, check existence, and run ID, first, single, filtered, sorted, and projected reads.
7. Run single-field and multi-field pagination with totals and navigation flags.
8. Update one row and apply a transaction-backed shared update.
9. Remove temporary tags with ADO.NET read/modify/write PullAsync.
10. Exercise ID, entity, predicate, and collection deletion workflows.
11. Verify default soft-delete filtering, include-deleted reads, both restore overloads, and hard deletion.
12. Remove every demonstration index.
13. Dispose the repository and delete the database file.

The app contains no static connection string or database credential.

Prerequisites:

• .NET 10 SDK. Nothing else — no Docker engine is required.

Run it from the repository root:

dotnet run --project Sqlite/Sqlite.Console/Sqlite.Console.csproj

The database file and its data are removed when the process exits.

Each successful check prints [OK]. Any unexpected result throws an exception and makes the process fail.

Tests and coverage

dotnet test Sqlite/Sqlite.Tests/Sqlite.Tests.csproj
dotnet test Sqlite/Sqlite.Tests/Sqlite.Tests.csproj --collect:"XPlat Code Coverage"

The SQLite suite includes ADO.NET repository behavior tests plus entity, expression-helper, paging, attribute, and sorting tests.

Coverage is a safety signal rather than a substitute for meaningful assertions. Preserve behavior-focused tests when extending the API.

Operational guidance

• Use one scoped repository per unit of work.
• Await each database operation before starting another on the same repository.
• Dispose the repository so the application controls when the database file is released.
• Enable write-ahead logging (PRAGMA journal_mode=WAL) for concurrent-reader workloads.
• Index fields used by common predicates and sorts, but avoid redundant indexes.
• Prefer projections and pagination for large data sets.
• Use UTC timestamps consistently.
• Review hard deletes and truncation carefully before production use.

Copyright 2026 Devspace.