CosmoSQLClient.MySql 1.9.31

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)
• Integrated Security & NTLMv2
• 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

Feature	MSSQL	PostgreSQL	MySQL	SQLite
Wire protocol	TDS 7.4	v3	v10	sqlite3
Connection pooling	✅	✅	✅	✅
Transactions	✅	✅	✅	✅
Parameterized queries	✅	✅	✅	✅
TLS/SSL	✅	✅	✅	✅
TrustServerCertificate	✅	—	—	—
SCRAM-SHA-256 auth	—	✅	—	—
NTLM / Windows auth	✅	—	—	—
Named instance resolution (SERVER\INSTANCE)	✅	—	—	—
Stored procedures + OUTPUT params	✅	—	—	—
Multiple result sets	✅	✅	✅	—
Bulk insert	✅	—	—	—
IAsyncEnumerable row streaming	✅	✅	✅	—
JSON streaming (QueryJsonStreamAsync) 🏆	✅	✅	✅	—
SqlDataTable / ToJson() / ToList<T>()	✅	✅	✅	✅
Native SQL backup / restore	✅ (MSSQL)	✅ (Logical)	✅ (Logical)	✅ (SQLite)
SqlClient-compatible API	✅	—	—	—

---

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

CosmoSQLClient supports both transparent pooling via connection strings and manual pool management.

Transparent Pooling (Recommended)

New in 1.9.16: For MSSQL, you can enable connection pooling directly in your connection string. This is the easiest way to add pooling to existing ADO.NET-style code.

// Just add "Pooling=true" to your connection string
var connStr = "Server=localhost;Database=mydb;User Id=sa;Password=pass;Pooling=true;Max Pool Size=100;";

// Every time you open/close this connection, it uses a global pool transparently
await using var conn = await MsSqlConnection.OpenAsync(connStr);
var rows = await conn.QueryAsync("SELECT 1");
// Upon disposal/close, the physical connection returns to the pool

Manual Pool Management

For more granular control (e.g., custom warming up, different pools for the same string), use the *ConnectionPool classes directly:

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

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

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

---

Integrated Security & NTLMv2

CosmoSQLClient includes a fully managed NTLMv2 implementation, allowing for Windows/Domain authentication on all supported platforms (Windows, Linux, and macOS) without requiring native dependencies or SSPI.

Usage

To enable Integrated Security, add Integrated Security=True to your connection string. You can also specify a Domain.

// Multiplatform Domain Authentication
var connStr = "Server=db.internal;Database=mydb;Integrated Security=True;" +
              "Domain=MYDOMAIN;User Id=myuser;Password=mypass;";

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

If User Id is omitted, the library defaults to Environment.UserName. Note that because this is a managed implementation, a Password is still required to compute the NTLMv2 response hashes.

---

Native Backup & Restore

CosmoSQLClient provides a unified interface for engine-native backup and restore operations. This allows you to trigger server-side backups directly via SQL commands without shell access.

Usage

Check if the connection supports backups and execute:

using CosmoSQLClient.Core;

if (conn is ISqlBackupable backupable)
{
    await backupable.BackupDatabaseAsync("MyDatabase", new BackupOptions
    {
        DestinationPath = "/var/opt/mssql/backups/MyBackup.bak",
        Compression = true,
        Format = true
    });
}

Engine Support

• MSSQL: Native BACKUP DATABASE and RESTORE DATABASE commands.
• SQLite: Native VACUUM INTO for safe, online backups.
• Postgres/MySQL: Interface hooks provided; currently requires external CLI tools.

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)

Benchmark	CosmoSQL	ADO.NET	Winner
Cold connect + query	14.1 ms	0.63 ms*	ADO.NET*
Pool acquire + query	593 µs	—	—
Warm query (full table)	589 µs	599 µs	🏆 CosmoSQL +2%
Warm single-row query	575 µs	580 µs	🏆 CosmoSQL +1%
Warm ToList<T>	592 µs	604 µs	🏆 CosmoSQL +2%
Warm ToJson	612 µs	729 µs	🏆 CosmoSQL +16%
FOR JSON streamed	565 µs	✅ N/A	🏆 CosmoSQL exclusive
FOR JSON buffered	552 µs	569 µs	🏆 CosmoSQL +3%

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

MySQL vs MySqlConnector

Benchmark	CosmoSQL	MySqlConnector	Winner
Cold connect + query	4.99 ms	5.93 ms	🏆 CosmoSQL +16%
Pool acquire + query	333 µs	435 µs	🏆 CosmoSQL +24%
Warm query	331 µs	214 µs	MySqlConnector +35%
JSON streamed	310 µs	✅ N/A	🏆 CosmoSQL exclusive

PostgreSQL vs Npgsql

Benchmark	CosmoSQL	Npgsql	Winner
Cold connect + query	4.53 ms	4.60 ms	🏆 CosmoSQL +2%
Pool acquire + query	294 µs	223 µs	Npgsql +24%
Warm query	288 µs	193 µs	Npgsql +33%
JSON streamed	296 µs	✅ N/A	🏆 CosmoSQL exclusive

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)

Package	Size	Notes
CosmoSQLClient.MsSql	67 KB	Single target: net10.0
CosmoSQLClient.Core	29 KB	Required dependency
CosmoSQL total	~96 KB
Microsoft.Data.SqlClient 6.1.4	8.3 MB	67 files · 14 target frameworks · 13 language resource DLLs

CosmoSQL is ~87× smaller as a download.

Published footprint (what lands in your app)

CosmoSQL	Size
CosmoSQLClient.MsSql.dll	134 KB
CosmoSQLClient.Core.dll	45 KB
Cosmo.Transport.dll (Pipelines wrapper)	28 KB
Total	~207 KB

Microsoft.Data.SqlClient	Size
Microsoft.Data.SqlClient.dll (unix/net9)	2.0 MB — just the one DLL
+ Azure.Identity, Azure.Core, MSAL, SNI native, JWT tokens…	~15 MB
Total	~17 MB+

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

Key	Default	Description
Server	—	Host and port. Aliases: Data Source, Address, Addr. Supports (local) and . shorthands.
Database	—	Database name. Aliases: Initial Catalog.
User Id	—	Username. Aliases: UID, User.
Password	—	Password. Aliases: PWD.
Encrypt	True	True to enable TLS (default is True).
TrustServerCertificate	False	Skip certificate validation (dev/test only).
Connect Timeout	30	Connection timeout in seconds.
Application Name	CosmoSQLClient	Appears in sys.dm_exec_sessions.
Integrated Security	False	Set to True for multiplatform Windows/Domain auth (Uses a fully managed NTLMv2 implementation).
Domain	—	Windows Domain name for NTLM authentication.
Pooling	True	New in 1.9.16: Enable transparent connection pooling.
Max Pool Size	100	Maximum number of pooled connections.
Min Pool Size	0	Minimum number of idle connections.

Examples:

Server=localhost,1433;Database=mydb;User Id=sa;Password=pass;Encrypt=True;TrustServerCertificate=True;Pooling=true;Max Pool Size=50;

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:

SqlValue Accessor	.NET Type	MSSQL	PostgreSQL	MySQL	SQLite
AsInt32()	int	INT	integer	INT	INTEGER
AsInt64()	long	BIGINT	bigint	BIGINT	INTEGER
AsString()	string	NVARCHAR, TEXT	text, varchar	VARCHAR, TEXT	TEXT
AsDecimal()	decimal	DECIMAL, MONEY	numeric	DECIMAL	REAL
AsDouble()	double	FLOAT	float8	DOUBLE	REAL
AsFloat()	float	REAL	float4	FLOAT	REAL
AsBoolean()	bool	BIT	boolean	TINYINT(1)	INTEGER
AsDateTime()	DateTime	DATETIME2, DATETIME	timestamp	DATETIME	TEXT
AsDateTimeOffset()	DateTimeOffset	DATETIMEOFFSET	timestamptz	—	—
AsGuid()	Guid	UNIQUEIDENTIFIER	uuid	CHAR(36)	TEXT
AsBytes()	byte[]	VARBINARY	bytea	BLOB	BLOB
IsNull	bool	NULL	NULL	NULL	NULL

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