PostgreSQL Column Types

Numeric Types

integer()

PostgreSQL INTEGER type for whole numbers.

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

const products = pgTable('products', {
  stock: integer('stock').notNull().default(0),
  views: integer('views'),
});

TypeScript type: number SQL type: INTEGER

serial()

Auto-incrementing integer column.

import { pgTable, serial } from 'drizzle-orm/pg-core';

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

TypeScript type: number SQL type: SERIAL (equivalent to INTEGER with DEFAULT nextval('sequence')) Note: Automatically has .notNull() and .default() applied.

bigint()

PostgreSQL BIGINT type for large integers.

import { pgTable, bigint } from 'drizzle-orm/pg-core';

const analytics = pgTable('analytics', {
  // Mode: 'number' (default, values up to 2^53)
  views: bigint('views', { mode: 'number' }),
  
  // Mode: 'bigint' (for values beyond 2^53)
  veryLargeNumber: bigint('very_large', { mode: 'bigint' }),
});

config.mode

'number' | 'bigint'

required

'number': Use JavaScript number (safe up to 2^53)
'bigint': Use JavaScript bigint for larger values

TypeScript type: number or bigint (based on mode) SQL type: BIGINT

bigserial()

Auto-incrementing bigint column.

import { pgTable, bigserial } from 'drizzle-orm/pg-core';

const logs = pgTable('logs', {
  id: bigserial('id', { mode: 'number' }).primaryKey(),
  // or
  id2: bigserial('id2', { mode: 'bigint' }).primaryKey(),
});

config.mode

'number' | 'bigint'

required

Determines whether to use JavaScript number or bigint

SQL type: BIGSERIAL

smallint()

PostgreSQL SMALLINT type for small integers (-32,768 to 32,767).

import { pgTable, smallint } from 'drizzle-orm/pg-core';

const settings = pgTable('settings', {
  priority: smallint('priority'),
});

TypeScript type: number SQL type: SMALLINT

smallserial()

Auto-incrementing smallint column.

import { pgTable, smallserial } from 'drizzle-orm/pg-core';

const items = pgTable('items', {
  id: smallserial('id').primaryKey(),
});

SQL type: SMALLSERIAL

numeric()

PostgreSQL NUMERIC/DECIMAL type for exact decimal values.

import { pgTable, numeric } from 'drizzle-orm/pg-core';

const products = pgTable('products', {
  // Default: string mode
  price: numeric('price', { precision: 10, scale: 2 }),
  
  // Number mode
  weight: numeric('weight', { precision: 8, scale: 3, mode: 'number' }),
  
  // Bigint mode for very large precise numbers
  largeValue: numeric('large', { precision: 30, scale: 10, mode: 'bigint' }),
});

config.precision

number

Total number of digits

config.scale

number

Number of digits after decimal point

config.mode

'string' | 'number' | 'bigint'

'string' (default): Returns string for exact precision
'number': Converts to JavaScript number
'bigint': Converts to JavaScript bigint

TypeScript type: string, number, or bigint (based on mode) SQL type: NUMERIC(precision, scale) Alias: decimal() - same as numeric()

real()

PostgreSQL REAL type for floating-point numbers (single precision).

import { pgTable, real } from 'drizzle-orm/pg-core';

const measurements = pgTable('measurements', {
  temperature: real('temperature'),
});

TypeScript type: number SQL type: REAL

doublePrecision()

PostgreSQL DOUBLE PRECISION type for floating-point numbers.

import { pgTable, doublePrecision } from 'drizzle-orm/pg-core';

const coordinates = pgTable('coordinates', {
  latitude: doublePrecision('latitude'),
  longitude: doublePrecision('longitude'),
});

TypeScript type: number SQL type: DOUBLE PRECISION

String Types

varchar()

PostgreSQL VARCHAR type for variable-length strings.

import { pgTable, varchar } from 'drizzle-orm/pg-core';

const users = pgTable('users', {
  // With length limit
  name: varchar('name', { length: 255 }),
  
  // With enum values
  status: varchar('status', { 
    length: 20,
    enum: ['active', 'inactive', 'pending'] as const 
  }),
});

config.length

number

Maximum string length. If omitted, creates VARCHAR without length limit.

config.enum

readonly string[]

TypeScript enum values for type safety

TypeScript type: string SQL type: VARCHAR or VARCHAR(length)

char()

PostgreSQL CHAR type for fixed-length strings.

import { pgTable, char } from 'drizzle-orm/pg-core';

const codes = pgTable('codes', {
  countryCode: char('country_code', { length: 2 }),
});

config.length

number

Fixed string length

TypeScript type: string SQL type: CHAR(length)

text()

PostgreSQL TEXT type for unlimited-length strings.

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

const posts = pgTable('posts', {
  content: text('content'),
  
  // With enum values
  category: text('category', { 
    enum: ['tech', 'lifestyle', 'business'] as const 
  }),
});

config.enum

readonly string[]

TypeScript enum values for type safety

TypeScript type: string SQL type: TEXT

Date and Time Types

timestamp()

PostgreSQL TIMESTAMP type for date and time.

import { pgTable, timestamp } from 'drizzle-orm/pg-core';

const posts = pgTable('posts', {
  // Default: Date object, no timezone
  createdAt: timestamp('created_at').defaultNow(),
  
  // With timezone
  updatedAt: timestamp('updated_at', { withTimezone: true }),
  
  // String mode
  publishedAt: timestamp('published_at', { mode: 'string' }),
  
  // With precision
  preciseTiming: timestamp('precise', { precision: 6 }),
});

config.mode

'date' | 'string'

'date' (default): Use JavaScript Date objects
'string': Keep as ISO 8601 string

config.withTimezone

boolean

If true, creates TIMESTAMP WITH TIME ZONE. Default: false

config.precision

0 | 1 | 2 | 3 | 4 | 5 | 6

Fractional seconds precision (0-6 digits)

TypeScript type: Date or string (based on mode) SQL type: TIMESTAMP or TIMESTAMP WITH TIME ZONE

date()

PostgreSQL DATE type for dates without time.

import { pgTable, date } from 'drizzle-orm/pg-core';

const events = pgTable('events', {
  // String mode (default): 'YYYY-MM-DD'
  eventDate: date('event_date'),
  
  // Date mode: JavaScript Date object
  birthDate: date('birth_date', { mode: 'date' }),
});

config.mode

'date' | 'string'

'string' (default): ISO date string format
'date': JavaScript Date object

TypeScript type: string or Date (based on mode) SQL type: DATE

time()

PostgreSQL TIME type for time without date.

import { pgTable, time } from 'drizzle-orm/pg-core';

const schedules = pgTable('schedules', {
  startTime: time('start_time'),
  endTime: time('end_time', { withTimezone: true, precision: 3 }),
});

config.withTimezone

boolean

If true, creates TIME WITH TIME ZONE

config.precision

0 | 1 | 2 | 3 | 4 | 5 | 6

Fractional seconds precision

TypeScript type: string SQL type: TIME or TIME WITH TIME ZONE

interval()

PostgreSQL INTERVAL type for time intervals.

import { pgTable, interval } from 'drizzle-orm/pg-core';

const tasks = pgTable('tasks', {
  duration: interval('duration'),
  timeout: interval('timeout', { fields: 'hour to second' }),
});

config.fields

string

Interval fields specification (e.g., ‘day’, ‘hour to second’)

config.precision

0 | 1 | 2 | 3 | 4 | 5 | 6

Fractional seconds precision

TypeScript type: string SQL type: INTERVAL

Boolean Type

boolean()

PostgreSQL BOOLEAN type.

import { pgTable, boolean } from 'drizzle-orm/pg-core';

const users = pgTable('users', {
  isActive: boolean('is_active').default(true),
  emailVerified: boolean('email_verified').notNull().default(false),
});

TypeScript type: boolean SQL type: BOOLEAN

JSON Types

json()

PostgreSQL JSON type for JSON data.

import { pgTable, json } from 'drizzle-orm/pg-core';

const users = pgTable('users', {
  metadata: json('metadata'),
  settings: json<{ theme: string; locale: string }>('settings'),
});

// Usage
await db.insert(users).values({
  metadata: { key: 'value' },
  settings: { theme: 'dark', locale: 'en' },
});

TypeScript type: unknown (use generic type parameter for type safety) SQL type: JSON

jsonb()

PostgreSQL JSONB type for binary JSON (more efficient, supports indexing).

import { pgTable, jsonb } from 'drizzle-orm/pg-core';

type Metadata = {
  tags: string[];
  views: number;
};

const posts = pgTable('posts', {
  metadata: jsonb<Metadata>('metadata'),
});

// Usage
await db.insert(posts).values({
  metadata: { tags: ['tech'], views: 0 },
});

TypeScript type: unknown (use generic type parameter for type safety) SQL type: JSONB Note: JSONB is generally preferred over JSON for better performance and indexing support.

UUID Type

uuid()

PostgreSQL UUID type.

import { pgTable, uuid } from 'drizzle-orm/pg-core';

const users = pgTable('users', {
  // Manual UUID
  id: uuid('id').primaryKey(),
  
  // Auto-generate with gen_random_uuid()
  id2: uuid('id2').defaultRandom().primaryKey(),
});

TypeScript type: string SQL type: UUID Methods:

.defaultRandom(): Sets default to gen_random_uuid()

Network Types

inet()

PostgreSQL INET type for IPv4 or IPv6 addresses.

import { pgTable, inet } from 'drizzle-orm/pg-core';

const logs = pgTable('logs', {
  ipAddress: inet('ip_address'),
});

TypeScript type: string SQL type: INET

cidr()

PostgreSQL CIDR type for network addresses.

import { pgTable, cidr } from 'drizzle-orm/pg-core';

const networks = pgTable('networks', {
  network: cidr('network'),
});

TypeScript type: string SQL type: CIDR

macaddr()

PostgreSQL MACADDR type for MAC addresses.

import { pgTable, macaddr } from 'drizzle-orm/pg-core';

const devices = pgTable('devices', {
  mac: macaddr('mac'),
});

TypeScript type: string SQL type: MACADDR

macaddr8()

PostgreSQL MACADDR8 type for MAC addresses (EUI-64 format).

import { pgTable, macaddr8 } from 'drizzle-orm/pg-core';

const devices = pgTable('devices', {
  mac: macaddr8('mac'),
});

TypeScript type: string SQL type: MACADDR8

Geometric Types

point()

PostgreSQL POINT type for geometric points.

import { pgTable, point } from 'drizzle-orm/pg-core';

type Point = { x: number; y: number };

const locations = pgTable('locations', {
  position: point<Point>('position'),
});

TypeScript type: Configurable via generic SQL type: POINT

line()

PostgreSQL LINE type.

import { pgTable, line } from 'drizzle-orm/pg-core';

const geometry = pgTable('geometry', {
  line: line<{ a: number; b: number; c: number }>('line'),
});

SQL type: LINE

Enum Types

pgEnum()

Defines a PostgreSQL enum type.

import { pgEnum, pgTable, serial } from 'drizzle-orm/pg-core';

// Define enum
export const roleEnum = pgEnum('role', ['admin', 'user', 'guest']);

// Use in table
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  role: roleEnum('role').default('user'),
});

name

string

required

Enum type name in database

values

readonly string[]

required

Array of possible values

TypeScript type: Union of enum values SQL type: Custom ENUM type

Custom Types

customType()

Defines a custom column type with custom serialization.

import { customType } from 'drizzle-orm/pg-core';

const bytea = customType<{ data: Buffer; notNull: false; default: false }>({
  dataType() {
    return 'bytea';
  },
  toDriver(value: Buffer): string {
    return value.toString('hex');
  },
  fromDriver(value: string): Buffer {
    return Buffer.from(value, 'hex');
  },
});

const files = pgTable('files', {
  data: bytea('data'),
});

Column Modifiers

All column types support these modifiers:

.notNull()

Marks column as NOT NULL.

name: varchar('name', { length: 255 }).notNull()

.default()

Sets a default value.

status: varchar('status').default('pending')
createdAt: timestamp('created_at').defaultNow()
count: integer('count').default(0)

.primaryKey()

Marks column as primary key.

id: serial('id').primaryKey()

.unique()

Adds unique constraint.

email: varchar('email', { length: 255 }).unique()

.references()

Adds foreign key reference.

userId: integer('user_id').references(() => users.id)

// With options
userId: integer('user_id').references(() => users.id, { 
  onDelete: 'cascade',
  onUpdate: 'cascade',
})

.$type()

Overrides TypeScript type without changing runtime behavior.

metadata: json('metadata').$type<{ key: string; value: number }>()

PostgreSQL

MySQL

SQLite

Core APIs

Drizzle Kit

​Numeric Types

​integer()

​serial()

​bigint()

​bigserial()

​smallint()

​smallserial()

​numeric()

​real()

​doublePrecision()

​String Types

​varchar()

​char()

​text()

​Date and Time Types

​timestamp()

​date()

​time()

​interval()

​Boolean Type

​boolean()

​JSON Types

​json()

​jsonb()

​UUID Type

​uuid()

​Network Types

​inet()

​cidr()

​macaddr()

​macaddr8()

​Geometric Types

​point()

​line()

​Enum Types

​pgEnum()

​Custom Types

​customType()

​Column Modifiers

​.notNull()

​.default()

​.primaryKey()

​.unique()

​.references()

​.$type()

Build docs developers (and LLMs) love

Numeric Types

integer()

serial()

bigint()

bigserial()

smallint()

smallserial()

numeric()

real()

doublePrecision()

String Types

varchar()

char()

text()

Date and Time Types

timestamp()

date()

time()

interval()

Boolean Type

boolean()

JSON Types

json()

jsonb()

UUID Type

uuid()

Network Types

inet()

cidr()

macaddr()

macaddr8()

Geometric Types

point()

line()

Enum Types

pgEnum()

Custom Types

customType()

Column Modifiers

.notNull()

.default()

.primaryKey()

.unique()

.references()

.$type()