Install Zod with Drizzle ORM for MSSQL
To use Zod with Drizzle ORM for MSSQL, install drizzle-orm@rc and zod as dependencies.
mssql/zod
11 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 example for MSSQL table
import { int, mssqlTable, text } from 'drizzle-orm/mssql-core'; import { createSelectSchema } from 'drizzle-orm/zod'; const users = mssqlTable('users', { id: int().primaryKey().identity(), name: text().notNull(), age: int().notNull() }); const userSelectSchema = createSelectSchema(users); const rows = await db.select({ id: users.id, name: users.name }).top(1).from(users); const parsed: { id: number; name: string; age: number } = userSelectSchema.parse(rows[0]); // Error: `age` is not returned in the query const rows = await db.select().top(1).from(users); const parsed: { id: number; name: string; age: number } = userSelectSchema.parse(rows[0]); // Will parse successfully
createSelectSchema works with MSSQL views
The createSelectSchema function can also be used with MSSQL views created using mssqlView to generate Zod schemas for view data.
createSelectSchema with MSSQL view example
import { createSelectSchema } from 'drizzle-orm/zod'; import { mssqlView } from 'drizzle-orm/mssql-core'; const usersView = mssqlView('users_view').as((qb) => qb.select().from(users).where(gt(users.age, 18))); const usersViewSchema = createSelectSchema(usersView); const parsed: { id: number; name: string; age: number } = usersViewSchema.parse(...);
createInsertSchema example for MSSQL table
import { int, mssqlTable, text } from 'drizzle-orm/mssql-core'; import { createInsertSchema } from 'drizzle-orm/zod'; const users = mssqlTable('users', { id: int().primaryKey().identity(), name: text().notNull(), age: int().notNull() }); const userInsertSchema = createInsertSchema(users); const user = { name: 'John' }; const parsed: { name: string, age: number } = userInsertSchema.parse(user); // Error: `age` is not defined const user = { name: 'Jane', age: 30 }; const parsed: { name: string, age: number } = userInsertSchema.parse(user); // Will parse successfully await db.insert(users).values(parsed);
createUpdateSchema example for MSSQL table
import { int, mssqlTable, text } from 'drizzle-orm/mssql-core'; import { createUpdateSchema } from 'drizzle-orm/zod'; import { eq } from 'drizzle-orm'; const users = mssqlTable('users', { id: int().primaryKey().identity(), name: text().notNull(), age: int().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'));
Schema refinements example with callback and overwrite
import { int, mssqlTable, text } from 'drizzle-orm/mssql-core'; import { createSelectSchema } from 'drizzle-orm/zod'; import { z } from 'zod/v4'; const users = mssqlTable('users', { id: int().primaryKey(), name: text().notNull(), bio: text(), preferences: text() }); 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(...);
createSchemaFactory function for advanced use cases
The createSchemaFactory function from 'drizzle-orm/zod' allows for more advanced use cases such as using an extended Zod instance or configuring type coercion. It returns an object with createInsertSchema, createSelectSchema, and createUpdateSchema methods.
createSchemaFactory with extended Zod instance example
import { int, mssqlTable, text } from 'drizzle-orm/mssql-core'; import { createSchemaFactory } from 'drizzle-orm/zod'; import { z } from '@hono/zod-openapi'; // Extended Zod instance const users = mssqlTable('users', { id: int().primaryKey().identity(), 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' }) });
createSchemaFactory with type coercion example
import { mssqlTable, datetime2 } from 'drizzle-orm/mssql-core'; import { createSchemaFactory } from 'drizzle-orm/zod'; import { z } from 'zod/v4'; const users = mssqlTable('users', { ..., createdAt: datetime2().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() });
MSSQL Zod data types follow MSSQL column builders
MSSQL data type mappings in Zod schemas follow the MSSQL column builders. Refer to the MSSQL column types documentation for the full reference of available column types.