# @probitas/client-sql-mysql

> Version: 0.5.0

MySQL client for [Probitas](https://github.com/probitas-test/probitas) scenario testing framework.

This package provides a MySQL client designed for integration testing,
with transaction support and prepared statement capabilities.

## Features

- **Query Execution**: Parameterized queries with type-safe results
- **Transactions**: Full transaction support with isolation levels
- **Prepared Statements**: Automatic parameter escaping and type conversion
- **Connection Pooling**: Configurable pool with idle timeout
- **Resource Management**: Implements `AsyncDisposable` for proper cleanup

## Installation

```bash
deno add jsr:@probitas/client-sql-mysql
```

## Quick Start

```ts
import { createMySqlClient } from "@probitas/client-sql-mysql";

const client = await createMySqlClient({
  url: {
    host: "localhost",
    port: 3306,
    username: "root",
    password: "secret",
    database: "mydb",
  },
});

// Query with parameters (uses ? placeholders)
const result = await client.query<{ id: number; name: string }>(
  "SELECT id, name FROM users WHERE active = ?",
  [true]
);
console.log(result.rows);

// Get first row
const user = await client.queryOne<{ id: number; name: string }>(
  "SELECT * FROM users WHERE id = ?",
  [1]
);

await client.close();
```

## Transactions

```ts
import { createMySqlClient } from "@probitas/client-sql-mysql";
import type { SqlTransaction } from "@probitas/client-sql";

const client = await createMySqlClient({ url: "mysql://localhost:3306/testdb" });
await client.transaction(async (tx: SqlTransaction) => {
  await tx.query("INSERT INTO accounts (id, balance) VALUES (?, ?)", [1, 100]);
  await tx.query("INSERT INTO accounts (id, balance) VALUES (?, ?)", [2, 200]);
  // Automatically committed if no error, rolled back on exception
}, { isolationLevel: "serializable" });
await client.close();
```

## Using with `using` Statement

```ts
import { createMySqlClient } from "@probitas/client-sql-mysql";

await using client = await createMySqlClient({
  url: { host: "localhost", username: "root", database: "testdb" },
});

const result = await client.query("SELECT 1 as n");
// Client automatically closed when block exits
```

## Related Packages

| Package | Description |
|---------|-------------|
| [`@probitas/client-sql`](https://jsr.io/@probitas/client-sql) | Common SQL types and utilities |
| [`@probitas/client-sql-postgres`](https://jsr.io/@probitas/client-sql-postgres) | PostgreSQL client |
| [`@probitas/client-sql-sqlite`](https://jsr.io/@probitas/client-sql-sqlite) | SQLite client |
| [`@probitas/client-sql-duckdb`](https://jsr.io/@probitas/client-sql-duckdb) | DuckDB client |

## Links

- [GitHub Repository](https://github.com/probitas-test/probitas-client)
- [Probitas Framework](https://github.com/probitas-test/probitas)
- [MySQL](https://www.mysql.com/)


## Classes

### `MySqlTransactionImpl`

```typescript
class MySqlTransactionImpl implements MySqlTransaction
```

**Constructor:**

```typescript
new MySqlTransactionImpl(connection: AnyPoolConnection)
```

**Methods:**

```typescript
static begin(): unknown
```

Begin a new transaction.

```typescript
query(): unknown
```

```typescript
queryOne(): unknown
```

```typescript
commit(): unknown
```

```typescript
rollback(): unknown
```

```typescript
savepoint(): unknown
```

```typescript
rollbackToSavepoint(): unknown
```

```typescript
releaseSavepoint(): unknown
```

---

### `MySqlError`

```typescript
class MySqlError extends SqlError
```

Base error class for MySQL-specific errors.
Extends SqlError with MySQL-specific properties like errno.

**Constructor:**

```typescript
new MySqlError(message: string, options?: MySqlErrorOptions)
```

**Properties:**

- [readonly] `name`: `string`
- [readonly] `errno?`: `number`

---

### `AccessDeniedError`

```typescript
class AccessDeniedError extends MySqlError
```

Error thrown when access is denied (wrong credentials).

**Constructor:**

```typescript
new AccessDeniedError(message: string, options?: MySqlErrorOptions)
```

**Properties:**

- [readonly] `name`: `string`
- [readonly] `mysqlKind`: `"access_denied"`

---

### `ConnectionRefusedError`

```typescript
class ConnectionRefusedError extends MySqlError
```

Error thrown when connection is refused.

**Constructor:**

```typescript
new ConnectionRefusedError(message: string, options?: MySqlErrorOptions)
```

**Properties:**

- [readonly] `name`: `string`
- [readonly] `mysqlKind`: `"connection_refused"`

---

### `SqlError`

```typescript
class SqlError extends ClientError
```

Base error class for SQL-specific errors.
Extends ClientError with SQL-specific properties.

**Constructor:**

```typescript
new SqlError(message: string, kind: SqlErrorKind, options?: SqlErrorOptions)
```

**Properties:**

- [readonly] `name`: `string`
- [readonly] `kind`: `SqlErrorKind`
- [readonly] `sqlState`: `string | null`

---

### `QuerySyntaxError`

```typescript
class QuerySyntaxError extends SqlError
```

Error thrown when a SQL query has syntax errors.

**Constructor:**

```typescript
new QuerySyntaxError(message: string, options?: SqlErrorOptions)
```

**Properties:**

- [readonly] `name`: `string`
- [readonly] `kind`: `"query"`

---

### `ConstraintError`

```typescript
class ConstraintError extends SqlError
```

Error thrown when a constraint violation occurs.

**Constructor:**

```typescript
new ConstraintError(
  message: string,
  constraint: string,
  options?: SqlErrorOptions,
)
```

**Properties:**

- [readonly] `name`: `string`
- [readonly] `kind`: `"constraint"`
- [readonly] `constraint`: `string`

---

### `DeadlockError`

```typescript
class DeadlockError extends SqlError
```

Error thrown when a deadlock is detected.

**Constructor:**

```typescript
new DeadlockError(message: string, options?: SqlErrorOptions)
```

**Properties:**

- [readonly] `name`: `string`
- [readonly] `kind`: `"deadlock"`

---

### `SqlConnectionError`

```typescript
class SqlConnectionError extends SqlError
```

Error thrown when a connection or network-level error occurs.

This includes:
- Connection refused (server not running)
- Authentication failure
- Connection timeout
- Pool exhaustion
- TLS handshake failure
- DNS resolution failure

**Constructor:**

```typescript
new SqlConnectionError(message: string, options?: SqlErrorOptions)
```

**Properties:**

- [readonly] `name`: `string`
- [readonly] `kind`: `"connection"`

---

## Interfaces

### `MySqlClient`

```typescript
interface MySqlClient extends AsyncDisposable
```

MySQL client interface.

**Properties:**

- [readonly] `config`: `MySqlClientConfig` — The client configuration.
- [readonly] `dialect`: `"mysql"` — The SQL dialect identifier.

**Methods:**

```typescript
query<T = Record<string, any>>(
  sql: string,
  params?: unknown[],
  options?: SqlQueryOptions,
): Promise<SqlQueryResult<T>>
```

Execute a SQL query.

```typescript
queryOne<T = Record<string, any>>(
  sql: string,
  params?: unknown[],
  options?: SqlQueryOptions,
): Promise<T | undefined>
```

Execute a query and return the first row or undefined.

```typescript
transaction<T>(
  fn: (tx: SqlTransaction) => unknown,
  options?: SqlTransactionOptions,
): Promise<T>
```

Execute a function within a transaction.
Automatically commits on success or rolls back on error.

```typescript
close(): Promise<void>
```

Close the client and release all connections.

---

### `MySqlTransaction`

```typescript
interface MySqlTransaction extends SqlTransaction
```

MySQL-specific transaction interface.
Extends SqlTransaction with MySQL-specific features.

**Methods:**

```typescript
savepoint(name: string): Promise<void>
```

Create a savepoint.

```typescript
rollbackToSavepoint(name: string): Promise<void>
```

Rollback to a savepoint.

```typescript
releaseSavepoint(name: string): Promise<void>
```

Release a savepoint.

---

### `MySqlTlsConfig`

```typescript
interface MySqlTlsConfig
```

TLS/SSL configuration for MySQL connections.

**Properties:**

- [readonly] `ca?`: `string` — Root CA certificate (PEM format).
- [readonly] `cert?`: `string` — Client certificate (PEM format).
- [readonly] `key?`: `string` — Client private key (PEM format).
- [readonly] `rejectUnauthorized?`: `boolean` — Skip server certificate verification (use only for testing).

---

### `MySqlPoolConfig`

```typescript
interface MySqlPoolConfig
```

Connection pool configuration.

**Properties:**

- [readonly] `connectionLimit?`: `number` — Maximum number of connections in the pool.
- [readonly] `minConnections?`: `number` — Minimum number of idle connections.
- [readonly] `queueLimit?`: `number` — Maximum number of connection requests the pool will queue.
- [readonly] `waitForConnections?`: `boolean` — Whether to wait for connections to become available.
- [readonly] `idleTimeout?`: `number` — Time in milliseconds before connection is considered idle.

---

### `MySqlConnectionConfig`

```typescript
interface MySqlConnectionConfig extends CommonConnectionConfig
```

MySQL connection configuration.

Extends CommonConnectionConfig with MySQL-specific options.

**Properties:**

- [readonly] `database?`: `string` — Database name to connect to.
- [readonly] `tls?`: `MySqlTlsConfig` — TLS configuration.
- [readonly] `charset?`: `string` — Character set.

---

### `MySqlClientConfig`

```typescript
interface MySqlClientConfig extends CommonOptions
```

Configuration for creating a MySQL client.

**Properties:**

- [readonly] `url`: `string | MySqlConnectionConfig` — Connection URL or configuration.

Can be a connection URL string (e.g., "mysql://user:pass@host:port/database")
or a detailed MySqlConnectionConfig object.
- [readonly] `pool?`: `MySqlPoolConfig` — Connection pool configuration.
- [readonly] `timezone?`: `string` — Timezone for the connection.
- [readonly] `throwOnError?`: `boolean` — Whether to throw an error for query failures.
When false, failures are returned as SqlQueryResultError or SqlQueryResultFailure.
Can be overridden per-query via SqlQueryOptions.

---

### `MySqlErrorOptions`

```typescript
interface MySqlErrorOptions extends SqlErrorOptions
```

Options for MySqlError constructor.

**Properties:**

- [readonly] `errno?`: `number` — MySQL error code (e.g., 1045, 1062).

---

### `SqlQueryOptions`

```typescript
interface SqlQueryOptions extends CommonOptions
```

Options for individual SQL queries.

**Properties:**

- [readonly] `throwOnError?`: `boolean` — Whether to throw an error for query failures.
When false, failures are returned as SqlQueryResultError or SqlQueryResultFailure.

---

### `SqlTransactionOptions`

```typescript
interface SqlTransactionOptions
```

Options for starting a transaction.

**Properties:**

- [readonly] `isolationLevel?`: `SqlIsolationLevel` — Isolation level for the transaction

---

### `SqlTransaction`

```typescript
interface SqlTransaction
```

SQL transaction interface.
Implementations should provide actual database-specific transaction handling.

**Methods:**

```typescript
query<T = Record<string, any>>(
  sql: string,
  params?: unknown[],
  options?: SqlQueryOptions,
): Promise<SqlQueryResult<T>>
```

Execute a query within the transaction.

```typescript
queryOne<T = Record<string, any>>(
  sql: string,
  params?: unknown[],
  options?: SqlQueryOptions,
): Promise<T | undefined>
```

Execute a query and return the first row or undefined.

```typescript
commit(): Promise<void>
```

Commit the transaction.

```typescript
rollback(): Promise<void>
```

Rollback the transaction.

---

### `SqlErrorOptions`

```typescript
interface SqlErrorOptions extends ErrorOptions
```

Options for SqlError constructor.

**Properties:**

- [readonly] `sqlState?`: `string` — SQL State code (e.g., "23505" for unique violation)

---

### `SqlQueryResultSuccess`

```typescript
interface SqlQueryResultSuccess<T = any> extends SqlQueryResultBase<T>
```

SQL query result for successful queries.

The query was executed successfully and returned results.

**Properties:**

- [readonly] `processed`: `true` — Server processed the query.
- [readonly] `ok`: `true` — Query succeeded.
- [readonly] `error`: `null` — No error for successful queries.
- [readonly] `rows`: `readonly T[]` — Query result rows.
- [readonly] `rowCount`: `number` — Number of affected rows.
- [readonly] `lastInsertId`: `bigint | string | null` — Last inserted ID (for INSERT statements).
- [readonly] `warnings`: `unknown | null` — Warning messages from the database.

---

### `SqlQueryResultError`

```typescript
interface SqlQueryResultError<T = any> extends SqlQueryResultBase<T>
```

SQL query result for query errors (syntax errors, constraint violations, etc.).

Server received and processed the query, but it failed due to a SQL error.

**Properties:**

- [readonly] `processed`: `true` — Server processed the query.
- [readonly] `ok`: `false` — Query failed.
- [readonly] `error`: `SqlError` — Error describing the SQL error.
- [readonly] `rows`: `readonly never[]` — Empty rows for failed queries.
- [readonly] `rowCount`: `0` — Zero affected rows for failed queries.
- [readonly] `lastInsertId`: `null` — No lastInsertId for failed queries.
- [readonly] `warnings`: `null` — No warnings for failed queries.

---

### `SqlQueryResultFailure`

```typescript
interface SqlQueryResultFailure<T = any> extends SqlQueryResultBase<T>
```

SQL query result for connection failures (network errors, timeouts, etc.).

Query could not be processed by the server (connection refused, timeout,
pool exhausted, authentication failure, etc.).

**Properties:**

- [readonly] `processed`: `false` — Server did not process the query.
- [readonly] `ok`: `false` — Query failed.
- [readonly] `error`: `SqlFailureError` — Error describing the failure.
- [readonly] `rows`: `null` — No rows (query didn't reach server).
- [readonly] `rowCount`: `null` — No row count (query didn't reach server).
- [readonly] `lastInsertId`: `null` — No lastInsertId (query didn't reach server).
- [readonly] `warnings`: `null` — No warnings (query didn't reach server).

---

### `SqlQueryResultSuccessParams`

```typescript
interface SqlQueryResultSuccessParams<T = any>
```

Parameters for creating a SqlQueryResultSuccess.

**Properties:**

- [readonly] `rows`: `readonly T[]` — The result rows
- [readonly] `rowCount`: `number` — Number of affected rows (for INSERT/UPDATE/DELETE)
- [readonly] `duration`: `number` — Query execution duration in milliseconds
- [readonly] `lastInsertId?`: `bigint | string` — Last inserted ID (for INSERT statements)
- [readonly] `warnings?`: `readonly string[]` — Warning messages from the database

---

## Functions

### `createMySqlClient`

```typescript
async function createMySqlClient(
  config: MySqlClientConfig,
): Promise<MySqlClient>
```

Create a new MySQL client instance with connection pooling.

The client provides connection pooling, parameterized queries, transaction support,
and automatic result type mapping.


**Parameters:**

- `config`: `MySqlClientConfig` — - MySQL client configuration

**Returns:** `Promise<MySqlClient>`

A promise resolving to a new MySQL client instance


**Example:**

Using URL string
```ts
import { createMySqlClient } from "@probitas/client-sql-mysql";

const client = await createMySqlClient({
  url: "mysql://user:password@localhost:3306/testdb",
});

const result = await client.query<{ id: number; name: string }>(
  "SELECT * FROM users WHERE id = ?",
  [1],
);
if (result.ok) {
  console.log(result.rows[0]);  // { id: 1, name: "Alice" }
}

await client.close();
```

Using connection config object
```ts
import { createMySqlClient } from "@probitas/client-sql-mysql";

const client = await createMySqlClient({
  url: {
    host: "localhost",
    port: 3306,
    username: "root",
    password: "password",
    database: "testdb",
  },
  pool: { connectionLimit: 20 },
});
await client.close();
```

Transaction with auto-commit/rollback
```ts
import { createMySqlClient } from "@probitas/client-sql-mysql";
import type { SqlTransaction } from "@probitas/client-sql";

const client = await createMySqlClient({ url: "mysql://localhost:3306/testdb" });
const user = await client.transaction(async (tx: SqlTransaction) => {
  await tx.query("INSERT INTO users (name) VALUES (?)", ["Alice"]);
  return await tx.queryOne("SELECT LAST_INSERT_ID() as id");
});
await client.close();
```

Using `await using` for automatic cleanup
```ts
import { createMySqlClient } from "@probitas/client-sql-mysql";

await using client = await createMySqlClient({
  url: "mysql://localhost:3306/testdb",
});

const result = await client.query("SELECT 1");
// Client automatically closed when scope exits
```

---

### `isConnectionError`

```typescript
function isConnectionError(error: unknown): boolean
```

Check if an error is a connection-level error.

**Parameters:**

- `error`: `unknown`

**Returns:** `boolean`

---

### `convertMySqlError`

```typescript
function convertMySqlError(error: unknown): SqlError
```

Convert a mysql2 error to the appropriate error class.

**Parameters:**

- `error`: `unknown`

**Returns:** `SqlError`

---

## Types

### `MySqlErrorKind`

```typescript
type MySqlErrorKind = "access_denied" | "connection_refused"
```

MySQL-specific error kinds.

---

### `SqlIsolationLevel`

```typescript
type SqlIsolationLevel = "read_uncommitted" | "read_committed" | "repeatable_read" | "serializable"
```

Transaction isolation level.

---

### `SqlErrorKind`

```typescript
type SqlErrorKind = "query" | "constraint" | "deadlock" | "connection" | "unknown"
```

SQL-specific error kinds.

---

### `SqlOperationError`

```typescript
type SqlOperationError = QuerySyntaxError | ConstraintError | DeadlockError | SqlError
```

Error types that indicate an operation was processed by the server.
These errors occur after the query reaches the SQL server.

---

### `SqlFailureError`

```typescript
type SqlFailureError = SqlConnectionError | AbortError | TimeoutError
```

Error types that indicate the operation was not processed.
These are errors that occur before the query reaches the SQL server.

---

### `SqlQueryResult`

```typescript
type SqlQueryResult<T = any> = SqlQueryResultSuccess<T> | SqlQueryResultError<T> | SqlQueryResultFailure<T>
```

SQL query result union type representing all possible result states.

- **Success**: `processed: true, ok: true, error: null`
- **Error**: `processed: true, ok: false, error: SqlError`
- **Failure**: `processed: false, ok: false, error: SqlConnectionError`


---

## Related Links

### This Package

- [`ConstraintError`](https://probitas-test.github.io/documents/api/client-sql-mysql#ConstraintError)
- [`DeadlockError`](https://probitas-test.github.io/documents/api/client-sql-mysql#DeadlockError)
- [`MySqlClient`](https://probitas-test.github.io/documents/api/client-sql-mysql#MySqlClient)
- [`MySqlClientConfig`](https://probitas-test.github.io/documents/api/client-sql-mysql#MySqlClientConfig)
- [`MySqlConnectionConfig`](https://probitas-test.github.io/documents/api/client-sql-mysql#MySqlConnectionConfig)
- [`MySqlErrorOptions`](https://probitas-test.github.io/documents/api/client-sql-mysql#MySqlErrorOptions)
- [`MySqlPoolConfig`](https://probitas-test.github.io/documents/api/client-sql-mysql#MySqlPoolConfig)
- [`MySqlTlsConfig`](https://probitas-test.github.io/documents/api/client-sql-mysql#MySqlTlsConfig)
- [`MySqlTransaction`](https://probitas-test.github.io/documents/api/client-sql-mysql#MySqlTransaction)
- [`QuerySyntaxError`](https://probitas-test.github.io/documents/api/client-sql-mysql#QuerySyntaxError)
- [`SqlConnectionError`](https://probitas-test.github.io/documents/api/client-sql-mysql#SqlConnectionError)
- [`SqlError`](https://probitas-test.github.io/documents/api/client-sql-mysql#SqlError)
- [`SqlErrorKind`](https://probitas-test.github.io/documents/api/client-sql-mysql#SqlErrorKind)
- [`SqlErrorOptions`](https://probitas-test.github.io/documents/api/client-sql-mysql#SqlErrorOptions)
- [`SqlFailureError`](https://probitas-test.github.io/documents/api/client-sql-mysql#SqlFailureError)
- [`SqlIsolationLevel`](https://probitas-test.github.io/documents/api/client-sql-mysql#SqlIsolationLevel)
- [`SqlQueryOptions`](https://probitas-test.github.io/documents/api/client-sql-mysql#SqlQueryOptions)
- [`SqlQueryResult`](https://probitas-test.github.io/documents/api/client-sql-mysql#SqlQueryResult)
- [`SqlQueryResultError`](https://probitas-test.github.io/documents/api/client-sql-mysql#SqlQueryResultError)
- [`SqlQueryResultFailure`](https://probitas-test.github.io/documents/api/client-sql-mysql#SqlQueryResultFailure)
- [`SqlQueryResultSuccess`](https://probitas-test.github.io/documents/api/client-sql-mysql#SqlQueryResultSuccess)
- [`SqlTransaction`](https://probitas-test.github.io/documents/api/client-sql-mysql#SqlTransaction)
- [`SqlTransactionOptions`](https://probitas-test.github.io/documents/api/client-sql-mysql#SqlTransactionOptions)

### Other Packages

- [`AbortError`](https://probitas-test.github.io/documents/api/client#AbortError) (@probitas/client)
- [`CommonConnectionConfig`](https://probitas-test.github.io/documents/api/client#CommonConnectionConfig) (@probitas/client)
- [`CommonOptions`](https://probitas-test.github.io/documents/api/client#CommonOptions) (@probitas/client)
- [`TimeoutError`](https://probitas-test.github.io/documents/api/client#TimeoutError) (@probitas/client)

### Built-in Types

- [`Promise`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise)
- [`Record`](https://www.typescriptlang.org/docs/handbook/utility-types.html#recordkeys-type)

---

*Last updated: 2026-01-12*