or run

npx @tessl/cli init

Version

Tile

Overview

Evals

Files

docs

examples

edge-cases.md real-world-scenarios.md

guides

quick-start.md

reference

advanced-features.md drivers.md mysql.md postgresql.md query-building.md relations.md schema-definition.md sqlite.md

index.md

tile.json

Schema Definition

Comprehensive API reference for defining database schemas in Drizzle ORM, including tables, columns, constraints, indexes, views, and enums for PostgreSQL, MySQL, and SQLite.

Table Definitions
Column Types
Column Modifiers
Constraints
Indexes
Schemas and Namespaces
Views
Enums
Custom Types
Sequences
Type Inference

Table Definitions

PostgreSQL Tables

Define PostgreSQL tables with typed columns and constraints.

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

function pgTable<
  TTableName extends string,
  TColumnsMap extends Record<string, PgColumnBuilderBase>,
>(
  name: TTableName,
  columns: TColumnsMap | ((columnTypes: PgColumnsBuilders) => TColumnsMap),
  extraConfig?: (self: BuildExtraConfigColumns<TTableName, TColumnsMap, 'pg'>) =>
    Array<IndexBuilder | CheckBuilder | ForeignKeyBuilder | PrimaryKeyBuilder | UniqueConstraintBuilder | PgPolicy>
): PgTableWithColumns<{
  name: TTableName;
  schema: undefined;
  columns: BuildColumns<TTableName, TColumnsMap, 'pg'>;
  dialect: 'pg';
}>;

Basic Usage:

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

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

With Constraints and Indexes:

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

export const posts = pgTable('posts', {
  id: integer('id').primaryKey(),
  userId: integer('user_id').notNull(),
  title: text('title').notNull(),
  slug: text('slug').notNull(),
}, (table) => [
  index('user_idx').on(table.userId),
  uniqueIndex('slug_idx').on(table.slug),
]);

Custom Table Name Function:

function pgTableCreator(
  customizeTableName: (name: string) => string
): PgTableFn;

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

const pgTable = pgTableCreator((name) => `myapp_${name}`);

// Creates table named "myapp_users"
export const users = pgTable('users', {
  id: integer('id').primaryKey(),
});

Enable Row-Level Security:

interface PgTableWithColumns<T> {
  enableRLS(): Omit<PgTableWithColumns<T>, 'enableRLS'>;
}

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  name: text('name'),
}).enableRLS();

Column Types Callback:

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

export const users = pgTable('users', (t) => ({
  id: t.integer('id').primaryKey(),
  name: t.text('name').notNull(),
  email: t.varchar('email', { length: 255 }),
}));

MySQL Tables

Define MySQL tables with typed columns and constraints.

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

function mysqlTable<
  TTableName extends string,
  TColumnsMap extends Record<string, MySqlColumnBuilderBase>,
>(
  name: TTableName,
  columns: TColumnsMap | ((columnTypes: MySqlColumnsBuilders) => TColumnsMap),
  extraConfig?: (self: BuildExtraConfigColumns<TTableName, TColumnsMap, 'mysql'>) =>
    Array<IndexBuilder | CheckBuilder | ForeignKeyBuilder | PrimaryKeyBuilder | UniqueConstraintBuilder>
): MySqlTableWithColumns<{
  name: TTableName;
  schema: undefined;
  columns: BuildColumns<TTableName, TColumnsMap, 'mysql'>;
  dialect: 'mysql';
}>;

Usage:

import { mysqlTable, int, varchar, timestamp } from 'drizzle-orm/mysql-core';

export const users = mysqlTable('users', {
  id: int('id').primaryKey().autoincrement(),
  name: varchar('name', { length: 255 }).notNull(),
  email: varchar('email', { length: 255 }).notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

Custom Table Name Function:

function mysqlTableCreator(
  customizeTableName: (name: string) => string
): MySqlTableFn;

SQLite Tables

Define SQLite tables with typed columns and constraints.

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

function sqliteTable<
  TTableName extends string,
  TColumnsMap extends Record<string, SQLiteColumnBuilderBase>,
>(
  name: TTableName,
  columns: TColumnsMap | ((columnTypes: SQLiteColumnsBuilders) => TColumnsMap),
  extraConfig?: (self: BuildExtraConfigColumns<TTableName, TColumnsMap, 'sqlite'>) =>
    Array<IndexBuilder | CheckBuilder | ForeignKeyBuilder | PrimaryKeyBuilder | UniqueConstraintBuilder>
): SQLiteTableWithColumns<{
  name: TTableName;
  schema: undefined;
  columns: BuildColumns<TTableName, TColumnsMap, 'sqlite'>;
  dialect: 'sqlite';
}>;

Usage:

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

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
});

Custom Table Name Function:

function sqliteTableCreator(
  customizeTableName: (name: string) => string
): SQLiteTableFn;

Column Types

PostgreSQL Column Types

All PostgreSQL column types can be imported from drizzle-orm/pg-core.

Numeric Types

// Integer types
function smallint(name?: string): PgSmallIntBuilder;  // 16-bit integer (-32768 to 32767)
function integer(name?: string): PgIntegerBuilder;     // 32-bit integer
function bigint(name?: string, config: { mode: 'number' | 'bigint' }): PgBigInt53Builder | PgBigInt64Builder;  // 64-bit integer
function serial(name?: string): PgSerialBuilder;       // Auto-incrementing 32-bit integer
function smallserial(name?: string): PgSmallSerialBuilder;  // Auto-incrementing 16-bit integer
function bigserial(name?: string): PgBigSerialBuilder;      // Auto-incrementing 64-bit integer

// Floating-point types
function real(name?: string): PgRealBuilder;                 // Single precision (4 bytes)
function doublePrecision(name?: string): PgDoublePrecisionBuilder;  // Double precision (8 bytes)

// Exact numeric type
function numeric(name?: string, config?: {
  precision?: number;
  scale?: number;
}): PgNumericBuilder;  // Arbitrary precision decimal

Numeric Types Usage:

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

export const products = pgTable('products', {
  id: serial('id').primaryKey(),
  quantity: integer('quantity').notNull(),
  price: numeric('price', { precision: 10, scale: 2 }).notNull(),
  views: bigint('views', { mode: 'number' }),  // JavaScript number
  largeId: bigint('large_id', { mode: 'bigint' }),  // JavaScript BigInt
});

String Types

function text(name?: string, config?: {
  enum?: [string, ...string[]];
}): PgTextBuilder;

function varchar(name?: string, config?: {
  length?: number;
  enum?: [string, ...string[]];
}): PgVarcharBuilder;

function char(name?: string, config?: {
  length?: number;
}): PgCharBuilder;

String Types Usage:

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

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
  email: varchar('email', { length: 255 }).notNull(),
  role: text('role', { enum: ['admin', 'user', 'guest'] }),
  countryCode: char('country_code', { length: 2 }),
});

Date and Time Types

function date(name?: string, config?: {
  mode?: 'string' | 'date';
}): PgDateBuilder;

function time(name?: string, config?: {
  precision?: number;
  withTimezone?: boolean;
}): PgTimeBuilder;

function timestamp(name?: string, config?: {
  mode?: 'string' | 'date';
  precision?: number;
  withTimezone?: boolean;
}): PgTimestampBuilder;

function interval(name?: string, config?: {
  fields?: 'year' | 'month' | 'day' | 'hour' | 'minute' | 'second' |
           'year to month' | 'day to hour' | 'day to minute' | 'day to second' |
           'hour to minute' | 'hour to second' | 'minute to second';
  precision?: number;
}): PgIntervalBuilder;

Date/Time Types Usage:

import { pgTable, integer, date, time, timestamp, interval } from 'drizzle-orm/pg-core';

export const events = pgTable('events', {
  id: integer('id').primaryKey(),
  eventDate: date('event_date', { mode: 'date' }),
  eventTime: time('event_time', { precision: 3 }),
  createdAt: timestamp('created_at', { withTimezone: true, mode: 'date' }).defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow(),
  duration: interval('duration', { fields: 'hour to minute' }),
});

Boolean Type

function boolean(name?: string): PgBooleanBuilder;

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

JSON Types

function json<TData = unknown>(name?: string): PgJsonBuilder<TData>;
function jsonb<TData = unknown>(name?: string): PgJsonbBuilder<TData>;

JSON Types Usage:

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

type UserPreferences = {
  theme: 'light' | 'dark';
  notifications: boolean;
};

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  settings: json<UserPreferences>('settings'),
  metadata: jsonb('metadata'),  // Binary storage, indexable
});

UUID Type

function uuid(name?: string): PgUUIDBuilder;

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

export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),  // UUID v4
  name: text('name').notNull(),
});

Network Types

function inet(name?: string): PgInetBuilder;  // IPv4 or IPv6 host address
function cidr(name?: string): PgCidrBuilder;  // IPv4 or IPv6 network
function macaddr(name?: string): PgMacaddrBuilder;   // MAC address (6 bytes)
function macaddr8(name?: string): PgMacaddr8Builder; // MAC address (8 bytes)

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

export const devices = pgTable('devices', {
  id: integer('id').primaryKey(),
  ipAddress: inet('ip_address'),
  network: cidr('network'),
  macAddress: macaddr('mac_address'),
});

Geometric Types

function point(name?: string, config?: {
  mode?: 'xy' | 'tuple';
}): PgPointBuilder;

function line(name?: string, config?: {
  mode?: 'abc' | 'tuple';
}): PgLineBuilder;

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

export const locations = pgTable('locations', {
  id: integer('id').primaryKey(),
  coordinates: point('coordinates', { mode: 'xy' }),  // { x: number, y: number }
  path: line('path', { mode: 'tuple' }),  // [a, b, c]
});

Array Types

All column types support array variants via the .array() modifier.

interface PgColumnBuilder {
  array<TSize extends number | undefined = undefined>(
    size?: TSize
  ): PgArrayBuilder;
}

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

export const posts = pgTable('posts', {
  id: integer('id').primaryKey(),
  tags: text('tags').array(),
  dimensions: integer('dimensions').array(3),  // Fixed size array
  emails: varchar('emails', { length: 255 }).array().notNull(),
});

PostGIS Extension Types

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

function geometry<
  TType extends string = string,
  TMode extends 'tuple' | 'xy' = 'xy',
>(name?: string, config?: {
  type?: TType;  // 'point', 'linestring', 'polygon', 'multipoint', etc.
  mode?: TMode;
  srid?: number;
}): PgGeometryBuilder<TType, TMode>;

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

export const locations = pgTable('locations', {
  id: integer('id').primaryKey(),
  location: geometry('location', { type: 'point', mode: 'xy', srid: 4326 }),
  boundary: geometry('boundary', { type: 'polygon', mode: 'tuple' }),
});

pgvector Extension Types

import { vector, halfvec, bit, sparsevec } from 'drizzle-orm/pg-core/vector_extension';

function vector(name?: string, config: {
  dimensions: number;
}): PgVectorBuilder;

function halfvec(name?: string, config: {
  dimensions: number;
}): PgHalfvecBuilder;

function bit(name?: string, config: {
  dimensions: number;
}): PgBitBuilder;

function sparsevec(name?: string, config: {
  dimensions: number;
}): PgSparsevecBuilder;

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

export const embeddings = pgTable('embeddings', {
  id: integer('id').primaryKey(),
  content: text('content'),
  embedding: vector('embedding', { dimensions: 1536 }),
});

MySQL Column Types

All MySQL column types can be imported from drizzle-orm/mysql-core.

Numeric Types

// Integer types
function tinyint(name?: string, config?: {
  unsigned?: boolean;
}): MySqlTinyIntBuilder;  // 8-bit integer

function smallint(name?: string, config?: {
  unsigned?: boolean;
}): MySqlSmallIntBuilder;  // 16-bit integer

function mediumint(name?: string, config?: {
  unsigned?: boolean;
}): MySqlMediumIntBuilder;  // 24-bit integer

function int(name?: string, config?: {
  unsigned?: boolean;
}): MySqlIntBuilder;  // 32-bit integer

function bigint(name?: string, config: {
  mode: 'number' | 'bigint';
  unsigned?: boolean;
}): MySqlBigInt53Builder | MySqlBigInt64Builder;  // 64-bit integer

function serial(name?: string): MySqlSerialBuilder;  // Auto-incrementing BIGINT UNSIGNED

// Floating-point types
function float(name?: string, config?: {
  precision?: number;
  scale?: number;
  unsigned?: boolean;
}): MySqlFloatBuilder;

function double(name?: string, config?: {
  precision?: number;
  scale?: number;
  unsigned?: boolean;
}): MySqlDoubleBuilder;

function real(name?: string, config?: {
  precision?: number;
  scale?: number;
  unsigned?: boolean;
}): MySqlRealBuilder;

// Exact numeric type
function decimal(name?: string, config?: {
  precision?: number;
  scale?: number;
  unsigned?: boolean;
}): MySqlDecimalBuilder;

Numeric Types Usage:

import { mysqlTable, int, bigint, decimal, serial } from 'drizzle-orm/mysql-core';

export const products = mysqlTable('products', {
  id: serial('id').primaryKey(),
  quantity: int('quantity').notNull(),
  price: decimal('price', { precision: 10, scale: 2 }).notNull(),
  views: bigint('views', { mode: 'number' }),
});

String Types

function char(name?: string, config?: {
  length?: number;
  enum?: [string, ...string[]];
}): MySqlCharBuilder;

function varchar(name?: string, config: {
  length: number;
  enum?: [string, ...string[]];
}): MySqlVarcharBuilder;

function text(name?: string, config?: {
  enum?: [string, ...string[]];
}): MySqlTextBuilder<'text'>;

function text<T extends MySqlTextSize>(name?: string, config: {
  size: T;
  enum?: [string, ...string[]];
}): MySqlTextBuilder<T>;

type MySqlTextSize = 'tinytext' | 'text' | 'mediumtext' | 'longtext';

// Binary types
function binary(name?: string, config?: {
  length?: number;
}): MySqlBinaryBuilder;

function varbinary(name?: string, config: {
  length: number;
}): MySqlVarbinaryBuilder;

String Types Usage:

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

export const posts = mysqlTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content', { size: 'longtext' }),
  status: varchar('status', {
    length: 20,
    enum: ['draft', 'published', 'archived']
  }),
});

Date and Time Types

function date(name?: string, config?: {
  mode?: 'string' | 'date';
}): MySqlDateBuilder;

function datetime(name?: string, config?: {
  mode?: 'string' | 'date';
  fsp?: 0 | 1 | 2 | 3 | 4 | 5 | 6;  // Fractional seconds precision
}): MySqlDatetimeBuilder;

function time(name?: string, config?: {
  fsp?: 0 | 1 | 2 | 3 | 4 | 5 | 6;
}): MySqlTimeBuilder;

function timestamp(name?: string, config?: {
  mode?: 'string' | 'date';
  fsp?: 0 | 1 | 2 | 3 | 4 | 5 | 6;
}): MySqlTimestampBuilder;

function year(name?: string): MySqlYearBuilder;

Date/Time Types Usage:

import { mysqlTable, int, date, datetime, timestamp, year } from 'drizzle-orm/mysql-core';

export const events = mysqlTable('events', {
  id: int('id').primaryKey(),
  eventDate: date('event_date', { mode: 'date' }),
  eventTime: datetime('event_time', { mode: 'date', fsp: 3 }),
  createdAt: timestamp('created_at').defaultNow(),
  publishYear: year('publish_year'),
});

Boolean Type

function boolean(name?: string): MySqlBooleanBuilder;  // Stored as TINYINT(1)

export const users = mysqlTable('users', {
  id: int('id').primaryKey(),
  isActive: boolean('is_active').default(true),
});

JSON Type

function json<TData = unknown>(name?: string): MySqlJsonBuilder<TData>;

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

type UserPreferences = {
  theme: 'light' | 'dark';
  notifications: boolean;
};

export const users = mysqlTable('users', {
  id: int('id').primaryKey(),
  settings: json<UserPreferences>('settings'),
});

MySQL Enum Type

function mysqlEnum<T extends [string, ...string[]]>(
  name?: string,
  values: T,
  config?: object
): MySqlEnumBuilder<T>;

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

export const users = mysqlTable('users', {
  id: int('id').primaryKey(),
  role: mysqlEnum('role', ['admin', 'user', 'guest']).notNull().default('user'),
});

SQLite Column Types

All SQLite column types can be imported from drizzle-orm/sqlite-core.

SQLite has a flexible type system with storage classes: INTEGER, REAL, TEXT, BLOB.

Integer Type

function integer(name?: string, config?: {
  mode?: 'number' | 'boolean' | 'timestamp' | 'timestamp_ms';
}): SQLiteIntegerBuilder;

Integer Type Modes:

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

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  age: integer('age'),  // JavaScript number
  isActive: integer('is_active', { mode: 'boolean' }),  // Boolean (0/1)
  createdAt: integer('created_at', { mode: 'timestamp' }),  // Unix timestamp (seconds)
  updatedAt: integer('updated_at', { mode: 'timestamp_ms' }),  // Unix timestamp (milliseconds)
});

Real Type

function real(name?: string): SQLiteRealBuilder;  // Floating-point number

export const products = sqliteTable('products', {
  id: integer('id').primaryKey(),
  price: real('price').notNull(),
  rating: real('rating'),
});

Numeric Type

function numeric(name?: string): SQLiteNumericBuilder;  // Stored as string

export const products = sqliteTable('products', {
  id: integer('id').primaryKey(),
  price: numeric('price'),  // High-precision decimal as string
});

Text Type

function text(name?: string, config?: {
  enum?: [string, ...string[]];
  mode?: 'text' | 'json';
}): SQLiteTextBuilder;

Text Type Usage:

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

type UserSettings = {
  theme: string;
  notifications: boolean;
};

export const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull(),
  role: text('role', { enum: ['admin', 'user', 'guest'] }).default('user'),
  settings: text('settings', { mode: 'json' }).$type<UserSettings>(),
});

Blob Type

function blob(name?: string, config?: {
  mode?: 'buffer' | 'json' | 'bigint';
}): SQLiteBlobBuilder;

Blob Type Usage:

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

export const files = sqliteTable('files', {
  id: integer('id').primaryKey(),
  data: blob('data', { mode: 'buffer' }),  // Buffer/Uint8Array
  metadata: blob('metadata', { mode: 'json' }),  // JSON stored as blob
  bigValue: blob('big_value', { mode: 'bigint' }),  // BigInt
});

Column Modifiers

All column types support common modifiers that affect constraints and behavior.

Type Casting

interface ColumnBuilder {
  /**
   * Changes the data type of the column. Commonly used with json columns
   * and for branded types.
   *
   * @param TType - The TypeScript type to use for this column
   */
  $type<TType>(): $Type<this, TType>;
}

type UserId = number & { readonly brand: unique symbol };
type UserDetails = { name: string; age: number };

export const users = pgTable('users', {
  id: integer('id').$type<UserId>().primaryKey(),
  details: json('details').$type<UserDetails>().notNull(),
});

Not Null Constraint

interface ColumnBuilder {
  /**
   * Adds a NOT NULL constraint to the column.
   * Affects the select model - columns without notNull() will be nullable.
   */
  notNull(): NotNull<this>;
}

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),  // Required on select
  nickname: text('nickname'),    // Optional on select (can be null)
});

Default Values

interface ColumnBuilder {
  /**
   * Adds a DEFAULT clause to the column.
   * Affects the insert model - columns with default() are optional on insert.
   *
   * @param value - The default value or SQL expression
   */
  default(value: Data | SQL): HasDefault<this>;

  /**
   * Adds a dynamic default value computed at runtime.
   * The function is called when inserting rows.
   * Note: This is runtime-only and does not affect drizzle-kit migrations.
   *
   * @param fn - Function that returns the default value
   */
  $defaultFn(fn: () => Data | SQL): HasRuntimeDefault<HasDefault<this>>;

  /**
   * Alias for $defaultFn()
   */
  $default: this['$defaultFn'];
}

Static Default Values:

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

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  role: text('role').default('user'),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').default(sql`now()`),
  uuid: uuid('uuid').defaultRandom(),  // PostgreSQL specific
});

Dynamic Default Values:

import { pgTable, integer, text, timestamp } from 'drizzle-orm/pg-core';
import { v4 as uuidv4 } from 'uuid';

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  externalId: text('external_id').$defaultFn(() => uuidv4()),
  createdAt: timestamp('created_at', { mode: 'date' }).$defaultFn(() => new Date()),
  randomValue: integer('random_value').$default(() => Math.floor(Math.random() * 1000)),
});

Update Values

interface ColumnBuilder {
  /**
   * Adds a dynamic update value computed at runtime.
   * The function is called when updating rows if no value is provided.
   * If no default is set, also called on insert.
   * Note: This is runtime-only and does not affect drizzle-kit migrations.
   *
   * @param fn - Function that returns the update value
   */
  $onUpdateFn(fn: () => Data | SQL): HasDefault<this>;

  /**
   * Alias for $onUpdateFn()
   */
  $onUpdate: this['$onUpdateFn'];
}

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

export const posts = pgTable('posts', {
  id: integer('id').primaryKey(),
  title: text('title').notNull(),
  createdAt: timestamp('created_at', { mode: 'date' }).$defaultFn(() => new Date()),
  updatedAt: timestamp('updated_at', { mode: 'date' })
    .$defaultFn(() => new Date())
    .$onUpdate(() => new Date()),
});

Primary Key

interface ColumnBuilder {
  /**
   * Adds a PRIMARY KEY constraint to the column.
   * Implicitly makes the column NOT NULL.
   * In SQLite, integer primary key implicitly auto-increments.
   */
  primaryKey(): IsPrimaryKey<NotNull<this>>;
}

// SQLite specific
interface SQLiteIntegerBuilder {
  primaryKey(config?: { autoIncrement?: boolean }): IsPrimaryKey<NotNull<this>>;
}

// PostgreSQL
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull(),
});

// SQLite with auto-increment
export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  email: text('email').notNull(),
});

Unique Constraint

interface ColumnBuilder {
  /**
   * Adds a UNIQUE constraint to the column.
   *
   * @param name - Optional constraint name
   * @param config - Configuration options (PostgreSQL only)
   */
  unique(
    name?: string,
    config?: { nulls?: 'distinct' | 'not distinct' }
  ): this;
}

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  email: text('email').notNull().unique(),
  username: text('username').unique('unique_username'),
  taxId: text('tax_id').unique('unique_tax_id', { nulls: 'not distinct' }),
});

Foreign Key References

interface PgColumnBuilder {
  /**
   * Adds a foreign key reference to another column.
   *
   * @param ref - Function returning the referenced column
   * @param actions - ON UPDATE and ON DELETE actions
   */
  references(
    ref: () => PgColumn,
    actions?: {
      onUpdate?: 'cascade' | 'restrict' | 'no action' | 'set null' | 'set default';
      onDelete?: 'cascade' | 'restrict' | 'no action' | 'set null' | 'set default';
    }
  ): this;
}

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
});

export const posts = pgTable('posts', {
  id: integer('id').primaryKey(),
  userId: integer('user_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  title: text('title').notNull(),
});

Generated Columns

interface PgColumnBuilder {
  /**
   * Creates a generated column with GENERATED ALWAYS AS.
   *
   * @param as - SQL expression or function returning the computed value
   */
  generatedAlwaysAs(
    as: SQL | Data | (() => SQL)
  ): HasGenerated<this, { type: 'always' }>;
}

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

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  firstName: text('first_name').notNull(),
  lastName: text('last_name').notNull(),
  fullName: text('full_name').generatedAlwaysAs(
    sql`first_name || ' ' || last_name`
  ),
});

Generated Identity Columns (PostgreSQL 10+)

interface PgIntColumnBuilder {
  /**
   * Creates a GENERATED ALWAYS AS IDENTITY column.
   *
   * @param config - Sequence configuration
   */
  generatedAlwaysAsIdentity(config?: {
    sequenceName?: string;
    startWith?: number;
    increment?: number;
    minValue?: number;
    maxValue?: number;
    cache?: number;
    cycle?: boolean;
  }): IsIdentity<this, 'always'>;

  /**
   * Creates a GENERATED BY DEFAULT AS IDENTITY column.
   * Allows manual value insertion.
   *
   * @param config - Sequence configuration
   */
  generatedByDefaultAsIdentity(config?: {
    sequenceName?: string;
    startWith?: number;
    increment?: number;
    minValue?: number;
    maxValue?: number;
    cache?: number;
    cycle?: boolean;
  }): IsIdentity<this, 'byDefault'>;
}

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

export const users = pgTable('users', {
  id: integer('id').generatedAlwaysAsIdentity({ startWith: 1000 }),
  name: text('name').notNull(),
});

export const posts = pgTable('posts', {
  id: integer('id').generatedByDefaultAsIdentity(),  // Can override on insert
  title: text('title').notNull(),
});

Constraints

Primary Keys

Single Column Primary Key:

Use the .primaryKey() column modifier (see Primary Key section).

Composite Primary Key:

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

function primaryKey(config: {
  name?: string;
  columns: [Column, ...Column[]];
}): PrimaryKeyBuilder;

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

export const userRoles = pgTable('user_roles', {
  userId: integer('user_id').notNull(),
  roleId: integer('role_id').notNull(),
  assignedAt: timestamp('assigned_at').notNull(),
}, (table) => [
  primaryKey({ columns: [table.userId, table.roleId] }),
]);

Foreign Keys

Inline Foreign Key:

Use the .references() column modifier (see Foreign Key References section).

Composite Foreign Key:

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

function foreignKey(config: {
  name?: string;
  columns: [Column, ...Column[]];
  foreignColumns: [Column, ...Column[]];
}): ForeignKeyBuilder;

interface ForeignKeyBuilder {
  /**
   * Sets the ON UPDATE action.
   */
  onUpdate(action: 'cascade' | 'restrict' | 'no action' | 'set null' | 'set default'): this;

  /**
   * Sets the ON DELETE action.
   */
  onDelete(action: 'cascade' | 'restrict' | 'no action' | 'set null' | 'set default'): this;
}

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

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  tenantId: integer('tenant_id').notNull(),
});

export const posts = pgTable('posts', {
  id: integer('id').primaryKey(),
  userId: integer('user_id').notNull(),
  tenantId: integer('tenant_id').notNull(),
}, (table) => [
  foreignKey({
    columns: [table.userId, table.tenantId],
    foreignColumns: [users.id, users.tenantId],
  }).onDelete('cascade'),
]);

Unique Constraints

Single Column Unique:

Use the .unique() column modifier (see Unique Constraint section).

Composite Unique Constraint:

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

function unique(name?: string): UniqueConstraintBuilder;

interface UniqueConstraintBuilder {
  /**
   * Specifies the columns for the unique constraint.
   */
  on(...columns: [Column, ...Column[]]): this;

  /**
   * PostgreSQL-only: Treat NULL values as not distinct.
   */
  nullsNotDistinct(): this;
}

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

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  email: text('email').notNull(),
  tenantId: integer('tenant_id').notNull(),
  username: text('username').notNull(),
}, (table) => [
  unique('unique_email_per_tenant').on(table.email, table.tenantId),
  unique().on(table.username).nullsNotDistinct(),
]);

Check Constraints

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

function check(
  name: string,
  condition: SQL
): CheckBuilder;

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

export const products = pgTable('products', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
  price: integer('price').notNull(),
  discount: integer('discount'),
}, (table) => [
  check('price_positive', sql`${table.price} > 0`),
  check('discount_valid', sql`${table.discount} >= 0 AND ${table.discount} <= 100`),
]);

Indexes

PostgreSQL Indexes

import { index, uniqueIndex } from 'drizzle-orm/pg-core';

function index(name?: string): IndexBuilderOn;
function uniqueIndex(name?: string): IndexBuilderOn;

interface IndexBuilderOn {
  /**
   * Specifies the columns or expressions to index.
   */
  on(...columns: [Column | SQL, ...Array<Column | SQL>]): IndexBuilder;

  /**
   * Creates index on only the specified table (not inherited tables).
   */
  onOnly(...columns: [Column | SQL, ...Array<Column | SQL>]): IndexBuilder;
}

interface IndexBuilder {
  /**
   * Specifies the index method.
   *
   * @param method - Index method: 'btree', 'hash', 'gist', 'spgist', 'gin', 'brin', 'hnsw', 'ivfflat'
   */
  using(method: 'btree' | 'hash' | 'gist' | 'spgist' | 'gin' | 'brin' | 'hnsw' | 'ivfflat' | string): this;

  /**
   * Adds a WHERE clause for partial index.
   */
  where(condition: SQL): this;

  /**
   * Sets the WITH clause storage parameters.
   */
  with(params: Record<string, any>): this;

  /**
   * Creates the index concurrently.
   */
  concurrently(): this;

  /**
   * Configures index column options.
   */
  asc(): this;
  desc(): this;
  nullsFirst(): this;
  nullsLast(): this;

  /**
   * Specifies the operator class.
   */
  op(opClass: string): this;
}

type PgIndexMethod = 'btree' | 'hash' | 'gist' | 'spgist' | 'gin' | 'brin' | 'hnsw' | 'ivfflat';

type PgIndexOpClass =
  | 'text_ops' | 'varchar_ops' | 'int4_ops' | 'int8_ops' | 'float_ops' | 'numeric_ops'
  | 'date_ops' | 'timestamp_ops' | 'timestamptz_ops' | 'bool_ops' | 'uuid_ops'
  | 'jsonb_ops' | 'array_ops' | 'vector_l2_ops' | 'vector_ip_ops' | 'vector_cosine_ops'
  | 'vector_l1_ops' | 'bit_hamming_ops' | 'bit_jaccard_ops' | string;

Basic Indexes:

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

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  email: text('email').notNull(),
  name: text('name'),
  age: integer('age'),
}, (table) => [
  index('email_idx').on(table.email),
  uniqueIndex('unique_email').on(table.email),
  index('name_age_idx').on(table.name, table.age),
]);

Advanced Index Options:

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

export const posts = pgTable('posts', {
  id: integer('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content'),
  published: boolean('published').default(false),
  views: integer('views').default(0),
}, (table) => [
  // Partial index
  index('published_posts_idx')
    .on(table.title)
    .where(sql`${table.published} = true`),

  // Index with specific method
  index('content_search_idx')
    .on(table.content)
    .using('gin'),

  // Expression index
  index('title_lower_idx')
    .on(sql`lower(${table.title})`),

  // Index with column options
  index('views_desc_idx')
    .on(table.views.desc().nullsLast()),

  // Concurrent index creation
  index('title_idx')
    .on(table.title)
    .concurrently(),
]);

pgvector Indexes:

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

export const embeddings = pgTable('embeddings', {
  id: integer('id').primaryKey(),
  content: text('content'),
  embedding: vector('embedding', { dimensions: 1536 }),
}, (table) => [
  // HNSW index for approximate nearest neighbor search
  index('embedding_hnsw_idx')
    .on(table.embedding.op('vector_cosine_ops'))
    .using('hnsw')
    .with({ m: 16, ef_construction: 64 }),

  // IVFFlat index
  index('embedding_ivfflat_idx')
    .on(table.embedding.op('vector_l2_ops'))
    .using('ivfflat')
    .with({ lists: 100 }),
]);

MySQL Indexes

import { index, uniqueIndex, fullTextIndex, spatialIndex } from 'drizzle-orm/mysql-core';

function index(name?: string): IndexBuilderOn;
function uniqueIndex(name?: string): IndexBuilderOn;
function fullTextIndex(name?: string): IndexBuilderOn;
function spatialIndex(name?: string): IndexBuilderOn;

interface IndexBuilderOn {
  on(...columns: [Column, ...Column[]]): IndexBuilder;
}

interface IndexBuilder {
  /**
   * Specifies the index method (btree or hash).
   */
  using(method: 'btree' | 'hash'): this;
}

MySQL Index Usage:

import { mysqlTable, int, varchar, text, index, uniqueIndex, fullTextIndex } from 'drizzle-orm/mysql-core';

export const posts = mysqlTable('posts', {
  id: int('id').primaryKey().autoincrement(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content').notNull(),
  authorId: int('author_id').notNull(),
}, (table) => [
  index('author_idx').on(table.authorId),
  uniqueIndex('title_idx').on(table.title),
  fullTextIndex('content_search').on(table.title, table.content),
  index('title_btree').on(table.title).using('btree'),
]);

SQLite Indexes

import { index, uniqueIndex } from 'drizzle-orm/sqlite-core';

function index(name?: string): IndexBuilderOn;
function uniqueIndex(name?: string): IndexBuilderOn;

interface IndexBuilderOn {
  on(...columns: [Column | SQL, ...Array<Column | SQL>]): IndexBuilder;
}

interface IndexBuilder {
  /**
   * Adds a WHERE clause for partial index.
   */
  where(condition: SQL): this;
}

SQLite Index Usage:

import { sqliteTable, integer, text, index, uniqueIndex } from 'drizzle-orm/sqlite-core';
import { sql } from 'drizzle-orm';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  email: text('email').notNull(),
  name: text('name'),
  active: integer('active', { mode: 'boolean' }),
}, (table) => [
  index('email_idx').on(table.email),
  uniqueIndex('unique_email').on(table.email),

  // Partial index
  index('active_users_idx')
    .on(table.name)
    .where(sql`${table.active} = 1`),

  // Expression index
  index('email_lower_idx')
    .on(sql`lower(${table.email})`),
]);

Schemas and Namespaces

PostgreSQL Schemas

PostgreSQL schemas provide namespaces for organizing database objects.

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

function pgSchema<TName extends string>(name: TName): PgSchema<TName>;

interface PgSchema<TName extends string> {
  /**
   * Define a table in this schema.
   */
  table: PgTableFn<TName>;

  /**
   * Define a view in this schema.
   */
  view: typeof pgView;

  /**
   * Define a materialized view in this schema.
   */
  materializedView: typeof pgMaterializedView;

  /**
   * Define an enum in this schema.
   */
  enum<U extends string, T extends Readonly<[U, ...U[]]>>(
    enumName: string,
    values: T
  ): PgEnum<T>;

  enum<E extends Record<string, string>>(
    enumName: string,
    enumObj: E
  ): PgEnumObject<E>;

  /**
   * Define a sequence in this schema.
   */
  sequence: typeof pgSequence;
}

Schema Usage:

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

const authSchema = pgSchema('auth');

export const users = authSchema.table('users', {
  id: integer('id').primaryKey(),
  email: text('email').notNull(),
});

export const sessions = authSchema.table('sessions', {
  id: text('id').primaryKey(),
  userId: integer('user_id')
    .notNull()
    .references(() => users.id),
  expiresAt: timestamp('expires_at').notNull(),
});

export const userRole = authSchema.enum('user_role', ['admin', 'user', 'guest']);

MySQL Schemas (Databases)

In MySQL, schemas are synonymous with databases.

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

function mysqlSchema<TName extends string>(name: TName): MySqlSchema<TName>;

interface MySqlSchema<TName extends string> {
  /**
   * Define a table in this schema/database.
   */
  table: MySqlTableFn<TName>;

  /**
   * Define a view in this schema/database.
   */
  view: typeof mysqlView;
}

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

const appSchema = mysqlSchema('app_database');

export const users = appSchema.table('users', {
  id: int('id').primaryKey().autoincrement(),
  name: varchar('name', { length: 255 }).notNull(),
});

Views

PostgreSQL Views

Regular Views

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

function pgView<TName extends string>(
  name: TName
): ViewBuilder<TName>;

function pgView<TName extends string, TColumns extends Record<string, PgColumnBuilderBase>>(
  name: TName,
  columns: TColumns
): ManualViewBuilder<TName, TColumns>;

interface ViewBuilder<TName extends string> {
  /**
   * Configures view options.
   */
  with(config: {
    checkOption?: 'local' | 'cascaded';
    securityBarrier?: boolean;
    securityInvoker?: boolean;
  }): this;

  /**
   * Defines the view query.
   */
  as<TSelectedFields extends ColumnsSelection>(
    qb: TypedQueryBuilder<TSelectedFields> | ((qb: QueryBuilder) => TypedQueryBuilder<TSelectedFields>)
  ): PgViewWithSelection<TName, false, TSelectedFields>;
}

interface ManualViewBuilder<TName extends string, TColumns> {
  /**
   * Configures view options.
   */
  with(config: {
    checkOption?: 'local' | 'cascaded';
    securityBarrier?: boolean;
    securityInvoker?: boolean;
  }): this;

  /**
   * Marks this as an existing view (for drizzle-kit introspection).
   */
  existing(): PgViewWithSelection<TName, true, TColumns>;
}

View Usage:

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

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull(),
  createdAt: timestamp('created_at').notNull(),
});

// View from query builder
export const activeUsers = pgView('active_users').as((qb) =>
  qb.select({
    id: users.id,
    name: users.name,
    email: users.email,
  })
  .from(users)
  .where(sql`${users.createdAt} > now() - interval '30 days'`)
);

// Manual view (existing in database)
export const userStats = pgView('user_stats', {
  userId: integer('user_id').notNull(),
  postCount: integer('post_count').notNull(),
  commentCount: integer('comment_count').notNull(),
}).existing();

// View with options
export const protectedUsers = pgView('protected_users')
  .with({
    securityBarrier: true,
    checkOption: 'cascaded',
  })
  .as((qb) => qb.select().from(users));

Materialized Views

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

function pgMaterializedView<TName extends string>(
  name: TName
): MaterializedViewBuilder<TName>;

function pgMaterializedView<TName extends string, TColumns extends Record<string, PgColumnBuilderBase>>(
  name: TName,
  columns: TColumns
): ManualMaterializedViewBuilder<TName, TColumns>;

interface MaterializedViewBuilder<TName extends string> {
  /**
   * Configures materialized view options.
   */
  with(config: {
    fillfactor?: number;
    toast_tuple_target?: number;
    parallel_workers?: number;
    autovacuum_enabled?: boolean;
  }): this;

  /**
   * Adds tablespace.
   */
  tablespace(tablespace: string): this;

  /**
   * Adds WITH NO DATA clause.
   */
  withNoData(): this;

  /**
   * Specifies storage parameters.
   */
  using(method: 'heap'): this;

  /**
   * Defines the materialized view query.
   */
  as<TSelectedFields extends ColumnsSelection>(
    qb: TypedQueryBuilder<TSelectedFields> | ((qb: QueryBuilder) => TypedQueryBuilder<TSelectedFields>)
  ): PgMaterializedViewWithSelection<TName, false, TSelectedFields>;
}

Materialized View Usage:

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

export const posts = pgTable('posts', {
  id: integer('id').primaryKey(),
  userId: integer('user_id').notNull(),
  title: text('title').notNull(),
  views: integer('views').default(0),
});

export const popularPosts = pgMaterializedView('popular_posts')
  .with({ fillfactor: 80 })
  .as((qb) =>
    qb.select({
      id: posts.id,
      title: posts.title,
      views: posts.views,
    })
    .from(posts)
    .where(sql`${posts.views} > 1000`)
    .orderBy(desc(posts.views))
  );

// Refresh materialized view
await db.refreshMaterializedView(popularPosts);
await db.refreshMaterializedView(popularPosts).concurrently();
await db.refreshMaterializedView(popularPosts).withNoData();

MySQL Views

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

function mysqlView<TName extends string>(
  name: TName
): ViewBuilder<TName>;

function mysqlView<TName extends string, TColumns extends Record<string, MySqlColumnBuilderBase>>(
  name: TName,
  columns: TColumns
): ManualViewBuilder<TName, TColumns>;

interface ViewBuilder<TName extends string> {
  /**
   * Configures view options.
   */
  with(config: {
    checkOption?: 'local' | 'cascaded';
    securityType?: 'definer' | 'invoker';
  }): this;

  /**
   * Defines the view query.
   */
  as<TSelectedFields extends ColumnsSelection>(
    qb: TypedQueryBuilder<TSelectedFields> | ((qb: QueryBuilder) => TypedQueryBuilder<TSelectedFields>)
  ): MySqlViewWithSelection<TName, false, TSelectedFields>;
}

import { mysqlTable, mysqlView, int, varchar } from 'drizzle-orm/mysql-core';

export const users = mysqlTable('users', {
  id: int('id').primaryKey().autoincrement(),
  name: varchar('name', { length: 255 }).notNull(),
  status: varchar('status', { length: 50 }).notNull(),
});

export const activeUsers = mysqlView('active_users').as((qb) =>
  qb.select().from(users).where(sql`${users.status} = 'active'`)
);

SQLite Views

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

function sqliteView<TName extends string>(
  name: TName
): ViewBuilder<TName>;

function sqliteView<TName extends string, TColumns extends Record<string, SQLiteColumnBuilderBase>>(
  name: TName,
  columns: TColumns
): ManualViewBuilder<TName, TColumns>;

interface ViewBuilder<TName extends string> {
  /**
   * Defines the view query.
   */
  as<TSelectedFields extends ColumnsSelection>(
    qb: TypedQueryBuilder<TSelectedFields> | ((qb: QueryBuilder) => TypedQueryBuilder<TSelectedFields>)
  ): SQLiteViewWithSelection<TName, false, TSelectedFields>;
}

import { sqliteTable, sqliteView, integer, text } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
  active: integer('active', { mode: 'boolean' }),
});

export const activeUsers = sqliteView('active_users').as((qb) =>
  qb.select().from(users).where(sql`${users.active} = 1`)
);

Enums

PostgreSQL Enums

PostgreSQL supports native enum types that must be created before use.

Array-based Enums

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

function pgEnum<U extends string, T extends Readonly<[U, ...U[]]>>(
  enumName: string,
  values: T
): PgEnum<T>;

interface PgEnum<TValues extends [string, ...string[]]> {
  (name?: string): PgEnumColumnBuilderInitial<'', TValues>;

  readonly enumName: string;
  readonly enumValues: TValues;
  readonly schema: string | undefined;
}

Array-based Enum Usage:

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

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

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
  role: roleEnum('role').default('user').notNull(),
});

Object-based Enums

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

function pgEnum<E extends Record<string, string>>(
  enumName: string,
  enumObj: E
): PgEnumObject<E>;

interface PgEnumObject<TValues extends object> {
  (name?: string): PgEnumObjectColumnBuilderInitial<'', TValues>;

  readonly enumName: string;
  readonly enumValues: string[];
  readonly schema: string | undefined;
}

Object-based Enum Usage:

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

export const statusEnum = pgEnum('status', {
  DRAFT: 'draft',
  PUBLISHED: 'published',
  ARCHIVED: 'archived',
});

export const posts = pgTable('posts', {
  id: integer('id').primaryKey(),
  title: text('title').notNull(),
  status: statusEnum('status').default('draft').notNull(),
});

// Usage in queries
const draftPosts = await db.select()
  .from(posts)
  .where(eq(posts.status, statusEnum.DRAFT));

Enums in Schemas

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

const mySchema = pgSchema('my_schema');

export const roleEnum = mySchema.enum('role', ['admin', 'user', 'guest']);

export const users = mySchema.table('users', {
  id: integer('id').primaryKey(),
  role: roleEnum('role').notNull(),
});

MySQL Enums

MySQL uses inline enum definitions within column definitions.

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

function mysqlEnum<T extends [string, ...string[]]>(
  name?: string,
  values: T,
  config?: object
): MySqlEnumBuilder<T>;

import { mysqlTable, int, varchar, mysqlEnum } from 'drizzle-orm/mysql-core';

export const users = mysqlTable('users', {
  id: int('id').primaryKey().autoincrement(),
  name: varchar('name', { length: 255 }).notNull(),
  role: mysqlEnum('role', ['admin', 'user', 'guest']).default('user'),
  status: mysqlEnum('status', ['active', 'inactive', 'suspended']).notNull(),
});

SQLite Enums (Text-based)

SQLite doesn't have native enums, but you can use the enum option with text columns for type safety.

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

function text<T extends [string, ...string[]]>(
  name?: string,
  config: {
    enum: T;
  }
): SQLiteTextBuilder<T>;

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

export const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
  role: text('role', { enum: ['admin', 'user', 'guest'] }).default('user'),
});

// Type-safe values
const newUser = {
  name: 'John',
  role: 'user' as const,  // Only 'admin', 'user', or 'guest' allowed
};

Custom Types

All databases support defining custom column types with custom marshalling logic.

PostgreSQL Custom Types

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

function customType<T extends {
  data: unknown;
  driverData?: unknown;
  notNull?: boolean;
  default?: boolean;
}>(config: {
  dataType: () => string;
  toDriver?: (value: T['data']) => T['driverData'];
  fromDriver?: (value: T['driverData']) => T['data'];
}): (name?: string) => PgCustomColumnBuilder<T>;

Custom Type Usage:

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

// Custom type for currency stored as integer (cents)
const currency = customType<{
  data: number;
  driverData: number;
}>({
  dataType() {
    return 'integer';
  },
  toDriver(value: number): number {
    // Convert dollars to cents
    return Math.round(value * 100);
  },
  fromDriver(value: number): number {
    // Convert cents to dollars
    return value / 100;
  },
});

export const products = pgTable('products', {
  id: integer('id').primaryKey(),
  price: currency('price').notNull(),
});

// Usage
await db.insert(products).values({ id: 1, price: 19.99 });  // Stores 1999
const product = await db.select().from(products).where(eq(products.id, 1));
console.log(product[0].price);  // Returns 19.99

Custom Type for Complex Objects:

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

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

const point = customType<{
  data: Point;
  driverData: string;
}>({
  dataType() {
    return 'point';
  },
  toDriver(value: Point): string {
    return `(${value.x},${value.y})`;
  },
  fromDriver(value: string): Point {
    const [x, y] = value.slice(1, -1).split(',').map(Number);
    return { x, y };
  },
});

export const locations = pgTable('locations', {
  id: integer('id').primaryKey(),
  coordinates: point('coordinates').notNull(),
});

MySQL Custom Types

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

function customType<T extends {
  data: unknown;
  driverData?: unknown;
  notNull?: boolean;
  default?: boolean;
}>(config: {
  dataType: () => string;
  toDriver?: (value: T['data']) => T['driverData'];
  fromDriver?: (value: T['driverData']) => T['data'];
}): (name?: string) => MySqlCustomColumnBuilder<T>;

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

const ipAddress = customType<{
  data: string;
  driverData: number;
}>({
  dataType() {
    return 'int unsigned';
  },
  toDriver(value: string): number {
    const parts = value.split('.').map(Number);
    return (parts[0] << 24) + (parts[1] << 16) + (parts[2] << 8) + parts[3];
  },
  fromDriver(value: number): string {
    return [
      (value >>> 24) & 0xff,
      (value >>> 16) & 0xff,
      (value >>> 8) & 0xff,
      value & 0xff,
    ].join('.');
  },
});

export const connections = mysqlTable('connections', {
  id: int('id').primaryKey(),
  ip: ipAddress('ip').notNull(),
});

SQLite Custom Types

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

function customType<T extends {
  data: unknown;
  driverData?: unknown;
  notNull?: boolean;
  default?: boolean;
}>(config: {
  dataType: () => string;
  toDriver?: (value: T['data']) => T['driverData'];
  fromDriver?: (value: T['driverData']) => T['data'];
}): (name?: string) => SQLiteCustomColumnBuilder<T>;

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

const dateString = customType<{
  data: Date;
  driverData: string;
}>({
  dataType() {
    return 'text';
  },
  toDriver(value: Date): string {
    return value.toISOString();
  },
  fromDriver(value: string): Date {
    return new Date(value);
  },
});

export const events = sqliteTable('events', {
  id: integer('id').primaryKey(),
  occurredAt: dateString('occurred_at').notNull(),
});

Sequences

PostgreSQL sequences are standalone database objects that generate sequential values.

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

function pgSequence(
  name: string,
  options?: {
    startWith?: number;
    increment?: number;
    minValue?: number;
    maxValue?: number;
    cache?: number;
    cycle?: boolean;
  }
): PgSequence;

Sequence Usage:

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

export const userIdSequence = pgSequence('user_id_seq', {
  startWith: 1000,
  increment: 1,
  minValue: 1000,
  maxValue: 999999,
  cache: 10,
  cycle: false,
});

export const users = pgTable('users', {
  id: integer('id').default(sql`nextval('user_id_seq')`).primaryKey(),
  name: text('name').notNull(),
});

// Sequence in schema
const appSchema = pgSchema('app');

export const orderIdSeq = appSchema.sequence('order_id_seq', {
  startWith: 1,
  increment: 1,
});

export const orders = appSchema.table('orders', {
  id: integer('id').default(sql`nextval('app.order_id_seq')`).primaryKey(),
  total: numeric('total', { precision: 10, scale: 2 }),
});

Type Inference

Drizzle ORM provides powerful type inference for schema definitions.

Infer Select Model

type InferSelectModel<TTable extends Table> = {
  [K in keyof TTable['_']['columns']]: TTable['_']['columns'][K]['_']['data'];
};

Alternative Syntax:

interface Table {
  $inferSelect: InferSelectModel<this>;
}

Usage:

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

export const users = pgTable('users', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull(),
  age: integer('age'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

// Method 1: Using InferSelectModel
export type User = InferSelectModel<typeof users>;

// Method 2: Using .$inferSelect
export type User = typeof users.$inferSelect;

// Inferred type:
// {
//   id: number;
//   name: string;
//   email: string;
//   age: number | null;
//   createdAt: Date;
// }

Infer Insert Model

type InferInsertModel<TTable extends Table> = {
  [K in keyof TTable['_']['columns']]:
    TTable['_']['columns'][K]['_']['hasDefault'] extends true
      ? TTable['_']['columns'][K]['_']['data'] | undefined
      : TTable['_']['columns'][K]['_']['notNull'] extends true
        ? TTable['_']['columns'][K]['_']['data']
        : TTable['_']['columns'][K]['_']['data'] | null | undefined;
};

Alternative Syntax:

interface Table {
  $inferInsert: InferInsertModel<this>;
}

Usage:

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

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

// Method 1: Using InferInsertModel
export type NewUser = InferInsertModel<typeof users>;

// Method 2: Using .$inferInsert
export type NewUser = typeof users.$inferInsert;

// Inferred type:
// {
//   id?: number;                    // Has default (serial)
//   name: string;                   // Required (notNull, no default)
//   email: string;                  // Required (notNull, no default)
//   bio?: string | null;            // Optional (nullable)
//   isActive?: boolean;             // Optional (has default)
//   createdAt?: Date;               // Optional (has default)
// }

// Usage
const newUser: NewUser = {
  name: 'John Doe',
  email: 'john@example.com',
  // All other fields are optional
};

Partial Insert Models

For updates or partial inserts:

import type { InferInsertModel } from 'drizzle-orm';

export type PartialUser = Partial<InferInsertModel<typeof users>>;

// Usage
const updateData: PartialUser = {
  name: 'Jane Doe',
  // Only updating name field
};

Combined Type Usage

import { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';
import type { InferSelectModel, InferInsertModel } from 'drizzle-orm';

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  authorId: integer('author_id').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// Select model (what you get from queries)
export type Post = InferSelectModel<typeof posts>;

// Insert model (what you provide for inserts)
export type NewPost = InferInsertModel<typeof posts>;

// Update model (partial updates)
export type UpdatePost = Partial<NewPost>;

// Usage
async function createPost(data: NewPost): Promise<Post> {
  const [post] = await db.insert(posts).values(data).returning();
  return post;
}

async function updatePost(id: number, data: UpdatePost): Promise<Post> {
  const [post] = await db.update(posts)
    .set(data)
    .where(eq(posts.id, id))
    .returning();
  return post;
}

async function getPost(id: number): Promise<Post | undefined> {
  return await db.query.posts.findFirst({
    where: eq(posts.id, id),
  });
}

Complete Schema Example

Comprehensive example demonstrating all schema definition features:

import {
  pgTable,
  pgSchema,
  pgEnum,
  serial,
  integer,
  text,
  varchar,
  boolean,
  timestamp,
  json,
  uuid,
  numeric,
  index,
  uniqueIndex,
  foreignKey,
  primaryKey,
  check,
  pgView,
  pgMaterializedView,
} from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
import type { InferSelectModel, InferInsertModel } from 'drizzle-orm';

// Schema namespace
const appSchema = pgSchema('app');

// Enums
export const userRole = appSchema.enum('user_role', ['admin', 'moderator', 'user']);
export const postStatus = pgEnum('post_status', ['draft', 'published', 'archived']);

// Users table with RLS
export const users = appSchema.table('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  username: varchar('username', { length: 50 }).notNull().unique(),
  role: userRole('role').default('user').notNull(),
  isActive: boolean('is_active').default(true).notNull(),
  profile: json<{ bio: string; avatar: string }>('profile'),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true })
    .defaultNow()
    .notNull()
    .$onUpdate(() => new Date()),
}, (table) => [
  index('users_email_idx').on(table.email),
  index('users_username_idx').on(table.username),
  check('valid_email', sql`${table.email} ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'`),
]).enableRLS();

// Posts table with constraints
export const posts = appSchema.table('posts', {
  id: serial('id').primaryKey(),
  authorId: uuid('author_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  title: varchar('title', { length: 255 }).notNull(),
  slug: varchar('slug', { length: 255 }).notNull(),
  content: text('content').notNull(),
  status: postStatus('status').default('draft').notNull(),
  views: integer('views').default(0).notNull(),
  publishedAt: timestamp('published_at', { withTimezone: true }),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
}, (table) => [
  uniqueIndex('posts_slug_idx').on(table.slug),
  index('posts_author_idx').on(table.authorId),
  index('posts_status_idx')
    .on(table.status)
    .where(sql`${table.status} = 'published'`),
  check('valid_views', sql`${table.views} >= 0`),
]);

// Tags table
export const tags = appSchema.table('tags', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 50 }).notNull().unique(),
  slug: varchar('slug', { length: 50 }).notNull().unique(),
});

// Junction table with composite primary key
export const postTags = appSchema.table('post_tags', {
  postId: integer('post_id')
    .notNull()
    .references(() => posts.id, { onDelete: 'cascade' }),
  tagId: integer('tag_id')
    .notNull()
    .references(() => tags.id, { onDelete: 'cascade' }),
  assignedAt: timestamp('assigned_at').defaultNow().notNull(),
}, (table) => [
  primaryKey({ columns: [table.postId, table.tagId] }),
  index('post_tags_post_idx').on(table.postId),
  index('post_tags_tag_idx').on(table.tagId),
]);

// View of published posts
export const publishedPosts = appSchema.view('published_posts').as((qb) =>
  qb.select({
    id: posts.id,
    title: posts.title,
    slug: posts.slug,
    authorId: posts.authorId,
    views: posts.views,
    publishedAt: posts.publishedAt,
  })
  .from(posts)
  .where(sql`${posts.status} = 'published'`)
);

// Materialized view for analytics
export const postStats = appSchema.materializedView('post_stats')
  .with({ fillfactor: 80 })
  .as((qb) =>
    qb.select({
      authorId: posts.authorId,
      postCount: sql<number>`count(*)::int`.as('post_count'),
      totalViews: sql<number>`sum(${posts.views})::int`.as('total_views'),
      avgViews: sql<number>`avg(${posts.views})::int`.as('avg_views'),
    })
    .from(posts)
    .groupBy(posts.authorId)
  );

// Type inference
export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;
export type Post = InferSelectModel<typeof posts>;
export type NewPost = InferInsertModel<typeof posts>;
export type Tag = InferSelectModel<typeof tags>;
export type PublishedPost = InferSelectModel<typeof publishedPosts>;

Version

Tile

Files