CosmoSQLClient.MySql 1.9.15

CosmoSQLClient

CosmoSQLClient is a unified .NET 10 database driver for MSSQL, PostgreSQL, MySQL, and SQLite built on System.IO.Pipelines (high-performance async I/O). It implements the full wire protocols from scratch — TDS 7.4 for SQL Server, PostgreSQL wire protocol v3, MySQL wire protocol v10, and the native SQLite3 file format — without delegating to ADO.NET for the network layer. All four backends share a single ISqlDatabase interface, giving you a familiar, SqlClient-compatible API that works identically regardless of the underlying engine.

Table of Contents

• 🏆 Advanced Features
  • JSON Streaming
  • IAsyncEnumerable Support
  • Zero-Allocation Type System
  • Connection Pool Warp Speed
  • Reflection-Free Mapping
• Standard ADO.NET API
• Feature Matrix
• Installation
• Quick Start
  • MSSQL
  • PostgreSQL
  • MySQL
  • SQLite
• Connection Pooling
• Stored Procedures (MSSQL)
• Named Instances (MSSQL)
• Transactions
• SqlDataTable & Output Formats
• ⚡ Benchmark Results
• 📦 Package Size vs Microsoft.Data.SqlClient
• Architecture
• Connection String Reference
• SqlValue Type Mapping
• Related Projects

🏆 Advanced Features

CosmoSQLClient provides a suite of advanced features designed for high-throughput, low-latency applications. These are accessible via the .Advanced property on any connection or pool.

await using var conn = await MsSqlConnection.OpenAsync(connStr);

// Use Advanced features
await foreach (var row in conn.Advanced.QueryStreamAsync("...")) { ... }

JSON Streaming

No other .NET SQL library has this. QueryJsonStreamAsync() delivers each complete JSON object the instant its closing } arrives — without ever buffering the full result array.

SQL Server's FOR JSON PATH fragments output at ~2033-character row boundaries that do not align with JSON object boundaries. QueryJsonStreamAsync() uses JsonChunkAssembler — backed by Utf8JsonReader + JsonReaderState — to detect exact {...} boundaries across arbitrary chunk splits, yielding each complete JSON object as a JsonElement the moment it is fully received.

Untyped streaming — iterate JsonElement directly:

await foreach (var elem in conn.Advanced.QueryJsonStreamAsync(
    "SELECT Id, Name, Price FROM Products FOR JSON PATH"))
{
    var id    = elem.GetProperty("Id").GetInt32();
    var name  = elem.GetProperty("Name").GetString();
    Console.WriteLine($"{id}: {name}");
}

IAsyncEnumerable Support

Rows are yielded as they arrive from the socket. This is perfect for reactive processing or pushing data directly to a web socket (NDJSON).

await foreach (var row in conn.Advanced.QueryStreamAsync(
    "SELECT * FROM LargeTable"))
{
    // Process one row at a time — the full result set is never buffered
    var id = row["Id"].AsInt32();
}

Zero-Allocation Type System

Unlike ADO.NET which uses classes for almost everything, CosmoSQLClient uses readonly record struct for SqlValue, SqlParameter, and SqlRow. This significantly reduces heap allocations and Garbage Collection overhead in high-concurrency scenarios.

Connection Pool Warp Speed

• TLS Session Resumption: For SQL Server, the pool reuses TLS session tickets. This reduces "cold connect" time from ~17ms to ~4ms for new physical connections.
• Pool Pre-warming: Call pool.WarmUpAsync() during application startup so that the very first database request is instant.

Reflection-Free Mapping

The SqlRowDecoder uses compiled LINQ Expressions to map database rows to POCO classes. It is faster than standard reflection-based mappers, approaching the speed of handwritten code.

Standard ADO.NET API

For easy migration from Microsoft.Data.SqlClient, Npgsql, or MySqlConnector, all CosmoSQLClient providers implement the standard System.Data.Common base classes. This makes it a drop-in replacement for libraries like Dapper and Entity Framework Core.

// Standard ADO.NET usage
DbConnection conn = new MsSqlConnection(connStr);
await conn.OpenAsync();

using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT Name FROM Products WHERE Id = @id";
cmd.Parameters.Add(new MsSqlParameter("id", 123));

// Works with Dapper!
var products = await conn.QueryAsync<Product>("SELECT * FROM Products");

Feature Matrix

Installation

Install only the package(s) you need:

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

All packages target .NET 10.0 and share the CosmoSQLClient.Core dependency automatically.

Quick Start

MSSQL

Factory method (recommended):

await using var conn = await MsSqlConnection.OpenAsync(
    "Server=localhost,1433;Database=mydb;User Id=sa;Password=YourPassword!;" +
    "Encrypt=True;TrustServerCertificate=True;");

var users = await conn.QueryAsync("SELECT * FROM Users WHERE Active = @active",
    new SqlParameter("active", true));

foreach (var row in users)
    Console.WriteLine(row["Email"].AsString());

SqlClient-compatible constructor (familiar to ADO.NET users):

var conn = new MsSqlConnection(
    "Server=localhost,1433;Database=mydb;User Id=sa;Password=YourPassword!;");
await conn.OpenAsync();

var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM Users WHERE Id = @id";
cmd.Parameters.AddWithValue("id", 42);

await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine(reader["Email"]);
}

PostgreSQL

await using var conn = await PgConnection.OpenAsync(
    "Host=localhost;Port=5432;Database=mydb;Username=postgres;Password=secret;");

var table = await conn.QueryTableAsync(
    "SELECT id, name FROM products WHERE price > @minPrice",
    new SqlParameter("minPrice", 9.99m));

Console.WriteLine(table.ToMarkdownTable());

MySQL

await using var conn = await MySqlConnection.OpenAsync(
    "Server=localhost;Port=3306;Database=mydb;User=root;Password=secret;");

await conn.ExecuteAsync(
    "INSERT INTO orders (customer_id, total) VALUES (@cid, @total)",
    new SqlParameter("cid", 7),
    new SqlParameter("total", 149.95m));

SQLite

await using var conn = await SqliteConnection.OpenAsync("Data Source=app.db;");

await conn.ExecuteAsync(@"
    CREATE TABLE IF NOT EXISTS notes (
        id   INTEGER PRIMARY KEY AUTOINCREMENT,
        body TEXT NOT NULL
    )");

var notes = await conn.QueryAsync("SELECT * FROM notes ORDER BY id DESC");
var list  = notes.ToList<Note>();

Connection Pooling

All four backends support connection pooling through a shared *ConnectionPool implementation:

var pool = new MsSqlConnectionPool(
    MsSqlConfiguration.Parse(connStr),
    maxConnections: 20);

// Pre-create idle connections at startup
await pool.WarmUpAsync();

// Explicit acquire / release
var conn = await pool.AcquireAsync();
try
{
    var rows = await conn.QueryAsync("SELECT 1");
}
finally
{
    await pool.ReleaseAsync(conn);
}

// Or use the pool directly as ISqlDatabase — acquire and release happen automatically
var rows = await pool.QueryAsync("SELECT * FROM Users WHERE Active = @a",
    new SqlParameter("a", true));

The pool uses a channel-based queue and never creates more than maxConnections simultaneous physical connections. Idle connections are health-checked before being returned to callers.

Stored Procedures (MSSQL)

// Simple call
var result = await conn.ExecuteProcedureAsync("usp_GetUser",
    new SqlParameter("UserId", 42),
    new SqlParameter("IncludeDeleted", false));

// With OUTPUT parameter
var outParam = new SqlParameter("TotalCount", SqlDbType.Int)
{
    Direction = ParameterDirection.Output
};
await conn.ExecuteProcedureAsync("usp_CountUsers", outParam);
Console.WriteLine($"Total users: {outParam.Value}");

// Procedure returning a result set
var table = await conn.QueryProcedureTableAsync("usp_ListActiveOrders",
    new SqlParameter("CustomerId", 123));

Named Instances (MSSQL)

Named SQL Server instances are resolved automatically via the SQL Server Browser service (SSRP, UDP 1434). No manual port lookup needed:

// SERVER\INSTANCE — CosmoSQLClient resolves the dynamic port via UDP 1434
await using var conn = await MsSqlConnection.OpenAsync(
    "Server=MYSERVER\\SQLEXPRESS;Database=mydb;User Id=sa;Password=pass;" +
    "Encrypt=True;TrustServerCertificate=True;");

Transactions

await using var tx = await conn.BeginTransactionAsync();
try
{
    await conn.ExecuteAsync(
        "INSERT INTO accounts (owner, balance) VALUES (@owner, @bal)",
        new object[] { new SqlParameter("owner", "Alice"), new SqlParameter("bal", 1000m) },
        tx);

    await conn.ExecuteAsync(
        "UPDATE accounts SET balance = balance - @amount WHERE id = @id",
        new object[] { new SqlParameter("amount", 200m), new SqlParameter("id", 7) },
        tx);

    await tx.CommitAsync();
}
catch
{
    await tx.RollbackAsync();
    throw;
}

SqlDataTable & Output Formats

QueryTableAsync materializes a result set into a SqlDataTable that can be exported in several formats:

var table = await conn.QueryTableAsync("SELECT * FROM Products");

// Markdown table — useful for logging, reports, or README generation
Console.WriteLine(table.ToMarkdownTable());

// JSON array
string json = table.ToJson(indented: false);

// Typed list — column-to-property matching is case-insensitive
List<Product> products = table.ToList<Product>();

// Access individual cells
int count = table.Rows.Count;
string name = table.Rows[0]["Name"].AsString();
decimal price = table.Rows[0]["Price"].AsDecimal();

⚡ Benchmark Results

All benchmarks run with BenchmarkDotNet on .NET 10.0, Apple M-series (ARM64), with databases running on localhost.

MSSQL vs ADO.NET (Microsoft.Data.SqlClient)

* ADO.NET "cold" reuses its own built-in pool internally — not a truly cold connection.

MySQL vs MySqlConnector

PostgreSQL vs Npgsql

Takeaway: On warm-path throughput CosmoSQL matches or beats ADO.NET for MSSQL. For cold connections and certain warm queries, mature drivers with years of micro-optimisation (Npgsql, MySqlConnector) still have an edge. JSON streaming is CosmoSQL-exclusive across all three backends.

📦 Package Size vs Microsoft.Data.SqlClient

Measured against Microsoft.Data.SqlClient 6.1.4 — the official Microsoft MSSQL driver.

Download size (.nupkg)

CosmoSQL is ~87× smaller as a download.

Published footprint (what lands in your app)

Why MDS is so much larger

Microsoft.Data.SqlClient ships:

• 14 target frameworks — net462 through net9, netstandard2.0
• 13 language resource DLLs — ru, ja, fr, de, ko, and more
• 13 mandatory NuGet dependencies including Azure.Identity, Microsoft.Identity.Client, Microsoft.Data.SqlClient.SNI, Microsoft.IdentityModel.JsonWebTokens, and OpenIdConnect — even if you never use AAD authentication

CosmoSQL ships one target (net10.0), no localisation resources, and a single high-performance transport library (Cosmo.Transport) as its only transitive dependency.

Architecture

System.IO.Pipelines & Channels

CosmoSQLClient is built on System.IO.Pipelines, the same high-performance I/O library that powers Kestrel. This allows for zero-copy parsing of database protocols. Every connection manages a PipeReader and PipeWriter, ensuring that data is processed directly from the socket buffer without intermediate allocations.

Application code
      │
      ▼
ISqlDatabase (MsSqlConnection / PgConnection / MySqlConnection / SqliteConnection)
      │
      ▼
Cosmo.Transport (System.IO.Pipelines wrapper)
  ├─ TlsStream (optional)
  ├─ Protocol framer  (TDS / PG / MySQL length-prefix decoder)
  ├─ Message decoder  (token-stream / backend-message / packet parser)
  └─ Command handler  (request/response correlation via Channels)
      │
      ▼
TCP socket (System.Net.Sockets — zero blocking I/O)

ISqlDatabase interface

All four backends implement ISqlDatabase, so you can write backend-agnostic application code and swap the underlying engine via dependency injection:

public interface ISqlDatabase
{
    Task<SqlDataTable>          QueryTableAsync(string sql, params SqlParameter[] parameters);
    Task<IEnumerable<SqlRow>>   QueryAsync(string sql, params SqlParameter[] parameters);
    IAsyncEnumerable<SqlRow>    QueryStreamAsync(string sql, params SqlParameter[] parameters);
    IAsyncEnumerable<JsonElement> QueryJsonStreamAsync(string sql, params SqlParameter[] parameters);
    Task<int>                   ExecuteAsync(string sql, params SqlParameter[] parameters);
    Task<ISqlTransaction>       BeginTransactionAsync();
}

JsonChunkAssembler

QueryJsonStreamAsync feeds raw byte chunks from the wire directly into JsonChunkAssembler. It uses Utf8JsonReader with a persisted JsonReaderState to scan incrementally — tracking brace depth without allocating intermediate strings. Each time depth returns to zero after a }, the assembled span is handed to JsonSerializer and yielded to the caller. Peak memory for a 1 GB FOR JSON PATH result is proportional to the largest single JSON object, not the full result.

Connection String Reference

MSSQL

Examples:

Server=localhost,1433;Database=mydb;User Id=sa;Password=pass;Encrypt=True;TrustServerCertificate=True;
Server=PROD\SQLEXPRESS;Database=mydb;Integrated Security=True;
Server=db.internal;Database=mydb;User Id=app;Password=pass;Encrypt=True;Connect Timeout=10;

PostgreSQL

Host=localhost;Port=5432;Database=mydb;Username=postgres;Password=secret;
Host=db.internal;Port=5432;Database=mydb;Username=app;Password=secret;SslMode=Require;

MySQL

Server=localhost;Port=3306;Database=mydb;User=root;Password=secret;
Server=db.internal;Port=3306;Database=mydb;User=app;Password=secret;CharSet=utf8mb4;

SQLite

Data Source=app.db;
Data Source=/absolute/path/to/app.db;Mode=ReadOnly;
Data Source=:memory:;

SqlValue Type Mapping

SqlValue is the universal cell type returned by QueryAsync and QueryTableAsync. It wraps the raw wire value and provides typed accessors:

var row = rows.First();
if (!row["DeletedAt"].IsNull)
{
    var deletedAt = row["DeletedAt"].AsDateTimeOffset();
}

Related Projects

• CosmoSQLClient-Swift — Swift NIO port of CosmoSQLClient with the same API design and wire-protocol implementations for server-side Swift (Vapor, Hummingbird).