SqlNadoAot 3.0.0

SQLNado

SQLNado (SQLite Not ADO) is a lightweight, bloat-free object-persistence framework for SQLite, the fully NativeAOT and trim-clean evolution of SQLNado, rebuilt on .NET 10 with a Roslyn source generator instead of runtime reflection.

It wraps the native SQLite library through P/Invoke (it is not a reimplementation of SQLite). All interop uses modern [LibraryImport] source-generated marshalling, so a consumer app can PublishAot=true with zero trim/AOT warnings.

Features

• Automatic class-to-table mapping, Save, Delete, Load, LoadAll, LoadByPrimaryKey, LoadByForeignKey, …
• Automatic schema synchronization (tables, columns) between your classes and the database
• Designed for thread-safe operations
• The SQLite schema (tables, columns, indexes, …) is exposed to .NET
• Custom SQLite scalar functions written in .NET
• Custom SQLite collations written in .NET
• Incremental BLOB I/O exposed as a .NET Stream (avoids loading whole blobs in memory)
• Full-Text Search (FTS3/4) with custom tokenizers written in .NET
• Ships no native binary, uses each platform's own SQLite (winsqlite3 on Windows, system libsqlite3 on Linux/macOS, a consumer-provided library on Android/iOS)
• Fully NativeAOT + trim compatible via a source generator, no Reflection.Emit, no Expression.Compile

Changed from the original SQLNado

• Targets .NET 10 (was netstandard2.0).
• Mapping is done by a source generator keyed on [SQLiteTable], not reflection (a reflection fallback still exists for JIT use, see below).
• No native binary is bundled anymore (the original shipped sqlite3.*.dll).
• The experimental LINQ/IQueryable query translator was removed (it required runtime code generation, incompatible with AOT). Use Load<T>("WHERE …") with SQL, which is fully supported.
• The TableString console pretty-printer was removed.
• No amalgamation. The original offered a single-file sqlnado.cs; because AOT mapping now relies on a Roslyn analyzer (which can't be delivered as one source file), SQLNadoAot is distributed only as a NuGet package.

Installation

Available on NuGet: nuget.org/packages/SqlNadoAot

dotnet add package SqlNadoAot

That single package (id SqlNadoAot; the assembly and root namespace remain SqlNado) contains both pieces:

• lib/net10.0/SqlNado.dll, the runtime library
• analyzers/dotnet/cs/SqlNado.Generator.dll, the source generator (runs automatically)

Then: mark your entities with [SQLiteTable], and you're AOT-ready. Nothing else to reference.

Native SQLite per platform

No SQLite binary is shipped. The provider is selected at open time by SQLiteDatabase.GetNativeDefaults():

Windows is 64-bit only

Microsoft's winsqlite3.dll is compiled __stdcall, while the official sqlite.org sqlite3.dll is __cdecl. That difference only exists on x86 (x64 and ARM64 have a single calling convention), so SQLNadoAot supports x64 and ARM64 only on Windows. A 32-bit (x86) process throws a clear exception at database-open time rather than risk stack corruption — build/run as x64.

Bringing your own build (opting out of winsqlite3). On Windows the probe order is SQLNADO_SQLITE_<ARCH>_DLL env var → sqlite3.<arch>.dll next to the app → winsqlite3.dll (System32); the first that loads wins. Point SQLNADO_SQLITE_<ARCH>_DLL at a full path, or drop sqlite3.x64.dll / sqlite3.arm64.dll beside the executable, to ship a specific SQLite build (a particular version, or one compiled with extra features) instead of the OS one. Your DLL is loaded by full path via a NativeLibrary.SetDllImportResolver, which feeds the clean cdecl [LibraryImport("sqlite3")] provider — no shimming, no custom marshalling.

You can also override provider selection entirely: GetNativeDefaults() is public static and yields the ISQLiteNative providers to try; or call SQLiteDatabase.LoadNative(...) with your own before opening a database.

Get started

using SqlNado;

// creates my.db if it doesn't exist
using var db = new SQLiteDatabase("my.db");

// insert or update (decided by whether the primary key is set).
// Save also synchronizes the table schema (creates the table on first run).
db.Save(new Customer { Email = "kilroy@example.com", Name = "Kilroy" });

// load all rows
foreach (var c in db.LoadAll<Customer>())
{
    Console.WriteLine($"{c.Email} - {c.Name}");
}

// load one row by primary key
var one = db.LoadByPrimaryKey<Customer>("kilroy@example.com");

// arbitrary SQL (parameterized)
foreach (var c in db.Load<Customer>("WHERE Name = ?", "Kilroy"))
{
    Console.WriteLine(c.Email);
}

[SQLiteTable]
public class Customer
{
    [SQLiteColumn(IsPrimaryKey = true)]
    public string Email { get; set; } = "";

    public string? Name { get; set; }
}

The [SQLiteTable] attribute is what the source generator hooks onto: it emits, per entity, a reflection-free mapping (instance factory + typed column get/set delegates) registered at module load, so Save/Load/… never touch reflection.

Reflection fallback (JIT only)

If a type is used without [SQLiteTable], SQLNadoAot falls back to a reflection-based mapping so it still works under the JIT, convenient for quick apps and tests. That fallback is not trim/AOT-safe, so for PublishAot=true builds every persisted type must carry [SQLiteTable].

Custom SQLite functions in .NET

db.SetScalarFunction("my_add", 2, deterministic: true, ctx =>
    ctx.SetResult(ctx.Values[0].Int32Value + ctx.Values[1].Int32Value));

var sum = db.ExecuteScalar<int>("SELECT my_add(20, 22)"); // 42

Custom collations in .NET

db.SetCollationFunction("REVSTR", Comparer<string>.Create((x, y) =>
    string.CompareOrdinal(Reverse(x), Reverse(y))));

var rows = db.LoadObjects("SELECT w FROM words ORDER BY w COLLATE REVSTR");

Incremental BLOB I/O (Stream)

Read or write a blob column without materializing it fully in memory:

using var blob = db.OpenBlob("Customer", "Photo", rowId, SQLiteBlobOpenMode.ReadWrite);
blob.CopyFrom(File.OpenRead("photo.jpg")); // stream in
// or: blob.CopyTo(outputStream); byte[] all = blob.ToArray(); blob.Read(buffer, count, offset);

Full-Text Search: a custom FTS tokenizer in .NET

using var db = new SQLiteDatabase(":memory:");

// custom tokenizers must be enabled first
db.Configure(SQLiteDatabaseConfiguration.SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, true, 1);
db.SetTokenizer(new StopWordTokenizer(db));

db.Save(new Mail { docid = 1, Subject = "software feedback", Body = "found it too slow" });
db.Save(new Mail { docid = 2, Subject = "software feedback", Body = "no feedback" });

// 'no' is a stop word, so this returns nothing
foreach (var m in db.Load<Mail>("WHERE mail MATCH 'no'"))
{
    Console.WriteLine(m);
}

[SQLiteTable(Module = "fts4", ModuleArguments = nameof(Subject) + ", " + nameof(Body) + ", tokenize=" + StopWordTokenizer.TokenizerName)]
public class Mail
{
    public long docid { get; set; }
    public string? Subject { get; set; }
    public string? Body { get; set; }
    public override string ToString() => Subject + ":" + Body;
}

public class StopWordTokenizer : SQLiteTokenizer
{
    public const string TokenizerName = "unicode_stopwords";
    private static readonly HashSet<string> _words = ["a", "it", "no", "too", "was"];
    private readonly SQLiteTokenizer _unicode;

    public StopWordTokenizer(SQLiteDatabase database, params string[] arguments)
        : base(database, TokenizerName) => _unicode = database.GetUnicodeTokenizer(arguments)!;

    public override IEnumerable<SQLiteToken> Tokenize(string input)
    {
        foreach (var token in _unicode.Tokenize(input))
        {
            if (!_words.Contains(token.Text))
                yield return token;
        }
    }

    protected override void Dispose(bool disposing)
    {
        if (disposing)
            _unicode.Dispose();

        base.Dispose(disposing);
    }
}

FTS tokenizers, scalar functions and collations all run through native→managed callbacks; these are implemented with source-generated interop and are validated to work under NativeAOT (see below).

NativeAOT

Consumers just publish normally, no extra configuration beyond [SQLiteTable] on entities:

dotnet publish -c Release -r win-x64    # or linux-x64, osx-arm64, …

The library itself is built IsAotCompatible=true and produces 0 trim/AOT (IL) warnings.