Columns

Define type-safe columns with Drizzle ORM for PostgreSQL, MySQL, and SQLite databases.

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

1. .notNull()

   Makes the column required (NOT NULL constraint)

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

   Sets a default value for the column

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

   Marks the column as the primary key

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

   Adds a unique constraint

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

   Creates a foreign key reference

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

PostgreSQL column types

Integer types

smallint

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

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

Serial (auto-increment) types

serial

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

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

Text types

text

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'] })

Numeric types

real

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

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

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

timestamp

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

JSON types

json

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

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

Network types

inet

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

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

Other PostgreSQL types

uuid

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

// UUID type
id: uuid('id').defaultRandom().primaryKey()

MySQL column types

Integer types

tinyint

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

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

Serial type

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

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

String types

varchar

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

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

Numeric types

float

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

rating: float('rating')

Date and time types

datetime

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

createdAt: datetime('created_at')

Other MySQL types

boolean

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

isActive: boolean('is_active')

SQLite column types

SQLite uses a flexible type system with type affinities:

integer

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

Custom column types

Create custom column types for all databases:

PostgreSQL

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

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 }