MilkStore 0.3.0

MilkStore

A lightweight micro-ORM for .NET with a static, string-free API and support for PostgreSQL, SQL Server and SQLite.

• Entities are plain classes; tables are created and extended automatically.
• Insert and update take anonymous objects.
• Where takes a lambda and is translated to SQL.
• Interpolated SQL is always parameterised.
• Every operation has an async form.
• No dependency on any database driver; install only the one you use.

Installation

dotnet add package MilkStore

# Then the provider(s) you need:
dotnet add package Npgsql                    # PostgreSQL
dotnet add package Microsoft.Data.SqlClient  # SQL Server
dotnet add package Microsoft.Data.Sqlite     # SQLite

Targets .NET 10.

Quick start

using MilkStore;

PGStore.Connect.Host("localhost").Database("mydb").User("user").Password("pass");

public class User : PGStoreObject<User>
{
    [Identity] public int UserID { get; set; }

    [Required, MaxLength(100)]
    public string Name { get; set; } = "";

    public string? Email { get; set; }
    public Tier    Tier  { get; set; }
}

var user = User.Insert(new { Name = "Alice", Email = "alice@example.com" });

user.Update(new { Name = "Alice Smith" });

var found  = User.FindById(user.UserID);
var admins = User.Where(u => u.Name.Contains("Admin") && u.Tier == Tier.Pro);

user.Delete();

Derive from PGStoreObject<T>, MSSQLStoreObject<T> or SQLiteStoreObject<T> to bind an entity to a store. The table is created the first time the entity is used.

Entities

Supported property types: bool, char, every integer type, decimal, double, float, string, Guid, DateTime, DateTimeOffset, DateOnly, TimeOnly, TimeSpan, byte[], any enum, and the Nullable<T> form of any of these.

Reading

User.FindById(id)                         // one row by identity, or null
User.All()                                // every row
User.Where(u => u.Active)                 // rows matching a predicate
User.First(u => u.Email == address)       // first match, or null

Store<PGStore>.Count<User>(u => u.Active) // COUNT(*)
Store<PGStore>.Any<User>(u => u.Active)   // COUNT(*) > 0

Count and Any are on Store<TStore> rather than on the entity, because an entity property named Count would shadow a static method of the same name.

Predicates

User.Where(u => u.Name == name)
User.Where(u => u.Active && u.Age >= 18)
User.Where(u => u.Score == null)                    // IS NULL
User.Where(u => u.Name.StartsWith("Ad"))            // LIKE 'Ad%'
User.Where(u => tiers.Contains(u.Tier))             // IN (...)
User.Where(u => u.Joined > DateTime.UtcNow.AddDays(-7))

Supported: == != < <= > >=, && || !, comparison with null, .HasValue, bare bool columns, string.Contains / StartsWith / EndsWith / Equals, and Contains over a collection.

Anything else throws NotSupportedException naming the subexpression. Predicates are never evaluated in memory, so a query either runs in the database or fails loudly.

User.Where(u => u.Name.Length > 3)   // throws: Length is not a column

Ordering and paging

Ordering and paging are applied by the database.

User.All(u => u.Name)                                      // ORDER BY Name
User.All(u => u.Joined, descending: true, take: 20)        // newest 20
User.Where(u => u.Active, u => u.Name, take: 20, skip: 40) // third page of 20
User.First(u => u.Active, u => u.Joined, descending: true) // newest match

orderBy must be a single mapped column; anything else throws.

take or skip without an orderBy returns that many rows, but which rows is undefined and varies by database. Pass an ordering whenever the result must be stable or pages must line up.

Writing

var user = User.Insert(new { Name = "Alice" });   // returns the entity, identity populated

user.Update(new { Name = "Alice Smith" });        // by identity
user.Delete();                                    // by identity

Columns omitted from an Insert take the column's default.

Many rows

User.InsertMany(people.Select(p => new { p.Name, p.Email }));

Sends multi-row INSERT statements, split to stay under the dialect's parameter limit, inside a transaction of its own. Identity values are not read back; use Insert when you need them.

Many rows matching a predicate

User.Delete(u => u.Joined < cutoff);
User.Update(u => u.Joined < cutoff, new { Active = false });

Each sends a single statement.

Transactions

PGStore.Transaction(() =>
{
    from.Update(new { Balance = from.Balance - 10 });
    to.Update(new { Balance = to.Balance + 10 });
});

Transaction commits when the work returns and rolls back if it throws. The exception propagates unchanged. An overload returns a value:

var total = PGStore.Transaction(() => Store<PGStore>.Count<User>(u => u.Active));

To control the outcome yourself, open a scope:

using var tx = PGStore.Begin();

foreach (var row in rows)
    User.Insert(new { row.Name });

tx.Commit();

Leaving the scope without committing rolls back. Both forms require a using, or the connection is held until garbage collection.

The scope is ambient: every operation on that store joins it, so nothing takes a transaction parameter. Nesting joins the outer scope rather than opening a second connection, and a nested Rollback() prevents the outer scope committing. Work after Commit() runs outside the scope.

If a rollback fails — because the server already ended the transaction, or the connection dropped — the failure is suppressed and the connection is returned to the pool regardless.

Store<T>.InTransaction reports whether the caller is already inside a scope.

Async

Every operation has an async form taking an optional CancellationToken.

var user  = await User.InsertAsync(new { Name = "Ada" });
var found = await User.FindByIdAsync(user.UserID);
var page  = await User.WhereAsync(u => u.Active, u => u.Name, take: 20);
var first = await User.FirstAsync(u => u.Active);
var all   = await User.AllAsync();

await User.InsertManyAsync(rows.Select(r => new { r.Name }));
await User.UpdateAsync(u => u.Stale, new { Active = false });
await User.DeleteAsync(u => u.Stale);

await user.UpdateAsync(new { Name = "Ada L." });
await user.DeleteAsync();

await Store<PGStore>.CountAsync<User>(u => u.Active);
await Store<PGStore>.AnyAsync<User>(u => u.Active);

Transactions take the work:

await PGStore.TransactionAsync(async () =>
{
    await from.UpdateAsync(new { Balance = from.Balance - 10 });
    await to.UpdateAsync(new { Balance = to.Balance + 10 });
});

var total = await PGStore.TransactionAsync(async () => await Store<PGStore>.CountAsync<User>(u => u.Active));

There is no BeginAsync. A transaction scope must be established in the frame that runs the work, so the async form takes a delegate rather than returning a scope.

Awaiting inside a scope is supported, including when the continuation resumes on another thread.

Microsoft.Data.Sqlite implements its async methods synchronously. The async API returns correct results on SQLite but performs no asynchronous I/O there; PostgreSQL and SQL Server do.

Threads

Without a transaction, use MilkStore from as many threads as you like. Every statement takes its own pooled connection, so parallel readers, parallel writers and parallel InsertMany are all safe, and identity values are per-connection and cannot cross between threads.

A transaction scope runs one statement at a time. It holds a single connection, and no driver permits two statements at once on one connection. The scope flows into Task.Run and Parallel.For, so work started inside a scope joins it:

using var tx = MSSQLStore.Begin();

Parallel.ForEach(rows, row => User.Insert(new { row.Name }));   // throws

tx.Commit();

Use one of these instead:

User.InsertMany(rows.Select(r => new { r.Name }));

Parallel.ForEach(rows, row =>
{
    using var tx = MSSQLStore.Begin();
    User.Insert(new { row.Name });
    tx.Commit();
});

Overlapping statements on one scope throw InvalidOperationException. Sequential use across threads — including an await that resumes elsewhere — is allowed.

Raw SQL

Query takes an interpolated string and binds every hole as a parameter.

User.Query($"""SELECT * FROM "User" WHERE "Name" = {name} AND "Tier" = {Tier.Pro}""")
// SELECT * FROM "User" WHERE "Name" = @0 AND "Tier" = @1

A hole is always a parameter and can never become SQL text. The parameter is an interpolated string handler, so this is enforced at compile time.

Each has a *Raw counterpart — QueryRaw, ExecuteRaw, ExecuteScalarRaw, IsRaw — taking SQL verbatim, with no parameterisation. Async forms: QueryAsync, QueryRawAsync, ExecuteAsync, ExecuteRawAsync, ExecuteScalarAsync, ExecuteScalarRawAsync.

Identifiers

Quote identifiers in the SQL text. Tables are created with quoted names, so an unquoted identifier will not match on PostgreSQL, and User is a reserved word on several dialects. A raw string literal keeps this readable:

User.Query($"""SELECT * FROM "User" WHERE "Name" LIKE {pattern}""")

A hole cannot be an identifier — no database accepts a parameter where a table or column name belongs, so this compiles and then fails at runtime:

User.Query($"""SELECT * FROM {"User"}""")   // becomes: SELECT * FROM @0

For an identifier that is not known until runtime, Sql.Id validates and quotes it for the target dialect ("Name" on PostgreSQL and SQLite, [Name] on SQL Server):

User.Query($"SELECT * FROM {User.Table} ORDER BY {Sql.Id(sortColumn)} DESC")

Sql.Id accepts only letters, digits, underscore and ., and throws otherwise, so it is safe with untrusted input. Entity.Table is the entity's own table name.

Sql.Raw splices a fragment verbatim, for things that are neither values nor identifiers:

User.Query($"SELECT * FROM {User.Table} ORDER BY {Sql.Id(col)} {Sql.Raw(direction)}")

Sql.Raw is unchecked. Never build one from user input.

Connections

Each store carries a connection builder.

SQLiteStore.Connect.File("app.db");
PGStore.Connect.Host("localhost").Database("mydb").User("u").Password("p");
MSSQLStore.Connect.Server(".").Database("mydb").Trusted();

Set applies any keyword the fluent surface does not cover, and returns the same builder, so it composes with the rest:

MSSQLStore.Connect.Server("db").Set("Packet Size", 8192).Database("app").Trusted();

Raw replaces the whole string and discards every default. Assigning ConnectionString is the same as calling Raw.

PGStore.Connect.Raw("Host=...;Username=...");
PGStore.ConnectionString = "Host=...;Username=...";

Has("Encrypt") reports whether a keyword is set, including inside a Raw string. IsRaw reports whether a raw string is in use.

These keywords are set unless you override them:

Turn WAL off with SQLiteStore.Connect.Wal(false).

Encryption (SQL Server)

MSSQLStore.Connect.Encrypt(true);                      // Mandatory
MSSQLStore.Connect.Encrypt("Strict");                  // TDS 8.0
MSSQLStore.Connect.TrustCertificate();                 // accept a self-signed certificate
MSSQLStore.Connect.CertificateHostName("db.internal"); // validate against a different name

Microsoft.Data.SqlClient encrypts by default, so a server with a self-signed certificate fails until you call TrustCertificate(). Encrypt("Strict") ignores TrustCertificate and requires a certificate that validates; use CertificateHostName when connecting by IP or through a load balancer.

Server(host, port) pins the connection to TCP:

MSSQLStore.Connect.Server("10.0.0.5", 1433).Database("app");

Given a bare host name, SqlClient may fall back to Named Pipes when TCP does not answer and report a Named Pipes error, which obscures the real cause. Prefer the port overload for any server that is not local.

Pool warming

Connection pooling is left to the provider. Pools start empty, so the first query otherwise pays for the full connection handshake.

Three connections are opened on a background task as soon as the connection settings are set.

Store<PGStore>.AutoWarm = 0;    // disable
Store<PGStore>.AutoWarm = 16;   // open more
PGStore.Warm(4);                // open some now, synchronously

AutoWarm is capped by the pool's maximum size, leaving one connection free for the caller.

Defining a store

The built-in stores are ordinary types. Declare your own to configure a database in code, or to use several databases at once:

public class Reporting : SelfLoadingStore<Reporting>, IStore, IProviderInfo
{
    public static SqliteOptions Connect { get; } = new SqliteOptions().File("reports.db");

    static Reporting() => Store<Reporting>.Warms(Connect);

    public static string DatabaseName { get; set; } = "reporting";

    public static string ConnectionString
    {
        get => Connect.ToString();
        set => Connect.Raw(value);
    }

    public static ISqlSyntax Syntax => ISqlSyntax.SQLite;

    public static string Package    => "Microsoft.Data.Sqlite";
    public static string Connection => $"Microsoft.Data.Sqlite.SqliteConnection, {Package}";
}

public class Metric : StoreObject<Reporting, Metric>
{
    [Identity] public int    MetricID { get; set; }
    public            string Label    { get; set; }
}

Stores are independent: separate connections, separate pools and separate ambient transactions.

Store<T>.Warms(Connect) opts the store into pool warming. It must be called from a static constructor, so that Connect is assigned by the time it runs.

Logging

Logging is off by default and costs one null check per statement while off.

Log.ToConsole();                              // every statement
Log.Slow(TimeSpan.FromMilliseconds(50));      // only statements at or over a threshold
Log.On = q => logger.LogDebug("{Query}", q);  // any other sink
Log.Off();

A Query carries the store name, SQL text, elapsed time, rows affected, and whether it ran inside a transaction.

Log.Collect() aggregates instead of reporting each statement, grouping by SQL text:

using var stats = Log.Collect();

DoSomeWork();

Console.Write(stats.Report());

233 statements, 59.7 ms total
 count   total ms    avg ms    max ms  statement
   200       59.0      0.30      3.10  INSERT INTO "Person" ("Name", "Age") VALUES (@p0, @p1) ...
    30        0.1      0.00      0.02  SELECT "PersonID", "Name", "Age" FROM "Person" WHERE "PersonID" = @id

* 200 separate INSERTs into the same table, 59 ms. Entity.InsertMany(rows) sends them as one
  statement, so the whole loop costs one round trip.
* the same SELECT ran 30 times, 0.11 ms, which usually means a query inside a loop. Fetch the
  rows once with Where(...) and match them up in memory.
* nothing ran inside a transaction. One scope around a batch of work saves a round trip and
  a disk flush per statement.

stats.Report(top) returns the table and the notes; stats.Advice() returns the notes alone. stats.Count and stats.Total are the totals. Disposing the collector restores the previous sink.

Schema generation

Tables are created the first time an entity is used, and properties added to an entity later are added to the table. Removed properties leave their columns alone.

• [Identity] uses the dialect's auto-increment: SERIAL on PostgreSQL, IDENTITY(1,1) on SQL Server, the INTEGER PRIMARY KEY rowid alias on SQLite.
• A non-nullable value-type column is NOT NULL DEFAULT default(T), so an Insert may omit it and the column can be added to a table that already has rows.
• Enums map to their backing integral type. Nullable<T> maps to the same column type as T.

To execute a setting on every new connection, implement IStore.Configure(DbConnection) on the store.

Building

dotnet build
dotnet test
dotnet run --project samples/MilkStore.Sample

License

MIT