CockroachDB column types

import Section from '@mdx/Section.astro'; import Callout from '@mdx/Callout.astro'; import Npm from '@mdx/Npm.astro';

We have native support for all of them, yet if that's not enough for you, feel free to create custom types.

You can use database aliases in column names if you want, and you can also use the casing parameter to define a mapping strategy for Drizzle.

You can read more about it here </Callout>

bigint

int int8 int64 integer

Signed 8-byte integer

If you're expecting values above 2^31 but below 2^53, you can utilize mode: 'number' and deal with javascript number as opposed to bigint.

export const table = cockroachTable('table', { bigint: bigint({ mode: 'number' }) });

// will be inferred as number bigint: bigint({ mode: 'number' })

// will be inferred as bigint bigint: bigint({ mode: 'bigint' })

```sql
CREATE TABLE "table" (
	"bigint" bigint
);

export const table = cockroachTable('table', { bigint1: bigint().default(10), bigint2: bigint().default(sql'10'::bigint) });

```sql
CREATE TABLE "table" (
	"bigint1" bigint DEFAULT 10,
	"bigint2" bigint DEFAULT '10'::bigint
);

smallint

smallint int2
Small-range signed 2-byte integer

export const table = cockroachTable('table', { smallint: smallint() });

```sql
CREATE TABLE "table" (
	"smallint" smallint
);

export const table = cockroachTable('table', { smallint1: smallint().default(10), smallint2: smallint().default(sql'10'::smallint) });

```sql
CREATE TABLE "table" (
	"smallint1" smallint DEFAULT 10,
	"smallint2" smallint DEFAULT '10'::smallint
);

int4

Signed 4-byte integer

export const table = cockroachTable('table', { int4: int4() });

```sql
CREATE TABLE "table" (
	"int4" int4
);

export const table = cockroachTable('table', { int1: int4().default(10), int2: int4().default(sql'10'::int4) });

```sql
CREATE TABLE "table" (
	"int1" int4 DEFAULT 10,
	"int2" int4 DEFAULT '10'::int4
);

int8

An alias of bigint.

int2

An alias of smallint.

---

bool

Cockroach provides the standard SQL type bool.

For more info please refer to the official Cockroach docs.

export const table = cockroachTable('table', { boolean: bool() });

```sql
CREATE TABLE "table" (
	"boolean" bool
);

---

string

text varchar, char

The STRING data type stores a string of Unicode characters.

VARCHAR (and alias CHARACTER VARYING) CHAR (and alias CHARACTER) NAME </Callout>

For more info please refer to the official CockroachDB docs.

You can define { enum: ["value1", "value2"] } config to infer insert and select types, it won't check runtime values.

export const table = cockroachTable('table', { stringColumn: string(), // equivalent to text PostgreSQL type stringColumn1: string({ length: 256 }), // equivalent to varchar(256) PostgreSQL type });

// will be inferred as text: "value1" | "value2" | null stringColumn: string({ enum: ["value1", "value2"] })

```sql
CREATE TABLE "table" (
	"stringColumn" string,
    "stringColumn1" string(256),
);

text

CockroachDB alias for STRING:

For more info please refer to the official CockroachDB docs.

You can define { enum: ["value1", "value2"] } config to infer insert and select types, it won't check runtime values.

export const table = cockroachTable('table', { text: text() });

// will be inferred as text: "value1" | "value2" | null text: text({ enum: ["value1", "value2"] })

```sql
CREATE TABLE "table" (
	"text" text
);

varchar

character varying(n) varchar(n)

STRING alias used to stay compatible with PostgreSQL

For more info please refer to the official CockroachDB docs.

You can define { enum: ["value1", "value2"] } config to infer insert and select types, it won't check runtime values.

The length parameter is optional according to PostgreSQL docs.

export const table = cockroachTable('table', { varchar1: varchar(), varchar2: varchar({ length: 256 }), });

// will be inferred as text: "value1" | "value2" | null varchar: varchar({ enum: ["value1", "value2"] }),

```sql
CREATE TABLE "table" (
	"varchar1" varchar,
	"varchar2" varchar(256)
);

char

character(n) char(n)

STRING alias used to stay compatible with PostgreSQL

For more info please refer to the official CockroachDB docs.

You can define { enum: ["value1", "value2"] } config to infer insert and select types, it won't check runtime values.

The length parameter is optional according to PostgreSQL docs.

export const table = cockroachTable('table', { char1: char(), char2: char({ length: 256 }), });

// will be inferred as text: "value1" | "value2" | null char: char({ enum: ["value1", "value2"] }),

```sql
CREATE TABLE "table" (
	"char1" char,
	"char2" char(256)
);

---

https://www.cockroachlabs.com/docs/stable/float

decimal

numeric decimal dec The DECIMAL data type stores exact, fixed-point numbers. This type is used when it is important to preserve exact precision, for example, with monetary data.

For more info please refer to the official CockroachDB docs.

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' }), });

```sql
CREATE TABLE "table" (
	"decimal1" decimal,
	"decimal2" decimal(100),
	"decimal3" decimal(100, 20),
	"decimalNum" decimal,
	"decimalBig" decimal
);

numeric

An alias of decimal.

float

float float8 double precision

For more info please refer to the official CockroachDB docs.

const table = cockroachTable('table', { float1: float(), float2: float().default(10.10), float3: float().default(sql'10.10'::float), });

```sql
CREATE TABLE "table" (
	"float1" float,
	"float2" float default 10.10,
	"float3" float default '10.10'::float
);

real

real float4
Single precision floating-point number (4 bytes)

For more info please refer to the official CockroachDB docs.

const table = cockroachTable('table', { real1: real(), real2: real().default(10.10), real3: real().default(sql'10.10'::real), });

```sql
CREATE TABLE "table" (
	"real1" real,
	"real2" real default 10.10,
	"real3" real default '10.10'::real
);

double precision

An alias of float.

---

jsonb

jsonb

The JSONB data type stores JSON (JavaScript Object Notation) data as a binary representation of the JSONB value, which eliminates whitespace, duplicate keys, and key ordering

For more info please refer to the official CockroachDB docs.

const table = cockroachTable('table', { jsonb1: jsonb(), jsonb2: jsonb().default({ foo: "bar" }), jsonb3: jsonb().default(sql'{foo: "bar"}'::jsonb), });

```sql
CREATE TABLE "table" (
	"jsonb1" jsonb,
	"jsonb2" jsonb default '{"foo": "bar"}'::jsonb,
	"jsonb3" jsonb default '{"foo": "bar"}'::jsonb
);

You can specify .$type<..>() for json object inference, it won't check runtime values. It provides compile time protection for default values, insert and select schemas.

// will be inferred as { foo: string }
jsonb: jsonb().$type<{ foo: string }>();

// will be inferred as string[]
jsonb: jsonb().$type<string[]>();

// won't compile
jsonb: jsonb().$type<string[]>().default({});

---

bit

bit

The BIT data types store bit arrays. With BIT, the length is fixed.

Size

The number of bits in a BIT value is determined as follows:

For more info please refer to the official CockroachDB docs.

export const table = cockroachTable('table', { bit1: bit(), bit2: bit({ length: 15 }), bit3: bit({ length: 15 }).default('10011'), bit4: bit({ length: 15 }).default(sql'10011') });

```sql
CREATE TABLE "table" (
	"bit1" bit,
	"bit2" bit(15),
	"bit3" bit(15) DEFAULT '10011',
	"bit4" bit(15) DEFAULT '10011'
);

varbit

varbit

The VARBIT data types store bit arrays. With VARBIT, the length can be variable.

Size

The number of bits in a VARBIT value is determined as follows:

For more info please refer to the official CockroachDB docs.

export const table = cockroachTable('table', { varbit1: varbit(), varbit2: varbit({ length: 15 }), varbit3: varbit({ length: 15 }).default('10011'), varbit4: varbit({ length: 15 }).default(sql'10011') });

```sql
CREATE TABLE "table" (
	"varbit1" varbit,
	"varbit2" varbit(15),
	"varbit3" varbit(15) DEFAULT '10011',
	"varbit4" varbit(15) DEFAULT '10011'
);

---

uuid

uuid

The UUID (Universally Unique Identifier) data type stores a 128-bit value that is unique across both space and time.

For more info please refer to the official CockroachDB docs.

import { uuid, cockroachTable } from "drizzle-orm/cockroach-core";

const table = cockroachTable('table', {
  uuid1: uuid(),
  uuid2: uuid().defaultRandom(),
  uuid3: uuid().default('a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11')
});

CREATE TABLE "table" (
	"uuid1" uuid,
	"uuid2" uuid default gen_random_uuid(),
	"uuid3" uuid default 'a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11'
);

---

time

time timetz time with timezone time without timezone

The TIME data type stores the time of day in UTC. The TIMETZ data type stores a time of day with a time zone offset from UTC.

For more info please refer to the official CockroachDB docs.

const table = cockroachTable('table', { time1: time(), time2: time({ withTimezone: true }), time3: time({ precision: 6 }), time4: time({ precision: 6, withTimezone: true }) });

```sql
CREATE TABLE "table" (
	"time1" time,
	"time2" time with timezone,
	"time3" time(6),
	"time4" time(6) with timezone
);

timestamp

timestamp timestamptz timestamp with time zone timestamp without time zone

The TIMESTAMP and TIMESTAMPTZ data types store a date and time pair in UTC.

For more info please refer to the official CockroachDB docs.

const table = cockroachTable('table', { timestamp1: timestamp(), timestamp2: timestamp({ precision: 6, withTimezone: true }), timestamp3: timestamp().defaultNow(), timestamp4: timestamp().default(sqlnow()), });

```sql
CREATE TABLE "table" (
	"timestamp1" timestamp,
	"timestamp2" timestamp (6) with time zone,
	"timestamp3" timestamp default now(),
	"timestamp4" timestamp default now()
);

You can specify either date or string infer modes:

// will infer as date
timestamp: timestamp({ mode: "date" }),

// will infer as string
timestamp: timestamp({ mode: "string" }),

The string mode does not perform any mappings for you. This mode was added to Drizzle ORM to provide developers with the possibility to handle dates and date mappings themselves, depending on their needs. Drizzle will pass raw dates as strings to and from the database, so the behavior should be as predictable as possible and aligned 100% with the database behavior

The date mode is the regular way to work with dates. Drizzle will take care of all mappings between the database and the JS Date object

date

date

The DATE data type stores a year, month, and day.

For more info please refer to the official CockroachDB docs.

const table = cockroachTable('table', { date: date(), });

```sql
CREATE TABLE "table" (
	"date" date
);

// will infer as string date: date({ mode: "string" }),

### interval
`interval`  

Stores a value that represents a span of time.

For more info please refer to the official CockroachDB **[docs.](https://www.cockroachlabs.com/docs/stable/interval)** 

<Section>
```typescript
import { interval, cockroachTable } from "drizzle-orm/cockroach-core";

const table = cockroachTable('table', {
	interval1: interval(),
  interval2: interval({ fields: 'day' }),
  interval3: interval({ fields: 'month' , precision: 6 }),
});

CREATE TABLE "table" (
	"interval1" interval,
	"interval2" interval day,
	"interval3" interval(6) month
);

---

enum

enum enumerated types
Enumerated (enum) types are data types that comprise a static, ordered set of values. They are equivalent to the enum types supported in a number of programming languages. An example of an enum type might be the days of the week, or a set of status values for a piece of data.

For more info please refer to the official CockroachDB docs.

export const moodEnum = cockroachEnum('mood', ['sad', 'ok', 'happy']);

export const table = cockroachTable('table', { mood: moodEnum(), });

```sql
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE "table" (
	"mood" mood
);

---

Customizing data type

Every column builder has a .$type() method, which allows you to customize the data type of the column.

This is useful, for example, with unknown or branded types:

type UserId = number & { __brand: 'user_id' };
type Data = {
	foo: string;
	bar: number;
};

const users = cockroachTable('users', {
  id: int().$type<UserId>().primaryKey(),
  jsonField: json().$type<Data>(),
});

Identity Columns

PostgreSQL and CockroachDB supports identity columns as a way to automatically generate unique integer values for a column. These values are generated using sequences and can be defined using the GENERATED AS IDENTITY clause.

Types of Identity Columns

• GENERATED ALWAYS AS IDENTITY: The database always generates a value for the column. Manual insertion or updates to this column are not allowed unless the OVERRIDING SYSTEM VALUE clause is used.
• GENERATED BY DEFAULT AS IDENTITY: 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.

Usage example

import { pgTable, integer, text } from 'drizzle-orm/pg-core' 

export const ingredients = pgTable("ingredients", {
  id: integer().primaryKey().generatedAlwaysAsIdentity({ startWith: 1000 }),
  name: text().notNull(),
  description: text(),
});

You can specify all properties available for sequences in the .generatedAlwaysAsIdentity() function. Additionally, you can specify custom names for these sequences

PostgreSQL docs reference.

Default value

The DEFAULT clause specifies a default value to use for the column if no value is explicitly provided by the user when doing an INSERT. If there is no explicit DEFAULT clause attached to a column definition, then the default value of the column is NULL.

An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses.

const table = pgTable('table', { integer1: integer().default(42), integer2: integer().default(sql'42'::integer), uuid1: uuid().defaultRandom(), uuid2: uuid().default(sqlgen_random_uuid()), });

```sql
CREATE TABLE IF NOT EXISTS "table" (
	"integer1" integer DEFAULT 42,
	"integer2" integer DEFAULT '42'::integer,
	"uuid1" uuid DEFAULT gen_random_uuid(),
	"uuid2" uuid DEFAULT gen_random_uuid()
);

When using $default() or $defaultFn(), which are simply different aliases for the same function, you can generate defaults at runtime and use these values in all insert queries.

These functions can assist you in utilizing various implementations such as uuid, cuid, cuid2, and many more.

import { text, pgTable } from "drizzle-orm/pg-core";
import { createId } from '@paralleldrive/cuid2';

const table = pgTable('table', {
	id: text().$defaultFn(() => createId()),
});

When using $onUpdate() or $onUpdateFn(), which are simply different aliases for the same function, you can generate defaults at runtime and use these values in all update queries.

Adds a dynamic update value to the column. The function will be called when the row is updated, and the returned value will be used as the column value if none is provided. If no default (or $defaultFn) value is provided, the function will be called when the row is inserted as well, and the returned value will be used as the column value.

import { integer, timestamp, text, pgTable } from "drizzle-orm/pg-core";

const table = pgTable('table', {
	updateCounter: integer().default(sql`1`).$onUpdateFn((): SQL => sql`${table.update_counter} + 1`),
	updatedAt: timestamp({ mode: 'date', precision: 3 }).$onUpdate(() => new Date()),
    	alwaysNull: text().$type<string | null>().$onUpdate(() => null),
});

Not null

NOT NULL constraint dictates that the associated column may not contain a NULL value.

const table = pgTable('table', { integer: integer().notNull(), });

```sql
CREATE TABLE IF NOT EXISTS "table" (
	"integer" integer NOT NULL
);

Primary key

A primary key constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table. This requires that the values be both unique and not null.

const table = pgTable('table', { id: serial().primaryKey(), });

```sql
CREATE TABLE IF NOT EXISTS "table" (
	"id" serial PRIMARY KEY NOT NULL
);