CockroachDB inet column type
CockroachDB inet stores an IPv4 or IPv6 address. Import inet from drizzle-orm/cockroach-core.
cockroachdb/column-types
90 notes in this subject, read out of this brain and free to use. This is page 1 of 2.
CockroachDB bigint column type with mode option
CockroachDB supports bigint (also called int8, int64, integer) as a signed 8-byte integer. You can use `mode: 'number'` to handle values between 2^31 and 2^53 as JavaScript numbers instead of bigint. With `mode: 'number'`, the column infers to number type. With `mode: 'bigint'`, the column infers to bigint type. Import from drizzle-orm/cockroach-core.
CockroachDB smallint column type
CockroachDB supports smallint (also called int2) as a small-range signed 2-byte integer. Import int2 or smallint from drizzle-orm/cockroach-core.
CockroachDB int4 column type
CockroachDB supports int4 as a signed 4-byte integer. Import int4 from drizzle-orm/cockroach-core.
CockroachDB bool column type
CockroachDB supports the standard SQL bool type for boolean values. Import bool from drizzle-orm/cockroach-core.
CockroachDB geometry column type
CockroachDB geometry stores 2D spatial data. You can specify `{ type: 'point' }` to define geometry type. You can use `mode: 'xy'` to infer as { x: number, y: number } instead of [number, number]. You can specify SRID with `{ type: 'point', srid: 4326 }`. Import geometry from drizzle-orm/cockroach-core.
CockroachDB decimal column type with precision and scale
CockroachDB decimal (also called numeric, dec) stores exact fixed-point numbers for preserving precision with monetary data. You can specify precision and scale: `decimal()`, `decimal({ precision: 100 })`, `decimal({ precision: 100, scale: 20 })`. You can use `mode: 'number'` or `mode: 'bigint'` for type inference. Import decimal from drizzle-orm/cockroach-core.
CockroachDB timestamp column type with timezone, precision, and mode
CockroachDB timestamp stores date and time in UTC. You can specify `{ precision: 6, withTimezone: true }` for TIMESTAMPTZ. Use `.defaultNow()` or `.default(sql`now()`)` for current timestamp. You can specify `mode: 'date'` for JavaScript Date inference or `mode: 'string'` for string inference. The string mode passes raw dates as strings to/from database for developer control over date handling.
CockroachDB enum column type with cockroachEnum
CockroachDB enum types comprise a static ordered set of values. Create an enum with `cockroachEnum('enumName', ['value1', 'value2'])` which generates CREATE TYPE enumName AS ENUM (...). Then use that enum in a table column. Import cockroachEnum from drizzle-orm/cockroach-core.
CockroachDB vector column type with dimensions
CockroachDB vector stores fixed-length arrays of floating-point numbers representing data points in multi-dimensional space. You must specify the number of dimensions: `vector({ dimensions: 3 })`. Import vector from drizzle-orm/cockroach-core.
Drizzle column $type() method for branded and unknown types
Every column builder has a `.$type()` method to customize the data type. This is useful for branded types (type UserId = number & { __brand: 'user_id' }) and unknown types. Example: `int4().$type<UserId>().primaryKey()` or `jsonb().$type<Data>()`.
Drizzle column default values with .default() and .defaultRandom()
The DEFAULT clause specifies a default value when no value is provided in INSERT. If no DEFAULT clause is attached, the default is NULL. Use `.default(42)`, `.default(sql`gen_random_uuid()`)`, or `.defaultRandom()` for UUIDs. Examples: `int4().default(42)`, `uuid().defaultRandom()`, `uuid().default(sql`gen_random_uuid()`)`. Import sql from drizzle-orm.
Drizzle runtime defaults with $defaultFn() and $onUpdateFn()
$defaultFn() and $default() are aliases. They generate defaults at runtime and use these values in all insert queries. Supports uuid, cuid, cuid2, and other implementations. Example: `text().$defaultFn(() => createId())`. $onUpdateFn() and $onUpdate() generate values at runtime in update queries. If no $defaultFn is provided, the $onUpdateFn is also called on insert. These values only affect drizzle-orm runtime behavior, not drizzle-kit migrations. Example: `timestamp({ mode: 'date', precision: 3 }).$onUpdate(() => new Date())`.
CockroachDB table creation example with int8 and bigint
Example showing CockroachDB table schema with int8 and bigint columns: `import { int8, bigint, cockroachTable } from "drizzle-orm/cockroach-core"; export const table = cockroachTable('table', { int8: int8({ mode: 'number' }), bigint: bigint({ mode: 'number' }) });` This creates a table with columns that infer to number type.
CockroachDB table creation example with decimal
Example showing CockroachDB table schema with decimal columns: `import { decimal, cockroachTable } from "drizzle-orm/cockroach-core"; export const table = cockroachTable('table', { decimal1: decimal(), decimal2: decimal({ precision: 100 }), decimal3: decimal({ precision: 100, scale: 20 }), decimalNum: decimal({ mode: 'number' }), decimalBig: decimal({ mode: 'bigint' }) });`
CockroachDB table creation example with enum
Example showing CockroachDB enum type and table usage: `import { cockroachEnum, cockroachTable } from "drizzle-orm/cockroach-core"; export const moodEnum = cockroachEnum('mood', ['sad', 'ok', 'happy']); export const table = cockroachTable('table', { mood: moodEnum() });` This creates CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy') and a table using it.
CockroachDB table creation example with identity column
Example showing CockroachDB identity column: `import { cockroachTable, int4, text } from 'drizzle-orm/cockroach-core' export const ingredients = cockroachTable("ingredients", { id: int4().primaryKey().generatedAlwaysAsIdentity({ startWith: 1000 }), name: text().notNull(), description: text() });`
CockroachDB table creation example with jsonb type inference
Example showing CockroachDB jsonb with type inference: `import { jsonb, cockroachTable } from "drizzle-orm/cockroach-core"; const table = cockroachTable('table', { jsonbField: jsonb().$type<{ foo: string }>() });` This infers the jsonb column as { foo: string } for compile-time type safety.
CockroachDB table creation example with geometry point
Example showing CockroachDB geometry type: `import { geometry, cockroachTable } from "drizzle-orm/cockroach-core"; export const table = cockroachTable('table', { geo1: geometry({ type: 'point' }), geo2: geometry({ type: 'point', mode: 'xy' }), geo3: geometry({ type: 'point', srid: 4326 }) });` geo1 infers as [number, number], geo2 as { x: number, y: number }.
Drizzle ORM supports custom column types for CockroachDB
If native CockroachDB column types are not enough, you can create custom types for CockroachDB. This is documented in the custom-types section.
SQL schema declaration example with CockroachDB
Example schema declaration in TypeScript: ```typescript export const countries = cockroachTable('countries', { id: int4().primaryKey(), name: varchar({ length: 256 }), }); export const cities = cockroachTable('cities', { id: int4().primaryKey(), name: varchar({ length: 256 }), countryId: int4('country_id').references(() => countries.id), }); ```
Generated columns in CockroachDB with Drizzle
Generated columns (or computed columns) in CockroachDB are stored in the database and computed when a row is inserted or updated. They simplify data access by precomputing complex expressions and can be indexed to improve query performance since values do not need to be recomputed for each query.
Generated column limitations in CockroachDB
Generated columns in CockroachDB have the following limitations: cannot specify default values, expressions cannot reference other generated columns or include subqueries, schema changes are required to modify generated column expressions, and they cannot be directly used in primary keys, foreign keys, or unique constraints.
generatedAlwaysAs() method in Drizzle
In Drizzle, you can use the `.generatedAlwaysAs()` function on any column type to specify a generated expression that will compute the column data. This method accepts generated expressions in two ways: using the `sql` tag for value escaping, or using a callback function to reference columns from the table.
Generated column with sql tag example
Example using `sql` tag for generated column: ```ts export const test = cockroachTable("test", { generatedName: string("gen_name").generatedAlwaysAs(sql`'hello "world"!'`), }); ``` This generates SQL: `CREATE TABLE "test" ("gen_name" text GENERATED ALWAYS AS ('hello "world"!') STORED);`
Generated column with callback function example
Example using callback function to reference other columns in generated expression: ```ts export const test = cockroachTable("test", { name: string("first_name"), generatedName: string("gen_name").generatedAlwaysAs( (): SQL => sql`'hi, ' || ${test.name} || '!'` ), }); ``` This generates SQL: `CREATE TABLE "test" ("first_name" string, "gen_name" string GENERATED ALWAYS AS ('hi, ' || "test"."first_name" || '!') STORED);`
Full-text search with generated columns in CockroachDB
Example of generated columns with full-text search using tsvector: ```typescript import { SQL, sql } from "drizzle-orm"; import { customType, index, int4, cockroachTable, string } from "drizzle-orm/cockroach-core"; const tsVector = customType<{ data: string }>({ dataType() { return "tsvector"; }, }); export const test = cockroachTable( "test", { id: int4("id").primaryKey().generatedAlwaysAsIdentity(), content: string("content"), contentSearch: tsVector("content_search", { dimensions: 3, }).generatedAlwaysAs( (): SQL => sql`to_tsvector('english', ${test.content})` ), }, (t) => [ index("idx_content_search").using("gin", t.contentSearch) ] ); ```
Infer Select and Insert types from CockroachDB table schema
Use Drizzle's type helpers to infer select and insert models from a CockroachDB table schema. You can use the syntax `type SelectUser = typeof users.$inferSelect;` and `type InsertUser = typeof users.$inferInsert;` directly on the table. Alternatively, import and use the helper types `InferSelectModel<typeof users>` and `InferInsertModel<typeof users>` from 'drizzle-orm'.
Enable query logging for CockroachDB
Enable default query logging by passing `{ logger: true }` as an option to the drizzle function when creating a database instance: `const db = drizzle(process.env.DB_URL, { logger: true });`
Custom query log writer for CockroachDB
Create a custom log writer by implementing the LogWriter interface with a write method, then pass it to a DefaultLogger instance: `const logger = new DefaultLogger({ writer: new MyLogWriter() });` and provide the logger to drizzle. The write method signature is `write(message: string): void`.
Custom logger implementation for CockroachDB
Implement a custom logger by creating a class that implements the Logger interface with a logQuery method: `logQuery(query: string, params: unknown[]): void`. Then pass the logger instance to drizzle: `const db = drizzle(process.env.DB_URL, { logger: new MyLogger() });`
Multi-project schema with cockroachTableCreator
Use `cockroachTableCreator` to customize table names when several projects share one database. Pass a function that prefixes table names: `const cockroachTable = cockroachTableCreator((name) => 'project1_' + name);`. Then use the created table function to define tables. In drizzle-kit config, set `tablesFilter: ['project1_*']` to filter tables by the prefix.
Inspect CockroachDB table metadata with getTableConfig
Use `getTableConfig` from 'drizzle-orm/cockroach-core' to inspect table metadata. It returns an object with properties: columns, indexes, foreignKeys, checks, primaryKeys, name, and schema.
Create mock database instance without real connection
Use `drizzle.mock()` to create a typed database object without a real CockroachDB connection: `import { drizzle } from 'drizzle-orm/node-postgres'; const db = drizzle.mock({ schema });`
CockroachDB bigint column type
bigint stores a signed 8-byte integer. It can be imported from 'drizzle-orm/cockroach-core'. The SQL aliases are int, int8, int64, and integer. When expecting values above 2^31 but below 2^53, you can use `mode: 'number'` to deal with JavaScript number instead of bigint. The `mode: 'bigint'` option infers as TypeScript bigint type. Default values can be specified with `.default()` method, either as a plain number or SQL expression like `sql`'10'::bigint``.
CockroachDB smallint column type
smallint stores a small-range signed 2-byte integer. It can be imported from 'drizzle-orm/cockroach-core'. The SQL aliases are smallint and int2. Default values can be specified with `.default()` method, either as a plain number or SQL expression like `sql`'10'::smallint``.
CockroachDB int4 column type
int4 stores a signed 4-byte integer. It can be imported from 'drizzle-orm/cockroach-core'. Default values can be specified with `.default()` method, either as a plain number or SQL expression like `sql`'10'::int4``.
CockroachDB int8 is alias for bigint
int8 is an alias of bigint in CockroachDB column types.
CockroachDB int2 is alias for smallint
int2 is an alias of smallint in CockroachDB column types.
CockroachDB bool column type
bool stores a standard SQL boolean value. It can be imported from 'drizzle-orm/cockroach-core'.
CockroachDB string column type
string stores Unicode characters and can be imported from 'drizzle-orm/cockroach-core'. SQL aliases are text, varchar, and char. The `length` parameter specifies a maximum length (e.g., `string({ length: 256 })` for varchar(256)). You can define `{ enum: ["value1", "value2"] }` to infer insert and select types at compile time, though it does not check runtime values.
CockroachDB text column type
text is a CockroachDB alias for STRING and stores Unicode text. It can be imported from 'drizzle-orm/cockroach-core'. You can define `{ enum: ["value1", "value2"] }` to infer insert and select types at compile time, though it does not check runtime values.
CockroachDB varchar column type
varchar is a STRING alias used for PostgreSQL compatibility. It can be imported from 'drizzle-orm/cockroach-core'. The length parameter is optional. The `varchar()` creates a column with no length constraint, while `varchar({ length: 256 })` creates varchar(256). You can define `{ enum: ["value1", "value2"] }` to infer insert and select types at compile time, though it does not check runtime values.
CockroachDB char column type
char is a STRING alias used for PostgreSQL compatibility. It can be imported from 'drizzle-orm/cockroach-core'. The length parameter is optional. The `char()` creates a column with no length constraint, while `char({ length: 256 })` creates char(256). You can define `{ enum: ["value1", "value2"] }` to infer insert and select types at compile time, though it does not check runtime values.
CockroachDB decimal column type
decimal stores exact, fixed-point numbers and can be imported from 'drizzle-orm/cockroach-core'. SQL aliases are numeric, decimal, and dec. This type is used for preserving exact precision, such as monetary data. Options include `precision` (e.g., `decimal({ precision: 100 })`), `scale` (e.g., `decimal({ precision: 100, scale: 20 })`), `mode: 'number'` for JavaScript number, and `mode: 'bigint'` for bigint representation.
CockroachDB numeric is alias for decimal
numeric is an alias of decimal in CockroachDB column types.
CockroachDB float column type
float is a double precision floating-point number (8 bytes). It can be imported from 'drizzle-orm/cockroach-core'. SQL aliases are float, float8, and double precision. Default values can be specified with `.default()` method, either as a plain number like `10.10` or SQL expression like `sql`'10.10'::float``.
CockroachDB real column type
real is a single precision floating-point number (4 bytes). It can be imported from 'drizzle-orm/cockroach-core'. SQL aliases are real and float4. Default values can be specified with `.default()` method, either as a plain number like `10.10` or SQL expression like `sql`'10.10'::real``.
CockroachDB double precision is alias for float
double precision is an alias of float in CockroachDB column types.
CockroachDB jsonb column type
jsonb stores JSON data as a binary representation that eliminates whitespace, duplicate keys, and key ordering. It can be imported from 'drizzle-orm/cockroach-core'. Default values can be specified as objects like `{ foo: "bar" }` or SQL expressions like `sql`'{foo: "bar"}'::jsonb``. Use `.$type<...>()` for JSON object type inference at compile time for insert, select, and default value protection, though it does not check runtime values.
CockroachDB bit column type
bit stores fixed-length bit arrays. It can be imported from 'drizzle-orm/cockroach-core'. BIT defaults to 1 bit if no length is specified. BIT(N) stores N bits. Default values can be specified with `.default()` method using bit strings like '10011' or SQL expressions like `sql`'10011'`.
CockroachDB varbit column type
varbit stores variable-length bit arrays. It can be imported from 'drizzle-orm/cockroach-core'. VARBIT has no maximum length, while VARBIT(N) has a maximum of N bits. Default values can be specified with `.default()` method using bit strings like '10011' or SQL expressions like `sql`'10011'`.
CockroachDB date mode inference example
Example showing date mode inference: `date({ mode: 'date' })` infers as JavaScript Date type, `date({ mode: 'string' })` infers as string type.
CockroachDB uuid column type
uuid stores a 128-bit Universally Unique Identifier value. It can be imported from 'drizzle-orm/cockroach-core'. Use `.defaultRandom()` to generate a random UUID using `gen_random_uuid()`, or `.default()` to specify a specific UUID string.
CockroachDB time column type
time stores the time of day in UTC. It can be imported from 'drizzle-orm/cockroach-core'. SQL aliases include time, timetz, time with timezone, and time without timezone. Options include `withTimezone: true` for time with timezone and `precision` for fractional seconds precision (e.g., `time({ precision: 6, withTimezone: true })`).
CockroachDB timestamp column type
timestamp stores a date and time pair in UTC. It can be imported from 'drizzle-orm/cockroach-core'. SQL aliases are timestamp, timestamptz, timestamp with time zone, and timestamp without time zone. Options include `precision` for fractional seconds, `withTimezone: true` for timestamp with timezone, `.defaultNow()` to use database now(), and `mode: 'date'` or `mode: 'string'` for TypeScript inference. The 'date' mode maps to JavaScript Date, while 'string' mode passes raw dates as strings without mapping.
CockroachDB date column type
date stores a year, month, and day. It can be imported from 'drizzle-orm/cockroach-core'. Options include `mode: 'date'` to infer as JavaScript Date or `mode: 'string'` to infer and pass dates as strings without mapping.
CockroachDB interval column type
interval stores a span of time. It can be imported from 'drizzle-orm/cockroach-core'. Options include `fields` to specify 'day' or 'month' and `precision` for fractional precision (e.g., `interval({ fields: 'month', precision: 6 })`).
CockroachDB enum column type
enum defines an enumerated type comprising a static, ordered set of values. Use `cockroachEnum()` function imported from 'drizzle-orm/cockroach-core' to create an enum type definition. For example, `cockroachEnum('mood', ['sad', 'ok', 'happy'])` creates an enum type named 'mood' with three values. This generates SQL `CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy')`.
CockroachDB column $type() customization
Every column builder has a `.$type()` method that allows customization of the data type for TypeScript type inference. This is useful for unknown, branded, or custom types. For example, `int().$type<UserId>().primaryKey()` or `jsonb().$type<Data>()`.