# @probitas/client-sql

> Version: 0.5.0

Common SQL types and utilities for [Probitas](https://github.com/probitas-test/probitas) SQL client packages.

This package provides shared types, result classes, and errors used across
all SQL-related client packages.

## Features

- **Query Results**: `SqlQueryResult` class with row iteration and metadata
- **Transactions**: Common transaction interface with isolation levels
- **Error Hierarchy**: SQL-specific errors (`SqlError`, `QuerySyntaxError`, `ConstraintError`, `DeadlockError`)

## Installation

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

## Usage

This package is typically used as a dependency by database-specific packages.
End users should import from the specific database client packages instead.

```ts
// Example of SQL error handling (requires database-specific client)
import {
  SqlQueryResult,
  SqlError,
  ConstraintError,
} from "@probitas/client-sql";
import type { SqlTransaction, SqlIsolationLevel } from "@probitas/client-sql";

// Error types are available for use with database-specific clients
const isolationLevel: SqlIsolationLevel = "read_committed";
console.log("Isolation level:", isolationLevel);
```

## Database-Specific Packages

| Package | Description |
|---------|-------------|
| [`@probitas/client-sql-postgres`](https://jsr.io/@probitas/client-sql-postgres) | PostgreSQL client |
| [`@probitas/client-sql-mysql`](https://jsr.io/@probitas/client-sql-mysql) | MySQL 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)


## Classes

### `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

### `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

---

## Types

### `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#ConstraintError)
- [`DeadlockError`](https://probitas-test.github.io/documents/api/client-sql#DeadlockError)
- [`QuerySyntaxError`](https://probitas-test.github.io/documents/api/client-sql#QuerySyntaxError)
- [`SqlConnectionError`](https://probitas-test.github.io/documents/api/client-sql#SqlConnectionError)
- [`SqlError`](https://probitas-test.github.io/documents/api/client-sql#SqlError)
- [`SqlErrorKind`](https://probitas-test.github.io/documents/api/client-sql#SqlErrorKind)
- [`SqlErrorOptions`](https://probitas-test.github.io/documents/api/client-sql#SqlErrorOptions)
- [`SqlFailureError`](https://probitas-test.github.io/documents/api/client-sql#SqlFailureError)
- [`SqlIsolationLevel`](https://probitas-test.github.io/documents/api/client-sql#SqlIsolationLevel)
- [`SqlQueryOptions`](https://probitas-test.github.io/documents/api/client-sql#SqlQueryOptions)
- [`SqlQueryResult`](https://probitas-test.github.io/documents/api/client-sql#SqlQueryResult)
- [`SqlQueryResultError`](https://probitas-test.github.io/documents/api/client-sql#SqlQueryResultError)
- [`SqlQueryResultFailure`](https://probitas-test.github.io/documents/api/client-sql#SqlQueryResultFailure)
- [`SqlQueryResultSuccess`](https://probitas-test.github.io/documents/api/client-sql#SqlQueryResultSuccess)

### Other Packages

- [`AbortError`](https://probitas-test.github.io/documents/api/client#AbortError) (@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*