# @probitas/client-sql-sqlite

> Version: 0.5.0

SQLite client for [Probitas](https://github.com/probitas-test/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

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

## Quick Start

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

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

const client = await createSqliteClient({ 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 { createSqliteClient } from "@probitas/client-sql-sqlite";

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`](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-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)
- [SQLite](https://www.sqlite.org/)


## Classes

### `SqliteTransactionImpl`

```typescript
class SqliteTransactionImpl implements SqlTransaction
```

**Constructor:**

```typescript
new SqliteTransactionImpl(db: Database)
```

**Methods:**

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

Begin a new transaction.

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

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

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

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

---

### `SqliteError`

```typescript
class SqliteError extends SqlError
```

Base error class for SQLite-specific errors.
Extends SqlError with SQLite-specific properties like extendedCode.

**Constructor:**

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

**Properties:**

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

---

### `DatabaseLockedError`

```typescript
class DatabaseLockedError extends SqliteError
```

Error thrown when the database is locked.

**Constructor:**

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

**Properties:**

- [readonly] `name`: `string`
- [readonly] `sqliteKind`: `"database_locked"`

---

### `ReadonlyDatabaseError`

```typescript
class ReadonlyDatabaseError extends SqliteError
```

Error thrown when trying to write to a readonly database.

**Constructor:**

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

**Properties:**

- [readonly] `name`: `string`
- [readonly] `sqliteKind`: `"readonly"`

---

### `BusyError`

```typescript
class BusyError extends SqliteError
```

Error thrown when the database is busy.

**Constructor:**

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

**Properties:**

- [readonly] `name`: `string`
- [readonly] `sqliteKind`: `"busy"`

---

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

### `SqliteClient`

```typescript
interface SqliteClient extends AsyncDisposable
```

SQLite client interface.

**Properties:**

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

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

```typescript
backup(destPath: string): Promise<void>
```

Backup the database to a file.
Uses VACUUM INTO for a consistent backup.

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

Run VACUUM to rebuild the database file, reclaiming unused space.

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

Close the database connection.

---

### `SqliteTransactionOptions`

```typescript
interface SqliteTransactionOptions extends SqlTransactionOptions
```

SQLite-specific transaction options.

**Properties:**

- [readonly] `mode?`: `SqliteTransactionMode` — Transaction behavior mode.

---

### `SqliteClientConfig`

```typescript
interface SqliteClientConfig extends CommonOptions
```

Configuration for creating a SQLite client.

**Properties:**

- [readonly] `path`: `string` — Database file path.
Use ":memory:" for an in-memory database.
- [readonly] `readonly?`: `boolean` — Open the database in read-only mode.
- [readonly] `wal?`: `boolean` — Enable WAL (Write-Ahead Logging) mode.
WAL mode provides better concurrency and performance.
- [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.

---

### `SqliteErrorOptions`

```typescript
interface SqliteErrorOptions extends SqlErrorOptions
```

Options for SqliteError constructor.

**Properties:**

- [readonly] `extendedCode?`: `number` — SQLite extended error code.

---

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

### `createSqliteClient`

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

Create a new SQLite client instance.

The client provides parameterized queries, transaction support,
WAL mode for better concurrency, and SQLite-specific features like backup and vacuum.


**Parameters:**

- `config`: `SqliteClientConfig` — - SQLite client configuration

**Returns:** `Promise<SqliteClient>`

A promise resolving to a new SQLite client instance


**Example:**

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

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

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 in-memory database
```ts
import { createSqliteClient } from "@probitas/client-sql-sqlite";

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

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

const client = await createSqliteClient({ path: ":memory:" });
const user = await client.transaction(async (tx: SqlTransaction) => {
  await tx.query("INSERT INTO users (name) VALUES (?)", ["Alice"]);
  const result = await tx.query<{ id: number }>("SELECT last_insert_rowid() as id");
  if (!result.ok) throw result.error;
  return result.rows[0];
});
await client.close();
```

Database backup
```ts
import { createSqliteClient } from "@probitas/client-sql-sqlite";

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

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

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

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`

---

### `convertSqliteError`

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

Convert a @db/sqlite error to the appropriate error class.

Note: @db/sqlite throws plain Error objects without SQLite error codes.
Error classification is based on message content analysis.

**Parameters:**

- `error`: `unknown`

**Returns:** `SqlError`

---

## Types

### `SqliteTransactionMode`

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

SQLite transaction behavior mode.

- "deferred": Locks are acquired on first read/write (default)
- "immediate": Acquires RESERVED lock immediately
- "exclusive": Acquires EXCLUSIVE lock immediately

---

### `SqliteErrorKind`

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

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