@probitas/client-sql-duckdb

DuckDB client for 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

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

Quick Start

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

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

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

Links

• GitHub Repository
• Probitas Framework
• DuckDB

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

class CatalogError extends DuckDbError

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

class ConstraintError extends SqlError

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

class DeadlockError extends SqlError

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

class DuckDbError extends SqlError

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

class DuckDbTransactionImpl implements SqlTransaction

new DuckDbTransactionImpl(conn: DuckDBConnection)

static begin(): unknown

query(): unknown

queryOne(): unknown

commit(): unknown

rollback(): unknown

class IoError extends DuckDbError

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

class QuerySyntaxError extends SqlError

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

class SqlConnectionError extends SqlError

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

class SqlError extends ClientError

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

interface DuckDbClient extends AsyncDisposable

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

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

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

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

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

close(): Promise<void>

interface DuckDbClientConfig extends CommonOptions

interface DuckDbErrorOptions extends SqlErrorOptions

interface DuckDbTransactionOptions extends SqlTransactionOptions

interface SqlErrorOptions extends ErrorOptions

interface SqlQueryOptions extends CommonOptions

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

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

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

interface SqlQueryResultSuccessParams<T = any>

interface SqlTransaction

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

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

commit(): Promise<void>

rollback(): Promise<void>

interface SqlTransactionOptions

function convertDuckDbError(error: unknown): SqlError

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

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();

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

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

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();

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();

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();

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

function isConnectionError(error: unknown): boolean

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

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

type SqlFailureError = SqlConnectionError | AbortError | TimeoutError

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

type SqlOperationError = QuerySyntaxError | ConstraintError | DeadlockError | SqlError

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