--- title: "Tables and column types" description: "table(), column options, external tables, and the column-type constructors." --- > 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. # Tables and column types :::note The examples on this page use ClickHouse and Postgres until the MySQL versions are written. The query builder is the same in every dialect: import `@maple-dev/effect-orm/mysql` as `MySQL` instead, and use MySQL's column types and functions ([MySQL specifics](https://effect-orm.maple.dev/mysql/specifics/overview/)). ::: 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](https://effect-orm.maple.dev/mysql/database/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: ```ts 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`](https://effect-orm.maple.dev/mysql/database/migrations/#generating-migrations) 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: ```ts 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](#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](https://effect-orm.maple.dev/postgres/specifics/overview/) for its types and enums, and [migrations](https://effect-orm.maple.dev/mysql/database/migrations/#postgres) 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. ```ts 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: | Option (ClickHouse) | DDL | On insert | | ------------------------- | ---------------------- | ---------------------------- | | `default: 200` | `DEFAULT 200` | optional | | `defaultExpr: "now()"` | `DEFAULT now()` | optional | | `materialized: "…"` | `MATERIALIZED …` | not writable | | `alias: "…"` | `ALIAS …` | not writable | | `codec`, `comment` | `CODEC(…)`, `COMMENT` | unchanged | | `name: "org_id"` | the column's name | unchanged | 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. ```ts 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 // { OrgId: string; Route: string; Timestamp?: …; Status?: number } — Hour cannot be written ``` `tenantColumn` names the column that carries tenancy; see [Tenant scoping](https://effect-orm.maple.dev/mysql/database/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](https://effect-orm.maple.dev/mysql/queries/inserts/#which-columns-have-defaults) 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. ```ts 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](https://effect-orm.maple.dev/mysql/database/migrations/#postgres). ## 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`: ```ts 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](https://effect-orm.maple.dev/mysql/queries/decoding-results/). Each dialect entry has its own; the ClickHouse ones are below, the Postgres ones in [Postgres](https://effect-orm.maple.dev/postgres/specifics/overview/#column-types). The constructors are values, not calls (except the parameterised ones): | Constructor | ClickHouse type | Decodes to | From the wire | | --------------------- | ----------------- | ------------------- | ------------------------- | | `CH.string` | `String` | `string` | `string` | | `CH.uint8` | `UInt8` | `number` | number or quoted number | | `CH.uint16` | `UInt16` | `number` | number or quoted number | | `CH.uint32` | `UInt32` | `number` | number or quoted number | | `CH.uint64` | `UInt64` | `number` | number or quoted number | | `CH.int64` | `Int64` | `number` | number or quoted number | | `CH.int32` | `Int32` | `number` | number or quoted number | | `CH.float64` | `Float64` | `number` | number or quoted number | | `CH.bool` | `Bool` | `boolean` | `true`/`false` or `1`/`0` | | `CH.dateTime` | `DateTime` | `DateTime.Utc` | `YYYY-MM-DD hh:mm:ss` | | `CH.dateTime64` | `DateTime64` | `DateTime.Utc` | with a fractional part | | `CH.dateTimeString` | `DateTime` | `string` | unparsed, as sent | | `CH.dateTime64String` | `DateTime64` | `string` | unparsed, as sent | | `CH.map(k, v)` | `Map(K, V)` | `Record` | object | | `CH.array(e)` | `Array(E)` | `ReadonlyArray` | array | | `CH.nullable(t)` | `Nullable(T)` | `T \| null` | value or `null` | | `CH.untyped(sql)` | whatever you name | `unknown` | unvalidated | 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](https://effect-orm.maple.dev/mysql/start/recipes/#preserve-large-integer-ids). **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](https://clickhouse.com/docs/reference/data-types/datetime). **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` 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: ```ts import type { InferTS } from "@maple-dev/effect-orm/clickhouse" type Ms = InferTS // number ``` `InferEncoded` is its counterpart — the wire type the schema decodes _from_. For a whole table, `SelectRowOf` is the decoded row and `InsertRowOf` 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: | Variant | Columns | Decodes from | | ------------ | ---------------------------------------------------- | ------------------ | | `select` | every column | the database wire | | `insert` | no computed column; defaulted and nullable optional | the database wire | | `update` | no computed column; every column optional | the database wire | | `json` | as `select` | JSON | | `jsonCreate` | as `insert` | JSON | | `jsonUpdate` | as `update` | JSON | 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. ```ts const { select, jsonCreate } = CH.tableSchemas(Requests) type Request = typeof select.Type // SelectRowOf 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: ```ts 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` for a `Map(String, String)`, `Expr` for a `Map(String, UInt64)`. For the other map operations — `mapContains`, `mapKeys`, `mapValues`, `mapGet`, `mapLiteral` — see the [API reference](https://effect-orm.maple.dev/reference/reference/#map). _(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: ```ts CH.from(Events, "e") // FROM events AS e, columns emit as e.Name ``` See [Joins and subqueries](https://effect-orm.maple.dev/mysql/queries/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`. ```ts title="branded-columns.ts" 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 // { 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` 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](https://effect-orm.maple.dev/mysql/queries/extending/#a-column-type-of-your-own). Source: https://effect-orm.maple.dev/mysql/queries/tables-and-types/index.mdx