Columns

Columns define the structure and data types of your tables. Drizzle provides database-specific column types with full TypeScript inference.

Column modifiers

All column types support these common modifiers:

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

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  bio: text('bio').default(''),
  status: text('status').default('active'),
});

Available modifiers

.notNull()

Makes the column required (NOT NULL constraint)

name: text('name').notNull()

.default()

Sets a default value for the column

status: text('status').default('active')

.primaryKey()

Marks the column as the primary key

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

.unique()

Adds a unique constraint

email: text('email').unique()

.references()

Creates a foreign key reference

authorId: integer('author_id').references(() => users.id)

PostgreSQL column types

Integer types

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

// 16-bit integer (-32,768 to 32,767)
age: smallint('age')

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

// 32-bit integer (-2,147,483,648 to 2,147,483,647)
count: integer('count')

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

// 64-bit integer
views: bigint('views', { mode: 'number' })
// mode: 'number' | 'bigint'

Serial (auto-increment) types

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

// Auto-incrementing 32-bit integer
id: serial('id').primaryKey()

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

// Auto-incrementing 64-bit integer
id: bigserial('id', { mode: 'number' }).primaryKey()

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

// Auto-incrementing 16-bit integer
id: smallserial('id').primaryKey()

Text types

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

// Variable unlimited length
description: text('description')

// With enum values for type safety
status: text('status', { enum: ['active', 'inactive'] })

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

// Variable-length with limit
email: varchar('email', { length: 255 })

// With enum values
role: varchar('role', { length: 50, enum: ['admin', 'user'] })

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

// Fixed-length
countryCode: char('country_code', { length: 2 })

Numeric types

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

// Single precision floating point (4 bytes)
temperature: real('temperature')

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

// Double precision floating point (8 bytes)
price: doublePrecision('price')

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

// Exact numeric with precision and scale
amount: numeric('amount', { precision: 10, scale: 2 })

Boolean type

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

isActive: boolean('is_active').default(true)
isVerified: boolean('is_verified').notNull().default(false)

Date and time types

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

// Returns Date object
createdAt: timestamp('created_at').defaultNow()

// With timezone
updatedAt: timestamp('updated_at', { withTimezone: true })

// With precision (0-6)
publishedAt: timestamp('published_at', { precision: 3 })

// String mode
createdAt: timestamp('created_at', { mode: 'string' })

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

// Date without time
birthDate: date('birth_date')

// String mode
birthDate: date('birth_date', { mode: 'string' })

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

// Time without date
officehours: time('office_hours')

// With precision and timezone
startTime: time('start_time', { precision: 3, withTimezone: true })

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

// Time interval
duration: interval('duration')

JSON types

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

// JSON data (stored as text)
metadata: json('metadata').$type<{ key: string; value: number }>()

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

// Binary JSON (more efficient, supports indexing)
settings: jsonb('settings').$type<{ theme: string; notifications: boolean }>()

Network types

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

// IPv4 or IPv6 address
ipAddress: inet('ip_address')

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

// Network address
network: cidr('network')

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

// MAC address
macAddress: macaddr('mac_address')

MySQL column types

Integer types

import { tinyint } from 'drizzle-orm/mysql-core';

// 8-bit integer
status: tinyint('status')

import { smallint } from 'drizzle-orm/mysql-core';

// 16-bit integer
count: smallint('count')

import { int } from 'drizzle-orm/mysql-core';

// 32-bit integer
quantity: int('quantity')

import { mediumint } from 'drizzle-orm/mysql-core';

// 24-bit integer
value: mediumint('value')

import { bigint } from 'drizzle-orm/mysql-core';

// 64-bit integer
views: bigint('views', { mode: 'number' })

Serial type

import { serial } from 'drizzle-orm/mysql-core';

// Auto-increment BIGINT UNSIGNED
id: serial('id').primaryKey()

String types

import { varchar } from 'drizzle-orm/mysql-core';

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

import { text, mediumtext, longtext, tinytext } from 'drizzle-orm/mysql-core';

description: text('description')
Content: mediumtext('content')
article: longtext('article')
snippet: tinytext('snippet')

import { char } from 'drizzle-orm/mysql-core';

code: char('code', { length: 10 })

Numeric types

import { float } from 'drizzle-orm/mysql-core';

rating: float('rating')

import { double } from 'drizzle-orm/mysql-core';

price: double('price')

import { decimal } from 'drizzle-orm/mysql-core';

amount: decimal('amount', { precision: 10, scale: 2 })

Date and time types

import { datetime } from 'drizzle-orm/mysql-core';

createdAt: datetime('created_at')

import { timestamp } from 'drizzle-orm/mysql-core';

updatedAt: timestamp('updated_at').defaultNow()

import { date } from 'drizzle-orm/mysql-core';

birthDate: date('birth_date')

import { year } from 'drizzle-orm/mysql-core';

graduationYear: year('graduation_year')

Other MySQL types

import { boolean } from 'drizzle-orm/mysql-core';

isActive: boolean('is_active')

import { json } from 'drizzle-orm/mysql-core';

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

import { binary, varbinary } from 'drizzle-orm/mysql-core';

hash: binary('hash', { length: 32 })
data: varbinary('data', { length: 255 })

import { mysqlEnum } from 'drizzle-orm/mysql-core';

role: mysqlEnum('role', ['admin', 'user', 'guest'])

SQLite column types

SQLite uses a flexible type system with type affinities:

import { integer } from 'drizzle-orm/sqlite-core';

// Integer storage
id: integer('id').primaryKey({ autoIncrement: true })
count: integer('count')

// Boolean mode
isActive: integer('is_active', { mode: 'boolean' })

// Timestamp mode
createdAt: integer('created_at', { mode: 'timestamp' })
createdAtMs: integer('created_at_ms', { mode: 'timestamp_ms' })

import { real } from 'drizzle-orm/sqlite-core';

// Floating point
price: real('price')

import { text } from 'drizzle-orm/sqlite-core';

// Text storage
name: text('name')
email: text('email', { length: 255 })

// JSON mode
metadata: text('metadata', { mode: 'json' }).$type<{ key: string }>()

// Enum values
status: text('status', { enum: ['active', 'inactive'] })

import { blob } from 'drizzle-orm/blob-core';

// Binary data
avatar: blob('avatar', { mode: 'buffer' })
// mode: 'buffer' | 'json'

import { numeric } from 'drizzle-orm/sqlite-core';

// Numeric storage (TEXT affinity for precision)
amount: numeric('amount')

Custom column types

Create custom column types for all databases:

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

const customText = customType<{ data: string }>{
  dataType() {
    return 'text';
  },
  toDriver(value: string): string {
    return value.toLowerCase();
  },
  fromDriver(value: string): string {
    return value.toUpperCase();
  },
});

export const users = pgTable('users', {
  name: customText('name'),
});

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

const customInt = customType<{ data: number }>{
  dataType() {
    return 'int';
  },
  toDriver(value: number): number {
    return value * 100;
  },
  fromDriver(value: number): number {
    return value / 100;
  },
});

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

const customBlob = customType<{ data: Buffer }>{
  dataType() {
    return 'blob';
  },
});

Type inference

Drizzle automatically infers TypeScript types from your schema:

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

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull(),
  isActive: boolean('is_active').default(true),
});

// Inferred type
type User = typeof users.$inferSelect;
// { id: number; name: string; email: string; isActive: boolean | null }

type NewUser = typeof users.$inferInsert;
// { id?: number; name: string; email: string; isActive?: boolean | null }

Get Started

Core Concepts

Database Drivers

Query Builders

Schema Definition

Advanced Features

Ecosystem

Guides

Column modifiers

Available modifiers

PostgreSQL column types

Integer types

Serial (auto-increment) types

Text types

Numeric types

Boolean type

Date and time types

JSON types

Network types

Other PostgreSQL types

MySQL column types

Integer types

Serial type

String types

Numeric types

Date and time types

Other MySQL types

SQLite column types

Custom column types

Type inference

​Column modifiers

​Available modifiers

​PostgreSQL column types

​Integer types

​Serial (auto-increment) types

​Text types

​Numeric types

​Boolean type

​Date and time types

​JSON types

​Network types

​Other PostgreSQL types

​MySQL column types

​Integer types

​Serial type

​String types

​Numeric types

​Date and time types

​Other MySQL types

​SQLite column types

​Custom column types

​Type inference

Column modifiers

Available modifiers

PostgreSQL column types

Integer types

Serial (auto-increment) types

Text types

Numeric types

Boolean type

Date and time types

JSON types

Network types

Other PostgreSQL types

MySQL column types

Integer types

Serial type

String types

Numeric types

Date and time types

Other MySQL types

SQLite column types

Custom column types

Type inference