# @probitas/client-sql-duckdb

> Version: 0.5.0

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

This package provides a DuckDB client designed for integration testing,
with analytical query capabilities and Parquet/CSV file support.

## Features

- **Query Execution**: Parameterized queries with type-safe results
- **Transactions**: Full transaction support
- **File Formats**: Native support for Parquet, CSV, and JSON files
- **In-Memory Databases**: Perfect for isolated test scenarios
- **Analytical Queries**: Optimized for OLAP workloads
- **Resource Management**: Implements `AsyncDisposable` for proper cleanup

## Installation

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

## Quick Start

```ts
import { createDuckDbClient } from "@probitas/client-sql-duckdb";

// In-memory database for testing
const client = await createDuckDbClient({
  path: ":memory:",
});

// Query from Parquet files
const result = await client.query<{ id: number; name: string }>(
  "SELECT id, name FROM read_parquet('data/*.parquet') WHERE active = ?",
  [true]
);
console.log(result.rows);

// Analytical queries
const stats = await client.query(`
  SELECT
    date_trunc('month', created_at) as month,
    COUNT(*) as count,
    AVG(amount) as avg_amount
  FROM transactions
  GROUP BY 1
  ORDER BY 1
`);

await client.close();
```

## Transactions

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

const client = await createDuckDbClient({ path: ":memory:" });
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
});
await client.close();
```

## Using with `using` Statement

```ts
import { createDuckDbClient } from "@probitas/client-sql-duckdb";

await using client = await createDuckDbClient({ path: ":memory:" });

const result = await client.query("SELECT 42 as answer");
// 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-mysql`](https://jsr.io/@probitas/client-sql-mysql) | MySQL client |
| [`@probitas/client-sql-sqlite`](https://jsr.io/@probitas/client-sql-sqlite) | SQLite client |

## Links

- [GitHub Repository](https://github.com/probitas-test/probitas-client)
- [Probitas Framework](https://github.com/probitas-test/probitas)
- [DuckDB](https://duckdb.org/)


## Classes

### `DuckDbTransactionImpl`

```typescript
class DuckDbTransactionImpl implements SqlTransaction
```

**Constructor:**

```typescript
new DuckDbTransactionImpl(conn: DuckDBConnection)
```

**Methods:**

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

Begin a new transaction.

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

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

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

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

---

### `DuckDbError`

```typescript
class DuckDbError extends SqlError
```

Base error class for DuckDB-specific errors.
Extends SqlError with DuckDB-specific properties.

**Constructor:**

```typescript
new DuckDbError(message: string, options?: DuckDbErrorOptions)
```

**Properties:**

- [readonly] `name`: `string`
- [readonly] `errorType?`: `string`

---

### `IoError`

```typescript
class IoError extends DuckDbError
```

Error thrown for IO-related errors (file not found, permission denied, etc.).

**Constructor:**

```typescript
new IoError(message: string, options?: DuckDbErrorOptions)
```

**Properties:**

- [readonly] `name`: `string`
- [readonly] `duckdbKind`: `"io"`

---

### `CatalogError`

```typescript
class CatalogError extends DuckDbError
```

Error thrown for catalog errors (table not found, etc.).

**Constructor:**

```typescript
new CatalogError(message: string, options?: DuckDbErrorOptions)
```

**Properties:**

- [readonly] `name`: `string`
- [readonly] `duckdbKind`: `"catalog"`

---

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

### `DuckDbClient`

```typescript
interface DuckDbClient extends AsyncDisposable
```

DuckDB client interface.

**Properties:**

- [readonly] `config`: `DuckDbClientConfig` — The client configuration.
- [readonly] `dialect`: `"duckdb"` — 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 | DuckDbTransactionOptions,
): Promise<T>
```

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

```typescript
queryParquet<T = Record<string, any>>(path: string): Promise<SqlQueryResult<T>>
```

Query a Parquet file directly.
DuckDB can read Parquet files without importing them.

```typescript
queryCsv<T = Record<string, any>>(path: string): Promise<SqlQueryResult<T>>
```

Query a CSV file directly.
DuckDB can read CSV files without importing them.

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

Close the database connection.

---

### `DuckDbTransactionOptions`

```typescript
interface DuckDbTransactionOptions extends SqlTransactionOptions
```

DuckDB-specific transaction options.

---

### `DuckDbClientConfig`

```typescript
interface DuckDbClientConfig extends CommonOptions
```

Configuration for creating a DuckDB client.

**Properties:**

- [readonly] `path?`: `string` — Database file path.
Use `:memory:` or omit for an in-memory database.
- [readonly] `readonly?`: `boolean` — Open the database in read-only mode.
- [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.

---

### `DuckDbErrorOptions`

```typescript
interface DuckDbErrorOptions extends SqlErrorOptions
```

Options for DuckDbError constructor.

**Properties:**

- [readonly] `errorType?`: `string` — DuckDB error type if available.

---

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

### `createDuckDbClient`

```typescript
async function createDuckDbClient(
  config: DuckDbClientConfig,
): Promise<DuckDbClient>
```

Create a new DuckDB client instance.

The client provides parameterized queries, transaction support,
and DuckDB-specific features like direct Parquet and CSV file querying.


**Parameters:**

- `config`: `DuckDbClientConfig` — - DuckDB client configuration

**Returns:** `Promise<DuckDbClient>`

A promise resolving to a new DuckDB client instance


**Example:**

Using in-memory database (default)
```ts
import { createDuckDbClient } from "@probitas/client-sql-duckdb";

const client = await createDuckDbClient({});

const result = await client.query("SELECT 42 as answer");
if (result.ok) {
  console.log(result.rows[0]);  // { answer: 42 }
}

await client.close();
```

Using file-based database
```ts
import { createDuckDbClient } from "@probitas/client-sql-duckdb";

const client = await createDuckDbClient({
  path: "./data.duckdb",
});
await client.close();
```

Query Parquet files directly
```ts
import { createDuckDbClient } from "@probitas/client-sql-duckdb";

const client = await createDuckDbClient({ path: ":memory:" });

// Query directly from Parquet
const result = await client.queryParquet<{ id: number; value: string }>(
  "./data/events.parquet"
);

await client.close();
```

Query CSV files directly
```ts
import { createDuckDbClient } from "@probitas/client-sql-duckdb";

const client = await createDuckDbClient({ path: ":memory:" });

const result = await client.queryCsv<{ name: string; age: number }>(
  "./data/users.csv"
);

await client.close();
```

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

const client = await createDuckDbClient({ path: ":memory:" });
await client.transaction(async (tx: SqlTransaction) => {
  await tx.query("INSERT INTO users VALUES ($1, $2)", [1, "Alice"]);
  await tx.query("INSERT INTO users VALUES ($1, $2)", [2, "Bob"]);
});
await client.close();
```

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

await using client = await createDuckDbClient({});

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

---

### `convertDuckDbError`

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

Convert a DuckDB error to the appropriate error class.

DuckDB errors are classified based on message content analysis.

**Parameters:**

- `error`: `unknown`

**Returns:** `SqlError`

---

### `isConnectionError`

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

Check if an error is a connection-level error.
These are errors that indicate the database cannot be accessed at all,
not errors that occur during query execution.

**Parameters:**

- `error`: `unknown`

**Returns:** `boolean`

---

## Types

### `DuckDbErrorKind`

```typescript
type DuckDbErrorKind = "io" | "catalog" | "parser" | "binder"
```

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

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