mssql 0.2.0

A pure Dart driver for Microsoft SQL Server using the TDS 7.4 wire protocol, with TDS 8.0 strict encryption.

mssql

A pure-Dart driver for Microsoft SQL Server, built on the TDS 7.4 wire protocol. No native extensions, no FFI — just Dart and TCP.

dart pub add mssql

Quick start

import 'package:mssql/mssql.dart';

final conn = await MssqlConnection.connect(
  host: 'localhost',
  port: 1433,
  user: 'sa',
  password: 'P@ssw0rd',
  database: 'MyDb',
);

final result = await conn.query('SELECT id, name FROM users WHERE id = @id', {'id': 1});
print(result[0]['name']); // Alice

await conn.close();

---

API reference

MssqlConnection

Connecting

// SQL Server authentication (username + password)
final conn = await MssqlConnection.connect(
  host: 'localhost',      // required
  port: 1433,             // optional, default 1433
  user: 'sa',             // required
  password: 'P@ssw0rd',  // required
  database: 'MyDb',       // optional, default ''
  encrypt: true,          // optional, default true; set false for local dev containers
  trustServerCertificate: false, // optional, accept self-signed certs
  timeout: Duration(seconds: 30), // optional, connection timeout
);

// Azure AD authentication
final conn = await MssqlConnection.connectAzureAd(
  host: 'server.database.windows.net',
  azureAdAuth: AzureAdAuth.fromToken(token),   // pre-acquired bearer token
  database: 'MyDb',
  trustServerCertificate: false,
);

Querying

// Returns all rows buffered in a MssqlResult
final result = await conn.query('SELECT id, name FROM users');

// With named parameters (@name syntax)
final result = await conn.query(
  'SELECT * FROM orders WHERE customer = @cust AND active = @flag',
  {'cust': 'Acme', 'flag': true},
);

// Access by column name or zero-based index
final name   = result[0]['name'];       // by name
final first  = result[0].valueAt(0);    // by index
final cols   = result[0].columnNames;   // ['id', 'name']
final values = result[0].values;        // [1, 'Alice']

// Rows and counts
result.rows;         // List<MssqlRow>
result.rowsAffected; // int
result.length;       // row count
result.isEmpty;      // bool

Executing (DML / DDL)

// Returns rows affected
final n = await conn.execute(
  'INSERT INTO logs (msg) VALUES (@msg)',
  {'msg': 'hello'},
);
print(n); // 1

Multiple result sets

final multi = await conn.queryMultiple('SELECT 1 AS a; SELECT 2 AS b');
final first  = multi.first;   // MssqlResult for first SELECT
final second = multi.second;  // MssqlResult for second SELECT
final all    = multi.all;     // List<MssqlResult>

Streaming large result sets

await for (final row in conn.queryStream('SELECT * FROM bigTable')) {
  process(row);
}

// With parameters
await for (final row in conn.queryStream(
  'SELECT * FROM events WHERE date > @since',
  {'since': DateTime.now().subtract(Duration(days: 7))},
)) {
  print(row['event_type']);
}

Transactions

// Callback form — commits on success, rolls back on any exception
await conn.transaction((c) async {
  await c.execute('INSERT INTO accounts (id, balance) VALUES (1, 100)');
  await c.execute('INSERT INTO accounts (id, balance) VALUES (2, 200)');
});

// Manual form
await conn.beginTransaction();
try {
  await conn.execute('UPDATE accounts SET balance = balance - 50 WHERE id = 1');
  await conn.execute('UPDATE accounts SET balance = balance + 50 WHERE id = 2');
  await conn.commitTransaction();
} catch (_) {
  await conn.rollbackTransaction();
  rethrow;
}

Connection state

conn.isOpen;    // bool — false after close() or a fatal error
conn.database;  // String — current database name

await conn.close();

---

MssqlPool

A connection pool with configurable min/max, idle reaping, and acquire timeouts. Mirrors the node-mssql / tarn pool model.

Creating a pool

final pool = MssqlPool(MssqlPoolConfig(
  host: 'localhost',
  port: 1433,
  user: 'sa',
  password: 'P@ssw0rd',
  database: 'MyDb',
  encrypt: true,
  trustServerCertificate: false,

  min: 2,                              // minimum idle connections (default 0)
  max: 10,                             // maximum total connections (default 10)
  idleTimeout: Duration(seconds: 30),  // close idle connections after (default 30s)
  acquireTimeout: Duration(seconds: 15), // throw if no connection within (default 15s)
  connectionTimeout: Duration(seconds: 30), // TCP connect timeout (default 30s)
));

// Pre-warm min connections (optional)
await pool.open();

Pool query methods

// Same signatures as MssqlConnection
final result = await pool.query('SELECT * FROM users WHERE id = @id', {'id': 1});
final n      = await pool.execute('DELETE FROM tmp WHERE expired = 1');
final multi  = await pool.queryMultiple('SELECT 1; SELECT 2');

await for (final row in pool.queryStream('SELECT * FROM bigTable')) {
  process(row);
}

Pool transactions

await pool.transaction((conn) async {
  await conn.execute('INSERT INTO orders ...');
  await conn.execute('UPDATE inventory ...');
  // commits on return, rolls back on throw
});

Manual acquire / release

final conn = await pool.acquire();
try {
  await conn.execute('...');
} finally {
  pool.release(conn);
}

Closing the pool

await pool.close(); // closes idle connections, rejects any pending acquires

---

MssqlException

All driver and server errors throw MssqlException:

try {
  await conn.query('SELECT * FROM nonexistent');
} on MssqlException catch (e) {
  print(e.message);           // SQL Server error message
  print(e.errorCode);         // SQL Server error number (e.g. 208 = invalid object name)
  print(e.severity);          // TDS severity level (nullable int)
  print(e.precedingErrors);   // List<MssqlException> — earlier errors from the same batch
}

---

Parameters

Named parameters use @name placeholders. Supported Dart → SQL type mappings:

Dart type	SQL Server type
int	BIGINT
double	FLOAT
bool	BIT
String	NVARCHAR(MAX) or NVARCHAR
List<int>	VARBINARY(MAX)
DateTime	DATETIME2(7)
null	NULL (any type)

---

Supported SQL Server types (read)

Category	Types
Integer	TINYINT, SMALLINT, INT, BIGINT, BIT
Float	REAL (→ double), FLOAT (→ double)
Decimal	DECIMAL, NUMERIC (→ double)
Money	MONEY, SMALLMONEY (→ double)
String	VARCHAR, NVARCHAR, CHAR, NCHAR, TEXT, NTEXT, VARCHAR(MAX), NVARCHAR(MAX)
Binary	VARBINARY, BINARY, IMAGE, VARBINARY(MAX) (→ List<int>)
Date/Time	DATE, DATETIME, DATETIME2, SMALLDATETIME, TIME, DATETIMEOFFSET (→ DateTime)
GUID	UNIQUEIDENTIFIER (→ String in xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx form)
XML	XML (→ String)
Misc	SQL_VARIANT (→ decoded inner value), UDT (→ raw List<int>)
Null	NULL for any type (→ null)

---

TLS / Encryption

// TDS 8.0 strict encryption (recommended where supported: Azure SQL,
// SQL Server 2022+ on supported platforms, SQL Server 2025 Linux/Docker)
final conn = await MssqlConnection.connect(
  host: 'server.database.windows.net',
  encryptMode: MssqlEncryptMode.strict,
  trustServerCertificate: false,
  ...
);

// TDS 7.x mandatory encryption (legacy Encrypt=true behavior)
final conn = await MssqlConnection.connect(
  host: 'server.database.windows.net',
  encrypt: true,                  // default true
  trustServerCertificate: false,  // validate cert (default false)
  ...
);

// Local dev container (no TLS)
final conn = await MssqlConnection.connect(
  host: 'localhost',
  encrypt: false,
  ...
);

// Local dev container with self-signed cert
final conn = await MssqlConnection.connect(
  host: 'localhost',
  encrypt: true,
  trustServerCertificate: true,
  ...
);

MssqlEncryptMode.strict uses TDS 8.0 and starts TLS before any TDS packets, so it avoids the legacy TDS 7.x PRELOGIN-wrapped TLS handshake. Use it only with servers that support Encrypt=Strict. Strict mode requires certificate validation and rejects trustServerCertificate: true.

Encrypted-connection behavior

Dart's SecureSocket can seal one TDS packet as two TLS records, which SQL Server rejects by closing the connection. To avoid this, encrypted connections negotiate the minimum TDS packet size (512 bytes) and pad each message so packet boundaries stay aligned with SecureSocket's internal buffer. This is transparent to your code, with these observable effects:

• Each statement costs up to ~0.5 KB of extra bytes on the wire (trailing spaces on SQL batches; an unused varbinary(max) parameter on parameterized queries). Results and plan caching are unaffected.
• program_name and client_interface_name in sys.dm_exec_sessions show trailing spaces (and host_name can too, if the other two cannot absorb the padding).
• Very large statements are sent in 512-byte packets with a 1 ms pause between packets.
• encrypt: false is unchanged.

---

Requirements

• Dart SDK ≥ 3.4
• SQL Server 2012 or later for the default TDS 7.4 path
• SQL Server 2022+ / Azure SQL / SQL Server 2025 Linux for TDS 8.0 strict
• Azure SQL Database / Azure SQL Edge
• Port 1433 (or custom) reachable from the Dart process

---

Limitations

• Azure AD authentication requires a bearer token supplied by the caller (e.g. obtained via azure_identity); the driver does not fetch tokens itself.
• Bulk copy (BULK INSERT / TDS bulk-load protocol) is not supported.
• Prepared statement handles (sp_prepare / sp_execute) are not supported. All parameterized queries use sp_executesql, which SQL Server plan-caches by query hash, so repeated-query performance is similar in practice.