CodeLogic.SQLite 4.5.2-preview.68

CodeLogic.SQLite

SQLite database library for CodeLogic with connection pooling, a fluent LINQ-shaped query builder, attribute-driven table sync, and migration tracking. Built on Microsoft.Data.Sqlite.

Install

dotnet add package CodeLogic.SQLite

Quick start

await Libraries.LoadAsync<SQLiteLibrary>();

var sqlite = Libraries.Get<SQLiteLibrary>();

// 1. Sync the table from the entity (CREATE / ALTER to match the class)
await sqlite.TableSync.SyncTableAsync<NoteRecord>("Default");

// 2. CRUD via the repository — every call returns a Result<T>
var repo = sqlite.GetRepository<NoteRecord>("Default");
var insert = await repo.InsertAsync(new NoteRecord { Title = "Hello", Body = "World" });
if (insert.IsSuccess)
    Console.WriteLine($"new rowid = {insert.Value}");

// 3. Fluent queries via the query builder
var notes = await sqlite.GetQueryBuilder<NoteRecord>("Default")
    .Where(n => n.Title.Contains("Hello"))
    .OrderByDescending(n => n.CreatedUtc)
    .Take(20)
    .ToListAsync();

foreach (var note in notes.Value)
    Console.WriteLine(note.Title);

The entity is plain C# annotated with [SQLiteTable] / [SQLiteColumn]:

[SQLiteTable("notes")]
public sealed class NoteRecord
{
    [SQLiteColumn(IsPrimaryKey = true, IsAutoIncrement = true)]
    public long Id { get; set; }

    [SQLiteColumn(ColumnName = "title", IsNotNull = true, IsIndexed = true)]
    public string Title { get; set; } = "";

    [SQLiteColumn(ColumnName = "body")]
    public string Body { get; set; } = "";

    [SQLiteColumn(ColumnName = "created_utc")]
    public DateTime CreatedUtc { get; set; } = DateTime.UtcNow;
}

Only properties marked with [SQLiteColumn] are mapped — unannotated properties are ignored for schema, reads, and writes. The connection id defaults to "Default" on every entry point, so GetRepository<NoteRecord>() is equivalent to GetRepository<NoteRecord>("Default").

What's in the box

Library entry points

All data operations return CodeLogic Result / Result<T> — check .IsSuccess and read .Value, or inspect .Error. They do not throw on query failure.

Repository

GetRepository<T>() exposes:

var repo = sqlite.GetRepository<NoteRecord>();

var page  = await repo.GetPagedAsync(page: 1, pageSize: 20, orderBy: "created_utc", desc: true);
var byTag = await repo.FindAsync(n => n.Title.StartsWith("draft"));
var raw   = await repo.RawQueryAsync(
    "SELECT * FROM notes WHERE title LIKE @q",
    new() { ["@q"] = "%hello%" });

Always bind values via named parameters — never interpolate user input into the SQL.

Fluent query builder

GetQueryBuilder<T>() chains LINQ-shaped clauses and translates them to SQL.

Terminal operations (each returns a Result):

var qb = sqlite.GetQueryBuilder<NoteRecord>();

var total = await qb.Where(n => n.Title.Contains("hello")).CountAsync();

var page = await sqlite.GetQueryBuilder<NoteRecord>()
    .Where(n => n.CreatedUtc >= since)
    .OrderByDescending(n => n.CreatedUtc)
    .ToPagedListAsync(page: 1, pageSize: 20);

// Bulk predicate mutations
await sqlite.GetQueryBuilder<NoteRecord>()
    .Where(n => n.CreatedUtc < cutoff)
    .DeleteAsync();

await sqlite.GetQueryBuilder<NoteRecord>()
    .Where(n => n.Title == "")
    .UpdateAsync(new() { ["title"] = "(untitled)" });

Attribute-driven schema sync

Entity classes are the source of truth. TableSync.SyncTableAsync<T>() creates or alters the SQLite table to match the class; SyncTablesAsync and SyncNamespaceAsync batch-sync many types.

SQLiteDataType values: INTEGER, REAL, TEXT, BLOB, NUMERIC, DATETIME, DATE, BOOLEAN, UUID. When omitted the type is inferred from the property type. bool, DateTime, DateTimeOffset, Guid, and enum are converted automatically on read and write.

[SQLiteTable("orders")]
[SQLiteIndex("customer_id", "created_utc", Name = "ix_orders_customer")]
public sealed class OrderRecord
{
    [SQLiteColumn(IsPrimaryKey = true, IsAutoIncrement = true)]
    public long Id { get; set; }

    [SQLiteColumn(ColumnName = "customer_id", IsNotNull = true)]
    [SQLiteForeignKey("customers", "id", OnDelete = ForeignKeyAction.Cascade)]
    public long CustomerId { get; set; }

    [SQLiteColumn(ColumnName = "total", DataType = SQLiteDataType.NUMERIC)]
    public decimal Total { get; set; }

    [SQLiteColumn(ColumnName = "created_utc")]
    public DateTime CreatedUtc { get; set; } = DateTime.UtcNow;
}

Observability

Queries are logged when the runtime is in development mode, and any query that runs longer than the per-database SlowQueryThresholdMs is logged as a warning.

Configuration

Auto-generated on first run under data/codelogic/Libraries/CL.SQLite/config.sqlite.json. The config is a map of named databases — each key is a connection id you pass to GetRepository / GetQueryBuilder / SyncTableAsync.

{
  "databases": {
    "Default": {
      "enabled": true,
      "databasePath": "database.db",
      "connectionTimeoutSeconds": 30,
      "commandTimeoutSeconds": 120,
      "skipTableSync": false,
      "cacheMode": "default",
      "useWAL": true,
      "enableForeignKeys": true,
      "maxPoolSize": 10,
      "slowQueryThresholdMs": 500
    }
  }
}

A database with enabled: false is skipped at startup; if no database is enabled the library initializes in a disabled state and the health check reports healthy-but-disabled.

Documentation

• Database Libraries Guide

Requirements

• CodeLogic 3.x or 4.x | .NET 10

License

MIT — see LICENSE