column types & schema

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

CockroachDB char column type with optional length and enum

Drizzle supports the CockroachDB char type (aliases: character(n), char(n)) imported from 'drizzle-orm/cockroach-core' as `char`. It is a string alias for PostgreSQL compatibility. The `length` parameter is optional. It accepts `enum` parameter for type inference without runtime validation.

CockroachDB decimal column type with precision and scale

Drizzle supports the CockroachDB decimal type (aliases: numeric, decimal, dec) imported from 'drizzle-orm/cockroach-core' as `decimal`. It stores exact, fixed-point numbers. The constructor accepts `precision` parameter for total digits and `scale` parameter for decimal places. It also supports `mode: 'number'` to return JavaScript numbers and `mode: 'bigint'` to return bigint values.

CockroachDB numeric column type

Drizzle supports the CockroachDB numeric type, which is an alias for decimal. It can be imported from 'drizzle-orm/cockroach-core' and stores exact, fixed-point numbers with optional precision and scale parameters.

CockroachDB float column type

Drizzle supports the CockroachDB float type (aliases: float, float8, double precision) imported from 'drizzle-orm/cockroach-core' as `float`. It represents a double-precision floating-point number.

CockroachDB real column type

Drizzle supports the CockroachDB real type (aliases: real, float4) imported from 'drizzle-orm/cockroach-core' as `real`. It represents a single-precision floating-point number storing 4 bytes.

CockroachDB jsonb column type with type inference

Drizzle supports the CockroachDB jsonb type imported from 'drizzle-orm/cockroach-core' as `jsonb`. It stores JSON data as binary representation. The `.$type<T>()` method can be used to specify compile-time type inference without runtime checking. Example: `jsonb().$type<{ foo: string }>()` will infer the field as `{ foo: string }`.

CockroachDB bit column type with optional fixed length

Drizzle supports the CockroachDB bit type imported from 'drizzle-orm/cockroach-core' as `bit`. It stores fixed-length bit arrays. The constructor accepts optional `length` parameter to specify number of bits. Without length, it defaults to 1 bit. Example: `bit()` creates a 1-bit column, `bit({ length: 15 })` creates a 15-bit column.

CockroachDB varbit column type with optional variable length

Drizzle supports the CockroachDB varbit type imported from 'drizzle-orm/cockroach-core' as `varbit`. It stores variable-length bit arrays. The constructor accepts optional `length` parameter to specify maximum number of bits. Without length, it has no maximum. Example: `varbit()` creates a variable-length column, `varbit({ length: 15 })` creates a variable-length column with maximum 15 bits.

CockroachDB uuid column type

Drizzle supports the CockroachDB uuid type imported from 'drizzle-orm/cockroach-core' as `uuid`. It stores a 128-bit universally unique identifier. It supports `.defaultRandom()` to generate random UUIDs using gen_random_uuid() and `.default()` to set a specific default UUID value.

CockroachDB time column type with precision and timezone

Drizzle supports the CockroachDB time type (aliases: time, timetz, time with timezone, time without timezone) imported from 'drizzle-orm/cockroach-core' as `time`. The constructor accepts `precision` parameter for decimal places in seconds and `withTimezone` boolean parameter to store time with UTC offset. Example: `time()` stores time without timezone, `time({ withTimezone: true })` stores time with timezone, `time({ precision: 6 })` stores time with 6-digit precision.

CockroachDB timestamp column type with mode, precision and timezone

Drizzle supports the CockroachDB timestamp type (aliases: timestamp, timestamptz, timestamp with time zone, timestamp without time zone) imported from 'drizzle-orm/cockroach-core' as `timestamp`. The constructor accepts `precision` parameter, `withTimezone` boolean parameter, and `mode` parameter. The mode can be 'date' (default, returns JavaScript Date objects) or 'string' (returns raw date strings from database without mapping). Example: `timestamp({ mode: 'date' })`, `timestamp({ mode: 'string' })`, `timestamp({ precision: 6, withTimezone: true })`. Helpers like `.defaultNow()` generate NOW() defaults.

CockroachDB date column type with mode

Drizzle supports the CockroachDB date type imported from 'drizzle-orm/cockroach-core' as `date`. It stores year, month, and day. The constructor accepts `mode` parameter which can be 'date' (default, returns JavaScript Date objects) or 'string' (returns raw date strings from database).

CockroachDB interval column type with fields and precision

Drizzle supports the CockroachDB interval type imported from 'drizzle-orm/cockroach-core' as `interval`. It stores a span of time. The constructor accepts `fields` parameter to specify the interval field (e.g., 'day', 'month') and `precision` parameter for fractional seconds precision. Example: `interval()`, `interval({ fields: 'day' })`, `interval({ fields: 'month', precision: 6 })`.

CockroachDB enum type definition

Drizzle supports CockroachDB enumerated types imported from 'drizzle-orm/cockroach-core' as `cockroachEnum`. Enum types comprise a static, ordered set of values. Create an enum using `cockroachEnum('enumName', ['value1', 'value2', ...])` and then reference it in a table definition. This generates a CREATE TYPE statement and uses the enum in a column.

CockroachDB geometry column type for spatial data

Drizzle supports the CockroachDB geometry type imported from 'drizzle-orm/cockroach-core' as `geometry`. It stores 2D spatial data. The constructor accepts `type` parameter (e.g., 'point'), `mode` parameter (e.g., 'xy' to infer as { x: number, y: number }), and `srid` parameter for spatial reference system. Default mode returns [number, number] for point types.

CockroachDB inet column type for IP addresses

Drizzle supports the CockroachDB inet type imported from 'drizzle-orm/cockroach-core' as `inet`. It stores an IPv4 or IPv6 address.

CockroachDB int8 column type

Drizzle supports the CockroachDB int8 type, which is an alias for bigint. It can be imported from 'drizzle-orm/cockroach-core'. Like bigint, it supports `mode: 'number'` and `mode: 'bigint'` to control whether values are inferred as JavaScript numbers or bigint.

CockroachDB vector column type for embeddings

Drizzle supports the CockroachDB vector type imported from 'drizzle-orm/cockroach-core' as `vector`. It stores fixed-length arrays of floating-point numbers representing data points in multi-dimensional space. The `dimensions` parameter is required to specify the vector size. Example: `vector({ dimensions: 3 })`.

Column type customization with .$type() method

Every Drizzle column builder has a `.$type()` method that allows customizing the inferred data type. This is useful for unknown, branded, or complex types. Example: `int4().$type<UserId>()` where UserId is a branded number type, or `jsonb().$type<Data>()` for specific JSON structures.

CockroachDB identity columns with GENERATED AS IDENTITY

Drizzle supports CockroachDB identity columns (requires drizzle-orm@0.32.0 or higher and drizzle-kit@0.23.0 or higher). Two types are available: GENERATED ALWAYS AS IDENTITY (database always generates value, manual insertion not allowed without OVERRIDING SYSTEM VALUE) and GENERATED BY DEFAULT AS IDENTITY (database generates default but manual values can also be inserted). Use `.generatedAlwaysAsIdentity({ startWith: 1000 })` on int4 columns to define identity behavior. All sequence properties can be specified in the function.

Column default values in Drizzle CockroachDB

The DEFAULT clause specifies a default value for a column if no value is provided in an INSERT. If no DEFAULT clause is specified, the default is NULL. Drizzle supports `.default(value)` for static defaults (string, number, blob, or constant expression) and `.defaultRandom()` for functions like gen_random_uuid(). Use `sql` helper for database functions: `.default(sql`gen_random_uuid()`)`. Default values are specified in the column definition SQL.

Runtime default values with $defaultFn() in Drizzle

Drizzle supports `.$defaultFn()` and `.$default()` (aliases) to generate defaults at runtime using JavaScript functions. These values are used in all insert queries at runtime but do not affect drizzle-kit migrations. Example: `text().$defaultFn(() => createId())` where createId() is from '@paralleldrive/cuid2'. This allows runtime generation of values like UUIDs or custom IDs.

Runtime update values with $onUpdateFn() in Drizzle

Drizzle supports `.$onUpdateFn()` and `.$onUpdate()` (aliases) to generate values at runtime during UPDATE queries. The function is called when the row is updated, and the returned value is used if none is provided. If no default or $defaultFn is provided, the function also runs on INSERT. Example: `timestamp().$onUpdate(() => new Date())` or `int4().$onUpdateFn((): SQL => sql`${table.updateCounter} + 1`)`. This only affects runtime behavior, not migrations.

NOT NULL constraint in Drizzle columns

The NOT NULL constraint dictates that a column may not contain a NULL value. In Drizzle, apply it using the `.notNull()` method on a column. Example: `int4().notNull()` generates SQL `int4 NOT NULL`.

PRIMARY KEY constraint in Drizzle columns

A primary key constraint indicates that a column or group of columns can be used as a unique identifier for rows in a table. Values must be both unique and not null. In Drizzle, apply it using the `.primaryKey()` method on a column. Example: `int4().primaryKey()` generates SQL `int4 PRIMARY KEY`.

CockroachDB int2 column type

Drizzle supports the CockroachDB int2 type, which is an alias for smallint. It can be imported from 'drizzle-orm/cockroach-core' and represents a small-range signed 2-byte integer.

CockroachDB int4 column type

Drizzle supports the CockroachDB int4 type as a signed 4-byte integer. It can be imported from 'drizzle-orm/cockroach-core' as `int4`.

CockroachDB bool column type

Drizzle supports the CockroachDB bool type for boolean values. It can be imported from 'drizzle-orm/cockroach-core' as `bool`.

CockroachDB string column type with length and enum

Drizzle supports the CockroachDB string type (aliases: text, varchar, char) imported from 'drizzle-orm/cockroach-core' as `string`. It stores Unicode characters. The `string()` constructor accepts `length` parameter for length constraints and `enum` parameter as an array of literal values for type inference without runtime validation. Without length, it is equivalent to text. With `{ length: 256 }` it is equivalent to varchar(256).

CockroachDB text column type with enum

Drizzle supports the CockroachDB text type imported from 'drizzle-orm/cockroach-core' as `text`. It is an alias for the string type. It accepts `enum` parameter as an array of literal values like `{ enum: ['value1', 'value2'] }` for type inference without runtime validation.

MSSQL column type example - numeric with precision and scale

Example: import { numeric, mssqlTable } from "drizzle-orm/mssql-core"; export const table = mssqlTable('table', { numeric1: numeric(), numeric2: numeric({ precision: 100 }), numeric3: numeric({ precision: 100, scale: 20 }) }); Generates SQL: CREATE TABLE [table] ( [numeric1] numeric, [numeric2] numeric(100), [numeric3] numeric(100, 20) );

MSSQL column type example - float with precision

Example: import { float, mssqlTable } from "drizzle-orm/mssql-core"; export const table = mssqlTable('table', { float1: float(), float2: float({ precision: 16 }) }); Generates SQL: CREATE TABLE [table] ( [float1] float, [float2] float(16) );

MSSQL column type example - time with mode and precision

Example: import { time, mssqlTable } from "drizzle-orm/mssql-core"; export const table = mssqlTable('table', { time1: time(), time2: time({ mode: 'string' }), time3: time({ precision: 6 }), time4: time({ precision: 6, mode: 'date' }) }); Generates SQL: CREATE TABLE [table] ( [time1] time, [time2] time, [time3] time(6), [time4] time(6) );

MSSQL column type example - $onUpdateFn with counter and timestamp

Example: import { int, datetime2, text, mssqlTable } from "drizzle-orm/mssql-core"; import { SQL, sql } from 'drizzle-orm'; export const table = mssqlTable('table', { updateCounter: int().default(sql`1`).$onUpdateFn((): SQL => sql`${table.updateCounter} + 1`), updatedAt: datetime2({ mode: 'date', precision: 3 }).$onUpdate(() => new Date()), alwaysNull: text().$type<string | null>().$onUpdate(() => null), });

MSSQL column type example - PRIMARY KEY constraint

Example: import { int, mssqlTable } from "drizzle-orm/mssql-core"; export const table = mssqlTable('table', { id: int().primaryKey(), }); Generates SQL: CREATE TABLE [table] ( [id] int, CONSTRAINT [table_pkey] PRIMARY KEY([id]) );

Column DEFAULT clause - specify default value for inserts

The DEFAULT clause specifies a default value to use for a column if no value is explicitly provided during an INSERT. If no explicit DEFAULT clause is attached to a column definition, the default value is NULL. An explicit DEFAULT clause may specify that the default value is NULL. Use .default() method with a static value: .default(42) or .default('text').

MSSQL int column type - 4-byte signed integer

The int column type in MSSQL represents a signed 4-byte integer. It can be defined using int() from drizzle-orm/mssql-core. It supports default values via .default() method.

MSSQL tinyint column type - 1-byte signed integer

The tinyint column type in MSSQL represents a small-range signed 1-byte integer. It can be defined using tinyint() from drizzle-orm/mssql-core. It supports default values via .default() method.

MSSQL bigint column type - 8-byte signed integer with mode options

The bigint column type in MSSQL represents a signed 8-byte integer. It can be defined using bigint() from drizzle-orm/mssql-core with mode options: mode: 'number' infers as JavaScript number (for values 2^31 to 2^53), mode: 'bigint' infers as BigInt, mode: 'string' infers as string. It supports default values via .default() method.

MSSQL bit column type - boolean-like integer

The bit column type in MSSQL is an integer data type that can take values 1, 0, or NULL. Drizzle accepts true or false as values instead of 1 and 0. It can be defined using bit() from drizzle-orm/mssql-core.

MSSQL text column type - variable-length non-Unicode string

The text column type in MSSQL stores variable-length non-Unicode data with a maximum string length of 2^31 - 1 (2,147,483,647). It can be defined using text() from drizzle-orm/mssql-core. An optional enum configuration like { enum: ["value1", "value2"] } can be provided to infer insert and select types (but does not check runtime values).

MSSQL ntext column type - variable-length Unicode string

The ntext column type in MSSQL stores variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823). It can be defined using ntext() from drizzle-orm/mssql-core. An optional enum configuration like { enum: ["value1", "value2"] } can be provided to infer insert and select types (but does not check runtime values).

MSSQL varchar column type - variable-size string data

The varchar column type in MSSQL stores variable-size string data. The length parameter is optional and can be: a value from 1 through 8,000 (in bytes), or 'max' for up to 2^31-1 bytes. It can be defined using varchar() from drizzle-orm/mssql-core. Syntax: varchar(), varchar({ length: 256 }), varchar({ length: 'max' }). An optional enum configuration like { enum: ["value1", "value2"] } can be provided to infer insert and select types (but does not check runtime values).

MSSQL nvarchar column type - variable-size Unicode string

The nvarchar column type in MSSQL stores variable-size Unicode string data. The length parameter is optional and represents string size in byte-pairs. It can be defined using nvarchar() from drizzle-orm/mssql-core. Syntax: nvarchar(), nvarchar({ length: 256 }). Supports mode: 'json' to infer as JSON type. An optional enum configuration like { enum: ["value1", "value2"] } can be provided to infer insert and select types (but does not check runtime values).

MSSQL nchar column type - fixed-size Unicode string

The nchar column type in MSSQL stores fixed-size Unicode string data. The length parameter is optional and can be a value from 1 through 4,000 (in byte-pairs). If not specified, defaults to nchar(1). It can be defined using nchar() from drizzle-orm/mssql-core. Syntax: nchar(), nchar({ length: 256 }). An optional enum configuration like { enum: ["value1", "value2"] } can be provided to infer insert and select types (but does not check runtime values).

MSSQL binary column type - fixed-length binary data

The binary column type in MSSQL stores fixed-length binary data with a length of n bytes (1 through 8,000). The storage size is n bytes. It can be defined using binary() from drizzle-orm/mssql-core. Syntax: binary(), binary({ length: 256 }).

MSSQL varbinary column type - variable-length binary data

The varbinary column type in MSSQL stores variable-length binary data. The length parameter is optional and can be a value from 1 through 8,000, or 'max' where max indicates maximum storage size is 2^31-1 bytes. It can be defined using varbinary() from drizzle-orm/mssql-core. Syntax: varbinary(), varbinary({ length: 256 }), varbinary({ length: 'max' }).

MSSQL numeric column type - fixed precision and scale decimal

The numeric column type in MSSQL stores fixed precision and scale numbers. Valid values range from -10^38 + 1 through 10^38 - 1 at maximum precision. It can be defined using numeric() from drizzle-orm/mssql-core. Parameters: precision (optional), scale (optional). Syntax: numeric(), numeric({ precision: 100 }), numeric({ precision: 100, scale: 20 }).

MSSQL decimal column type - alias for numeric

The decimal column type in MSSQL is an alias for the numeric type. It stores fixed precision and scale numbers.

MSSQL real column type - 24-bit float

The real column type in MSSQL is the ISO synonym for float(24). It can be defined using real() from drizzle-orm/mssql-core. It supports default values via .default() method.

MSSQL float column type - variable precision floating point

The float column type in MSSQL stores floating point numbers with optional precision. The precision parameter n is the number of bits used to store the mantissa and must be a value between 1 and 53 (default is 53). It can be defined using float() from drizzle-orm/mssql-core. Syntax: float(), float({ precision: 16 }).

MSSQL time column type - time of day without timezone

The time column type in MSSQL defines a time of day without time zone awareness, based on a 24-hour clock. It can be defined using time() from drizzle-orm/mssql-core. Optional parameters: mode ('date' or 'string'), precision (0-7, for fractional seconds). Syntax: time(), time({ mode: 'string' }), time({ precision: 6 }), time({ precision: 6, mode: 'date' }).

MSSQL date column type - calendar date

The date column type in MSSQL stores a calendar date (year, month, day). It can be defined using date() from drizzle-orm/mssql-core. Optional mode parameter: mode: 'date' infers as Date, mode: 'string' infers as string. Syntax: date(), date({ mode: 'date' }), date({ mode: 'string' }).

MSSQL datetime column type - date with time and fractional seconds

The datetime column type in MSSQL defines a date combined with a time of day with fractional seconds, based on a 24-hour clock. Note: Microsoft recommends using datetime2, time, date, or datetimeoffset for new work instead. It can be defined using datetime() from drizzle-orm/mssql-core. Optional mode parameter: mode: 'date' infers as Date, mode: 'string' infers as string. Syntax: datetime(), datetime({ mode: 'date' }), datetime({ mode: 'string' }).

MSSQL datetime2 column type - date with time and optional precision

The datetime2 column type in MSSQL defines a date combined with a time of day based on a 24-hour clock. It extends datetime with a larger date range, larger default fractional precision, and optional user-specified precision. It can be defined using datetime2() from drizzle-orm/mssql-core. Optional mode parameter: mode: 'date' infers as Date, mode: 'string' infers as string. Syntax: datetime2(), datetime2({ mode: 'date' }), datetime2({ mode: 'string' }).

MSSQL datetimeoffset column type - date with time and timezone awareness

The datetimeoffset column type in MSSQL defines a date combined with a time of day based on a 24-hour clock with time zone awareness based on Coordinated Universal Time (UTC). It can be defined using datetimeoffset() from drizzle-orm/mssql-core. Optional mode parameter: mode: 'date' infers as Date, mode: 'string' infers as string. Syntax: datetimeoffset(), datetimeoffset({ mode: 'date' }), datetimeoffset({ mode: 'string' }).

Column $type() method - customize column TypeScript type

Every column builder in Drizzle has a .$type() method that allows you to customize the TypeScript data type of the column. This is useful for unknown or branded types. Example: int().$type<UserId>() where UserId is a branded type like type UserId = number & { __brand: 'user_id' }.

Column $defaultFn() or $default() - runtime default generation

$defaultFn() and $default() are aliases for the same function. They allow you to generate defaults at runtime and use these values in all insert queries. These values do not affect drizzle-kit behavior; they are only used at runtime in drizzle-orm. Example: text().$defaultFn(() => createId()).

Column $onUpdateFn() or $onUpdate() - runtime update value generation

$onUpdateFn() and $onUpdate() are aliases for the same function. They generate values at runtime when a row is updated. The function is called during updates, and the returned value is used as the column value if none is provided. If no default or $defaultFn value is provided, the function is also called during inserts. These values do not affect drizzle-kit behavior; they are only used at runtime in drizzle-orm. Example: datetime2({ mode: 'date', precision: 3 }).$onUpdate(() => new Date()).

Column NOT NULL constraint - disallow null values

The NOT NULL constraint dictates that the associated column may not contain a NULL value. Use .notNull() method on column definition.

Give your agent this brain