0
# Schema Definition
1

2
Drizzle ORM provides a comprehensive schema definition system for creating type-safe database tables, columns, indexes, and constraints. Each database type (PostgreSQL, MySQL, SQLite) has its own specific column types and features.
3

4
## Table Definition
5

6
### PostgreSQL Tables
7

8
```typescript { .api }
9
function pgTable<TTableName extends string>(
10
  name: TTableName,
11
  columns: Record<string, AnyPgColumn>,
12
  extraConfig?: (table: Record<string, AnyPgColumn>) => PgTableExtraConfig
13
): PgTable<{ name: TTableName; columns: typeof columns; }>;
14
```
15

16
### MySQL Tables
17

18
```typescript { .api }
19
function mysqlTable<TTableName extends string>(
20
  name: TTableName,
21
  columns: Record<string, AnyMySqlColumn>,
22
  extraConfig?: (table: Record<string, AnyMySqlColumn>) => MySqlTableExtraConfig
23
): MySqlTable<{ name: TTableName; columns: typeof columns; }>;
24
```
25

26
### SQLite Tables
27

28
```typescript { .api }
29
function sqliteTable<TTableName extends string>(
30
  name: TTableName,
31
  columns: Record<string, AnySQLiteColumn>,
32
  extraConfig?: (table: Record<string, AnySQLiteColumn>) => SQLiteTableExtraConfig
33
): SQLiteTable<{ name: TTableName; columns: typeof columns; }>;
34
```
35

36
## PostgreSQL Column Types
37

38
### Numeric Types
39

40
```typescript { .api }
41
function serial(name: string): PgSerialBuilder;
42
function bigserial(name: string): PgBigSerialBuilder;
43
function smallserial(name: string): PgSmallSerialBuilder;
44
function integer(name: string): PgIntegerBuilder;
45
function bigint(name: string, config?: { mode: 'number' | 'bigint' }): PgBigIntBuilder;
46
function smallint(name: string): PgSmallIntBuilder;
47
function numeric(name: string, config?: { precision?: number; scale?: number }): PgNumericBuilder;
48
function real(name: string): PgRealBuilder;
49
function doublePrecision(name: string): PgDoublePrecisionBuilder;
50
```
51

52
### Text Types
53

54
```typescript { .api }
55
function text<T extends string>(name: string): PgTextBuilder<T>;
56
function text<T extends [string, ...string[]]>(name: string, config: { enum: T }): PgTextBuilder<T[number]>;
57
function varchar<T extends string>(name: string, config?: { length?: number }): PgVarcharBuilder<T>;
58
function varchar<T extends [string, ...string[]]>(name: string, config: { length?: number; enum: T }): PgVarcharBuilder<T[number]>;
59
function char<T extends string>(name: string, config?: { length?: number }): PgCharBuilder<T>;
60
function char<T extends [string, ...string[]]>(name: string, config: { length?: number; enum: T }): PgCharBuilder<T[number]>;
61
```
62

63
### Date/Time Types
64

65
```typescript { .api }
66
function date(name: string, config?: { mode?: 'date' | 'string' }): PgDateBuilder;
67
function time(name: string, config?: { precision?: number; withTimezone?: boolean }): PgTimeBuilder;
68
function timestamp(name: string, config?: {
69
  mode?: 'date' | 'string';
70
  precision?: number;
71
  withTimezone?: boolean
72
}): PgTimestampBuilder;
73
function interval(name: string, config?: {
74
  fields?: 'year' | 'month' | 'day' | 'hour' | 'minute' | 'second';
75
  precision?: number
76
}): PgIntervalBuilder;
77
```
78

79
### JSON Types
80

81
```typescript { .api }
82
function json<T = unknown>(name: string): PgJsonBuilder<T>;
83
function jsonb<T = unknown>(name: string): PgJsonbBuilder<T>;
84
```
85

86
### Other PostgreSQL Types
87

88
```typescript { .api }
89
function boolean(name: string): PgBooleanBuilder;
90
function uuid(name: string): PgUuidBuilder;
91
function inet(name: string): PgInetBuilder;
92
function cidr(name: string): PgCidrBuilder;
93
function macaddr(name: string): PgMacaddrBuilder;
94
function macaddr8(name: string): PgMacaddr8Builder;
95
function point(name: string, config?: { mode?: 'tuple' | 'xy' }): PgPointBuilder;
96
function line(name: string, config?: { mode?: 'tuple' | 'abc' }): PgLineBuilder;
97
```
98

99
### PostgreSQL Vector Extensions
100

101
```typescript { .api }
102
function vector(name: string, config: { dimensions: number }): PgVectorBuilder;
103
function halfvec(name: string, config: { dimensions: number }): PgHalfvecBuilder;
104
function bit(name: string, config: { dimensions: number }): PgBitBuilder;
105
function sparsevec(name: string, config: { dimensions: number }): PgSparsevecBuilder;
106
```
107

108
### PostGIS Extension
109

110
```typescript { .api }
111
function geometry(name: string, config?: {
112
  type?: 'point' | 'linestring' | 'polygon' | 'multipoint' | 'multilinestring' | 'multipolygon';
113
  srid?: number;
114
}): PgGeometryBuilder;
115
```
116

117
## MySQL Column Types
118

119
### Numeric Types
120

121
```typescript { .api }
122
function int(name: string, config?: { unsigned?: boolean }): MySqlIntBuilder;
123
function tinyint(name: string, config?: { unsigned?: boolean }): MySqlTinyIntBuilder;
124
function smallint(name: string, config?: { unsigned?: boolean }): MySqlSmallIntBuilder;
125
function mediumint(name: string, config?: { unsigned?: boolean }): MySqlMediumIntBuilder;
126
function bigint(name: string, config?: { mode: 'number' | 'bigint'; unsigned?: boolean }): MySqlBigIntBuilder;
127
function decimal(name: string, config?: { precision?: number; scale?: number; unsigned?: boolean }): MySqlDecimalBuilder;
128
function float(name: string, config?: { unsigned?: boolean }): MySqlFloatBuilder;
129
function double(name: string, config?: { precision?: number; scale?: number; unsigned?: boolean }): MySqlDoubleBuilder;
130
function serial(name: string): MySqlSerialBuilder;
131
```
132

133
### Text Types
134

135
```typescript { .api }
136
function varchar<T extends string>(name: string, config: { length: number }): MySqlVarcharBuilder<T>;
137
function varchar<T extends [string, ...string[]]>(name: string, config: { length: number; enum: T }): MySqlVarcharBuilder<T[number]>;
138
function char<T extends string>(name: string, config?: { length?: number }): MySqlCharBuilder<T>;
139
function text<T extends string>(name: string, config?: { enum?: T[] }): MySqlTextBuilder<T>;
140
function tinytext<T extends string>(name: string): MySqlTinyTextBuilder<T>;
141
function mediumtext<T extends string>(name: string): MySqlMediumTextBuilder<T>;
142
function longtext<T extends string>(name: string): MySqlLongTextBuilder<T>;
143
```
144

145
### Other MySQL Types
146

147
```typescript { .api }
148
function boolean(name: string): MySqlBooleanBuilder;
149
function date(name: string, config?: { mode?: 'date' | 'string' }): MySqlDateBuilder;
150
function datetime(name: string, config?: { mode?: 'date' | 'string'; fsp?: number }): MySqlDatetimeBuilder;
151
function time(name: string, config?: { fsp?: number }): MySqlTimeBuilder;
152
function timestamp(name: string, config?: { mode?: 'date' | 'string'; fsp?: number }): MySqlTimestampBuilder;
153
function year(name: string): MySqlYearBuilder;
154
function json<T = unknown>(name: string): MySqlJsonBuilder<T>;
155
function binary(name: string, config?: { length?: number }): MySqlBinaryBuilder;
156
function varbinary(name: string, config: { length: number }): MySqlVarbinaryBuilder;
157
```
158

159
## SQLite Column Types
160

161
```typescript { .api }
162
function integer(name: string, config?: { mode?: 'number' | 'timestamp' | 'timestamp_ms' | 'boolean' }): SQLiteIntegerBuilder;
163
function real(name: string): SQLiteRealBuilder;
164
function text<T extends string>(name: string, config?: { length?: number }): SQLiteTextBuilder<T>;
165
function text<T extends [string, ...string[]]>(name: string, config: { enum: T }): SQLiteTextBuilder<T[number]>;
166
function blob(name: string, config?: { mode?: 'buffer' | 'json' | 'bigint' }): SQLiteBlobBuilder;
167
function numeric(name: string): SQLiteNumericBuilder;
168
```
169

170
## Column Constraints
171

172
All column types support these constraint methods:
173

174
```typescript { .api }
175
interface ColumnBuilder<T> {
176
  primaryKey(): this;
177
  notNull(): this;
178
  default(value: T | SQL): this;
179
  unique(): this;
180
  unique(name: string): this;
181
  references(
182
    column: () => AnyColumn,
183
    config?: { onDelete?: 'cascade' | 'restrict' | 'no action' | 'set null' | 'set default' }
184
  ): this;
185
}
186
```
187

188
## Table Constraints
189

190
### Indexes
191

192
```typescript { .api }
193
function index(name?: string): IndexBuilder;
194
function uniqueIndex(name?: string): UniqueIndexBuilder;
195

196
interface IndexBuilder {
197
  on(...columns: AnyColumn[]): this;
198
  where(condition: SQL): this;
199
  using(method: 'btree' | 'hash' | 'gist' | 'gin'): this; // PostgreSQL only
200
}
201
```
202

203
### Foreign Keys
204

205
```typescript { .api }
206
function foreignKey(config: {
207
  columns: AnyColumn[];
208
  foreignColumns: AnyColumn[];
209
  name?: string;
210
}): ForeignKeyBuilder;
211

212
interface ForeignKeyBuilder {
213
  onDelete(action: 'cascade' | 'restrict' | 'no action' | 'set null' | 'set default'): this;
214
  onUpdate(action: 'cascade' | 'restrict' | 'no action' | 'set null' | 'set default'): this;
215
}
216
```
217

218
### Check Constraints
219

220
```typescript { .api }
221
function check(name: string, condition: SQL): CheckBuilder;
222
```
223

224
### Primary Keys
225

226
```typescript { .api }
227
function primaryKey(config: { columns: AnyColumn[]; name?: string }): PrimaryKeyBuilder;
228
```
229

230
### Unique Constraints
231

232
```typescript { .api }
233
function unique(name?: string): UniqueConstraintBuilder;
234

235
interface UniqueConstraintBuilder {
236
  on(...columns: AnyColumn[]): this;
237
}
238
```
239

240
## Schema Organization
241

242
### Schema Definition (PostgreSQL)
243

244
```typescript { .api }
245
function pgSchema(name: string): PgSchema;
246

247
interface PgSchema {
248
  table: typeof pgTable;
249
  view: typeof pgView;
250
  enum: typeof pgEnum;
251
  sequence: typeof pgSequence;
252
}
253
```
254

255
### MySQL Schema
256

257
```typescript { .api }
258
function mysqlSchema(name: string): MySqlSchema;
259

260
interface MySqlSchema {
261
  table: typeof mysqlTable;
262
  view: typeof mysqlView;
263
}
264
```
265

266
## Relations Definition
267

268
```typescript { .api }
269
function relations<TTable extends AnyTable>(
270
  table: TTable,
271
  callback: (helpers: TableRelationsHelpers<TTable>) => Record<string, Relation>
272
): Relations<TTable>;
273

274
interface TableRelationsHelpers<TTable extends AnyTable> {
275
  one<TTargetTable extends AnyTable>(
276
    targetTable: TTargetTable,
277
    config?: RelationConfig
278
  ): One<TTargetTable>;
279
  many<TTargetTable extends AnyTable>(
280
    targetTable: TTargetTable
281
  ): Many<TTargetTable>;
282
}
283

284
interface RelationConfig {
285
  fields: AnyColumn[];
286
  references: AnyColumn[];
287
  relationName?: string;
288
}
289
```
290

291
## Usage Examples
292

293
### Complete PostgreSQL Schema
294

295
```typescript
296
import { pgTable, serial, text, varchar, timestamp, boolean, index, foreignKey } from "drizzle-orm/pg-core";
297
import { relations } from "drizzle-orm";
298

299
const users = pgTable("users", {
300
  id: serial("id").primaryKey(),
301
  name: text("name").notNull(),
302
  email: varchar("email", { length: 255 }).unique().notNull(),
303
  createdAt: timestamp("created_at").defaultNow(),
304
  isActive: boolean("is_active").default(true),
305
}, (table) => ({
306
  emailIdx: index("email_idx").on(table.email),
307
}));
308

309
const posts = pgTable("posts", {
310
  id: serial("id").primaryKey(),
311
  title: varchar("title", { length: 255 }).notNull(),
312
  content: text("content"),
313
  authorId: integer("author_id").notNull(),
314
  publishedAt: timestamp("published_at"),
315
}, (table) => ({
316
  authorFk: foreignKey({
317
    columns: [table.authorId],
318
    foreignColumns: [users.id],
319
  }),
320
  titleIdx: index("title_idx").on(table.title),
321
}));
322

323
const usersRelations = relations(users, ({ many }) => ({
324
  posts: many(posts),
325
}));
326

327
const postsRelations = relations(posts, ({ one }) => ({
328
  author: one(users, {
329
    fields: [posts.authorId],
330
    references: [users.id],
331
  }),
332
}));
333
```
334

335
### SQLite Schema with Enums
336

337
```typescript
338
import { sqliteTable, integer, text } from "drizzle-orm/sqlite-core";
339

340
const statusEnum = ["active", "inactive", "pending"] as const;
341

342
const users = sqliteTable("users", {
343
  id: integer("id").primaryKey(),
344
  name: text("name").notNull(),
345
  status: text("status", { enum: statusEnum }).default("pending"),
346
});
347
```
348

349
## Types
350

351
```typescript { .api }
352
interface TableConfig<TColumn extends Column = Column> {
353
  name: string;
354
  schema: string | undefined;
355
  columns: Record<string, TColumn>;
356
  dialect: string;
357
}
358

359
interface ColumnBaseConfig<TDataType extends ColumnDataType, TColumnType extends string> {
360
  name: string;
361
  dataType: TDataType;
362
  columnType: TColumnType;
363
  data: any;
364
  driverParam: any;
365
  notNull: boolean;
366
  hasDefault: boolean;
367
  isPrimaryKey: boolean;
368
  enumValues: any[];
369
}
370

371
type ColumnDataType = 'string' | 'number' | 'boolean' | 'date' | 'json' | 'buffer' | 'array' | 'bigint' | 'custom';
372

373
interface Table<T extends TableConfig = TableConfig> {
374
  readonly [Table.Symbol.Name]: T['name'];
375
  readonly [Table.Symbol.Schema]: T['schema'];
376
  readonly [Table.Symbol.Columns]: T['columns'];
377
}
378

379
interface Column<T extends ColumnBaseConfig<ColumnDataType, string> = ColumnBaseConfig<ColumnDataType, string>> {
380
  readonly name: T['name'];
381
  readonly dataType: T['dataType'];
382
  readonly columnType: T['columnType'];
383
  readonly notNull: T['notNull'];
384
  readonly hasDefault: T['hasDefault'];
385
  readonly isPrimaryKey: T['isPrimaryKey'];
386
}
387

388
// Database-specific table types
389
interface PgTable<T extends TableConfig = TableConfig> extends Table<T> {}
390
interface MySqlTable<T extends TableConfig = TableConfig> extends Table<T> {}
391
interface SQLiteTable<T extends TableConfig = TableConfig> extends Table<T> {}
392

393
type PgTableExtraConfig = Record<string, any>;
394
type MySqlTableExtraConfig = Record<string, any>;
395
type SQLiteTableExtraConfig = Record<string, any>;
396

397
// Column builder types
398
interface ColumnBuilder<T> {
399
  primaryKey(): this;
400
  notNull(): this;
401
  default(value: T | any): this;
402
  unique(): this;
403
  unique(name: string): this;
404
  references(column: () => AnyColumn, config?: any): this;
405
}
406

407
// PostgreSQL column builders
408
interface PgSerialBuilder extends ColumnBuilder<number> {}
409
interface PgBigSerialBuilder extends ColumnBuilder<number> {}
410
interface PgSmallSerialBuilder extends ColumnBuilder<number> {}
411
interface PgIntegerBuilder extends ColumnBuilder<number> {}
412
interface PgBigIntBuilder extends ColumnBuilder<number | bigint> {}
413
interface PgSmallIntBuilder extends ColumnBuilder<number> {}
414
interface PgNumericBuilder extends ColumnBuilder<string> {}
415
interface PgRealBuilder extends ColumnBuilder<number> {}
416
interface PgDoublePrecisionBuilder extends ColumnBuilder<number> {}
417
interface PgTextBuilder<T> extends ColumnBuilder<T> {}
418
interface PgVarcharBuilder<T> extends ColumnBuilder<T> {}
419
interface PgCharBuilder<T> extends ColumnBuilder<T> {}
420
interface PgDateBuilder extends ColumnBuilder<Date | string> {}
421
interface PgTimeBuilder extends ColumnBuilder<string> {}
422
interface PgTimestampBuilder extends ColumnBuilder<Date | string> {}
423
interface PgIntervalBuilder extends ColumnBuilder<string> {}
424
interface PgJsonBuilder<T> extends ColumnBuilder<T> {}
425
interface PgJsonbBuilder<T> extends ColumnBuilder<T> {}
426
interface PgBooleanBuilder extends ColumnBuilder<boolean> {}
427
interface PgUuidBuilder extends ColumnBuilder<string> {}
428
interface PgInetBuilder extends ColumnBuilder<string> {}
429
interface PgCidrBuilder extends ColumnBuilder<string> {}
430
interface PgMacaddrBuilder extends ColumnBuilder<string> {}
431
interface PgMacaddr8Builder extends ColumnBuilder<string> {}
432
interface PgPointBuilder extends ColumnBuilder<any> {}
433
interface PgLineBuilder extends ColumnBuilder<any> {}
434
interface PgVectorBuilder extends ColumnBuilder<number[]> {}
435
interface PgHalfvecBuilder extends ColumnBuilder<number[]> {}
436
interface PgBitBuilder extends ColumnBuilder<string> {}
437
interface PgSparsevecBuilder extends ColumnBuilder<any> {}
438
interface PgGeometryBuilder extends ColumnBuilder<any> {}
439

440
// MySQL column builders
441
interface MySqlIntBuilder extends ColumnBuilder<number> {}
442
interface MySqlTinyIntBuilder extends ColumnBuilder<number> {}
443
interface MySqlSmallIntBuilder extends ColumnBuilder<number> {}
444
interface MySqlMediumIntBuilder extends ColumnBuilder<number> {}
445
interface MySqlBigIntBuilder extends ColumnBuilder<number | bigint> {}
446
interface MySqlDecimalBuilder extends ColumnBuilder<string> {}
447
interface MySqlFloatBuilder extends ColumnBuilder<number> {}
448
interface MySqlDoubleBuilder extends ColumnBuilder<number> {}
449
interface MySqlSerialBuilder extends ColumnBuilder<number> {}
450
interface MySqlVarcharBuilder<T> extends ColumnBuilder<T> {}
451
interface MySqlCharBuilder<T> extends ColumnBuilder<T> {}
452
interface MySqlTextBuilder<T> extends ColumnBuilder<T> {}
453
interface MySqlTinyTextBuilder<T> extends ColumnBuilder<T> {}
454
interface MySqlMediumTextBuilder<T> extends ColumnBuilder<T> {}
455
interface MySqlLongTextBuilder<T> extends ColumnBuilder<T> {}
456
interface MySqlBooleanBuilder extends ColumnBuilder<boolean> {}
457
interface MySqlDateBuilder extends ColumnBuilder<Date | string> {}
458
interface MySqlDatetimeBuilder extends ColumnBuilder<Date | string> {}
459
interface MySqlTimeBuilder extends ColumnBuilder<string> {}
460
interface MySqlTimestampBuilder extends ColumnBuilder<Date | string> {}
461
interface MySqlYearBuilder extends ColumnBuilder<number> {}
462
interface MySqlJsonBuilder<T> extends ColumnBuilder<T> {}
463
interface MySqlBinaryBuilder extends ColumnBuilder<Buffer> {}
464
interface MySqlVarbinaryBuilder extends ColumnBuilder<Buffer> {}
465

466
// SQLite column builders
467
interface SQLiteIntegerBuilder extends ColumnBuilder<number> {}
468
interface SQLiteRealBuilder extends ColumnBuilder<number> {}
469
interface SQLiteTextBuilder<T> extends ColumnBuilder<T> {}
470
interface SQLiteBlobBuilder extends ColumnBuilder<Buffer> {}
471
interface SQLiteNumericBuilder extends ColumnBuilder<number> {}
472

473
// Constraint builders
474
interface IndexBuilder {
475
  on(...columns: AnyColumn[]): this;
476
  where(condition: any): this;
477
  using(method: string): this;
478
}
479

480
interface UniqueIndexBuilder extends IndexBuilder {}
481
interface ForeignKeyBuilder {
482
  onDelete(action: string): this;
483
  onUpdate(action: string): this;
484
}
485
interface CheckBuilder {}
486
interface PrimaryKeyBuilder {}
487
interface UniqueConstraintBuilder {
488
  on(...columns: AnyColumn[]): this;
489
}
490

491
// Relations
492
interface Relations<TTable extends Table> {
493
  readonly table: TTable;
494
  readonly config: any;
495
}
496

497
interface One<TTableName extends string = string> {
498
  readonly sourceTable: Table;
499
  readonly referencedTable: any;
500
  readonly config: any;
501
  withFieldName(fieldName: string): One<TTableName>;
502
}
503

504
interface Many<TTableName extends string = string> {
505
  readonly sourceTable: Table;
506
  readonly referencedTable: any;
507
  withFieldName(fieldName: string): Many<TTableName>;
508
}
509

510
interface TableRelationsHelpers<TTable extends Table> {
511
  one<TTargetTable extends Table>(targetTable: TTargetTable, config?: any): One;
512
  many<TTargetTable extends Table>(targetTable: TTargetTable): Many;
513
}
514

515
interface RelationConfig {
516
  fields: AnyColumn[];
517
  references: AnyColumn[];
518
  relationName?: string;
519
}
520

521
type AnyColumn = Column<any>;
522
type AnyTable = Table<any>;
523
type AnyPgColumn = Column<ColumnBaseConfig<any, 'PgColumn'>>;
524
type AnyMySqlColumn = Column<ColumnBaseConfig<any, 'MySqlColumn'>>;
525
type AnySQLiteColumn = Column<ColumnBaseConfig<any, 'SQLiteColumn'>>;
526

527
interface PgSchema {
528
  table: typeof pgTable;
529
  view: any;
530
  enum: any;
531
  sequence: any;
532
}
533

534
interface MySqlSchema {
535
  table: typeof mysqlTable;
536
  view: any;
537
}
538
```