Zod schema generation from SQLite views
createSelectSchema supports views in addition to tables. You can pass a sqliteView to createSelectSchema to generate a Zod schema that validates data from that view.
Drizzle · SQLite · all subjects
zod
27 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
createSelectSchema for validating SELECT queries
createSelectSchema generates a Zod schema from a table or view that validates data queried from the database. It can be used to validate API responses. The schema enforces that all columns returned in the SELECT query match the expected structure.
createSelectSchema validates full column match
When using createSelectSchema, parsing will fail if the SELECT query does not return all columns defined in the table schema. For example, if a table has id, name, and age columns, but the SELECT only returns id and name, parsing will error because age is missing.
createInsertSchema for validating INSERT requests
createInsertSchema generates a Zod schema that validates data before inserting into the database. It can be used to validate API requests. Columns with autoIncrement or default values are optional in the schema, while other notNull columns are required.
createUpdateSchema for validating UPDATE requests
createUpdateSchema generates a Zod schema that validates data for UPDATE operations. All fields in an update schema are optional, allowing partial updates. The schema can be used to validate API requests.
Zod schema refinements with callback functions
When defining refinements in createSelectSchema, createInsertSchema, or createUpdateSchema, passing a callback function extends or modifies the generated schema for that field. The callback receives the generated schema and returns a modified version.
Zod schema refinements with Zod schemas
When defining refinements in schema creation functions, passing a Zod schema object directly overwrites the entire field schema, including its nullability and optionality. Use this when you need complete control over a field's validation.
createSchemaFactory for custom Zod instances
createSchemaFactory allows using an extended Zod instance (such as @hono/zod-openapi) with Drizzle schema generation. Pass the extended Zod instance via the zodInstance option: createSchemaFactory({ zodInstance: z }).
createSchemaFactory type coercion configuration
createSchemaFactory accepts a coerce option to enable type coercion. Set coerce to true to coerce all data types, or pass an object like { date: true } to coerce only specific types. This automatically wraps field schemas with z.coerce methods.
Zod schema for integer boolean mode
For sqlite.integer({ mode: 'boolean' }), the generated Zod schema is z.boolean().
Zod schema for integer timestamp modes
For sqlite.integer({ mode: 'timestamp' }) and sqlite.integer({ mode: 'timestamp_ms' }), the generated Zod schema is z.date().
Zod schema for numeric and text string modes
For sqlite.numeric() and sqlite.text({ mode: 'text' }), the generated Zod schema is z.string().
Zod schema for text with length constraint
For sqlite.text({ mode: 'text', length: ... }), the generated Zod schema is z.string().max(length), applying a maximum length constraint.
Zod schema for text enum mode
For sqlite.text({ mode: 'text', enum: ... }), the generated Zod schema is z.enum(enum), restricting values to the specified enum values.
Zod schema for real type
For sqlite.real(), the generated Zod schema is z.number().min(-140_737_488_355_328).max(140_737_488_355_327), enforcing 48-bit integer limits.
Zod schema for integer number mode
For sqlite.integer({ mode: 'number' }), the generated Zod schema is z.number().min(-9_007_199_254_740_991).max(9_007_199_254_740_991).int(), enforcing JavaScript safe integers and integer type.
Zod schema for blob bigint mode
For sqlite.blob({ mode: 'bigint' }), the generated Zod schema is z.bigint().min(-9_223_372_036_854_775_808n).max(9_223_372_036_854_775_807n), enforcing 64-bit integer limits.
Zod schema for JSON modes
For sqlite.blob({ mode: 'json' }) and sqlite.text({ mode: 'json' }), the generated Zod schema is z.union([z.union([z.string(), z.number(), z.boolean(), z.null()]), z.record(z.any()), z.array(z.any())]), allowing strings, numbers, booleans, null, objects, or arrays.
Zod schema for blob buffer mode
For sqlite.blob({ mode: 'buffer' }), the generated Zod schema is z.custom<Buffer>((v) => v instanceof Buffer), validating that the value is a Buffer instance.
Example: createSelectSchema with partial SELECT
const users = sqliteTable('users', { id: integer().primaryKey({ autoIncrement: true }), name: text().notNull(), age: integer().notNull() }); const userSelectSchema = createSelectSchema(users); const rows = await db.select({ id: users.id, name: users.name }).from(users).limit(1); const parsed = userSelectSchema.parse(rows[0]); // Error: age is not returned
Example: createSelectSchema with full SELECT
const users = sqliteTable('users', { id: integer().primaryKey({ autoIncrement: true }), name: text().notNull(), age: integer().notNull() }); const userSelectSchema = createSelectSchema(users); const rows = await db.select().from(users).limit(1); const parsed: { id: number; name: string; age: number } = userSelectSchema.parse(rows[0]); // Will parse successfully
Example: createInsertSchema missing required field
const users = sqliteTable('users', { id: integer().primaryKey({ autoIncrement: true }), name: text().notNull(), age: integer().notNull() }); const userInsertSchema = createInsertSchema(users); const user = { name: 'John' }; const parsed = userInsertSchema.parse(user); // Error: age is not defined
Example: createInsertSchema with all required fields
const users = sqliteTable('users', { id: integer().primaryKey({ autoIncrement: true }), name: text().notNull(), age: integer().notNull() }); const userInsertSchema = createInsertSchema(users); const user = { name: 'Jane', age: 30 }; const parsed: { name: string, age: number } = userInsertSchema.parse(user); // Will parse successfully await db.insert(users).values(parsed);
Example: createUpdateSchema with partial update
const users = sqliteTable('users', { id: integer().primaryKey({ autoIncrement: true }), name: text().notNull(), age: integer().notNull() }); const userUpdateSchema = createUpdateSchema(users); const user = { age: 35 }; const parsed: { name?: string | undefined, age?: number | undefined } = userUpdateSchema.parse(user); // Will parse successfully await db.update(users).set(parsed).where(eq(users.name, 'Jane'));
Example: createSelectSchema with refinements
const users = sqliteTable('users', { id: integer().primaryKey(), name: text().notNull(), bio: text(), preferences: text({ mode: 'json' }) }); const userSelectSchema = createSelectSchema(users, { name: (schema) => schema.max(20), bio: (schema) => schema.max(1000), preferences: z.object({ theme: z.string() }) }); const parsed = userSelectSchema.parse(...);
Example: createSchemaFactory with extended Zod instance
import { createSchemaFactory } from 'drizzle-orm/zod'; import { z } from '@hono/zod-openapi'; const users = sqliteTable('users', { id: integer().primaryKey({ autoIncrement: true }), name: text().notNull(), age: integer().notNull() }); const { createInsertSchema } = createSchemaFactory({ zodInstance: z }); const userInsertSchema = createInsertSchema(users, { name: (schema) => schema.openapi({ example: 'John' }) });
Example: createSchemaFactory with type coercion
import { createSchemaFactory } from 'drizzle-orm/zod'; import { z } from 'zod/v4'; const users = sqliteTable('users', { createdAt: integer({ mode: 'timestamp' }).notNull() }); const { createInsertSchema } = createSchemaFactory({ coerce: { date: true } }); const userInsertSchema = createInsertSchema(users); // createdAt field is z.coerce.date()