@probitas/client-sql-sqlite

SQLite client for Probitas scenario testing framework.

This package provides a SQLite client designed for integration testing, with transaction support and in-memory database capabilities.

Features

• Query Execution: Parameterized queries with type-safe results
• Transactions: Full transaction support with isolation levels
• In-Memory Databases: Perfect for isolated test scenarios
• File-Based Databases: Persist data for stateful testing
• Resource Management: Implements AsyncDisposable for proper cleanup

Installation

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

Quick Start

import { createSqliteClient } from "@probitas/client-sql-sqlite";

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

// Create table
await client.query(`
  CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    active INTEGER DEFAULT 1
  )
`);

// Insert and query with parameters (uses ? placeholders)
await client.query("INSERT INTO users (name) VALUES (?)", ["Alice"]);

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

await client.close();

Transactions

await client.transaction(async (tx) => {
  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
});

Using with using Statement

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

await client.query("CREATE TABLE test (id INTEGER PRIMARY KEY)");
// Client automatically closed when block exits

Related Packages

Package	Description
@probitas/client-sql	Common SQL types and utilities
@probitas/client-sql-postgres	PostgreSQL client
@probitas/client-sql-mysql	MySQL client
@probitas/client-sql-duckdb	DuckDB client

Links

• GitHub Repository
• Probitas Framework
• SQLite

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

class BusyError extends SqliteError

new BusyError(message: string, options?: SqliteErrorOptions)

class ConstraintError extends SqlError

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

class DatabaseLockedError extends SqliteError

new DatabaseLockedError(message: string, options?: SqliteErrorOptions)

class DeadlockError extends SqlError

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

class QuerySyntaxError extends SqlError

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

class ReadonlyDatabaseError extends SqliteError

new ReadonlyDatabaseError(message: string, options?: SqliteErrorOptions)

class SqlError extends ClientError

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

class SqliteError extends SqlError

new SqliteError(message: string, options?: SqliteErrorOptions)

class SqliteTransactionImpl implements SqlTransaction

new SqliteTransactionImpl(db: Database)

static begin(): unknown

query(): unknown

queryOne(): unknown

commit(): unknown

rollback(): unknown

class SqlQueryResult<T = Record<string, any>>

new SqlQueryResult(init: SqlQueryResultInit<T>)

map(): unknown

as(): unknown

class SqlRows<T> extends Array<T>

new SqlRows(items: readonly T[])

first(): unknown

firstOrThrow(): unknown

last(): unknown

lastOrThrow(): unknown

interface SqlErrorOptions extends ErrorOptions

interface SqliteClient extends AsyncDisposable

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

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

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

backup(destPath: string): Promise<void>

vacuum(): Promise<void>

close(): Promise<void>

interface SqliteClientConfig extends CommonOptions

interface SqliteErrorOptions extends SqlErrorOptions

interface SqliteTransactionOptions extends SqlTransactionOptions

interface SqlQueryResultExpectation<T>

ok(): this

notOk(): this

noContent(): this

hasContent(): this

rows(count: number): this

rowsAtLeast(count: number): this

rowsAtMost(count: number): this

rowCount(count: number): this

rowCountAtLeast(count: number): this

rowCountAtMost(count: number): this

rowContains(subset: Partial<T>): this

rowMatch(matcher: (rows: SqlRows<T>) => unknown): this

mapContains<U>(mapper: (row: T) => unknown, subset: Partial<U>): this

mapMatch<U>(mapper: (row: T) => unknown, matcher: (mapped: U[]) => unknown): this

asContains<U>(ctor: (row: T) => unknown, subset: Partial<U>): this

asMatch<U>(ctor: (row: T) => unknown, matcher: (instances: U[]) => unknown): this

lastInsertId(expected: bigint | string): this

hasLastInsertId(): this

durationLessThan(ms: number): this

interface SqlQueryResultInit<T>

interface SqlQueryResultMetadata

interface SqlTransaction

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

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

commit(): Promise<void>

rollback(): Promise<void>

interface SqlTransactionOptions

function convertSqliteError(error: unknown): SqlError

function createSqliteClient(config: SqliteClientConfig): Promise<SqliteClient>

const client = await createSqliteClient({
  path: "./data.db",
});

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

await client.close();

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

const user = await client.transaction(async (tx) => {
  await tx.query("INSERT INTO users (name) VALUES (?)", ["Alice"]);
  const result = await tx.query("SELECT last_insert_rowid() as id");
  return result.rows.first();
});

// Create a backup of the database
await client.backup("./backup.db");

await using client = await createSqliteClient({ path: "./data.db" });

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

function expectSqlQueryResult<T = Record<string, any>>(result: SqlQueryResult<T>): SqlQueryResultExpectation<T>

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

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

type SqliteErrorKind = "database_locked" | "readonly" | "busy"

type SqliteTransactionMode = "deferred" | "immediate" | "exclusive"