MilkStore 0.2.0

MilkStore

A lightweight micro-ORM for .NET with a clean API and multi-database support.

Features

• Multi-database support - PostgreSQL, SQL Server, SQLite
• Zero provider dependencies - Install only what you use
• Clean anonymous object API for Insert/Update operations
• Sync and async - every operation has an ...Async form
• Expression Where with ordering and paging done in the database
• Automatic table creation on first use
• Injection-safe by construction - interpolated SQL is always parameterised
• Modern C# - Uses static abstract interface members
• Convention-based with attribute overrides

On by default

You should not have to know a library's tricks to get its speed. These need no code:

The two that still need a decision from you are InsertMany instead of a loop of Insert, and a transaction around a batch of writes. Log.Collect() will tell you when you have missed either, using your timings rather than ours - see Logging.

Installation

dotnet add package MilkStore

# Then install only 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

Quick Start

using MilkStore;

// Configure the connection. Defaults are set for throughput - see Connections below.
PGStore.Connect.Host("localhost").Database("mydb").User("user").Password("pass");

// Define your entity
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; }   // enums are stored as their backing integer
}

// Insert. Columns you leave out fall back to the column default.
var user = User.Insert(new { Name = "Alice", Email = "alice@example.com" });

// Many rows at once - one statement instead of one per row
User.InsertMany(people.Select(p => new { p.Name, p.Email }));

// Update (uses [Identity] in WHERE clause)
user.Update(new { Name = "Alice Smith" });

// Find
var found = User.FindById(user.UserID);

// Get all
var allUsers = User.All();

// Typed query - no SQL, no strings, columns come from the entity
var admins = User.Where(u => u.Name.Contains("Admin") && u.Tier == Tier.Pro);

// Raw SQL when you need it - interpolated holes become parameters
var recent = User.Query($"""SELECT * FROM "User" WHERE "Joined" > {cutoff}""");

// Delete
user.Delete();

Connections

Each store carries a builder whose defaults are already set for throughput:

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

PGStore.Connect.Pool(min: 2, max: 50).CommandTimeout(60);   // override a default
PGStore.Connect.Set("Keepalive", 30);                        // any keyword the builder misses
PGStore.Connect.Raw("Host=...;Username=...");                // verbatim, no defaults at all

Assigning ConnectionString is the same as calling Raw, so existing code keeps working - and keeps opting out of the defaults.

Connection pooling is the provider's job and all three do it well, so MilkStore does not add its own. What it does add is warming, because pools start cold and Min Pool Size does not fill them.

This happens by default, when you set the connection string. That is the earliest moment the library knows where the database is, and in a real application it happens during startup - well before the first request:

PGStore.Connect.Host("db").Database("app").User("u").Password("p");
// three connections are already being opened on a background task

Warming on first use would be close to pointless, because the first caller is precisely the one that would have paid the cold cost, and it still would. Configuration time is early enough to actually help it. Verified against SQL Server by asking the server what it was holding: after configuring the store and doing nothing else, sys.dm_exec_sessions already listed three MilkStore connections.

A fluent chain sets several keywords in a row, so the warm is scheduled once the settings stop changing rather than after each call.

Store<PGStore>.AutoWarm = 0;   // opt out
Store<PGStore>.AutoWarm = 16;  // or open more
PGStore.Warm(8);               // or do it yourself, synchronously

It matters most against a remote server: a first query measured 54.9 ms cold against 11.3 ms warmed, and four concurrent first queries 99.4 ms against 13.2 ms.

SQL Server certificates

Microsoft.Data.SqlClient defaults to Encrypt=True, so a server with a self-signed certificate fails with "The certificate chain was issued by an authority that is not trusted". TrustServerCertificate=True is not a MilkStore default, because silently accepting any certificate is a downgrade. Opt in explicitly for development:

MSSQLStore.Connect.Server("...").Database("...").TrustCertificate();

Logging

Off by default, and free when off - a null check per statement, with nothing timed until something is listening.

Log.ToConsole();                              // every statement
Log.Slow(TimeSpan.FromMilliseconds(50));      // only the slow ones
Log.On = q => logger.LogDebug("{Query}", q);  // anything else

Log.Collect() aggregates instead, grouping by statement so a query run in a loop shows up once with its count - and then tells you what to do about it:

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       57.7      0.29      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.

Taking that advice turned those 233 statements and 59.7 ms into 2 statements and 0.4 ms. The advice quotes your elapsed time, not a benchmark multiplier - what a fix is worth depends entirely on where your database is and how far away it is. stats.Advice() returns the same lines on their own if you would rather assert on them in a test than read them.

Performance

What matters depends on where the database is, and the two cases are not alike.

A local database (SQLite)

Measured on one machine, single indexed row:

Batch writes into a transaction. Roughly 300x, because otherwise every statement is its own implicit transaction and pays a disk flush. Reads gain 3-4x too, because each statement otherwise re-acquires locks and re-checks the schema. Inside a scope MilkStore also reuses its commands, so the provider keeps its compiled statement rather than re-parsing - about 2x on a batch of inserts.

using var tx = SQLiteStore.Begin();
foreach (var row in rows) User.Insert(new { row.Name });
tx.Commit();

A remote database (PostgreSQL, SQL Server)

Latency dominates and the advice inverts. Measured against a real SQL Server 2022 over a ~9 ms link, writing 500 rows:

Those multipliers are a property of the 9 ms link, not of MilkStore. The thing that carries over is the shape: batch your writes, because everything remote is round trips. 500 statements at 9 ms each is 4.5 seconds no matter what else you do, and the same 500 rows over a 1 ms link would show a proportionally smaller win. InsertMany sends them as one multi-row INSERT, chunked to the dialect's parameter limit.

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

A transaction on its own does nothing for remote throughput - it does not remove a single round trip. Reused commands likewise measured within noise. Both are worth having for other reasons; neither is a remote performance strategy.

Warm the pool at startup, which is the other big one:

Again, the absolute numbers are this link's; what generalises is that a cold open costs about one full handshake and Min Pool Size does not remove it. Min Pool Size only caps how many connections are retained; it does not open any eagerly. Warm does, which is why it exists, and why three connections are warmed for you as soon as the store is configured.

PGStore.Warm(4);               // synchronously, if you want to block on it
Store<PGStore>.AutoWarm = 8;   // or change how many are warmed automatically

The 300x transaction figure above is a SQLite number and does not carry over.

Transactions

Without a transaction each statement commits on its own, which means one disk flush per statement. Batching writes into a scope removes all but one of those - on a local SQLite file that measured roughly 300x over a few thousand rows, and against a remote server it is worth much less, because there the cost is the round trip rather than the flush.

PGStore.Transaction(() =>
{
    foreach (var row in rows)
        User.Insert(new { row.Name });
});

Transaction commits when the work returns and rolls back if it throws, so there is no scope to forget to commit and none to forget to dispose. It returns a value too, if the work does.

The manual form is there when you need to decide the outcome yourself:

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 are safe when things go wrong. An exception rolls the scope back and the exception that reaches you is your own - not whatever the rollback ran into on the way out. If the rollback itself fails, because the server aborted the transaction or the connection dropped, that is swallowed and the connection still goes back to the pool. Verified by killing a SQL Server session mid-transaction: the caller still received its own exception, and the store kept working. That last part matters more than it sounds - a connection that fails to return to the pool is how one transient database error becomes a hung application a few hundred exceptions later. 200 consecutive failed transactions through a pool of 4 never exhausted it.

The scope is ambient: every operation on that store joins it, so nothing has to take a transaction parameter. Nesting joins the outer scope rather than opening a second connection, and a nested Rollback() stops the outer scope committing. Work after Commit() runs outside the scope, which is what it reads like.

Threads

Without a transaction, use MilkStore from as many threads as you like. Every statement takes its own pooled connection and nothing is shared, so parallel writers, parallel readers and parallel InsertMany are all fine. Identity keys are safe too: SCOPE_IDENTITY() and last_insert_rowid() are connection-scoped, and each thread has its own connection. Checked on SQL Server with eight threads inserting at once - no duplicate keys, none pointing at another thread's row.

A transaction scope runs one statement at a time. It holds a single connection, and no database driver allows two statements at once on one connection. The scope rides on AsyncLocal so that it survives an await, and the price of that is that it also flows into Task.Run and Parallel.For - so this looks reasonable and is not:

using var tx = MSSQLStore.Begin();

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

tx.Commit();

Every worker joins the outer scope and they pile onto one connection. MilkStore detects it and throws something you can act on. Left to the driver it is far worse: SQLite reports "bad parameter or other API misuse" and loses the batch, and SQL Server throws NullReferenceException from inside Microsoft.Data.SqlClient and leaves the connection in a state where the row count goes backwards between two reads.

Do one of these instead:

User.InsertMany(rows.Select(r => new { r.Name }));   // usually faster than either

Parallel.ForEach(rows, row =>                        // or a scope per thread
{
    using var tx = MSSQLStore.Begin();
    User.Insert(new { row.Name });
    tx.Commit();
});

The rule is deliberately about overlap rather than thread identity, because a thread-affinity check would be wrong for the async API - a continuation legitimately resumes on a different thread. Awaiting inside a scope is fine, including when it moves threads:

using var tx = PGStore.Begin();

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

tx.Commit();

Where

Where takes a predicate and translates it to SQL. Columns come from the entity's metadata and every value is bound as a parameter, so there is no SQL string to get wrong and nothing to quote:

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))

Store<PGStore>.Count<User>(u => u.Active)         // and Any<User>(...)

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

Anything it cannot translate throws NotSupportedException naming the subexpression, rather than quietly filtering in memory and returning rows you did not ask for:

User.Where(u => u.Name.Length > 3)   // throws - Length has no column

Count and Any live on Store<TStore> rather than on the entity because Count is a common column name, and a property of that name would shadow a static method of the same name.

Ordering and paging

Ordering and paging happen in the database, not after the rows arrive:

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

Each dialect gets its own spelling: LIMIT/OFFSET on PostgreSQL and SQLite, OFFSET ... FETCH NEXT on SQL Server, which has no LIMIT and will not accept a row limit without an ORDER BY.

take or skip without an ordering returns that many rows, but which rows is undefined - no database promises an order you did not ask for. The same unordered take returned a different set on SQL Server than on SQLite. Pass an ordering whenever the page must be stable.

Changing many rows at once

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

Both send a single statement rather than a round trip per row.

Async

Every operation has an async form. The point is not that the database gets faster - it is that a request handler stops holding a thread while it waits.

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);

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

Transactions take the work rather than handing back a scope:

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

There is deliberately no BeginAsync. The scope is ambient through AsyncLocal, and a value written to an AsyncLocal inside an async method is not visible to that method's caller - so using var tx = await BeginAsync() would hand back a scope that no later call could see, and every statement would quietly commit on its own. Passing the work in means the scope is established in the frame that then runs it, where it does flow correctly. This is not hypothetical: an earlier draft did it the other way and every write escaped its transaction.

Awaiting inside a scope is fine even when the continuation resumes on another thread. Verified against SQL Server: twenty inserts in one async transaction, spread over four distinct threads, committed as one unit and rolled back as one unit.

One caveat worth knowing: Microsoft.Data.Sqlite's async methods complete synchronously. The async API works there and returns the right answers, but it does no real async I/O, so there is nothing to gain from it on SQLite. PostgreSQL and SQL Server do the real thing.

Store<T>.InTransaction tells you whether you are already inside a scope, for code that can be called either way.

Queries and SQL safety

Query takes an interpolated string and binds every hole as a parameter - values are never concatenated into the SQL text:

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

This is enforced by the compiler rather than by convention: the parameter is a custom interpolated string handler, and a handler conversion outranks the string conversion in overload resolution, so an interpolated string can never fall through to a raw overload.

Quoting

The inner quotes are SQL identifier quoting and are not optional. Tables are created with quoted identifiers, so PostgreSQL stores the name as literally User; an unquoted identifier folds to lowercase, and FROM User would look for a table named user. User is a reserved word in PostgreSQL and T-SQL besides.

A raw string literal ($""" ... """) is the tidiest way to write them - the backslashes in \"User\" are only C# escaping and never reach the database.

Identifiers

A hole always binds as a parameter, and no database accepts a parameter where an identifier belongs. So a bare string table name compiles and then fails at runtime:

User.Query($"""SELECT * FROM {"User"}""")   // SELECT * FROM @0  -- wrong

That is the safe failure mode - a hole can never quietly become injected SQL. For names you are writing out by hand, just quote them - a raw string literal keeps it readable:

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

When the identifier is dynamic, or you want one query to work across dialects, Sql.Id validates the name and quotes it the way the target database wants ("Name" on PostgreSQL and SQLite, [Name] on SQL Server):

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

Sql.Id rejects anything but letters, digits, underscore and . (for schema qualification), so it cannot break out of the identifier position even when the name came from user input - Sql.Id(untrusted) throws rather than injects. User.Table is the entity's own table name.

Do not reach for Sql.Id on a name you typed as a literal; it is more ceremony than the quotes it replaces. It earns its place when the name is a variable.

For a fragment that is neither a value nor a plain identifier - DESC NULLS LAST, say - Sql.Raw splices verbatim. It is unchecked, so never build one from user input.

Why not infer this? Deciding "a hole after FROM is an identifier" would be wrong on standard SQL: EXTRACT(MONTH FROM {date}), SUBSTRING({s} FROM {start} FOR {len}) and TRIM(LEADING {c} FROM {text}) all need a parameter straight after FROM. A keyword heuristic that guesses wrong there concatenates user input into the SQL. The meaning of a hole is carried by its type instead, where it is unambiguous and reviewable.

To run SQL you assembled yourself, use the *Raw methods (QueryRaw, ExecuteRaw, ExecuteScalarRaw, IsRaw). They take their text verbatim and parameterise nothing.

Attributes

Schema generation

Tables are created on first use of an entity type, and columns added to an entity later are added to the table (removed properties are left alone).

• An [Identity] column uses the dialect's auto-increment mechanism - 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 that omits it still works 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.

Supported property types

bool, char, all integer types, decimal, double, float, string, Guid, DateTime, DateTimeOffset, DateOnly, TimeOnly, TimeSpan, byte[], any enum, and the Nullable<T> form of any of those.

Building

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

License

MIT