SQLite Columns

Column types and builders for SQLite tables

Column Types

SQLite uses a dynamic type system with type affinity. Drizzle provides typed column builders for common use cases.

integer()

Defines an integer column. Supports multiple modes for different data types.

Signature

function integer<TMode extends 'number' | 'timestamp' | 'timestamp_ms' | 'boolean'>(
  config?: { mode: TMode }
): SQLiteIntegerBuilder | SQLiteTimestampBuilder | SQLiteBooleanBuilder

Basic Integer

Number mode (default)

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

const table = sqliteTable('table', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  age: integer('age').notNull(),
  score: integer('score').default(0),
});

Timestamp Mode

Stores dates as Unix timestamps (seconds since epoch).

const table = sqliteTable('table', {
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).$onUpdate(() => new Date()),
});

// Stored as: 1703073600 (seconds)
// JavaScript: new Date()

Timestamp Milliseconds Mode

Stores dates as Unix timestamps in milliseconds.

const table = sqliteTable('table', {
  createdAt: integer('created_at', { mode: 'timestamp_ms' }).notNull(),
});

// Stored as: 1703073600000 (milliseconds)
// JavaScript: new Date()

Boolean Mode

Stores booleans as integers (0 or 1).

const table = sqliteTable('table', {
  isActive: integer('is_active', { mode: 'boolean' }).notNull().default(false),
  isVerified: integer('is_verified', { mode: 'boolean' }),
});

// Stored as: 0 or 1
// JavaScript: true or false

Primary Key Options

interface PrimaryKeyConfig {
  autoIncrement?: boolean;
  onConflict?: 'rollback' | 'abort' | 'fail' | 'ignore' | 'replace';
}

Auto-increment

id: integer('id').primaryKey({ autoIncrement: true })

text()

Defines a text column. Supports plain text, enums, and JSON.

Signature

function text<TEnum extends readonly string[], TMode extends 'text' | 'json'>(
  config?: {
    mode?: TMode;
    length?: number;
    enum?: TEnum;
  }
): SQLiteTextBuilder | SQLiteTextJsonBuilder

Basic Text

Simple text

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

const table = sqliteTable('table', {
  name: text('name').notNull(),
  description: text('description'),
  email: text('email').notNull().unique(),
});

Enum Text

Enforces type safety with predefined values.

const table = sqliteTable('table', {
  role: text('role', { enum: ['admin', 'user', 'guest'] as const }).notNull(),
  status: text('status', { enum: ['active', 'inactive', 'pending'] as const }),
});

// Type-safe: only accepts 'admin', 'user', or 'guest'
await db.insert(table).values({ role: 'admin' });

JSON Mode

Stores JSON data as text with automatic serialization.

const table = sqliteTable('table', {
  metadata: text('metadata', { mode: 'json' }),
  settings: text('settings', { mode: 'json' }).$type<{ theme: string; lang: string }>(),
});

// Automatic JSON serialization
await db.insert(table).values({
  metadata: { key: 'value' },
  settings: { theme: 'dark', lang: 'en' },
});

// Automatic JSON deserialization
const result = await db.select().from(table);
console.log(result[0].metadata.key); // 'value'

real()

Defines a real (floating-point) column.

function real(): SQLiteRealBuilder

Basic usage

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

const table = sqliteTable('table', {
  price: real('price').notNull(),
  rating: real('rating').default(0.0),
  latitude: real('latitude'),
  longitude: real('longitude'),
});

numeric()

Defines a numeric column. Useful for precise decimal values.

Signature

function numeric<TMode extends 'string' | 'number' | 'bigint'>(
  config?: { mode: TMode }
): SQLiteNumericBuilder | SQLiteNumericNumberBuilder | SQLiteNumericBigIntBuilder

String Mode (Default)

Preserves precision as string.

const table = sqliteTable('table', {
  amount: numeric('amount'),
  balance: numeric('balance').notNull(),
});

// Stored and retrieved as string
// JavaScript: '123.45'

Number Mode

Converts to JavaScript number.

const table = sqliteTable('table', {
  price: numeric('price', { mode: 'number' }),
});

// JavaScript: 123.45

BigInt Mode

Converts to JavaScript bigint.

const table = sqliteTable('table', {
  largeValue: numeric('large_value', { mode: 'bigint' }),
});

// JavaScript: 123n

blob()

Defines a blob (binary) column. Supports buffer, JSON, and bigint modes.

Signature

function blob<TMode extends 'buffer' | 'json' | 'bigint'>(
  config?: { mode: TMode }
): SQLiteBlobBufferBuilder | SQLiteBlobJsonBuilder | SQLiteBigIntBuilder

Buffer Mode (Default)

Stores binary data.

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

const table = sqliteTable('table', {
  avatar: blob('avatar', { mode: 'buffer' }),
  file: blob('file', { mode: 'buffer' }).notNull(),
});

// JavaScript: Buffer
await db.insert(table).values({
  avatar: Buffer.from('binary data'),
});

JSON Mode

Stores JSON as binary. Note: Use text('...', { mode: 'json' }) instead for JSON functions support.

const table = sqliteTable('table', {
  data: blob('data', { mode: 'json' }),
});

// Automatic JSON serialization to binary
await db.insert(table).values({
  data: { key: 'value' },
});

SQLite’s JSON functions don’t work with blob columns. Use text('...', { mode: 'json' }) if you need JSON functions.

BigInt Mode

Stores bigint values as binary.

const table = sqliteTable('table', {
  largeNumber: blob('large_number', { mode: 'bigint' }),
});

// JavaScript: bigint
await db.insert(table).values({
  largeNumber: 12345678901234567890n,
});

Column Modifiers

All column builders support these chainable methods:

notNull()

Makes the column non-nullable.

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

default()

Sets a default value.

createdAt: integer('created_at', { mode: 'timestamp' })
  .notNull()
  .default(sql`(unixepoch())`)

primaryKey()

Marks the column as the primary key.

id: integer('id').primaryKey({ autoIncrement: true })

unique()

Adds a unique constraint.

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

references()

Defines a foreign key reference.

userId: integer('user_id')
  .notNull()
  .references(() => users.id, { onDelete: 'cascade' })

$type()

Overrides the TypeScript type.

metadata: text('metadata', { mode: 'json' })
  .$type<{ version: number; flags: string[] }>()

$default()

Sets a runtime default value function.

id: text('id').$default(() => crypto.randomUUID())

$onUpdate()

Sets a function to run on every update.

updatedAt: integer('updated_at', { mode: 'timestamp' })
  .$onUpdate(() => new Date())

$onUpdateFn()

Sets a SQL expression to run on update.

updatedAt: integer('updated_at', { mode: 'timestamp' })
  .$onUpdateFn(() => sql`(unixepoch())`)

Generated Columns

SQLite supports generated columns (virtual and stored).

Virtual generated

const table = sqliteTable('table', {
  price: real('price').notNull(),
  quantity: integer('quantity').notNull(),
  total: real('total').generatedAlwaysAs(
    sql`${price} * ${quantity}`,
    { mode: 'virtual' }
  ),
});

Type Inference

Drizzle automatically infers TypeScript types from column definitions:

const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').notNull(),
  role: text('role', { enum: ['admin', 'user'] as const }).notNull(),
  isActive: integer('is_active', { mode: 'boolean' }).default(true),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
});

// Inferred types:
type User = typeof users.$inferSelect;
// {
//   id: number;
//   name: string;
//   email: string;
//   role: 'admin' | 'user';
//   isActive: boolean | null;
//   createdAt: Date;
// }

type NewUser = typeof users.$inferInsert;
// {
//   id?: number;
//   name: string;
//   email: string;
//   role: 'admin' | 'user';
//   isActive?: boolean | null;
//   createdAt: Date;
// }