drizzle-orm/schemas

46 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Schema declaration with pgTable for PostgreSQL

Tables are declared using pgTable from drizzle-orm/pg-core. Each table is defined with a name string and a columns object. Column types include serial() for auto-incrementing integers, text() for strings, integer() for whole numbers, and timestamp() for dates. Modifiers include .primaryKey(), .notNull(), .unique(), .references() for foreign keys, .defaultNow() for timestamp defaults, and .$onUpdate() for update triggers.

Infer insert and select types from schema

Use typeof tableName.$inferInsert to get the TypeScript type for inserting rows, and typeof tableName.$inferSelect to get the type for selecting/reading rows. Example: export type InsertUser = typeof usersTable.$inferInsert; export type SelectUser = typeof usersTable.$inferSelect;

Default timestamp with defaultNow

Use .defaultNow() on a timestamp column to automatically set it to the current time when a row is inserted. Example: createdAt: p.timestamp().notNull().defaultNow();

Update timestamp with $onUpdate

Use .$onUpdate(() => new Date()) on a timestamp column to automatically update it to the current time whenever the row is modified. Example: updatedAt: p.timestamp().notNull().$onUpdate(() => new Date());

typebox schema generation features

The typebox integration with Drizzle ORM allows you to create a select schema for tables, views and enums, and create insert and update schemas for tables. It supports dialects: CockroachDB, MSSQL, MySQL, PostgreSQL, SingleStore, SQLite.

createInsertSchema, createSelectSchema, createUpdateSchema

These functions are imported from 'drizzle-orm/typebox'. createInsertSchema generates a schema for inserting records and can be used to validate API requests. createSelectSchema generates a schema for selecting records and can be used to validate API responses. createUpdateSchema generates a schema for updating records and can be used to validate API requests.

Override fields in typebox schema generation

When using createInsertSchema, createSelectSchema, or createUpdateSchema, you can override individual fields by passing a second argument object that maps field names to their new typebox Type definitions. This allows you to customize the generated schema for specific fields.

Refine fields in typebox schema generation

When generating schemas with createInsertSchema, createSelectSchema, or createUpdateSchema, you can refine fields by passing a function as the value in the second argument. The function receives the original schema and returns a modified Type definition. This is useful for changing fields before they become nullable or optional in the final schema.

typebox schema generation example

import { pgEnum, pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core'; import { createInsertSchema, createSelectSchema, createUpdateSchema } from 'drizzle-orm/typebox'; import { Type } from 'typebox'; import { Value } from 'typebox/value'; const users = pgTable('users', { id: serial('id').primaryKey(), name: text('name').notNull(), email: text('email').notNull(), role: text('role', { enum: ['admin', 'user'] }).notNull(), createdAt: timestamp('created_at').notNull().defaultNow(), }); const insertUserSchema = createInsertSchema(users); const updateUserSchema = createUpdateSchema(users); const selectUserSchema = createSelectSchema(users); const insertUserSchema = createInsertSchema(users, { role: Type.String(), }); const insertUserSchema = createInsertSchema(users, { id: (schema) => Type.Number({ ...schema, minimum: 0 }), role: Type.String(), }); const isUserValid: boolean = Value.Check(insertUserSchema, { name: 'John Doe', email: 'johndoe@test.com', role: 'admin', });

Example of Drizzle schema definition

The following code shows how to define database tables in TypeScript: ```typescript export const countries = pgTable('countries', { id: serial('id').primaryKey(), name: varchar('name', { length: 256 }), }); export const cities = pgTable('cities', { id: serial('id').primaryKey(), name: varchar('name', { length: 256 }), countryId: integer('country_id').references(() => countries.id), }); ```

drizzle-zod deprecated in favor of first-class schema generation

Starting from drizzle-orm@1.0.0-beta.15, drizzle-zod has been deprecated in favor of first-class schema generation support within Drizzle ORM itself. The drizzle-zod package can still be used but all new updates will be added to Drizzle ORM directly.

createSelectSchema function for select validation

The createSelectSchema function from drizzle-orm/zod creates a Zod schema that defines the shape of data queried from the database. It can be used to validate API responses. The schema will only successfully parse rows that include all columns defined in the table schema.

createSelectSchema supports views and enums

The createSelectSchema function works with pgView and pgEnum in addition to tables. For enums, it validates that the value is one of the defined enum values. For views, it creates a schema matching the view's result shape.

createInsertSchema function for insert validation

The createInsertSchema function from drizzle-orm/zod creates a Zod schema that defines the shape of data to be inserted into the database. It can be used to validate API requests. The schema accounts for columns with defaults or auto-generation and requires all other columns.

createUpdateSchema function for update validation

The createUpdateSchema function from drizzle-orm/zod creates a Zod schema that defines the shape of data to be updated in the database. It can be used to validate API requests. Generated columns cannot be updated and will cause validation errors if included.

Schema refinements with callbacks or Zod schemas

Each create schema function (createSelectSchema, createInsertSchema, createUpdateSchema) accepts an optional second parameter for refinements. Passing a callback function extends or modifies the field's schema. Passing a Zod schema directly overwrites the field completely, including its nullability and optionality.

createSchemaFactory for advanced use cases

The createSchemaFactory function allows advanced use cases. It accepts a configuration object and returns create schema functions. It supports using an extended Zod instance via the zodInstance property and type coercion configuration via the coerce property.

Type coercion with createSchemaFactory

The createSchemaFactory function accepts a coerce configuration object to enable type coercion. Set coerce to true to coerce all data types, or specify individual types like { date: true } to coerce only dates. When enabled, schema fields will use z.coerce methods like z.coerce.date().

Boolean type Zod mapping

Boolean columns map to z.boolean() in Zod schemas. Supported column types: pg.boolean(), mysql.boolean(), sqlite.integer({ mode: 'boolean' }).

Date type Zod mapping

Date columns map to z.date() in Zod schemas. Supported column types: pg.date({ mode: 'date' }), pg.timestamp({ mode: 'date' }), mysql.date({ mode: 'date' }), mysql.datetime({ mode: 'date' }), mysql.timestamp({ mode: 'date' }), sqlite.integer({ mode: 'timestamp' }), sqlite.integer({ mode: 'timestamp_ms' }).

String type Zod mapping

String columns map to z.string() in Zod schemas. Supported column types: pg.date({ mode: 'string' }), pg.timestamp({ mode: 'string' }), pg.cidr(), pg.inet(), pg.interval(), pg.macaddr(), pg.macaddr8(), pg.numeric(), pg.text(), pg.sparsevec(), pg.time(), mysql.binary(), mysql.date({ mode: 'string' }), mysql.datetime({ mode: 'string' }), mysql.decimal(), mysql.time(), mysql.timestamp({ mode: 'string' }), mysql.varbinary(), sqlite.numeric(), sqlite.text({ mode: 'text' }).

Bit type Zod mapping

Bit columns map to z.string().regex(/^[01]+$/).max(dimensions) in Zod schemas. Supported column type: pg.bit({ dimensions: ... }).

UUID type Zod mapping

UUID columns map to z.string().uuid() in Zod schemas. Supported column type: pg.uuid().

Char type Zod mapping

Char columns map to z.string().length(length) in Zod schemas. Supported column types: pg.char({ length: ... }), mysql.char({ length: ... }).

Varchar type Zod mapping

Varchar columns map to z.string().max(length) in Zod schemas. Supported column types: pg.varchar({ length: ... }), mysql.varchar({ length: ... }), sqlite.text({ mode: 'text', length: ... }).

MySQL tinytext type Zod mapping

MySQL tinytext columns map to z.string().max(255) in Zod schemas. The limit of 255 corresponds to the unsigned 8-bit integer limit. Supported column type: mysql.tinytext().

MySQL longtext type Zod mapping

MySQL longtext columns map to z.string().max(4294967295) in Zod schemas. The limit of 4,294,967,295 corresponds to the unsigned 32-bit integer limit. Supported column type: mysql.longtext().

Enum type Zod mapping

Enum columns map to z.enum(enum) in Zod schemas. Supported column types: pg.text({ enum: ... }), pg.char({ enum: ... }), pg.varchar({ enum: ... }), mysql.tinytext({ enum: ... }), mysql.mediumtext({ enum: ... }), mysql.text({ enum: ... }), mysql.longtext({ enum: ... }), mysql.char({ enum: ... }), mysql.varchar({ enum: ... }), mysql.mysqlEnum(..., ...), sqlite.text({ mode: 'text', enum: ... }).

MySQL tinyint type Zod mapping

MySQL tinyint columns map to z.number().min(-128).max(127).int() in Zod schemas. The range corresponds to the 8-bit integer lower and upper limit. Supported column type: mysql.tinyint().

Smallint type Zod mapping

Smallint columns map to z.number().min(-32768).max(32767).int() in Zod schemas. The range corresponds to the 16-bit integer lower and upper limit. Supported column types: pg.smallint(), pg.smallserial(), mysql.smallint().

Real and float type Zod mapping

Real and float columns map to z.number().min(-8388608).max(8388607) in Zod schemas. The range corresponds to the 24-bit integer lower and upper limit. Supported column types: pg.real(), mysql.float().

MySQL mediumint type Zod mapping

MySQL mediumint columns map to z.number().min(-8388608).max(8388607).int() in Zod schemas. The range corresponds to the 24-bit integer lower and upper limit. Supported column type: mysql.mediumint().

Integer type Zod mapping

Integer columns map to z.number().min(-2147483648).max(2147483647).int() in Zod schemas. The range corresponds to the 32-bit integer lower and upper limit. Supported column types: pg.integer(), pg.serial(), mysql.int().

Double precision type Zod mapping

Double precision columns map to z.number().min(-140737488355328).max(140737488355327) in Zod schemas. The range corresponds to the 48-bit integer lower and upper limit. Supported column types: pg.doublePrecision(), mysql.double(), mysql.real(), sqlite.real().

MySQL unsigned double type Zod mapping

MySQL unsigned double columns map to z.number().min(0).max(281474976710655) in Zod schemas. The range corresponds to the unsigned 48-bit integer lower and upper limit. Supported column type: mysql.double({ unsigned: true }).

MySQL serial type Zod mapping

MySQL serial columns map to z.number().min(0).max(9007199254740991).int() in Zod schemas. The range corresponds to JavaScript's maximum safe integer. Supported column type: mysql.serial().

Bigint with mode bigint type Zod mapping

Bigint columns with mode 'bigint' map to z.bigint().min(-9223372036854775808n).max(9223372036854775807n) in Zod schemas. The range corresponds to the 64-bit integer lower and upper limit. Supported column types: pg.bigint({ mode: 'bigint' }), pg.bigserial({ mode: 'bigint' }), mysql.bigint({ mode: 'bigint' }), sqlite.blob({ mode: 'bigint' }).

MySQL unsigned bigint with mode bigint type Zod mapping

MySQL unsigned bigint columns with mode 'bigint' map to z.bigint().min(0).max(18446744073709551615n) in Zod schemas. The range corresponds to the unsigned 64-bit integer lower and upper limit. Supported column type: mysql.bigint({ mode: 'bigint', unsigned: true }).

MySQL year type Zod mapping

MySQL year columns map to z.number().min(1901).max(2155).int() in Zod schemas. Supported column type: mysql.year().

Point geometry with xy mode type Zod mapping

Point geometry columns with mode 'xy' map to z.object({ x: z.number(), y: z.number() }) in Zod schemas. Supported column types: pg.geometry({ type: 'point', mode: 'xy' }), pg.point({ mode: 'xy' }).

Halfvec and vector type Zod mapping

Halfvec and vector columns map to z.array(z.number()).length(dimensions) in Zod schemas. Supported column types: pg.halfvec({ dimensions: ... }), pg.vector({ dimensions: ... }).

Line geometry with abc mode type Zod mapping

Line geometry columns with mode 'abc' map to z.object({ a: z.number(), b: z.number(), c: z.number() }) in Zod schemas. Supported column type: pg.line({ mode: 'abc' }).

Line geometry with tuple mode type Zod mapping

Line geometry columns with mode 'tuple' map to z.tuple([z.number(), z.number(), z.number()]) in Zod schemas. Supported column type: pg.line({ mode: 'tuple' }).

JSON type Zod mapping

JSON columns map to z.union([z.union([z.string(), z.number(), z.boolean(), z.null()]), z.record(z.any()), z.array(z.any())]) in Zod schemas. Supported column types: pg.json(), pg.jsonb(), mysql.json(), sqlite.blob({ mode: 'json' }), sqlite.text({ mode: 'json' }).

Buffer type Zod mapping

Buffer columns map to z.custom<Buffer>((v) => v instanceof Buffer) in Zod schemas. Supported column type: sqlite.blob({ mode: 'buffer' }).

Array type Zod mapping

Array columns map to z.array(baseDataTypeSchema).length(size) in Zod schemas. The baseDataTypeSchema corresponds to the Zod schema for the array's element data type. Supported column type: pg.dataType().array(...).

Give your agent this brain