Native MySQL client written in Dart for Dart

Support My Work

I’m on @buymeacoffee. If you like my work, you can buy me a ☕ and share your thoughts 🎉 Buy me a coffee

fork from https://github.com/zim32/mysql.dart

See example directory for examples and usage

Tested in CI:

• MariaDB 10.11
• MariaDB 11.8
• MySQL Community Server 5.5 (without TLS, see below), 5.6 and 5.7
• MySQL Community Server 9
• MySQL Community Server 9.7
• MySQL Community Server 8.4 and 9.7 on Windows

Also verified outside CI:

• MySQL Percona Server 5.7 and 8
• MariaDB 10.x
• MariaDB 11.7.2

What's New in 2.0.0

• The compiled AOT driver is now the headline performance path: in the local MariaDB benchmark it reached 0.532 ms average connect latency, 11,255 text queries/s, 11,362 prepared queries/s, and 839,539 rows/s on 10,000-row result sets.
• In that same local AOT benchmark, mysql_dart 2.0.0 beats PHP PDO on connect latency, SELECT 1, and 10,000-row result-set throughput, and beats PHP mysqli on connect latency and 10,000-row throughput. PHP mysqli still leads tiny prepared statements through its native C extension.
• Performance work is not finished: the next targets are direct COM_STMT_EXECUTE encoding, per-result-set decode plans, fewer row wrapper allocations, a cheaper streaming path for large result sets, and a final incremental packet reader/ring buffer.
• Added compatibility with MySQL Community Server 9 and 9.7, including full caching_sha2_password authentication with TLS, pinned RSA public keys, or optional server public key retrieval.
• Added binary protocol support for MySQL JSON columns (column type 245), decoding them as UTF-8 JSON strings instead of failing at the protocol layer.
• Integration tests now read MYSQL_* environment variables, so the suite can run against arbitrary local or CI host/port/database configurations.
• GitHub Actions now runs the test suite against MariaDB 10.11, MariaDB 11.8, MySQL 9, and MySQL 9.7.
• Removed external runtime dependencies on asn1lib, pointycastle, buffer, crypto, and tuple by inlining the required PEM/RSA/OAEP, hashing, tuple, and byte-writer logic.
• Removed the unsafe pooled APIs MySQLConnectionPool.prepare() and MySQLConnectionPool.execute(..., iterable: true). Use withPrepared(...) and withConnection(...) instead.
• Result-set streaming now propagates backpressure to the socket subscription, and benchmark tooling now measures TLS/non-TLS connect latency, percentile statistics, result-set first-row latency, and streaming vs materialized throughput.

Breaking Changes in 2.0.0

• MySQLConnectionPool.prepare() no longer exists.
• MySQLConnectionPool.execute(..., iterable: true) is no longer supported.

Why:

• A prepared statement belongs to one physical connection. The old pool.prepare() API let a statement escape after the pool had already returned the owning connection to the idle queue.
• An iterable result set keeps its connection busy until EOF. The old pooled iterable path could release that connection before the stream had finished.

Those old APIs could lead to concurrency bugs such as:

• connection is busy failures when unrelated work reused the same connection;
• COM_STMT_CLOSE or COM_STMT_EXECUTE arriving on a connection already borrowed by another operation;
• result streams consuming packets while the pool had already handed the same connection to a different query;
• protocol desynchronization when prepared-statement lifecycle and connection lifecycle diverged.

Use:

await pool.withPrepared(
  'UPDATE book SET price = ? WHERE id = ?',
  (stmt) => stmt.execute([99, 1]),
);

await pool.withConnection((conn) async {
  final result = await conn.execute('SELECT * FROM book', null, true);
  await for (final row in result.rowsStream) {
    print(row.assoc());
  }
});

Local Driver Benchmark

This benchmark compares the current local mysql_dart tree (2.0.0) against mysql_dart 1.2.1, PHP PDO, PHP mysqli, friends-of-reactphp/mysql via its Composer package react/mysql, and amphp/mysql.

Environment:

• Server: 10.11.6-MariaDB-log (mariadb.org binary distribution)
• Host/port: 127.0.0.1:3306
• Transport: plain TCP (MYSQL_SECURE=false)
• PHP: 8.3.11 NTS
• Dart SDK: 3.6.2
• Dart modes: JIT (dart run) and AOT (dart compile exe)
• Workload: 2000 scalar iterations, 20 connect iterations, result sets of 10, 1000, and 10000 rows with positional row access
• Command: powershell -ExecutionPolicy Bypass -File tool/run_driver_comparison.ps1; AOT generated with dart compile exe tool/benchmark_mysql_dart_compat.dart -o build/benchmark_mysql_dart_compat_2_0_0.exe

Scalar results:

Metric	mysql_dart 2.0.0 AOT	mysql_dart 2.0.0 JIT	mysql_dart 1.2.1	PHP PDO	PHP mysqli	ReactPHP mysql	AMPHP mysql
Connect avg ms	0.532	1.131	130.411	2.711	13.266	156.278	0.618
Text ops/s	11,255	6,432	6,562	7,333	12,287	7,572	5,092
Auto prepared ops/s	11,259	7,113	7,115	-	7,207	6,651	2,594
Prepared ops/s	11,362	9,702	10,219	14,741	15,535	-	5,065

Result-set throughput:

Result set	mysql_dart 2.0.0 AOT	mysql_dart 2.0.0 JIT	mysql_dart 1.2.1	PHP PDO	PHP mysqli	ReactPHP mysql	AMPHP mysql
10 rows/s	76,658	65,746	56,117	55,106	101,693	44,625	23,110
1,000 rows/s	539,127	499,413	278,505	507,616	729,219	136,012	64,428
10,000 rows/s	839,539	818,274	207,385	599,171	746,236	146,778	67,594

Reading the numbers:

• mysql_dart 2.0.0 AOT removes the artificial connect latency present in 1.2.1, reducing average connect latency from 130.411 ms to 0.532 ms in this run.
• AOT is the best Dart production number in this benchmark: it is faster than PDO on connect latency, SELECT 1, and 10,000-row result-set throughput.
• AOT is faster than mysqli on connect latency and 10,000-row result-set throughput; mysqli still leads scalar prepared statements because it uses the native PHP/MySQL C stack.
• mysql_dart 2.0.0 AOT is materially faster than 1.2.1 on large result sets after the parser and row materialization changes.
• The driver has not reached the maximum possible Dart performance yet. Remaining expected gains are mostly in prepared-statement packet encoding, result-set cell decoding, row materialization, streaming memory behavior, and the packet reader.
• ReactPHP's package does not expose a separate public prepared-statement object in the benchmark path, so only its parameterized query path is reported.

Roadmap

• x Auth with mysql_native_password
• x Basic connection
• x Connection pool
• x Query placeholders
• x Transactions
• x Prepared statements (real, not emulated)
• x SSL connection
• x Auth using caching_sha2_password (default since MySQL 8)
• x Iterating large result sets
• x Typed data access
• x Send data in binary form when using prepared stmts (do not convert all into strings)
• x Multiple resul sets

Usage

Create connection pool

final pool = MySQLConnectionPool(
  host: '127.0.0.1',
  port: 3306,
  userName: 'your_user',
  password: 'your_password',
  maxConnections: 10,
  databaseName: 'your_database_name', // optional,
  timeZone: '+00:00', // optional: issues SET time_zone right after connect
  idleTestThreshold: Duration(seconds: 30), // validates idle connections
  maxConnectionAge: Duration(hours: 6),
  onConnectionOpen: (conn) async {
    await conn.execute("SET @app_name = 'api'");
  },
  retryOptions: MySQLPoolRetryOptions(
    maxAttempts: 3,
    delay: Duration(milliseconds: 100),
    retryIf: (error) => error is SocketException,
  ),
  allowPublicKeyRetrieval: true, // optional: for caching_sha2_password without TLS
  // serverPublicKey: '''-----BEGIN PUBLIC KEY-----...''', // safer than retrieval on insecure links
);

Starting with version 1.2.1, the pool exposes extra controls: it validates idle connections, recycles long-lived sessions, lets you apply custom time_zone/collation tweaks inside onConnectionOpen, and offers a basic retry policy (via MySQLPoolRetryOptions). For visibility, call pool.status() to inspect the active, idle, and pending connection counters.

Or single connection

final conn = await MySQLConnection.createConnection(
  host: "127.0.0.1",
  port: 3306,
  userName: "your_user",
  password: "your_password",
  databaseName: "your_database_name", // optional
  // secure: false,
  // allowPublicKeyRetrieval: true,
  // serverPublicKey: '''-----BEGIN PUBLIC KEY-----...''',
);

// actually connect to database
await conn.connect();

Warning By default connection is secure. If you don't want to use SSL (TLS) connection, pass secure: false

Network exposure

Exposing a database server to the internet is not recommended. Keep MySQL and MariaDB on private, controlled networks: the same host, a Docker or Kubernetes network, a private VPC/LAN, or behind a VPN or SSH tunnel, with the port closed to the outside by a firewall. TLS protects the traffic, but it does not replace network isolation.

TLS certificate verification (4.0.0+)

With TLS the server certificate is verified, as a browser does: it must chain to a trusted root and name the host you connect to. Otherwise the connection fails with a HandshakeException before the user name and password are sent.

// Server in Docker or on the same host, using the certificate MySQL/MariaDB
// generate on first start: encrypted, but the certificate is not checked.
final conn = await MySQLConnection.createConnection(
  host: 'localhost',
  port: 3306,
  userName: 'user',
  password: 'secret',
  verifyServerCertificate: false,
);

// Server with a certificate from your own CA: keep verification, trust the CA.
final conn = await MySQLConnection.createConnection(
  host: 'db.internal',
  port: 3306,
  userName: 'user',
  password: 'secret',
  securityContext: SecurityContext(withTrustedRoots: true)
    ..setTrustedCertificates('ca.pem'),
);

verifyServerCertificate: false is fine when nobody can sit between the application and the server (same host, a Docker network). Across a network it lets anyone in the path impersonate the server and read the password. A onBadCertificate callback, when given, decides instead of the flag. MySQLConnectionPool takes the same options.

TLS with old servers: UNSUPPORTED_PROTOCOL

dart:io sets TLS 1.2 as the minimum version and has no way to lower it (TlsProtocolVersion only offers tls1_2 and tls1_3). The official Community builds of older MySQL servers stop below that:

Server	TLS versions
MySQL 5.5, MySQL 5.6 before 5.6.46	TLS 1.0 only
MySQL 5.7 before 5.7.28 (yaSSL builds)	TLS 1.0 and 1.1
MySQL 5.6.46+, 5.7.28+, 8.x, 9.x (OpenSSL)	up to TLS 1.2 or 1.3

With no version in common, a secure connection fails during the handshake with:

HandshakeException: Handshake error in client (OS Error:
	UNSUPPORTED_PROTOCOL(handshake_client.cc:...))

It is not a certificate problem: verifyServerCertificate: false or an onBadCertificate callback do not change the protocol version, and no driver option can. Upgrade the server (SHOW GLOBAL VARIABLES LIKE 'tls_version' lists TLSv1.2 when it is supported; servers too old for it may not have the variable at all), or connect with secure: false only where the network is trusted (see Network exposure above). Builds from Linux distributions linked against OpenSSL may support TLS 1.2 on older versions too.

socketTimeout bounds the TCP connection attempt; connect(timeoutMs: ...) bounds the greeting, TLS handshake and authentication, and closes the connection when it expires.

For MySQL 8.4+ / 9.x with caching_sha2_password, non-TLS connections usually need one of:

• serverPublicKey: pin the server RSA public key and encrypt the password safely.
• allowPublicKeyRetrieval: true: request the RSA public key from the server during auth.

allowPublicKeyRetrieval is a compatibility option. If security matters and you are not using TLS, prefer serverPublicKey pinning.

Query database

var result = await pool.execute("SELECT * FROM book WHERE id = :id", {"id": 1});

Passing parameters with execute()

execute() accepts three invocation styles and, when parameters are present, it transparently switches to the binary protocol (prepared statements under the hood) so that blobs/bytes are transmitted safely and subsequent calls can reuse the cached statement:

// 1) Literal query only (text protocol)
final rs = await conn.execute('SELECT NOW() AS ts');

// 2) Named parameters
await conn.execute(
  'INSERT INTO book (title, price) VALUES (:title, :price)',
  {'title': 'Dart Up', 'price': 42.5},
);

// 3) Positional parameters
await conn.execute(
  'UPDATE book SET cover = ? WHERE id = ?',
  [Uint8List.fromList(bytes), 10],
);

If you need to stream results row-by-row instead of buffering the whole result, pass iterable: true to execute() (or prepare()), and consume rowsStream. For iterable result sets, the driver now propagates pause / resume from the consumer stream down to the socket subscription, so a slow consumer can apply real backpressure instead of only buffering rows in memory.

The automatic prepared-statement cache is per connection and defaults to 32 statements. If your workload has a larger hot set of parameterized SQL strings, tune it when creating a connection or pool:

final conn = await MySQLConnection.createConnection(
  host: 'localhost',
  port: 3306,
  userName: 'dart',
  password: 'dart',
  databaseName: 'app',
  autoPreparedStatementCacheCapacity: 128,
);

See doc/AUTO_PREPARED_CACHE_BENCHMARK.md for the hot-set vs thrash-set benchmark and sizing guidance.

Print result

  for (final row in result.rows) {
    print(row.assoc());
  }

There are two groups of methods to access column data. First group returns result as strings. Second one (methods starting with typed prefix) performs conversion to specified type.

F.e.:

row.colAt(0); // returns first column as String
row.typedColAt<int>(0); // returns first column as int 

Look at example/main_simple_conn.dart for other ways of getting column data, including typed data access.

⚠️ Decimal / NewDecimal columns – the driver deliberately returns String for these column types to preserve precision/scale. If you need native arithmetic inside Dart, either cast inside SQL (CAST(col AS DOUBLE)), parse manually, or rely on arbitrary-precision packages such as decimal. This behavior is covered in test/mysql_client.dart and test/column_type_test.dart.

Prepared statements

This library supports real prepared statements (using binary protocol).

Prepare statement

var stmt = await conn.prepare(
  "INSERT INTO book (author_id, title, price, created_at) VALUES (?, ?, ?, ?)",
);

Execute with params

await stmt.execute([null, 'Some book 1', 120, '2022-01-01']);
await stmt.execute([null, 'Some book 2', 10, '2022-01-01']);

Deallocate prepared statement

await stmt.deallocate();

For connection pools, do not keep a prepared statement outside the lifetime of the borrowed connection. Use pool.withPrepared(...):

await pool.withPrepared(
  'UPDATE book SET price = ? WHERE id = ?',
  (stmt) => stmt.execute([99, 1]),
);

Transactions

To execute queries in transaction, you can use transactional() method on connection or pool object Example:

await pool.transactional((conn) async {
  await conn.execute("UPDATE book SET price = :price", {"price": 300});
  await conn.execute("UPDATE book_author SET name = :name", {"name": "John Doe"});
});

In case of exception, transaction will roll back automatically.

Iterating large result sets

In case you need to process large result sets, you can use iterable result set. To use iterable result set, pass iterable = true, to execute() or prepare() methods. In this case rows will be ready as soon as they are delivered from the network. This allows you to process large amount of rows, one by one, in Stream fashion.

When using iterable result set, you need to use result.rowsStream.listen instead of result.rows to get access to rows.

MySQLConnectionPool.execute(..., iterable: true) is intentionally unsupported. A streamed result keeps its connection busy until EOF, so pooled streaming must be done through pool.withConnection(...) and fully consumed before the callback returns.

Example:

// make query (notice third parameter, iterable=true)
var result = await conn.execute("SELECT * FROM book", {}, true);

result.rowsStream.listen((row) {
  print(row.assoc());
});

Multiple statements queries

This library supports multiple statements in query() method. If your query contains multiple statements, result will contain next property, which will point to the next result set.

IResulSet class implements Iterable

Multple statements are not supported for prepared statements and iterable result sets. A CALL (which replies with its result sets and a final OK) works with prepared statements too, through next. An iterable result set streams the first result only; the results after it are read and discarded.

For example:

final resultSets = await conn.execute(
  "SELECT 1 as val_1_1; SELECT 2 as val_2_1, 3 as val_2_2",
);

assert(resultSets.next != null);

for (final result in resultSets) {
  // for every result set
  for (final row in result.rows) {
    // for every row in result set
    print(row.assoc());
  }
}

Tests

To run tests execute

dart test

The integration tests read MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DATABASE and MYSQL_SECURE (default true). With TLS, set MYSQL_CA_CERT to the CA that signed the server certificate, which must name MYSQL_HOST; .github/scripts/generate-test-certs.sh creates such a CA and certificate.

test/fuzz_decoders_test.dart feeds the packet decoders mutations of real server packets (test/fixtures, captured by tool/capture_server_packets.dart) and fails if anything other than a MySQLException escapes. It needs no server; for a longer run set FUZZ_ITERATIONS (default 200) and FUZZ_SEED:

FUZZ_ITERATIONS=20000 FUZZ_SEED=2 dart test test/fuzz_decoders_test.dart

test/protocol_faults_test.dart replays the same captures through a local TCP server and breaks each exchange at every packet (dropped connection, injected ERR/OK/EOF/empty packets, byte-by-byte or coalesced delivery); every call must end with a result or a MySQLException, never hang.

The pool test that restarts the database server runs only when MYSQL_RESTART_COMMAND is set to a command that restarts it (run by PowerShell on Windows, sh elsewhere), for example docker restart mysql:

MYSQL_RESTART_COMMAND="docker restart mysql" \
  dart test test/pool_test.dart --plain-name "recupera após reinício real"

Error handling

This library throws tree types of exceptions: MySQLServerException, MySQLClientException and MySQLProtocolException. See api reference for description of each type.

When exception is thrown, connection can be left in connected or closed state.

As a general rule, if cause of exception is MySQL server error packet, connection will be left in connected state and can be reused. If cause of exception is logical error, such as unexpected packet or something inside parsing of mysql protocol, connection will be closed and can not be used anymore.

It's up to developer to check connection state after catching exception. Inside your catch block, you can check connection status using conn.connected getter and decide what to do next.