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.CosmoKv.Cli` (dotnet tool: `cosmokv`)
`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

Engine	Protocol	Supported Features
MSSQL	TDS 7.x	Integrated Security (NTLMv2), Stored Procedures, Transactions, Named Instances
PostgreSQL	Frontend/Backend 3.0	MD5/Scram-SHA-256 Auth, Transactions, Parameterized Queries
MySQL	MySQL Protocol 4.1+	Standard Auth, Transactions, Parameterized Queries
SQLite	Local File	Native engine integration, Online Backup, Transactions
CosmoKv (embedded)	In-process, runs a T-SQL subset directly on a CosmoKv LSM-tree	Tables, single & multi-column indexes, INNER JOIN, OUTPUT clause, MVCC transactions, online COSMOBAK backup
CosmoKvHttp	HTTP/JSON wire to a CosmoKvD instance	Same T-SQL surface as CosmoKv, exposed over the network for multi-process sharing

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:

Area	Supported
DDL	`CREATE TABLE` with full T-SQL types (BIT/TINYINT/SMALLINT/INT/BIGINT/REAL/FLOAT/DECIMAL(p,s)/NVARCHAR(n\|MAX)/DATETIME2/UNIQUEIDENTIFIER/VARBINARY/…), `IDENTITY`, `PRIMARY KEY`, `NOT NULL`, `DEFAULT`, `UNIQUE`. `CREATE INDEX` single + multi-column. `DROP TABLE/INDEX`. `IF NOT EXISTS` / `IF EXISTS` on all CREATE/DROP forms.
DML	`INSERT VALUES` (multi-row), `SELECT * \| cols \| aliases`, `UPDATE SET WHERE`, `DELETE WHERE`. `OUTPUT INSERTED.col / DELETED.col / .*` on all three with optional `AS alias`. `UPDATE TOP(n)` and `DELETE TOP(n)` cap row counts.
Joins	`INNER JOIN` (and bare `JOIN`) with 2-3 table chains, `AS` and bare table aliases, qualified column references (`alias.col` or `table.col`), unambiguous bare-column resolution across joined tables. T-SQL alias precedence (bare table name is out of scope when an alias is declared).
WHERE	`=, <>, <, <=, >, >=, AND, OR, NOT`, parens, `LIKE`, `IN`, `IS [NOT] NULL` — full three-valued logic.
Paging	`TOP n`, `OFFSET n ROWS FETCH NEXT m ROWS ONLY`.
Aggregation	`COUNT(*) / COUNT(col), SUM, AVG, MIN, MAX, GROUP BY, HAVING`. Not yet supported across JOINs.
Scalar functions	`GETUTCDATE, GETDATE, DATEADD, DATEDIFF, DATEPART, LEN, SUBSTRING, CHARINDEX, REPLACE, UPPER, LOWER, LTRIM, RTRIM, ISNULL, COALESCE, NULLIF, FORMAT, JSON_VALUE, CAST, CONVERT`.
Query planner	Cost-based: equality > range > order-only index selection; `ORDER BY` pushdown when an index satisfies the order. Joins use cartesian + filter; index-driven inner scans planned for v2.2.
Transactions	`BEGIN/COMMIT/ROLLBACK [TRAN[SACTION]]` mapped to CosmoKv MVCC. Every DML statement is wrapped in an implicit transaction so multi-key writes (row + IDENTITY counter + N index entries) are atomic.
`SELECT` with no `FROM`	`SELECT 1`, `SELECT GETUTCDATE()`, `SELECT 1 AS a, 'hi' AS b` — useful for health probes and cross-dialect scalar callers.
Public surface helpers	`CosmoKvConnection.RawDb` for non-SQL co-tenancy, `BackupAsync(Stream)` for online COSMOBAK snapshots, `GetStats()` for size + write metrics.

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

Provider package	EF Core bridge package	Extension method
`CosmoSQLClient.MsSql`	`CosmoSQLClient.MsSql.EntityFrameworkCore`	`UseCosmoSqlServer(...)`
`CosmoSQLClient.Postgres`	`CosmoSQLClient.Postgres.EntityFrameworkCore`	`UseCosmoPostgres(...)`
`CosmoSQLClient.MySql`	`CosmoSQLClient.MySql.EntityFrameworkCore`	`UseCosmoMySql(...)`
`CosmoSQLClient.Sqlite`	`CosmoSQLClient.Sqlite.EntityFrameworkCore`	`UseCosmoSqlite(...)`

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

CLI: `cosmokv`

A sqlite3-style shell for CosmoKv embedded databases — REPL, one-shot SQL, stdin scripts, and four output formats. Ships as a dotnet global tool.

dotnet tool install -g CosmoSQLClient.CosmoKv.Cli

cosmokv ./mydb                                       # REPL
cosmokv ./mydb "SELECT * FROM Users"                 # one-shot
cat schema.sql | cosmokv ./mydb -                    # stdin script
cosmokv --format=json ./mydb "SELECT * FROM Users"   # JSON output

REPL dot-commands cover the usual introspection surface (.tables, .schema [TABLE], .indexes [TABLE], .dump, .format FMT, .quit). Full reference: src/CosmoSQLClient.CosmoKv.Cli/README.md.

The introspection used by .schema / .dump is also available programmatically from v2.5 — CosmoKvConnection.GetTableNames(), GetTableScript(name), GetIndexScriptsForTable(name), ScriptSchema() — so you can build your own dump/backup tooling without going through the CLI.

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.

Library	Published Footprint
CosmoSQLClient.MsSql	~207 KB
Microsoft.Data.SqlClient	~17 MB+

SqlValue Type Mapping

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

`SqlValue` Accessor	.NET Type
`AsInt32()` / `AsInt64()`	`int` / `long`
`AsString()`	`string`
`AsDecimal()`	`decimal`
`AsDouble()` / `AsFloat()`	`double` / `float`
`AsBoolean()`	`bool`
`AsDateTime()` / `AsDateTimeOffset()`	`DateTime` / `DateTimeOffset`
`AsGuid()`	`Guid`
`AsBytes()`	`byte[]`
`IsNull`	`bool`

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.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

CosmoSQLClient

NuGet Packages

Key Features

Supported Database Engines

CosmoKv T-SQL Surface (v2.1)

Installation

Quick Start

Basic Query (MSSQL)

Embedded T-SQL (CosmoKv)

Advanced Streaming (IAsyncEnumerable)

Entity Framework Core

CLI: `cosmokv`

Technical Architecture

Zero-Allocation Pipeline

Package Size Comparison

SqlValue Type Mapping

Advanced Usage

Integrated Security (MSSQL)

JSON Streaming

Stored Procedures (MSSQL)

Related Projects

License

FilesExpand file tree

README.md

Latest commit

History

README.md

File metadata and controls

CosmoSQLClient

NuGet Packages

Key Features

Supported Database Engines

CosmoKv T-SQL Surface (v2.1)

Installation

Quick Start

Basic Query (MSSQL)

Embedded T-SQL (CosmoKv)

Advanced Streaming (IAsyncEnumerable)

Entity Framework Core

CLI: cosmokv

Technical Architecture

Zero-Allocation Pipeline

Package Size Comparison

SqlValue Type Mapping

Advanced Usage

Integrated Security (MSSQL)

JSON Streaming

Stored Procedures (MSSQL)

Related Projects

License

CLI: `cosmokv`