mssql-native 0.2.0 – native Microsoft SQL Server client in pure D

[Vadim Lopatin]

Hi all,

I'd like to announce **mssql-native**, a native client for 
Microsoft SQL Server written in pure D. It implements the TDS 
protocol directly over TCP: no ODBC driver, no FreeTDS and no C 
libraries at build time.

- dub: https://code.dlang.org/packages/mssql-native
- GitHub: https://github.com/buggins/mssql-native

```d
import mssql;
import std.stdio;

auto conn = new Connection(
     "Server=localhost,1433;Database=sales;User 
Id=app;Password=secret;TrustServerCertificate=True");
scope (exit) conn.close();

conn.exec("INSERT INTO orders (id, customer, amount) VALUES (?, 
?, ?)",
           1, "Grüße GmbH", Decimal.fromString("1234.50"));

foreach (row; conn.query("SELECT id, customer, amount FROM orders 
WHERE amount > ?", 100))
     writeln(row.getInt(0), " ", row.getString(1), " ", 
row.getDecimal(2));
```

**Features**

- SQL Server 2017 and newer (TDS 7.4); CI runs the tests against 
2017, 2019 and 2022 on Linux, Windows and macOS.
- TLS is on by default, with certificate verification. OpenSSL is 
loaded at run time, and only when needed.
- Works with ordinary threads (blocking sockets) and with vibe.d 
fibers (vibe-core 1.22 to 2.x); the mode is chosen automatically.
- Connection strings in URL style 
(`sqlserver://user:pass@host/db?...`) or ADO style 
(`Server=...;Database=...`), with failover over several hosts.
- Queries with `?` parameters (sent with `sp_executesql`), 
prepared statements, stored procedures with output parameters and 
return status, multiple result sets, per-statement row counts.
- Cancellation, also from another thread or task, plus 
transactions with isolation levels and savepoints.
- A connection pool (sessions are reset on reuse) and struct 
mapping: `query!T`, `queryRow!T`, `queryValue!T`, `insert`.
- Bulk insert (`INSERT BULK` with bulk load data, the protocol 
`bcp` and `SqlBulkCopy` use). Every value is converted to its 
column's exact type. For 100k rows it's about 20× faster than 
`INSERT ... VALUES` on my machine.
- Types are read and written exactly:
   - `decimal`/`numeric`/`money` as an exact `Decimal`;
   - all date/time types with 100 ns precision, `datetimeoffset` 
with its offset;
   - `varchar` in the collation's code page (Japanese, Chinese and 
Korean included) and `nvarchar`;
   - the `(max)` types, `xml`, `uniqueidentifier`, `sql_variant` 
and `rowversion`.
- `getString` returns the same text SQL Server itself produces; 
the tests compare against the server's own `CONVERT` output.

**ddbc driver**

ddbc 0.9.0 adds a native SQL Server driver built on mssql-native: 
use the `MSSQL` configuration and URLs like 
`ddbc:mssql://host:1433/db?user=...,password=...`. With that 
configuration, existing `sqlserver://` URLs use the native driver 
too. A hibernated dialect will follow. (new ddbc version is 
tagged but DUB registry did not update it by some unknown reason)

**Requirements:** D frontend 2.097 or newer (DMD 2.097+, LDC 
1.27+). The library is pure D, BSL-1.0 licensed.

mssql-native is built on **dbwire**, a new package of building 
blocks shared with my ClickHouse client clickhoused: transports 
for threads and fibers, TLS, exact decimals, code pages and 
connection pooling.

This is an unofficial project, not affiliated with Microsoft. 
"Microsoft" and "SQL Server" are trademarks of the Microsoft 
group of companies.

Feedback, bug reports and pull requests are very welcome.

Vadim