---
title: "Decoding results"
description: "rowSchema, decodeRows, decodeFirstRow and decode errors."
---

> Documentation Index
> Fetch the complete documentation index at: https://effect-orm.maple.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Decoding results

:::note
The examples on this page use ClickHouse until the Postgres versions are written. The query builder is the same in every dialect: import `@maple-dev/effect-orm/postgres` as `PG` instead, and use Postgres's column types and functions ([Postgres specifics](https://effect-orm.maple.dev/postgres/specifics/overview/)).
:::

The builder does not execute anything. You run `compiled.sql` with your own client and hand the
rows back:

```ts
import { ClickhouseClient } from "@effect/sql-clickhouse"
import { Effect } from "effect"

const program = Effect.gen(function* () {
	const client = yield* ClickhouseClient.ClickhouseClient
	const wire = yield* client.unsafe<Record<string, unknown>>(compiled.sql)
	return yield* compiled.decodeRows(wire)
})
```

Provide the `ClickhouseClient` layer at the application boundary.
[Running a query](https://effect-orm.maple.dev/postgres/queries/running-queries/) has the whole loop, including formats and numeric precision.

## The row schema is derived from the SELECT

Column types _are_ Effect schemas — `CH.uint64` is "a 64-bit integer as ClickHouse actually sends
it", not a phantom tag. So a query built from typed pieces already knows how its rows decode, and
`compile` folds those schemas into one:

```ts
const compiled = CH.compileUnsafe(
	CH.from(Events)
		.select(($) => ({ name: $.Name, calls: CH.count() }))
		.where(($) => [$.OrgId.eq(CH.param.string("orgId"))])
		.groupBy("name"),
	{ orgId: "org_123" },
)

compiled.rowSchemaSource // "derived"
await Effect.runPromise(compiled.decodeRows([{ name: "checkout", calls: "42" }]))
// [{ name: "checkout", calls: 42 }]
```

Note `calls`. ClickHouse's `FORMAT JSON` quotes 64-bit integers, a client that sets
`output_format_json_quote_64bit_integers=0` gets them bare, and a gateway
that refuses `output_format_json_quote_64bit_integers=0` quotes them whatever you asked for.
`CH.uint64` accepts both wire representations and decodes them to JavaScript numbers.

## When there is nothing to derive from

Derivation is all-or-nothing per query. One selected expression the builder cannot type — an
`untypedExpr`, an untyped `dynamicColumn`, a `defineUntypedFn` — and there is no row schema at
all:

```ts
const compiled = CH.compileUnsafe(
	CH.from(Events).select(($) => ({ name: $.Name, odd: CH.untypedExpr("anyLast(Whatever)") })),
	params,
)

compiled.rowSchemaSource // "none"
compiled.untypedColumns // ["odd"] — the aliases responsible
await Effect.runPromise(compiled.decodeRows([{ name: 42, odd: 1 }]))
// [{ name: 42, odd: 1 }] — passes straight through
```

Inventing a permissive schema for that one field would hand back something that _looks_ validated
and is not, so the query keeps its honest answer instead. `untypedColumns` names what to fix;
close the gap by typing the escape hatch — `CH.rawExpr("anyLast(Whatever)", CH.string)`,
`CH.defineFn("myFn", CH.uint64)` — or by declaring the whole schema yourself.

## Going back to the wire

`encodeRows` runs the row schema in the other direction, turning decoded rows into the shape
ClickHouse sent them in:

```ts
const rows = await Effect.runPromise(compiled.decodeRows(wire))
const backToWire = await Effect.runPromise(compiled.encodeRows(rows))
// a `DateTime.Utc` becomes '2026-05-24 14:30:00' again — not ISO-8601
```

That matters for a service that forwards warehouse rows onto a wire of its own: it can decode to
the value worth computing with and still emit the codec's canonical wire representation, instead
of choosing between them. Encoding is not byte-for-byte round-tripping: quoted numbers can become numbers,
and parsed timestamps can lose sub-millisecond precision. Use string codecs when the exact
text matters. A query with no row schema passes the rows through, the same contract
`decodeRows` has. Failures are `CompiledQueryEncodeError`, carrying the offending `rowIndex`.

## Declaring one anyway

A declared `rowSchema` wins over the derived one, and it can do something derivation cannot:
**narrow**.

```ts
const compiled = CH.compileUnsafe(query, params, {
	rowSchema: Schema.Struct({
		name: Schema.String,
		status: Schema.Literals(["ok", "error"]), // narrower than the String column
	}),
})

compiled.rowSchemaSource // "declared"
```

The declared type must still be assignable to what the builder inferred, so a schema can sharpen
the row but never contradict it.

Replacing the derived schema is also how a declared one goes stale: the SELECT gains a column, or
loses one, and the schema that no longer describes it keeps decoding — silently dropping the new
value, or failing on the first row for a field the query stopped emitting. The builder holds both
shapes at compile time, so it compares their field names and says so:

```ts
compiled.rowSchemaMismatch
// { undeclared: ["count"], unselected: ["name"] }
//   undeclared — the SELECT emits it, the declared schema does not describe it
//   unselected — the declared schema demands it, the SELECT does not emit it
```

`undefined` means there is nothing to report: the names agree, no schema was declared, or the
SELECT has an untyped expression and there is no derived shape to compare against. Only names are
compared — narrowing a column's type is the point of declaring one, not drift. Assert it is
`undefined` across your query catalog and a schema cannot fall behind its query unnoticed.

_(Backed by `docs/decoding-results.md > The row schema is derived from the SELECT`,
`> An untyped expression leaves the query undecoded`.)_

`rowSchemaSource !== "none"` is the cheap "does this decode anything at all" check for a lint
over your query catalog.

Declare a schema for anything whose shape you do not fully control.

_(Backed by `docs/decoding-results.md > An untyped expression leaves the query undecoded`.)_

## `decodeFirstRow`

For point lookups, returning `Option<Output>` rather than making you hand-roll `rows[0] ?? null`:

```ts
import { Option } from "effect"

const first = await Effect.runPromise(compiled.decodeFirstRow(wireRows))
Option.getOrNull(first) // Output | null
```

Pass raw wire rows, just as for `decodeRows`; it decodes only the first row, not every row.
An empty input yields `Option.none()`.

_(Backed by `docs/decoding-results.md > decodeFirstRow returns an Option`.)_

## Decode failures

Both decoders fail with `CompiledQueryDecodeError`, carrying the index of the offending row:

```ts
const error = await Effect.runPromise(Effect.flip(compiled.decodeRows([{ name: 42, count: 1 }])))

error._tag // "@maple-dev/effect-orm/CompiledQueryDecodeError"
error.rowIndex // 0
error.message // 'Compiled query row 0 did not match its declared output schema: at ["name"] expected string'
error.dialect // "clickhouse"
error.collection // "events", the table the query reads
error.column // "name", the column alias of the first issue
error.issues // [{ path: ["name"], kind: "InvalidType", expected: "string" }]
error.schemaError // the underlying Schema error
```

It is an Effect `Schema.TaggedError`, so `Effect.catchTag` works on it directly. Decoding
stops at the first bad row rather than accumulating.

_(Backed by `docs/decoding-results.md > A bad row fails with CompiledQueryDecodeError`.)_

### Logging a decode failure

A row is user data. The error holds only what the schema says: the path of each issue, its
kind, and the expected type from the schema AST. Its `message`, `toString()`, `JSON.stringify`
output, `util.inspect` output and `Cause.pretty` output contain no row values.

The Schema error can contain row values: a codec message such as `` `<value>` is not a
ClickHouse DateTime ``, or a filter that builds its message from its input. So it is not the
error's `cause`. It is the non-enumerable `schemaError` property, which logging does not print.

`formatDecodeError` gives a one-line summary for a log. Set `verbose: true` to append the raw
Schema error message, for local debugging only:

```ts
Effect.tapErrorTag("@maple-dev/effect-orm/CompiledQueryDecodeError", (error) =>
	Effect.logError(CH.formatDecodeError(error)),
)
// CompiledQueryDecodeError (clickhouse, events): row 0: at ["name"] expected string

CH.formatDecodeError(error, { verbose: true }) // + the Schema message, which can hold values
```

Column aliases, field names and table names are schema identifiers. They are not redacted.

_(Backed by `docs/decoding-results.md > formatDecodeError leaves row values out`.)_

## Choosing schema types

Only relevant when you declare one by hand; the column types already handle these.

- **64-bit integers** — accept both wire shapes. A `UInt64` above `2^53` cannot survive as a
  JavaScript number at all; have such columns emitted as strings (`toString(...)`) in the SELECT
  and use a string schema for the projected field.
- **`DateTime` columns** — `CH.dateTime` parses them as UTC; `CH.dateTimeString` leaves them as
  sent. See [Tables and column types](https://effect-orm.maple.dev/postgres/queries/tables-and-types/#column-types).
- **`leftJoin` columns** — nullable on the SQL side, so pair them with `Schema.NullOr`.

Source: https://effect-orm.maple.dev/postgres/queries/decoding-results/index.mdx