cockroachdb/column-types

90 notes in this subject, read out of this brain and free to use. This is page 2 of 2.

CockroachDB identity columns generatedAlwaysAsIdentity

Identity columns automatically generate unique integer values using sequences. `GENERATED ALWAYS AS IDENTITY` means the database always generates a value and manual insertion/updates are not allowed unless OVERRIDING SYSTEM VALUE is used. In Drizzle, use `.generatedAlwaysAsIdentity({ startWith: 1000 })` to define this type. Requires drizzle-orm@0.32.0 or higher and drizzle-kit@0.23.0 or higher.

CockroachDB identity columns generatedByDefaultAsIdentity

Identity columns automatically generate unique integer values using sequences. `GENERATED BY DEFAULT AS IDENTITY` means the database generates a value by default, but manual values can also be inserted or updated. If a manual value is provided, it will be used instead of the system-generated value.

Column NOT NULL constraint

The NOT NULL constraint dictates that a column may not contain a NULL value. In Drizzle, use `.notNull()` method on column builders. This generates SQL `NOT NULL`.

CockroachDB column name aliasing and casing

Column names are generated from TypeScript keys by default. You can use database column name aliases if you want, and use the `casing` parameter to define a mapping strategy for Drizzle to control how TypeScript keys map to database column names.

CockroachDB bigint mode number example

Example showing bigint with mode: 'number' inferred as JavaScript number type: `bigint: bigint({ mode: 'number' })`

CockroachDB bigint default values example

Example showing bigint with default values: `bigint1: bigint().default(10), bigint2: bigint().default(sql`'10'::bigint`)`

CockroachDB string with length example

Example showing string column types: `stringColumn: string()` for text type and `stringColumn1: string({ length: 256 })` for varchar(256) type.

CockroachDB string enum inference example

Example showing string enum type inference: `stringColumn: string({ enum: ["value1", "value2"] })` infers type as 'value1' | 'value2' | null.

CockroachDB decimal column examples

Example showing decimal column configurations: `decimal1: decimal()`, `decimal2: decimal({ precision: 100 })`, `decimal3: decimal({ precision: 100, scale: 20 })`, `decimalNum: decimal({ mode: 'number' })`, `decimalBig: decimal({ mode: 'bigint' })`.

CockroachDB jsonb type inference example

Example showing jsonb type inference: `jsonb: jsonb().$type<{ foo: string }>()` infers as object with foo string property, `jsonb: jsonb().$type<string[]>()` infers as string array, and `jsonb: jsonb().$type<string[]>().default({})` does not compile.

CockroachDB uuid with defaultRandom example

Example showing uuid column with defaultRandom(): `uuid1: uuid()`, `uuid2: uuid().defaultRandom()`, `uuid3: uuid().default('a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11')`.

CockroachDB time column examples

Example showing time column configurations: `time1: time()`, `time2: time({ withTimezone: true })`, `time3: time({ precision: 6 })`, `time4: time({ precision: 6, withTimezone: true })`.

CockroachDB timestamp examples

Example showing timestamp column configurations: `timestamp1: timestamp()`, `timestamp2: timestamp({ precision: 6, withTimezone: true })`, `timestamp3: timestamp().defaultNow()`, `timestamp4: timestamp().default(sql`now()`)`. Also shows mode inference: `timestamp({ mode: 'date' })` or `timestamp({ mode: 'string' })`.

CockroachDB interval examples

Example showing interval column configurations: `interval1: interval()`, `interval2: interval({ fields: 'day' })`, `interval3: interval({ fields: 'month', precision: 6 })`.

CockroachDB enum definition example

Example showing enum type definition: `const moodEnum = cockroachEnum('mood', ['sad', 'ok', 'happy'])` and usage in table: `mood: moodEnum()`. Generates SQL `CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy')`.

CockroachDB custom type with $type() example

Example showing custom type definitions: `id: int().$type<UserId>().primaryKey()` for branded number type and `jsonField: jsonb().$type<Data>()` for custom object type.

CockroachDB generatedAlwaysAsIdentity example

Example showing identity column definition: `id: integer().primaryKey().generatedAlwaysAsIdentity({ startWith: 1000 })`. Requires drizzle-orm@0.32.0 or higher and drizzle-kit@0.23.0 or higher.

Column $defaultFn() cuid2 example

Example showing runtime default generation with cuid2: `id: text().$defaultFn(() => createId())` where createId is imported from '@paralleldrive/cuid2'.

Column $onUpdateFn() timestamp example

Example showing runtime update value for timestamp: `updatedAt: timestamp({ mode: 'date', precision: 3 }).$onUpdate(() => new Date())`.

Column $onUpdateFn() counter example

Example showing runtime update value for counter: `updateCounter: integer().default(sql`1`).$onUpdateFn((): SQL => sql`${table.update_counter} + 1`)`.

CockroachDB column types documentation version requirement

The CockroachDB column types features documented are available on drizzle versions 1.0.0-beta.2 and higher.

PostgreSQL vector type with dimensions parameter

The vector type in PostgreSQL schemas uses a dimensions parameter to specify the number of dimensions, for example: vector({ dimensions: 3 }).

UUID with defaultRandom in PostgreSQL

In Drizzle ORM, a UUID column can use defaultRandom() to automatically generate random UUIDs for new rows, for example: id: uuid().defaultRandom().

Varchar with length parameter in PostgreSQL schema

String columns in PostgreSQL schemas are defined using varchar with a length parameter, for example: varchar({ length: 256 }).

Timestamp column mode options in PostgreSQL

Timestamp columns in PostgreSQL schemas can specify a mode parameter, such as timestamp({ mode: 'string' }), which determines how the timestamp is handled.

uuid_generate_v7 default in PostgreSQL

PostgreSQL columns can use sql`public.uuid_generate_v7()` as a default value to generate version 7 UUIDs, specified as: uuid().default(sql`public.uuid_generate_v7()`).

LOCALTIMESTAMP default for timestamp columns

Timestamp columns can use LOCALTIMESTAMP as a default value to set the current local timestamp when a row is inserted, specified as: timestamp({ mode: 'string' }).default(sql`LOCALTIMESTAMP`).

Nile with vector column type

Nile supports the vector column type in schemas. The vector type accepts a dimensions parameter to specify the number of dimensions, for example vector({ dimensions: 3 }).

geometry column type with spatial index

Create a geometry column using the geometry() function from 'drizzle-orm/pg-core'. The geometry function accepts a column name and an options object with type (e.g., 'point'), mode (e.g., 'xy' or 'tuple'), and srid (e.g., 4326) properties. Create a spatial index using index().using('gist', columnName) to optimize geospatial queries.

geometry column declaration syntax

Import geometry from 'drizzle-orm/pg-core' and declare a geometry column as: geometry('columnName', { type: 'point', mode: 'xy', srid: 4326 }). The type specifies the geometry type (e.g., 'point'), mode specifies how data is represented ('xy' for objects or 'tuple' for arrays), and srid specifies the spatial reference system identifier.

Give your agent this brain