CosmoSQLClient.Sqlite.EntityFrameworkCore 2.2.0

CosmoSQLClient

A lightweight, high-performance database client library for .NET designed for low-latency, high-throughput applications. CosmoSQLClient provides a unified interface for multiple database engines while minimizing memory allocations and binary footprint.

NuGet Packages

Package	NuGet
CosmoSQLClient.Core
CosmoSQLClient.MsSql
CosmoSQLClient.Postgres
CosmoSQLClient.MySql
CosmoSQLClient.Sqlite
CosmoSQLClient.CosmoKv
CosmoSQLClient.CosmoKvHttp
CosmoSQLClient.MsSql.EntityFrameworkCore
CosmoSQLClient.Postgres.EntityFrameworkCore
CosmoSQLClient.MySql.EntityFrameworkCore
CosmoSQLClient.Sqlite.EntityFrameworkCore

Key Features

• High-Performance Pipeline Architecture: Built on top of System.IO.Pipelines for efficient asynchronous I/O and protocol decoding.
• Zero-Allocation Row Decoding: Employs a strictly-constrained generic pipeline and struct-based token handlers to eliminate boxing and reduce heap pressure.
• Native Streaming: Supports IAsyncEnumerable for row-by-row processing, ideal for large datasets and reactive streams.
• Lightweight Footprint: Core library and providers are significantly smaller than standard ADO.NET implementations (e.g., MSSQL provider is ~207KB vs ~17MB for Microsoft.Data.SqlClient).
• Unified API: Shared ISqlDatabase interface across all supported engines (MSSQL, PostgreSQL, MySQL, SQLite, CosmoKv, CosmoKvHttp).
• Entity Framework Core Integration: Optional bridge packages let you use CosmoSQLClient connections with the standard EF Core relational providers for MSSQL, PostgreSQL, MySQL, and SQLite.
• Advanced JSON Support: Direct streaming of database results to JSON (NDJSON/JSON array) with minimal buffering.

Supported Database Engines

CosmoKv T-SQL Surface (v2.1)

CosmoSQLClient.CosmoKv runs a T-SQL subset directly on top of an embedded CosmoKv LSM-tree store. It implements the same ISqlDatabase interface as the network drivers, so application code can swap to it without changing its query shapes.

The supported surface as of v2.1:

Out of scope (v2.1) — LEFT/RIGHT/FULL/CROSS JOIN, subqueries, CTEs (WITH), MERGE / ON CONFLICT, window functions (OVER, ROW_NUMBER), recursive CTEs, dynamic SQL, CREATE PROCEDURE / EXEC. These can be added in later releases if a consumer needs them; CosmoMailServer's migration verified zero usage of any of them.

Installation

Add the package for your specific database engine:

dotnet add package CosmoSQLClient.MsSql
dotnet add package CosmoSQLClient.Postgres
dotnet add package CosmoSQLClient.MySql
dotnet add package CosmoSQLClient.Sqlite
dotnet add package CosmoSQLClient.CosmoKv         # embedded T-SQL on CosmoKv
dotnet add package CosmoSQLClient.CosmoKvHttp     # T-SQL over HTTP to CosmoKvD

For Entity Framework Core integration, install the matching bridge package for your provider:

dotnet add package CosmoSQLClient.MsSql.EntityFrameworkCore
dotnet add package CosmoSQLClient.Postgres.EntityFrameworkCore
dotnet add package CosmoSQLClient.MySql.EntityFrameworkCore
dotnet add package CosmoSQLClient.Sqlite.EntityFrameworkCore

Quick Start

Basic Query (MSSQL)

using CosmoSQLClient.MsSql;

var connStr = "Server=localhost;Database=Sales;User Id=sa;Password=your_password;";
await using var conn = await MsSqlConnection.OpenAsync(connStr);

await using var cmd = conn.CreateCommand("SELECT TOP 10 * FROM Invoices");
await using var reader = await cmd.ExecuteReaderAsync();

while (await reader.ReadAsync())
{
    var total = reader.GetDecimal("TotalAmount");
    Console.WriteLine($"Invoice Total: {total}");
}

Embedded T-SQL (CosmoKv)

using CosmoSQLClient.CosmoKv;
using CosmoSQLClient.Core;

await using var conn = await CosmoKvConnection.OpenAsync(
    new CosmoKvConfiguration { DataSource = "/var/lib/app/db" });

await conn.ExecuteAsync("""
    CREATE TABLE IF NOT EXISTS Users (
        Id BIGINT IDENTITY PRIMARY KEY,
        Email NVARCHAR(256) NOT NULL,
        CreatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE())
    """);

// INSERT … OUTPUT returns the just-allocated IDENTITY.
var inserted = await conn.QueryAsync(
    "INSERT INTO Users (Email) OUTPUT INSERTED.Id VALUES (@e)",
    new[] { SqlParameter.Named("@e", SqlValue.From("alice@d.com")) });
long newId = inserted[0]["Id"].AsInt() ?? 0;

// INNER JOIN with qualified column refs.
var rows = await conn.QueryAsync(
    "SELECT u.Email FROM Users u JOIN Mailboxes m ON m.UserId = u.Id");

Advanced Streaming (IAsyncEnumerable)

Rows are yielded as they arrive from the socket, ensuring the full result set is never buffered in memory.

await foreach (var row in conn.Advanced.QueryStreamAsync("SELECT * FROM LargeTable"))
{
    var id = row["Id"].AsInt32();
    // Process one row at a time
}

Entity Framework Core

CosmoSQLClient can be used as the underlying DbConnection for the standard EF Core relational providers. Install the matching EF Core bridge package, then configure your DbContext with the Cosmo-specific extension for that database.

using Microsoft.EntityFrameworkCore;

services.AddDbContext<AppDbContext>(options =>
    options.UseCosmoSqlServer("Server=localhost;Database=Sales;User Id=sa;Password=your_password;TrustServerCertificate=True;"));

services.AddDbContext<AppDbContext>(options =>
    options.UseCosmoPostgres("Host=localhost;Database=Sales;User Id=postgres;Password=your_password;"));

services.AddDbContext<AppDbContext>(options =>
    options.UseCosmoMySql(
        "Host=localhost;Database=Sales;User Id=root;Password=your_password;",
        new MySqlServerVersion(new Version(8, 0, 0))));

services.AddDbContext<AppDbContext>(options =>
    options.UseCosmoSqlite("Data Source=sales.db"));

Technical Architecture

Zero-Allocation Pipeline

CosmoSQLClient implements a generic protocol decoder (ProcessTokensAsync) using struct handlers. This architecture ensures:

• No Boxing: SqlRow and SqlValue types are never cast to object during decoding.
• JIT Devirtualization: The row-parsing path is inlined by the .NET JIT compiler, removing interface dispatch overhead.
• Minimal Heap Pressure: Bounded memory footprint regardless of result set size.

Package Size Comparison

CosmoSQLClient avoids heavy dependencies like Azure.Identity, MSAL, or Microsoft.Data.SqlClient.SNI.

SqlValue Type Mapping

SqlValue is the universal cell type returned by advanced query methods. It wraps the raw wire value and provides typed accessors:

Advanced Usage

Integrated Security (MSSQL)

Supports native NTLMv2 authentication for Windows/Linux environments without external dependencies.

var connStr = "Server=myserver;Integrated Security=SSPI;Trust Server Certificate=true;";
await using var conn = await MsSqlConnection.OpenAsync(connStr);

JSON Streaming

Stream query results directly to a PipeWriter or Stream as JSON with zero intermediate allocations.

await conn.Advanced.QueryJsonStreamAsync(
    "SELECT * FROM Users",
    pipeWriter,
    format: JsonOutputFormat.Array);

Stored Procedures (MSSQL)

var result = await conn.Advanced.ExecuteProcAsync("GetCustomerInvoices", new {
    CustomerId = 123,
    Year = 2024
});

Related Projects

• CosmoSQLClient-Swift — Swift NIO port of CosmoSQLClient with the same API design and wire-protocol implementations for server-side Swift.
• SQLClient-Swift — The original MSSQL driver using FreeTDS (predecessor to the Swift port).

License

Licensed under the MIT License.