Zaiets.SqliteHelper 1.0.0

Zaiets.SqliteHelper

Zero-config SQLite helper for .NET — connection pooling, WAL mode, schema migrations, and generic CRUD in one lean package.

Installation

dotnet add package Zaiets.SqliteHelper

Quick Start

using Zaiets.SqliteHelper;

// Open (or create) a database — WAL mode and foreign keys enabled by default
await using var db = SqliteHelper.Open("app.db");

// Define your schema with migrations
var migrations = new MigrationBuilder()
    .Add(1, "Create users table", """
        CREATE TABLE users (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            name       TEXT    NOT NULL,
            email      TEXT    NOT NULL UNIQUE,
            created_at TEXT    NOT NULL DEFAULT (datetime('now'))
        )
    """)
    .Add(2, "Add age column", "ALTER TABLE users ADD COLUMN age INTEGER")
    .Build();

await db.MigrateAsync(migrations);

// Get a typed repository and start persisting data
var repo = db.Repository<User>();
var user = new User { Name = "Alice", Email = "alice@example.com" };
await repo.InsertAsync(user);   // sets user.Id automatically

var all   = await repo.GetAllAsync();
var alice = await repo.GetByIdAsync(user.Id);
alice!.Name = "Alice Smith";
await repo.UpdateAsync(alice);
await repo.DeleteByIdAsync(alice.Id);

Entity Mapping

[Table("users")]               // optional — defaults to class name
public class User
{
    [Key]                      // optional — "Id" and "{Class}Id" are auto-detected
    [AutoIncrement]            // optional — auto-detected for integer primary keys
    public long Id { get; set; }

    [Column("full_name")]      // optional — defaults to property name
    public string Name { get; set; } = "";

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

    public int? Age { get; set; }

    [NotMapped]                // excluded from all SQL
    public string DisplayLabel => $"{Name} <{Email}>";
}

Supported CLR → SQLite mappings:

Repository API

var repo = db.Repository<Product>();

// Reads
var all      = await repo.GetAllAsync();
var product  = await repo.GetByIdAsync(42);
var exists   = await repo.ExistsAsync(42);
var count    = await repo.CountAsync();
var filtered = await repo.CountAsync("price > @min", new { min = 9.99 });

// Raw SQL — results still mapped to Product
var results = await repo.QueryAsync(
    "SELECT * FROM products WHERE category = @cat ORDER BY price",
    new { cat = "Books" });

var first = await repo.QueryFirstOrDefaultAsync(
    "SELECT * FROM products WHERE sku = @sku",
    new { sku = "ABC-001" });

// Writes
await repo.InsertAsync(product);
await repo.InsertManyAsync(productList);   // single transaction
await repo.UpdateAsync(product);
await repo.DeleteAsync(product);
await repo.DeleteByIdAsync(42);

// Raw non-query
await repo.ExecuteAsync(
    "UPDATE products SET stock = stock - @qty WHERE id = @id",
    new { qty = 1, id = 42 });

var maxPrice = await repo.ExecuteScalarAsync<decimal>("SELECT MAX(price) FROM products");

Migrations

Migrations are versioned SQL scripts applied exactly once, tracked in a __migrations table.

var migrations = new MigrationBuilder()
    // Inline SQL
    .Add(1, "Initial schema", """
        CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
        CREATE TABLE products (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            category_id INTEGER NOT NULL REFERENCES categories(id),
            name        TEXT    NOT NULL,
            price       REAL    NOT NULL,
            stock       INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX idx_products_category ON products(category_id);
    """)
    // Embedded .sql file from an assembly
    .AddEmbedded(2, "Seed data", typeof(Program).Assembly, "MyApp.Migrations.002_seed.sql")
    .Build();

await db.MigrateAsync(migrations);

// Inspect applied migrations
var applied = await db.GetAppliedMigrationsAsync();
foreach (var m in applied)
    Console.WriteLine($"v{m.Version}: {m.Description} — {m.AppliedAt:g}");

Query Builder

var (sql, cmd) = new QueryBuilder("products")
    .Select("id", "name", "price")
    .Join("LEFT JOIN categories c ON c.id = products.category_id")
    .Where("price > @minPrice")
    .Where("c.name = @category")
    .OrderBy("price ASC")
    .Limit(20)
    .Offset(0)
    .Build(new { minPrice = 5.0, category = "Books" });

// Execute with a pooled connection
await using var pooled = await db.Pool.AcquireAsync();
cmd.Connection = pooled.Connection;
await using var reader = await cmd.ExecuteReaderAsync();

Microsoft DI Integration

// Program.cs
builder.Services.AddSqliteHelper(
    configure: o =>
    {
        o.DatabasePath     = "app.db";
        o.EnableWal        = true;
        o.EnableForeignKeys = true;
        o.MaxPoolSize       = 20;
    },
    migrations: new MigrationBuilder()
        .Add(1, "Initial schema", "CREATE TABLE ...")
        .Build());

// Register individual repositories
builder.Services.AddSqliteRepository<User>();
builder.Services.AddSqliteRepository<Product>();

Then inject anywhere:

public class UserService(SqliteRepository<User> users)
{
    public Task<User?> GetAsync(long id) => users.GetByIdAsync(id);
}

Configuration

var db = SqliteHelper.Open(o =>
{
    o.DatabasePath        = "data/myapp.db";
    o.EnableWal           = true;          // default: true
    o.EnableForeignKeys   = true;          // default: true
    o.MaxPoolSize         = 10;            // default: 10
    o.CommandTimeoutSeconds = 30;          // default: 30
    o.CacheSize           = -8000;         // 8 MB cache
    o.Synchronous         = SynchronousMode.Normal;
    o.AutoVacuum          = AutoVacuumMode.Incremental;
    // o.Password         = "secret";      // requires SQLCipher
});

In-Memory Database (Testing)

await using var db = SqliteHelper.OpenInMemory();
await db.MigrateAsync(TestMigrations);
var repo = db.Repository<Order>();
// full isolation — no file I/O

License

MIT © 2025 Vladyslav Zaiets — sarmkadan.com