Tables and column types

A table is declared once, with the dialect entry’s table. The same value serves the query builder (its name and columns), inserts (which columns may be left out), and migrations (its DDL). There is no second, lighter way to declare one: a table grows options as it needs them.

table(name, definition)

The smallest ClickHouse table is its columns, an engine, and the sorting key the MergeTree family requires:

import * as CH from "@maple-dev/effect-orm/clickhouse"

const Events = CH.table("events", {
	columns: {
		OrgId: CH.string,
		Name: CH.string,
		Timestamp: CH.dateTime,
		DurationMs: CH.uint64,
		Attributes: CH.map(CH.string, CH.string),
	},
	engine: CH.engine.mergeTree(),
	orderBy: ["OrgId", "Timestamp"],
})

The columns record is what every accessor, output type, and join is inferred from. name is emitted as the FROM target. orderBy: [] writes ORDER BY tuple().

The value is a plain Table with its DDL beside it on ddl. It is never checked against a live server, so a column that does not exist in ClickHouse will typecheck happily and fail at query time. effect-orm generate keeps the two in step when this definition is your schema; otherwise treat it as a contract you keep in sync with your migrations by hand.

A definition that cannot become DDL (a MergeTree table without orderBy, a name that is not a plain identifier, two of default/materialized on one column) records the problem on its problems instead of throwing; S.entitiesOf (and so generate) fails with a SchemaDefinitionError listing every problem.

The Postgres table has the same shape, with a primary key in place of an engine:

import * as PG from "@maple-dev/effect-orm/postgres"

const Users = PG.table("users", {
	columns: {
		id: PG.column(PG.int8, { identity: "always" }),
		email: PG.text,
		name: PG.nullable(PG.text),
		created_at: PG.column(PG.timestamptz, { defaultExpr: "now()" }),
	},
	primaryKey: ["id"],
})

A column’s key is its name in SQL unless the column or the table gives it another; see Column names.

A Postgres column is NOT NULL unless its type is PG.nullable(...). An enum column’s type is a PG.enumType("status", ["open", "closed"]), which generate creates as a type of its own. See Postgres for its types and enums, and migrations for its indexes, unique constraints and foreign keys.

Column names

A table’s first argument is its name in the database, and the variable that holds it is its name in TypeScript, so a Prisma @@map("users") model is const User = PG.table("users", …). A column’s key is its name in SQL unless the column or the table gives it another:

• name on column sets one column’s database name, as Prisma’s @map and drizzle’s text("org_id") do. orgId: PG.column(PG.text, { name: "org_id" }) (or CH.column(CH.string, { name: "org_id" }), SQLite.column(SQLite.text, { name: "org_id" })) is stored as org_id, and read, written, filtered and indexed as orgId.
• casing on the table (CH.table, PG.table or SQLite.table) names every column that gives no name. "snake_case" stores orgId as org_id and HTTPStatus as http_status, by drizzle-orm’s rule, so a schema drizzle-kit made with casing: "snake_case" keeps its names. "camelCase" goes the other way.

const Members = PG.table("members", {
	casing: "snake_case",
	columns: {
		orgId: PG.text, // "org_id"
		displayName: PG.column(PG.text, { name: "label" }), // "label": `name` wins
		createdAt: PG.column(PG.timestamptz, { defaultExpr: "now()" }), // "created_at"
	},
	primaryKey: ["orgId", "displayName"],
	tenantColumn: "orgId",
})

Every definition option (keys, indexes, foreign keys, tenantColumn) and every query, insert, update, upsert target and returning takes the key; the SQL, the DDL and the migrations generate writes use the database name. Rows decode under the key: a select writes "org_id" AS "orgId", so decoding reads the alias it wrote. The names are fixed where the table is defined, so there is no global casing; a project that wants one wraps table in its own function. Two keys that end up with one database name (orgId and OrgId under "snake_case") are a definition problem; SQLite compares names without case, so there Org_Id and org_id clash too. A ClickHouse materialized view fills its target by database name, so its target table cannot rename a column: the view records a problem if it does.

(Backed by docs/tables-and-types.md > Column names.)

Keys, defaults, and tenancy

column(type, options) wraps a column type with what a bare type cannot say. The options also decide the insert row type, so it is derived from the DDL rather than declared twice:

Postgres columns take default, defaultExpr, and identity ("always" or "by default"); each makes the column optional on insert. They also take unique (true, a constraint name, or { name, nullsNotDistinct }), a one-column UNIQUE constraint, which leaves the insert type alone. Postgres generated columns are not modeled yet.

const Requests = CH.table("requests", {
	columns: {
		OrgId: CH.string,
		Timestamp: CH.column(CH.dateTime, { defaultExpr: "now()" }),
		Route: CH.string,
		Status: CH.column(CH.uint16, { default: 200 }),
		Hour: CH.column(CH.dateTime, { materialized: "toStartOfHour(Timestamp)" }),
	},
	engine: CH.engine.mergeTree(),
	orderBy: ["OrgId", "Route", "Timestamp"],
	partitionBy: "toDate(Timestamp)",
	ttl: CH.ttlAfterDays("toDate(Timestamp)", 30),
	tenantColumn: "OrgId",
})

type NewRequest = CH.InsertRowOf<typeof Requests>
// { OrgId: string; Route: string; Timestamp?: …; Status?: number } — Hour cannot be written

tenantColumn names the column that carries tenancy; see Tenant scoping. The other table options are primaryKey, settings, and comment. Keys, partitions, and TTLs are SQL strings or callbacks over the columns (($) => [$.OrgId, CH.toStartOfHour($.Timestamp)]). Inserting rows covers how the insert type is used.

Indexes and materialized views

A data-skipping index is CH.index(name, expr, type, granularity?). A materialized view is CH.materializedView(name, { to, as }), whose body is a query: an output column the target table lacks, or of another type, is a type error.

const RequestsIndexed = CH.table("requests", {
	columns: { OrgId: CH.string, Timestamp: CH.dateTime, Route: CH.string, Status: CH.uint16 },
	engine: CH.engine.mergeTree(),
	orderBy: ["OrgId", "Timestamp"],
	indexes: [CH.index("idx_status", ($) => $.Status, "set(100)")],
})

const RoutesHourly = CH.table("routes_hourly", {
	columns: { OrgId: CH.string, Hour: CH.dateTime, Route: CH.string, Requests: CH.uint64 },
	engine: CH.engine.summingMergeTree(),
	orderBy: ["OrgId", "Hour", "Route"],
})

const RoutesHourlyMv = CH.materializedView("routes_hourly_mv", {
	to: RoutesHourly,
	as: CH.from(RequestsIndexed)
		.select(($) => ({ OrgId: $.OrgId, Hour: CH.toStartOfHour($.Timestamp), Route: $.Route, Requests: CH.count() }))
		.groupBy("OrgId", "Hour", "Route"),
})

Postgres indexes are PG.index and PG.uniqueIndex, over column names or expressions, with where for a partial index; unique constraints are PG.unique in uniqueConstraints, and foreign keys are PG.foreignKey. All are covered in Schema and migrations.

External tables

Not every FROM target is a table this schema owns. A system table, a table function, a view, a CTE, or a table another tool migrates is declared with external: true:

const One = CH.table("system.one", { external: true, columns: {} })
const Numbers = CH.table("numbers(10)", { external: true, columns: { number: CH.uint64 } })
const Stats = PG.table("pg_stat_user_tables", { external: true, columns: { relname: PG.text } })

CH.from(Numbers).select("number")
// SELECT __ch_source.number AS number FROM numbers(10) AS __ch_source

An external table carries no DDL, so generate never creates, alters, or drops it. Its name is written verbatim as the FROM target (one that is not a plain identifier, like numbers(10), gets an alias to qualify its columns), it may have no columns (for a FROM that only anchors constants), and it takes only columns and tenantColumn. Column options still drive insert typing: CH.column(CH.uint16, { default: 200 }) is optional on insert here too.

Column types

A column type is an Effect Schema plus the database type name it stands for. That schema is the single source of truth: the TypeScript column type is read off it, and compile folds the selected columns’ schemas into the row schema decodeRows validates against — see Decoding results. Each dialect entry has its own; the ClickHouse ones are below, the Postgres ones in Postgres.

The constructors are values, not calls (except the parameterised ones):

Three wrappers change only the DDL, never how a column reads back: CH.lowCardinality(t) (LowCardinality(T); inside nullable it renders LowCardinality(Nullable(T))), CH.simpleAggregateFunction("sum", t) (SimpleAggregateFunction(sum, T)), and CH.precision(CH.dateTime64String, 9) (DateTime64(9)).

Column types, functions, and the query builder share the one CH namespace, so a schema module and a query module import the same thing.

(Backed by docs/tables-and-types.md > Column types come from the dialect entry.)

Two of those deserve a note.

64-bit integers. ClickHouse’s FORMAT JSON quotes them, 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 regardless. Every integer type accepts both and decodes to a number — which also means a UInt64 above 2^53 cannot survive: select CH.toString($.Id) while leaving the actual table column declared CH.uint64. The resulting expression has a string codec; see the ID recipe.

DateTimes. The parsed codecs interpret zone-less strings such as 2026-05-24 14:30:00 as UTC. ClickHouse does not guarantee that all timestamp strings are UTC: text output follows the column/server timezone. Use UTC columns or normalize the selected expression to UTC before using CH.dateTime / CH.dateTime64. For an unchanged wire string, use CH.dateTimeString / CH.dateTime64String. See ClickHouse DateTime timezones.

Numeric validation. Built-in numeric codecs accept finite numbers and quoted finite numbers. They do not enforce each ClickHouse integer’s sign, bit width, safe-integer range, or integrality. Use schema checks through CH.custom when your application needs those constraints; param.int separately requires a safe integer. A successful decode does not prove an unsafe large number retained precision.

Arithmetic and aggregation can overflow even when their inputs are finite. ClickHouse JSON represents infinity and NaN as null, so sum, sumIf, toFloat64OrZero, +, -, and * decode that null as NaN when the result is not SQL Nullable. toFloat64OrZero returns zero for an invalid parse, but strings such as Inf, NaN, and 1e400 successfully parse to nonfinite numbers.

When built-in DateTime and DateTime64 codecs are combined by conditionals, arrays, or unions, result encoding prefers DateTime64 and retains milliseconds. This also works through nullable and array wrappers. Custom codecs retain their declared encoding behavior; provide an explicit result schema when different custom transforms need a particular encoding.

InferTS

InferTS<ColType> maps a column type to its TypeScript type. You rarely need it directly — select already infers output rows — but it is exported for writing your own helpers:

import type { InferTS } from "@maple-dev/effect-orm/clickhouse"

type Ms = InferTS<typeof CH.uint64> // number

InferEncoded<ColType> is its counterpart — the wire type the schema decodes from. For a whole table, SelectRowOf<typeof Events> is the decoded row and InsertRowOf<typeof Events> the row an insert takes.

Related utilities: ColumnDefs (the shape of a columns record), OutputToColumnDefs (converts a query’s output row back into column defs, used by fromQuery), and NullableColumnDefs (what leftJoin applies to the joined side).

Row Schemas

tableSchemas(table) derives the Effect Schemas of a table’s rows from its columns, so an HttpApi endpoint or a repository uses the same definition as the queries. The variant names are the ones Model.Class uses:

The JSON variants are Schema.toCodecJson of each row’s type, so a DateTime is an ISO string and a branded column keeps its brand. An optional key also takes undefined, as in an insert row. tableSchemas is on every entry; on a SQLite table select and insert read SQLite’s wire forms (0/1 booleans, timestamp and JSON text), and the JSON variants read plain JSON.

const { select, jsonCreate } = CH.tableSchemas(Requests)

type Request = typeof select.Type // SelectRowOf<typeof Requests>
const body = Schema.decodeUnknownSync(jsonCreate)({ OrgId: "org_1", Route: "/" })
CH.insertInto(Requests).values(body) // a decoded body is an insert row

(Backed by docs/tables-and-types.md > Row Schemas.)

Map columns

Map columns get a .get(key) accessor that compiles to ClickHouse’s bracket syntax:

const query = CH.from(Events)
	.select(($) => ({ method: $.Attributes.get("http.method") }))
	.where(($) => [$.OrgId.eq("org_123")])

// SELECT events.Attributes['http.method'] AS method FROM events WHERE events.OrgId = 'org_123'

.get() yields the map’s value type — Expr<string> for a Map(String, String), Expr<number> for a Map(String, UInt64). For the other map operations — mapContains, mapKeys, mapValues, mapGet, mapLiteral — see the API reference.

(Backed by docs/tables-and-types.md > Reading a Map column.)

Aliasing a table

from() takes an optional alias, which qualifies every column reference. You need this as soon as a join introduces ambiguity:

CH.from(Events, "e") // FROM events AS e, columns emit as e.Name

See Joins and subqueries.

CH.dateTime64 preserves milliseconds when encoding Date/DateTime.Utc comparison bounds and decoded rows. JavaScript timestamps have millisecond precision; use CH.dateTime64String when forwarding microseconds or nanoseconds unchanged. CH.dateTime encodes whole seconds.

Types not in the built-in list

CH.custom(sqlType, schema) models types such as UUID, LowCardinality, enums, or decimals using their JSON representation. Match your existing database schema rather than redesigning the physical table to fit this library’s constructors. For a LowCardinality(String) column, for example, CH.custom("LowCardinality(String)", Schema.String) decodes the ordinary string it emits.

Branded columns

brand(type, schema) narrows a column type with an Effect schema: a branded id, a literal union, a refined number. It keeps the base type’s SQL type and wire codec, so PG.brand(PG.int8, Cents) still reads the string node-postgres sends, and wraps like any type: nullable(brand(...)), array(brand(...)). Both entries have it: CH.brand, PG.brand.

import { Schema } from "effect"
import * as PG from "@maple-dev/effect-orm/postgres"

const OrgId = Schema.String.check(Schema.isMinLength(1)).pipe(Schema.brand("OrgId"))
const UserId = Schema.String.pipe(Schema.brand("UserId"))

// Declare the column type once; tables and params both use it.
const orgId = PG.brand(PG.text, OrgId)

const Dashboards = PG.table("dashboards", {
	columns: {
		org_id: orgId,
		id: PG.text,
		owner: PG.nullable(PG.brand(PG.text, UserId)),
	},
	primaryKey: ["org_id", "id"],
})

export type Dashboard = PG.SelectRowOf<typeof Dashboards>
// { readonly org_id: OrgId; readonly id: string; readonly owner: UserId | null }

export const byOrg = PG.from(Dashboards)
	.select("id", "owner")
	.where(($) => [$.org_id.eq(PG.param.of(orgId, "orgId"))])

export const compiled = PG.compileUnsafe(byOrg, { orgId: OrgId.make("org_1") })

declare const userId: typeof UserId.Type
// @ts-expect-error a UserId is not an OrgId
PG.from(Dashboards).select("id").where(($) => [$.org_id.eq(userId)])

A brand is strict everywhere it is written or compared:

• Rows decode to the brand, and SelectRowOf<typeof table> names the whole row.
• Comparisons (eq, in_, between, joins) take a value of the brand, a column of the same brand, or a param declared with the type: PG.param.of(orgId, "orgId"), whose value compile then requires to be an OrgId. A plain string, another brand, param.string, or an unbranded column is a type error.
• Inserts and updates take the brand, a param of it, or an expression of it.
• Checks run both ways. A row that fails the schema’s checks is a decode error; a literal or param value that fails them is a QueryBuilderError from compile.

A literal union (PG.brand(PG.text, Schema.Literals(["open", "closed"]))) is not a brand: it compares against its members, or a param of the primitive (param.string), which the database checks. A Postgres enum column, PG.enumType(...), behaves the same way.

CH.custom("String", OrgId) brands the same way, but replaces the wire codec with OrgId itself; prefer brand over a built-in type whose codec does work (numbers, timestamps).

CH.untyped(sqlType) accepts an unknown field without validating it. Unlike CH.untypedExpr, it supplies a Schema.Unknown codec, so other selected fields can still be validated. The unknown field itself has no guarantee. Prefer a real custom codec where you know the wire representation.

CH.aggregateState(fn, ...argumentTypes) describes an opaque aggregate-state value passed from an inner query into a matching merge function. It is not a decoder for inspecting state bytes. See Extending the DSL.