validation/zod

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

Views supported in Zod schema generation

Views are supported with createSelectSchema. You can generate a Zod schema from a mysqlView definition using createSelectSchema(usersView).

createSelectSchema function for validating query results

The createSelectSchema function generates a Zod validation schema from a Drizzle table or view definition. It defines the shape of data queried from the database and can be used to validate API responses. The schema enforces that all selected columns are present in the parsed data—attempting to parse results that do not include all fields defined in the schema will fail validation.

createInsertSchema function for validating insert data

The createInsertSchema function generates a Zod validation schema from a table definition to validate data before insertion. It defines the shape of data to be inserted into the database and can be used to validate API requests. Auto-increment primary key columns become optional in the schema (e.g., id?: number | undefined), while non-nullable columns remain required unless they have default values.

createUpdateSchema function for validating update data

The createUpdateSchema function generates a Zod validation schema from a table definition to validate data before updates. Unlike insert schemas, all fields in an update schema become optional, allowing partial updates where only some columns need to be modified.

Schema refinements with callback functions and Zod schemas

Each create schema function (createSelectSchema, createInsertSchema, createUpdateSchema) accepts an optional second parameter for refinements. Providing a callback function extends or modifies a field's schema (e.g., schema => schema.max(20)), while providing a Zod schema directly overwrites the field entirely, including its nullability settings.

createSchemaFactory for advanced schema generation

The createSchemaFactory function provides advanced schema generation capabilities. It accepts a configuration object with two main options: (1) zodInstance - allows using an extended Zod instance (e.g., from @hono/zod-openapi); (2) coerce - enables type coercion for specified data types (e.g., { date: true } for dates only, or true for all types).

Zod schema generation for mysql.boolean()

The mysql.boolean() column type generates the Zod schema z.boolean().

Zod schema generation for mysql.mysqlEnum()

The mysql.mysqlEnum('name', ['val1', 'val2']) column type generates the Zod schema z.enum(['val1', 'val2']).

Zod schema generation for date types

When mode is 'date', mysql.date({ mode: 'date' }), mysql.datetime({ mode: 'date' }), and mysql.timestamp({ mode: 'date' }) all generate the Zod schema z.date(). When mode is 'string', they generate z.string().

Zod schema generation for binary types

The mysql.binary(), mysql.varbinary(), and string-mode variants of date/datetime/timestamp generate z.string(). Binary types with specified lengths use regex patterns: mysql.binary({ length: ... }) generates schema.regex(/^[01]*$/).max(length), and mysql.varbinary({ length: ... }) generates schema.regex(/^[01]*$/).max(length).

Zod schema generation for blob types

mysql.tinyblob() and mysql.tinyblob({ mode: 'string' }) generate z.string().max(255). mysql.blob() and mysql.blob({ mode: 'string' }) generate z.string().max(65_535). mysql.mediumblob() and mysql.mediumblob({ mode: 'string' }) generate z.string().max(16_777_215). mysql.longblob() and mysql.longblob({ mode: 'string' }) generate z.string().max(4_294_967_295).

Zod schema generation for varchar type

mysql.varchar({ length: ... }) generates z.string().max(length).

Zod schema generation for text types

mysql.tinytext() generates z.string().max(255). mysql.text() generates z.string().max(65_535). mysql.mediumtext() generates z.string().max(16_777_215). mysql.longtext() generates z.string().max(4_294_967_295).

Zod schema generation for enum text types

mysql.tinytext({ enum: ... }), mysql.mediumtext({ enum: ... }), mysql.text({ enum: ... }), mysql.longtext({ enum: ... }), mysql.char({ enum: ... }), and mysql.varchar({ enum: ... }) all generate z.enum(enum).

Zod schema generation for mediumint type

mysql.mediumint() generates z.number().min(-8_388_608).max(8_388_607).int() (24-bit signed integer range). mysql.mediumint({ unsigned: true }) generates z.number().min(0).max(16_777_215).int() (24-bit unsigned integer range).

Zod schema generation for float type

mysql.float() generates z.number().min(-8_388_608).max(8_388_607) (24-bit signed range). mysql.float({ unsigned: true }) generates z.number().min(0).max(16_777_215) (24-bit unsigned range).

Zod schema generation for int type

mysql.int() generates z.number().min(-2_147_483_648).max(2_147_483_647).int() (32-bit signed integer range). mysql.int({ unsigned: true }) generates z.number().min(0).max(4_294_967_295).int() (32-bit unsigned integer range).

Zod schema generation for double and real types

mysql.double() and mysql.real() generate z.number().min(-140_737_488_355_328).max(140_737_488_355_327) (48-bit signed range). mysql.double({ unsigned: true }) generates z.number().min(0).max(281_474_976_710_655) (48-bit unsigned range).

Zod schema generation for decimal type

mysql.decimal({ mode: 'number' }) generates z.number().min(-9_007_199_254_740_991).max(9_007_199_254_740_991). mysql.decimal({ mode: 'number', unsigned: true }) generates z.number().min(0).max(9_007_199_254_740_991). mysql.decimal({ mode: 'bigint' }) generates z.bigint().min(-9_223_372_036_854_775_808n).max(9_223_372_036_854_775_807n). mysql.decimal({ mode: 'bigint', unsigned: true }) generates z.bigint().min(0).max(18_446_744_073_709_551_615n).

Zod schema generation for bigint type

mysql.bigint({ mode: 'number' }) generates z.number().min(-9_007_199_254_740_991).max(9_007_199_254_740_991).int() (JavaScript safe integer range). mysql.bigint({ mode: 'number', unsigned: true }) generates z.number().min(0).max(9_007_199_254_740_991).int(). mysql.bigint({ mode: 'string' }) generates z.string().regex(/^-?\d+$/).transform(BigInt).pipe(zod.bigint().gte(-9_223_372_036_854_775_808n).lte(9_223_372_036_854_775_807n)).transform(String). mysql.bigint({ mode: 'string', unsigned: true }) generates z.string().regex(/^\d+$/).transform(BigInt).pipe(zod.bigint().gte(0n).lte(9_223_372_036_854_775_807n)).transform(String). mysql.bigint({ mode: 'bigint' }) generates z.bigint().min(-9_223_372_036_854_775_808n).max(9_223_372_036_854_775_807n) (64-bit signed range). mysql.bigint({ mode: 'bigint', unsigned: true }) generates z.bigint().min(0).max(18_446_744_073_709_551_615n) (64-bit unsigned range).

Zod schema generation for serial type

mysql.serial() generates z.number().min(0).max(9_007_199_254_740_991).int() (JavaScript maximum safe integer).

Zod schema generation for year type

mysql.year() generates z.number().min(1_901).max(2_155).int().

Zod schema generation for json type

mysql.json() generates z.union([z.union([z.string(), z.number(), z.boolean(), z.null()]), z.record(z.any()), z.array(z.any())]).

Example of createSelectSchema with SELECT query validation

```ts import { int, mysqlTable, text } from 'drizzle-orm/mysql-core'; import { createSelectSchema } from 'drizzle-orm/zod'; const users = mysqlTable('users', { id: int().primaryKey().autoincrement(), name: text().notNull(), age: int().notNull() }); const userSelectSchema = createSelectSchema(users); const rows = await db.select({ id: users.id, name: users.name }).from(users).limit(1); const parsed: { id: number; name: string; age: number } = userSelectSchema.parse(rows[0]); // Error: `age` is not returned in the above query const rows = await db.select().from(users).limit(1); const parsed: { id: number; name: string; age: number } = userSelectSchema.parse(rows[0]); // Will parse successfully ``` This example shows that createSelectSchema validates that all table fields are present in the query results.

Example of createInsertSchema with partial data validation

```ts import { int, mysqlTable, text } from 'drizzle-orm/mysql-core'; import { createInsertSchema } from 'drizzle-orm/zod'; const users = mysqlTable('users', { id: int().primaryKey().autoincrement(), name: text().notNull(), age: int().notNull() }); const userInsertSchema = createInsertSchema(users); const user = { name: 'John' }; const parsed: { id?: number | undefined, name: string, age: number } = userInsertSchema.parse(user); // Error: `age` is not defined const user = { name: 'Jane', age: 30 }; const parsed: { id?: number | undefined, name: string, age: number } = userInsertSchema.parse(user); // Will parse successfully await db.insert(users).values(parsed); ``` This example shows that createInsertSchema makes auto-increment primary keys optional and requires non-nullable columns.

Example of createUpdateSchema with optional fields

```ts import { int, mysqlTable, text } from 'drizzle-orm/mysql-core'; import { createUpdateSchema } from 'drizzle-orm/zod'; import { eq } from "drizzle-orm"; const users = mysqlTable('users', { id: int().primaryKey().autoincrement(), name: text().notNull(), age: int().notNull() }); const userUpdateSchema = createUpdateSchema(users); const user = { age: 35 }; const parsed: { id?: number | undefined; name?: string | undefined; age?: number | undefined } = userUpdateSchema.parse(user); // Will parse successfully await db.update(users).set(parsed).where(eq(users.name, 'Jane')); ``` This example shows that createUpdateSchema makes all fields optional to support partial updates.

Example of schema refinements with callbacks and overwrites

```ts import { int, json, mysqlTable, text } from 'drizzle-orm/mysql-core'; import { createSelectSchema } from 'drizzle-orm/zod'; import { z } from 'zod/v4'; const users = mysqlTable('users', { id: int().primaryKey(), name: text().notNull(), bio: text(), preferences: json() }); const userSelectSchema = createSelectSchema(users, { name: (schema) => schema.max(20), // Extends schema bio: (schema) => schema.max(1000), // Extends schema before becoming nullable/optional preferences: z.object({ theme: z.string() }) // Overwrites the field, including its nullability }); const parsed: { id: number; name: string, bio: string | null; preferences: { theme: string; }; } = userSelectSchema.parse(...); ``` This example demonstrates extending schemas with callbacks and completely overwriting field schemas with custom Zod schemas.

Example of createSchemaFactory with extended Zod instance

```ts import { int, mysqlTable, text } from 'drizzle-orm/mysql-core'; import { createSchemaFactory } from 'drizzle-orm/zod'; import { z } from '@hono/zod-openapi'; // Extended Zod instance const users = mysqlTable('users', { id: int().primaryKey().autoincrement(), name: text().notNull(), age: int().notNull() }); const { createInsertSchema } = createSchemaFactory({ zodInstance: z }); const userInsertSchema = createInsertSchema(users, { // We can now use the extended instance name: (schema) => schema.openapi({ example: 'John' }) }); ``` This example shows how to use createSchemaFactory to provide an extended Zod instance for advanced use cases like OpenAPI integration.

Example of createSchemaFactory with type coercion

```ts import { mysqlTable, timestamp } from 'drizzle-orm/mysql-core'; import { createSchemaFactory } from 'drizzle-orm/zod'; import { z } from 'zod/v4'; const users = mysqlTable('users', { ..., createdAt: timestamp().notNull() }); const { createInsertSchema } = createSchemaFactory({ // This configuration will only coerce dates. Set `coerce` to `true` to coerce all data types or specify others coerce: { date: true } }); const userInsertSchema = createInsertSchema(users); // The above is the same as this: const userInsertSchema = z.object({ ..., createdAt: z.coerce.date() }); ``` This example shows how to use createSchemaFactory with the coerce option to enable type coercion for specific data types.

Give your agent this brain