Skip to content
Effect Days 2026 Get your ticket

Statement

Low-level SQL statement and fragment primitives.

SqlClient uses this module to build executable, parameterized SQL from reusable fragments. A statement can be executed, streamed, run without row transformation, or compiled to SQL text and parameters for a specific dialect. The module also contains helpers for identifiers, parameters, inserts, updates, custom dialect fragments, statement compilation, and row transformation.

41 exports Added in v4.0.0 Source

Constructors

and

Added in v4.0.0 Source

Combines clauses with AND, parenthesizing multiple clauses and returning 1=1 when the list is empty.

Signature

declare const and: (clauses: ReadonlyArray<string | Fragment>) => Fragment

arrayHelper

Added in v4.0.0 Source

Constructs an ArrayHelper segment for an array of values or fragments.

Signature

declare function arrayHelper(value: readonly Array<unknown>): ArrayHelper

csv

Added in v4.0.0 Source

Creates a comma-separated SQL fragment from values, optionally adding a prefix, and returns an empty fragment when no values are provided.

Signature

declare const csv: {
  (values: readonly Array<string | Fragment>): Fragment;
  (prefix: string, values: readonly Array<string | Fragment>): Fragment;
}

custom

Added in v4.0.0 Source

Creates a constructor for custom SQL segments of a specific kind handled by the active compiler.

Signature

declare function custom<C extends Custom<any, any, any, any>>(kind: C["kind"]): (paramA: C["paramA"], paramB: C["paramB"], paramC: C["paramC"]) => C

defaultEscape

Added in v4.0.0 Source

Creates an identifier escaping function that wraps names in the given delimiter, doubles delimiter characters, and escapes dots between identifier parts.

Signature

declare function defaultEscape(c: string): (str: string) => string

fragment

Added in v4.0.0 Source

Constructs a SQL Fragment from low-level statement segments.

Signature

declare function fragment(segments: readonly Array<Segment>): Fragment

identifier

Added in v4.0.0 Source

Constructs a SQL identifier segment that will be escaped by the active compiler.

Signature

declare function identifier(value: string): Identifier

join

Added in v4.0.0 Source

Creates a helper that joins SQL clauses with a literal separator, optionally wrapping multiple clauses in parentheses and using a fallback for an empty list.

Signature

declare function join(lit: string, addParens: boolean, fallback: string): (clauses: readonly Array<string | Fragment>) => Fragment

literal

Added in v4.0.0 Source

Constructs a raw SQL literal segment. The literal text is not escaped, so use bound parameters for untrusted values.

Signature

declare function literal(value: string, params?: readonly Array<unknown>): Literal

make

Added in v4.0.0 Source

Creates a cached SQL statement constructor from a connection acquirer, compiler, tracing attributes, and optional row transformation function.

Signature

declare function make(acquirer: Acquirer, compiler: Compiler, spanAttributes: readonly Array<readonly [string, unknown]>, transformRows: <A extends object>(row: readonly Array<A>) => readonly Array<A> | undefined): Constructor

makeCompiler

Added in v4.0.0 Source

Creates a dialect-specific SQL Compiler from rendering callbacks.

Signature

declare function makeCompiler<C extends Custom<any, any, any, any> = any>(options: CompilerOptions<C>): Compiler

makeCompilerSqlite

Added in v4.0.0 Source

Creates a SQLite compiler that uses ? placeholders and quoted identifiers, optionally transforming identifier names before escaping.

Signature

declare function makeCompilerSqlite(transform?: (_: string) => string): Compiler

or

Added in v4.0.0 Source

Combines clauses with OR, parenthesizing multiple clauses and returning 1=1 when the list is empty.

Signature

declare const or: (clauses: ReadonlyArray<string | Fragment>) => Fragment

parameter

Added in v4.0.0 Source

Constructs a bound parameter segment for a statement value.

Signature

declare function parameter(value: unknown): Parameter

recordInsertHelper

Added in v4.0.0 Source

Constructs a RecordInsertHelper from one or more row objects.

Signature

declare function recordInsertHelper(value: readonly Array<Record<string, unknown>>): RecordInsertHelper

recordUpdateHelper

Added in v4.0.0 Source

Constructs a RecordUpdateHelper for multi-row update compilation using the provided alias.

Signature

declare function recordUpdateHelper(value: readonly Array<Record<string, unknown>>, alias: string): RecordUpdateHelper

recordUpdateHelperSingle

Added in v4.0.0 Source

Constructs a RecordUpdateHelperSingle from a record and a list of columns to omit from the update.

Signature

declare function recordUpdateHelperSingle(value: Record<string, unknown>, omit: readonly Array<string>): RecordUpdateHelperSingle

statement

Added in v4.0.0 Source

Builds a Statement from template strings and arguments, preserving fragments and helper segments while converting ordinary interpolated values into bound parameters.

Signature

declare function statement<A = Row>(acquirer: Acquirer, compiler: Compiler, strings: TemplateStringsArray, args: Array<any>, spanAttributes: readonly Array<readonly [string, unknown]>, transformRows: <A extends object>(row: readonly Array<A>) => readonly Array<A> | undefined): Statement<A>

Converting

primitiveKind

Added in v4.0.0 Source

Classifies a JavaScript value as a SQL primitive kind, treating undefined as null and defaulting unrecognized objects to string.

Signature

declare function primitiveKind(value: unknown): PrimitiveKind

Guards

isCustom

Added in v4.0.0 Source

Creates a type guard for custom SQL segments with the specified custom kind.

Signature

declare function isCustom<A extends Custom<any, any, any, any>>(kind: A["kind"]): (u: unknown) => u is A

isFragment

Added in v4.0.0 Source

Returns true when a value is a SQL Fragment.

Signature

declare function isFragment(u: unknown): u is Fragment

Models

ArrayHelper interface

Added in v4.0.0 Source

Helper segment for compiling an array of values, commonly used to produce placeholder lists for IN clauses.

Signature

interface ArrayHelper {
  readonly _tag: "ArrayHelper";
  readonly value: readonly Array<unknown>;
}

Compiler interface

Added in v4.0.0 Source

Dialect-specific compiler that converts a SQL Fragment into SQL text and bind parameters, with a no-transform variant.

Signature

interface Compiler {
  readonly compile: (statement: Fragment, withoutTransform: boolean) => readonly [string, readonly Array<unknown>];
  readonly dialect: Dialect;
  readonly withoutTransform: Compiler;
}

CompilerOptions type

Added in v4.0.0 Source

Callbacks used by makeCompiler to render dialect placeholders, identifiers, insert helpers, update helpers, and custom SQL segments.

Signature

type CompilerOptions<C extends Custom<any, any, any, any> = any> = {
  readonly dialect: Dialect;
  readonly onCustom: (type: C, placeholder: (u: unknown) => string, withoutTransform: boolean) => readonly [sql: string, params: ReadonlyArray<unknown>];
  readonly onIdentifier: (value: string, withoutTransform: boolean) => string;
  readonly onInsert?: (columns: ReadonlyArray<string>, placeholders: string, values: ReadonlyArray<ReadonlyArray<unknown>>, returning: readonly [sql: string, params: ReadonlyArray<unknown>] | undefined) => readonly [sql: string, binds: ReadonlyArray<unknown>];
  readonly onRecordUpdate: (placeholders: string, alias: string, columns: string, values: ReadonlyArray<ReadonlyArray<unknown>>, returning: readonly [sql: string, params: ReadonlyArray<unknown>] | undefined) => readonly [sql: string, params: ReadonlyArray<unknown>];
  readonly onRecordUpdateSingle?: (columns: ReadonlyArray<string>, values: ReadonlyArray<unknown>, returning: readonly [sql: string, params: ReadonlyArray<unknown>] | undefined) => readonly [sql: string, params: ReadonlyArray<unknown>];
  readonly placeholder: (index: number, value: unknown) => string;
}

Constructor interface

Added in v4.0.0 Source

SQL tagged-template constructor and helper API for building parameterized statements, escaped identifiers, fragments, record helpers, and dialect-specific branches. Raw helpers such as unsafe and literal insert SQL text directly.

Signature

interface Constructor {
  <A extends object = Row>(strings: TemplateStringsArray, ...args: Array<any>): Statement<A>;
  (value: string): Identifier;
  readonly and: (clauses: readonly Array<string | Fragment>) => Fragment;
  readonly csv: {
    (values: readonly Array<string | Fragment>): Fragment;
    (prefix: string, values: readonly Array<string | Fragment>): Fragment;
  };
  readonly in: {
    (value: readonly Array<unknown>): ArrayHelper;
    (column: string, value: readonly Array<unknown>): Fragment;
  };
  readonly insert: {
    (value: readonly Array<Record<string, unknown>>): RecordInsertHelper;
    (value: Record<string, unknown>): RecordInsertHelper;
  };
  readonly join: (literal: string, addParens?: boolean, fallback?: string) => (clauses: readonly Array<string | Fragment>) => Fragment;
  readonly literal: (sql: string) => Fragment;
  readonly onDialect: <A, B, C, D, E>(options: {
    readonly clickhouse: () => E;
    readonly mssql: () => D;
    readonly mysql: () => C;
    readonly pg: () => B;
    readonly sqlite: () => A;
  }) => A | B | C | D | E;
  readonly onDialectOrElse: <A, B = never, C = never, D = never, E = never, F = never>(options: {
    readonly clickhouse?: () => F;
    readonly mssql?: () => E;
    readonly mysql?: () => D;
    readonly orElse: () => A;
    readonly pg?: () => C;
    readonly sqlite?: () => B;
  }) => A | B | C | D | E | F;
  readonly or: (clauses: readonly Array<string | Fragment>) => Fragment;
  readonly unsafe: <A extends object>(sql: string, params?: readonly Array<unknown>) => Statement<A>;
  readonly update: <A extends Record<string, unknown>>(value: A, omit?: readonly Array<keyof A>) => RecordUpdateHelperSingle;
  readonly updateValues: (value: readonly Array<Record<string, unknown>>, alias: string) => RecordUpdateHelper;
}

Custom interface

Added in v4.0.0 Source

Custom SQL segment identified by kind and interpreted by the compiler's onCustom callback.

Signature

interface Custom<T extends string = string, A = void, B = void, C = void> {
  readonly _tag: "Custom";
  readonly kind: T;
  readonly paramA: A;
  readonly paramB: B;
  readonly paramC: C;
}

Dialect type

Added in v4.0.0 Source

Supported SQL dialect identifiers used by statement compilers.

Signature

type Dialect = "sqlite" | "pg" | "mysql" | "mssql" | "clickhouse"

Fragment interface

Added in v4.0.0 Source

Composable SQL fragment represented as low-level segments that can be interpolated into statements.

Signature

interface Fragment {
  readonly "~effect/sql/Fragment": "~effect/sql/Fragment";
  readonly segments: readonly Array<Segment>;
}

Helper type

Added in v4.0.0 Source

Union of helper segment types accepted by the SQL statement constructor.

Signature

type Helper = ArrayHelper | RecordInsertHelper | RecordUpdateHelper | RecordUpdateHelperSingle | Identifier | Custom

Identifier interface

Added in v4.0.0 Source

SQL identifier segment whose value is escaped by the active dialect compiler.

Signature

interface Identifier {
  readonly _tag: "Identifier";
  readonly value: string;
}

Literal interface

Added in v4.0.0 Source

Raw SQL literal segment. The literal text is inserted directly into the compiled SQL, while optional params are appended as bind parameters.

Signature

interface Literal {
  readonly _tag: "Literal";
  readonly params?: readonly Array<unknown>;
  readonly value: string;
}

Parameter interface

Added in v4.0.0 Source

Bound parameter segment whose value is emitted as a dialect-specific placeholder and bind value.

Signature

interface Parameter {
  readonly _tag: "Parameter";
  readonly value: unknown;
}

PrimitiveKind type

Added in v4.0.0 Source

Names the primitive value categories recognized by SQL statement helpers and primitiveKind.

Signature

type PrimitiveKind = "string" | "number" | "bigint" | "boolean" | "Date" | "null" | "Int8Array" | "Uint8Array"

RecordInsertHelper interface

Added in v4.0.0 Source

Helper segment for compiling one or more record objects into an INSERT column/value clause, with optional returning output.

Signature

interface RecordInsertHelper {
  readonly _tag: "RecordInsertHelper";
  readonly returning: (sql: string | Identifier | Fragment) => RecordInsertHelper;
  readonly value: readonly Array<Record<string, unknown>>;
}

RecordUpdateHelper interface

Added in v4.0.0 Source

Helper segment for compiling multi-row update values with a table alias and optional returning output.

Signature

interface RecordUpdateHelper {
  readonly _tag: "RecordUpdateHelper";
  readonly alias: string;
  readonly returning: (sql: string | Identifier | Fragment) => RecordUpdateHelper;
  readonly value: readonly Array<Record<string, unknown>>;
}

RecordUpdateHelperSingle interface

Added in v4.0.0 Source

Helper segment for compiling a single record into update assignments, omitting selected columns and optionally returning output.

Signature

interface RecordUpdateHelperSingle {
  readonly _tag: "RecordUpdateHelperSingle";
  readonly omit: readonly Array<string>;
  readonly returning: (sql: string | Identifier | Fragment) => RecordUpdateHelperSingle;
  readonly value: Record<string, unknown>;
}

Segment type

Added in v4.0.0 Source

Union of low-level segment types that make up a SQL Fragment.

Signature

type Segment = Literal | Identifier | Parameter | ArrayHelper | RecordInsertHelper | RecordUpdateHelper | RecordUpdateHelperSingle | Custom<any, any, any, any>

Statement interface

Added in v4.0.0 Source

Executable SQL statement that is also a Fragment and Effect, with helpers for raw execution, streaming, value rows, unprepared execution, no-transform execution, and compilation.

Signature

interface Statement<A> extends Fragment, Effect<ReadonlyArray<A>, SqlError> {
  readonly compile: (withoutTransform?: boolean) => readonly [string, readonly Array<unknown>];
  readonly raw: Effect<unknown, SqlError>;
  readonly stream: Stream<A, SqlError>;
  readonly unprepared: Effect<readonly Array<A>, SqlError>;
  readonly values: Effect<readonly Array<readonly Array<unknown>>, SqlError>;
  readonly valuesUnprepared: Effect<readonly Array<readonly Array<unknown>>, SqlError>;
  readonly withoutTransform: Effect<readonly Array<A>, SqlError>;
}

Transformer type

Added in v4.0.0 Source

Hook that can rewrite or wrap a Statement before execution, using the current SQL constructor, fiber, and tracing span.

Signature

type Transformer = (self: Statement<unknown>, sql: Constructor, fiber: Fiber.Fiber<unknown, unknown>, span: Tracer.Span) => Effect.Effect<Statement<unknown>>

Services

CurrentTransformer

Added in v4.0.0 Source

Context reference for an optional current SQL statement transformer applied before statement execution.

Signature

declare const CurrentTransformer: Reference<Transformer | undefined>

Transforming

defaultTransforms

Added in v4.0.0 Source

Builds value, object, and row-array transformers that rename object keys with the supplied function and optionally recurse into nested object arrays.

Signature

declare function defaultTransforms(transformer: (str: string) => string, nested: boolean): {
  array: <A extends object>(rows: readonly Array<A>) => readonly Array<A>;
  readonly object: (obj: Record<string, any>) => any;
  readonly value: (value: any) => any;
}