BetterPractice.SqlCipher.Core 0.1.0

BetterPractice.SqlCipher

An encrypted embedded database for .NET, built on SQLCipher (SQLite with 256-bit AES encryption). Designed for concurrent server workloads — all database operations are serialized through a Channel<T>, so no external locking is required.

• Thread-safe by design — submit operations from any thread
• Encrypted at rest using SQLCipher 4 defaults (AES-256, PBKDF2-HMAC-SHA512, 256k iterations)
• Fluent query builder — no raw string concatenation required
• Typed expression system for WHERE clauses
• Source-generated ORM — zero reflection, AOT/iOS compatible
• Explicit migration system with rollback support

Installation

dotnet add package BetterPractice.SqlCipher.Core
dotnet add package BetterPractice.SqlCipher.runtime.osx-arm64   # or your target RID

The runtime package bundles the compiled libsqlcipher native binary for the target platform. Supported RIDs: osx-arm64, osx-x64, linux-x64, linux-arm64, win-x64, win-arm64.

Opening a Database

// Create a new encrypted database
await using var db = await DatabaseHandle.Create("/path/to/app.db", "your-passphrase");

// Open an existing database
await using var db = await DatabaseHandle.Open("/path/to/app.db", "your-passphrase");

DatabaseHandle is IAsyncDisposable. Always use await using to ensure the operation queue drains cleanly before the connection is closed.

Custom cipher configuration

var config = new CipherConfiguration(
    pageSize:      4096,
    kdfIterations: 256_000,
    kdfAlgorithm:  KdfAlgorithm.Pbkdf2HmacSha512,
    hmacAlgorithm: HmacAlgorithm.Sha512);

await using var db = await DatabaseHandle.Create("/path/to/app.db", "your-passphrase", config);

Cipher configuration is fixed at database creation and cannot be changed afterward.

Rekeying

await db.Rekey("new-passphrase");

Rekeying rewrites the entire database and is serialized through the operation queue like any other operation.

Raw SQL

Execute

// No parameters
await db.Execute("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)");

// Named parameters
int rowsAffected = await db.Execute(
    "INSERT INTO users (name) VALUES (@name)",
    new Dictionary<string, object?> { ["name"] = "Alice" });

Query

ResultSet rows = await db.Query(
    "SELECT id, name FROM users WHERE name = @name",
    new Dictionary<string, object?> { ["name"] = "Alice" });

foreach (Row row in rows)
{
    int    id   = row["id"].AsInt32();
    string name = row["name"].AsText();
}

Column values are accessed by name or index and expose typed accessors: AsInt32(), AsInt64(), AsDouble(), AsText(), AsBlob(), and IsNull.

Transactions

// Void body — use Action overload
await db.ExecuteTransaction(ctx =>
{
    ctx.Execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    ctx.Execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
});

// Returning a value — use generic Func overload
int rowsInserted = await db.ExecuteTransaction(ctx =>
    ctx.Execute("INSERT INTO accounts VALUES (3, 250)"));

If the body throws, the transaction is rolled back automatically. Reads within the body see uncommitted changes from earlier statements in the same transaction.

The body must be synchronous — async bodies are not supported. Awaiting inside a transaction would release the worker thread while the database connection is held, stalling all other operations.

Query Builder

The fluent query builder constructs SQL without string concatenation. All builders are immutable — each method returns a new instance.

Select

BuiltQuery q = Select.From("users")
    .Columns("id", "name", "email")
    .Where(new ColumnRef<string>("status") == "active")
    .OrderBy("name", SortDirection.Ascending)
    .Limit(20)
    .Offset(40)
    .Build();

ResultSet rows = await db.Query(q);

Insert

BuiltQuery q = Insert.Into("users")
    .Set("name",  "Bob")
    .Set("email", "bob@example.com")
    .Build();

await db.Execute(q);

Update

var id = new Param<int>("id");

BuiltQuery q = Update.Table("users")
    .Set("name", "Robert")
    .Where(new ColumnRef<int>("id") == id, id.Bind(42))
    .Build();

await db.Execute(q);

Create Table

BuiltQuery q = CreateTable.Named("events")
    .Column("id",         ColumnType.Integer, ColumnConstraint.PrimaryKey, ColumnConstraint.AutoIncrement)
    .Column("user_id",    ColumnType.Integer, ColumnConstraint.NotNull)
    .Column("name",       ColumnType.Text,    ColumnConstraint.NotNull)
    .Column("created_at", ColumnType.Text,    ColumnConstraint.NotNull)
    .Column("payload",    ColumnType.Blob)
    .IfNotExists()
    .Build();

await db.Execute(q);

Expression System

Expressions build typed WHERE clause fragments using operator overloads. They compose with & (AND), | (OR), and ! (NOT).

// Simple comparison
IExpression active = new ColumnRef<string>("status") == "active";

// Range check
IExpression senior = new ColumnRef<int>("age") >= 65;

// Compound expression
IExpression expr = active & senior;

ResultSet rows = await db.Query(
    Select.From("users").Where(expr).Build());

Named parameters in expressions

Use Param<T> when you want to bind a value by name rather than have the builder auto-generate a parameter name:

var minScore = new Param<double>("minScore");
var maxScore = new Param<double>("maxScore");

IExpression range = new ColumnRef<double>("score") >= minScore
                  & new ColumnRef<double>("score") <= maxScore;

BuiltQuery q = Select.From("leaderboard")
    .Where(range, minScore.Bind(50.0), maxScore.Bind(100.0))
    .Build();

Null checks

IExpression hasEmail    = new ColumnRef<string>("email").IsNotNull();
IExpression missingName = new ColumnRef<string>("name").IsNull();

IN clause

IExpression filter = new ColumnRef<int>("category_id").In(1, 2, 3);

BETWEEN

IExpression range = new ColumnRef<int>("age").Between(18, 65);

LIKE

IExpression search = new ColumnRef<string>("email").Like("%@example.com");

Migrations

Migrations implement IMigration and are applied in order. Already-applied migrations are skipped automatically. Each migration runs in its own transaction — a failure records no version and does not affect previously applied migrations.

public sealed class CreateUsersTable : IMigration
{
    public string Id => "001-create-users";

    public void Up(MigrationContext ctx)
    {
        ctx.Execute(
            CreateTable.Named("users")
                .Column("id",    ColumnType.Integer, ColumnConstraint.PrimaryKey, ColumnConstraint.AutoIncrement)
                .Column("name",  ColumnType.Text,    ColumnConstraint.NotNull)
                .Column("email", ColumnType.Text)
                .IfNotExists()
                .Build());
    }

    public void Down(MigrationContext ctx)
    {
        ctx.Execute("DROP TABLE users");
    }
}

public sealed class AddUserScore : IMigration
{
    public string Id => "002-add-user-score";

    public void Up(MigrationContext ctx)
    {
        ctx.Execute("ALTER TABLE users ADD COLUMN score REAL NOT NULL DEFAULT 0");
    }

    public void Down(MigrationContext ctx)
    {
        // SQLite does not support DROP COLUMN prior to 3.35.0
        ctx.Execute("CREATE TABLE users_new (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT)");
        ctx.Execute("INSERT INTO users_new SELECT id, name, email FROM users");
        ctx.Execute("DROP TABLE users");
        ctx.Execute("ALTER TABLE users_new RENAME TO users");
    }
}

Apply migrations on startup:

await db.Migrate(new IMigration[]
{
    new CreateUsersTable(),
    new AddUserScore(),
});

Roll back to a specific migration (exclusive — migrations after targetId are reversed in order):

await db.Rollback("001-create-users", new IMigration[]
{
    new CreateUsersTable(),
    new AddUserScore(),
});

Applied migration IDs are tracked in the _bp_migrations system table.

ORM

The source generator reads entity types annotated with [Table], [PrimaryKey], and [Column] and emits a companion mapper at compile time. No reflection is used — the generated code is fully AOT-compatible.

Define an entity

[Table("users")]
public sealed class User : IEntity<int>
{
    [PrimaryKey]
    [Column("id")]
    public int Id { get; set; }

    [Column("name")]
    public string Name { get; set; } = string.Empty;

    [Column("email")]
    public string? Email { get; set; }

    [Column("score")]
    public double Score { get; set; }

    [NotMapped]
    public string DisplayName => Name.ToUpperInvariant(); // not persisted
}

Save (insert or update)

// Insert — Id is 0, generator uses INSERT and populates Id from last_insert_rowid
var user = new User { Name = "Alice", Email = "alice@example.com", Score = 9.5 };
user = await db.Save(user);
// user.Id is now the auto-assigned primary key

// Update — Id is non-zero, generator uses INSERT OR REPLACE
user.Score = 10.0;
await db.Save(user);

Save multiple

var users = new[]
{
    new User { Name = "Bob" },
    new User { Name = "Carol" },
};
await db.SaveAll(users);

Fetch all

IReadOnlyList<User> all = await db.Fetch();

Fetch with expression

IReadOnlyList<User> topScorers = await db.Fetch(
    new ColumnRef<double>("score") >= 8.0);

Fetch one by primary key

User? user = await db.FetchOne(42);

Delete by primary key

await db.Delete(42);

Delete with expression

await db.Delete(new ColumnRef<double>("score") < 1.0);

Thread Safety

All operations on DatabaseHandle are thread-safe. Callers may submit operations concurrently from any number of threads; they are queued and executed serially in submission order. No external locking is needed.

License

BetterPractice.SqlCipher is released under the MIT License.

This package includes compiled binaries derived from SQLCipher (BSD 3-Clause, Copyright © 2008-2023 ZETETIC LLC) and SQLite (public domain). See THIRD_PARTY_NOTICES for full license text.