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 PostgreSQL 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 PostgreSQL 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 PostgreSQL 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 PostgreSQL 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 PostgreSQL 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 PostgreSQL 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 PostgreSQL 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 PostgreSQL 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 PostgreSQL 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 PostgreSQL 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 PostgreSQL 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 PostgreSQL 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";
export const moodEnum = pgEnum('mood', ['sad', 'ok', 'happy']);
export const table = pgTable('table', {
mood: moodEnum('mood'),
});
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE IF NOT EXISTS "table" (
"mood" mood,
);
Customizing column 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 = pgTable('users', {
id: serial('id').$type<UserId>().primaryKey(),
jsonField: json('json_field').$type<Data>(),
});
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`gen_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()
);
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
Note: This value does not affect the drizzle-kit behavior, it is only used at runtime in drizzle-orm
import { text, pgTable } from "drizzle-orm/pg-core";
import { createId } from '@paralleldrive/cuid2';
const table = pgTable('table', {
id: text('id').$defaultFn(() => createId()),
});
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,
);