🎉 We have launched Drizzle Studio! Read more →
Documentation
Column types
PostgreSQL

PostgreSQL column types

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

integer

integer int int4
Signed 4-byte integer
If you need integer autoincrement - please refer to serial

import { integer, pgTable } from "drizzle-orm/pg-core";
 
export const table = pgTable('table', {
	int: integer('int')
});
 
CREATE TABLE IF NOT EXISTS "table" (
	"int" integer
);
import { sql } from "drizzle-orm";
import { integer, pgTable } from "drizzle-orm/pg-core";
 
export const table = pgTable('table', {
	int1: integer('int1').default(10)
	int2: integer('int2').default(sql`'10'::int`)
});
 
CREATE TABLE IF NOT EXISTS "table" (
	"int1" integer DEFAULT 10
	"int2" integer DEFAULT '10'::int
);

smallint

smallint int2
Small-range signed 2-byte integer
If you need smallint autoincrement - please refer to smallserial

import { smallint, pgTable } from "drizzle-orm/pg-core";
 
export const table = pgTable('table', {
	smallint: smallint('smallint')
});
CREATE TABLE IF NOT EXISTS "table" (
	"smallint" smallint
);
import { sql } from "drizzle-orm";
import { smallint, pgTable } from "drizzle-orm/pg-core";
 
export const table = pgTable('table', {
	smallint1: smallint('smallint1').default(10)
	smallint2: smallint('smallint2').default(sql`'10'::smallint`)
});
CREATE TABLE IF NOT EXISTS "table" (
	"smallint1" smallint DEFAULT 10
	"smallint2" smallint DEFAULT '10'::smallint
);

bigint

bigint int8
Signed 8-byte integer
If you need bigint autoincrement - please refer to bigserial

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

import { bigint, pgTable } from "drizzle-orm/pg-core";
 
export const table = pgTable('table', {
	bigint: bigint('bigint', { mode: 'number' })
});
 
// will be inferred as `number`
bigint: bigint('bigint', { mode: 'number' })
 
// will be inferred as `bigint`
bigint: bigint('bigint', { mode: 'bigint' })
CREATE TABLE IF NOT EXISTS "table" (
	"bigint" bigint
);
import { sql } from "drizzle-orm";
import { bigint, pgTable } from "drizzle-orm/pg-core";
 
export const table = pgTable('table', {
	bigint1: bigint('bigint1').default(10)
	bigint2: bigint('bigint2').default(sql`'10'::bigint`)
});
CREATE TABLE IF NOT EXISTS "table" (
	"bigint1" bigint DEFAULT 10
	"bigint2" bigint DEFAULT '10'::bigint
);

serial

serial serial4
Auto incrementing 4-bytes integer, notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).
Official PostrgreSQL docs (opens in a new tab)

import { serial, pgTable } from "drizzle-orm/pg-core";
 
export const table = pgTable('table', {
  serial: serial('serial'),
});
CREATE TABLE IF NOT EXISTS "table" (
	"serial" serial NOT NULL,
);

smallserial

smallserial serial2
Auto incrementing 2-bytes integer, notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).
Official PostrgreSQL docs (opens in a new tab)

import { smallserial, pgTable } from "drizzle-orm/pg-core";
 
export const table = pgTable('table', {
  smallserial: smallserial('smallserial'),
});
CREATE TABLE IF NOT EXISTS "table" (
	"smallserial" smallserial NOT NULL,
);

bigserial

bigserial serial8
Auto incrementing 8-bytes integer, notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).
Official PostrgreSQL docs (opens in a new tab)

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

import { bigserial, pgTable } from "drizzle-orm/pg-core";
 
export const table = pgTable('table', {
  bigserial: bigserial('bigserial', { mode: 'number' }),
});
CREATE TABLE IF NOT EXISTS "table" (
	"bigserial" bigserial NOT NULL,
);

boolean

PostgreSQL provides the standard SQL type boolean Official PostrgreSQL docs (opens in a new tab)

import { boolean, pgTable } from "drizzle-orm/pg-core";
 
export const table = pgTable('table', {
	boolean: boolean('boolean')
});
 
CREATE TABLE IF NOT EXISTS "table" (
	"boolean" boolean,
);

text

text
Variable-length(unlimited) character string
Official PostrgreSQL docs (opens in a new tab)

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

import { text, pgTable } from "drizzle-orm/pg-core";
 
export const table = pgTable('table', {
  text: text('text')
});
 
// will be inferred as text: "value1" | "value2" | null
text: text('text', { enum: ["value1", "value2"] })
CREATE TABLE IF NOT EXISTS "table" (
	"text" text,
);

varchar

character varying(n) varchar(n)
Variable-length character string, can store strings up to n characters(not bytes)
Official PostrgreSQL docs (opens in a new tab)

You can define { enum: ["value1", "value2"] } config to infer insert and select types, it won't check runtime values
length parameter is optional according to PostgreSQL docs

import { varchar, pgTable } from "drizzle-orm/pg-core";
 
export const table = pgTable('table', {
  varchar1: varchar('varchar1'),
  varchar1: varchar('varchar2', { length: 256 }),
});
 
// will be inferred as text: "value1" | "value2" | null
varchar: varchar('varchar', { enum: ["value1", "value2"] }),
CREATE TABLE IF NOT EXISTS "table" (
	"varchar1" varchar,
	"varchar2" varchar(256),
);

char

character(n) char(n)
Fixed-length, blank padded character string, can store strings up to n characters(not bytes)
Official PostrgreSQL docs (opens in a new tab)

You can define { enum: ["value1", "value2"] } config to infer insert and select types, it won't check runtime values
length parameter is optional according to PostgreSQL docs

import { char, pgTable } from "drizzle-orm/pg-core";
 
export const table = pgTable('table', {
  char1: char('char1'),
  char2: char('char2', { length: 256 }),
});
 
// will be inferred as text: "value1" | "value2" | null
char: char('char', { enum: ["value1", "value2"] }),
CREATE TABLE IF NOT EXISTS "table" (
	"char1" char,
	"char2" char(256),
);

numeric

numeric decimal
Exact numeric of selectable precision. Can store numbers with a very large number of digits, up to 131072 digits before the decimal point and up to 16383 digits after the decimal point
Official PostrgreSQL docs (opens in a new tab)

import { numeric, pgTable } from "drizzle-orm/pg-core";
 
export const table = pgTable('table', {
  numeric1: numeric('numeric1'),
  numeric2: numeric('numeric2', { precision: 100 }),
  numeric3: numeric('numeric3', { precision: 100, scale: 20 }),
});
CREATE TABLE IF NOT EXISTS "table" (
	"numeric1" numeric,
	"numeric2" numeric(100),
	"numeric3" numeric(100, 20),
);

decimal

Is an alias of numeric

real

real float4
Single precision floating-point number (4 bytes)
Official PostrgreSQL docs (opens in a new tab)

import { sql } from "drizzle-orm";
import { real, pgTable } from "drizzle-orm/pg-core";  
 
const table = pgTable('table', {
	real1: real('real1'),
	real2: real('real2').default(10.10),
	real2: real('real2').default(sql`'10.10'::real`),
});
CREATE TABLE IF NOT EXISTS "table" (
	"real1" real,
	"real2" real default 10.10,
	"real2" real default '10.10'::real
);

double precision

double precision float8
Double precision floating-point number (8 bytes)
Official PostrgreSQL docs (opens in a new tab)

import { sql } from "drizzle-orm";
import { doublePrecision, pgTable } from "drizzle-orm/pg-core";
 
const table = pgTable('table', {
	double1: doublePrecision('double1'),
	double2: doublePrecision('double2').default(10.10),
	double3: doublePrecision('double3').default(sql`'10.10'::double precision`),
});
CREATE TABLE IF NOT EXISTS "table" (
	"double1" double precision,
	"double2" double precision default 10.10,
	"double3" double precision default '10.10'::double precision,
);

json

json
Textual JSON data, as specified in RFC 7159 (opens in a new tab)
Official PostrgreSQL docs (opens in a new tab)

import { sql } from "drizzle-orm";
import { json, pgTable } from "drizzle-orm/pg-core";
 
const table = pgTable('table', {
	json1: json('json1'),
	json2: json('json2').default({ foo: "bar" }),
	json3: json('json3').default(sql`'{foo: "bar"}'::json`),
});
CREATE TABLE IF NOT EXISTS "table" (
	"json1" json,
	"json2" json default '{"foo": "bar"}'::json,
	"json3" json default '{"foo": "bar"}'::json,
);

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 infered as { foo: string }
json: json('json').$type<{ foo: string }>();
 
// will be infered as string[]
json: json('json').$type<string[]>();
 
// won't compile
json: json('json').$type<string[]>().default({});

jsonb

jsonb
Binary JSON data, decomposed
Official PostrgreSQL docs (opens in a new tab)

import { jsonb, pgTable } from "drizzle-orm/pg-core";
 
const table = pgTable('table', {
	jsonb1: jsonb('jsonb1'),
	jsonb2: jsonb('jsonb2').default({ foo: "bar" }),
	jsonb3: jsonb('jsonb3').default(sql`'{foo: "bar"}'::jsonb`),
});
CREATE TABLE IF NOT EXISTS "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 infered as { foo: string }
jsonb: jsonb('jsonb').$type<{ foo: string }>();
 
// will be infered as string[]
jsonb: jsonb('jsonb').$type<string[]>();
 
// won't compile
jsonb: jsonb('jsonb').$type<string[]>().default({});

time

time timetz time with timezone time without timezone
Time of day with or without time zone
PostgreSQL docs (opens in a new tab)

import { time, pgTable } from "drizzle-orm/pg-core";
 
const table = pgTable('table', {
  time1: time('time1'),
  time2: time('time2', { withTimezone: true }),
  time3: time('time3', { precision: 6 }),
	time4: time('time4', { precision: 6, withTimezone: true })
});
CREATE TABLE IF NOT EXISTS "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
Date and time with or without time zone
PostgreSQL docs (opens in a new tab)

import { sql } from "drizzle-orm";
import { timestamp, pgTable } from "drizzle-orm/pg-core";
 
const table = pgTable('table', {
  timestamp1: timestamp('timestamp1'),
	timestamp2: timestamp('timestamp2', { precision: 6, withTimezone: true }),
	timestamp3: timestamp('timestamp3').defaultNow(),
	timestamp4: timestamp('timestamp4').default(sql`now()`),
});
CREATE TABLE IF NOT EXISTS "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('timestamp', { mode: "date" }),
 
// will infer as string
timestamp: timestamp('timestamp', { mode: "string" }),

date

date
Calendar date (year, month, day)
PostgreSQL docs (opens in a new tab)

import { date, pgTable } from "drizzle-orm/pg-core";
 
const table = pgTable('table', {
	date: date('date'),
});
CREATE TABLE IF NOT EXISTS "table" (
	"date" date,
);

You can specify either date or string infer modes

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

interval

interval
Time span
PostgreSQL docs (opens in a new tab)

import { interval, pgTable } from "drizzle-orm/pg-core";
 
const table = pgTable('table', {
	interval1: interval('interval1'),
  interval2: interval('interval2', { fields: 'day' }),
  interval3: interval('interval3', { fields: 'month' , precision: 6 }),
});
 
CREATE TABLE IF NOT EXISTS "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.

PostgreSQL docs (opens in a new tab)

import { pgEnum, pgTable } from "drizzle-orm/pg-core";
 
const moodEnum = pgEnum('mood', ['sad', 'ok', 'happy']);
 
const table = pgTable('table', {
  mood: moodEnum('mood'),
});
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
 
CREATE TABLE IF NOT EXISTS "table" (
	"mood" mood,
);

Constraints & defaults

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.

import { sql } from "drizzle-orm";
import { integer, pgTable, uuid } from "drizzle-orm/pg-core";
 
const table = pgTable('table', {
	integer1: integer('integer1').default(42),
	integer2: integer('integer2').default(sql`'42'::integer`),
	uuid1: uuid('uuid1').defaultRandom(),
	uuid2: uuid('uuid2').default(sql`get_random_uuid()`),
});
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()
);

Not null

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

import { integer, pgTable } from "drizzle-orm/pg-core";
 
const table = pgTable('table', {
	integer: integer('integer').notNull(),
});
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.

import { integer, pgTable } from "drizzle-orm/pg-core";
 
const table = pgTable('table', {
	id: serial('id').primaryKey(),
});
CREATE TABLE IF NOT EXISTS "table" (
	"integer" serial PRIMARY KEY NOT NULL,
);
Schema declarationMySQL
GitHubGitHub (opens in a new tab)DiscordDiscord (opens in a new tab)