Drizzle ORM + drizzle-zod validated inserts on SQLite: working versions (tested 2026-10-15)

{
  "name": "drizzle-zod-insert-schemas",
  "private": true,
  "type": "module",
  "scripts": {
    "test": "node --import tsx --test test/*.test.ts"
  },
  "dependencies": {
    "drizzle-orm": "0.45.4",
    "drizzle-zod": "0.8.3",
    "zod": "4.6.7",
    "better-sqlite3": "12.11.1"
  },
  "devDependencies": {
    "tsx": "4.23.15",
    "@types/node": "24.19.2",
    "@types/better-sqlite3": "9.6.0"
  }
}

import Database from "better-sqlite3";
import { drizzle } from "drizzle-orm/better-sqlite3";
import { sqliteTable, integer, text } from "drizzle-orm/sqlite-core";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";
import { z } from "zod";

export const products = sqliteTable("products", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
  priceCents: integer("price_cents").notNull(),
  sku: text("sku").notNull(),
});

// Refine generated schemas instead of hand-writing a second copy of the table.
export const insertProduct = createInsertSchema(products, {
  name: (s) => s.min(2).max(100),
  priceCents: (s) => s.int().positive(),
  sku: (s) => s.regex(/^[A-Z]{3}-\d{4}$/),
}).omit({ id: true });
export const selectProduct = createSelectSchema(products);
export type NewProduct = z.infer<typeof insertProduct>;

export function createDb() {
  const sqlite = new Database(":memory:");
  sqlite.exec("create table products (id integer primary key autoincrement, name text not null, price_cents integer not null, sku text not null)");
  return drizzle(sqlite);
}

export function addProduct(db: ReturnType<typeof createDb>, input: unknown) {
  const data = insertProduct.parse(input);
  return selectProduct.parse(db.insert(products).values(data).returning().get());
}

import { test } from "node:test";
import assert from "node:assert/strict";
import { ZodError } from "zod";
import { createDb, addProduct } from "../src/db.ts";

test("valid input is inserted", () => {
  const p = addProduct(createDb(), { name: "Mug", priceCents: 1299, sku: "MUG-0001" });
  assert.deepEqual(p, { id: 1, name: "Mug", priceCents: 1299, sku: "MUG-0001" });
});

test("invalid input never reaches the database", () => {
  const db = createDb();
  assert.throws(() => addProduct(db, { name: "M", priceCents: -1, sku: "bad" }), ZodError);
  assert.throws(() => addProduct(db, { name: "Mug", priceCents: 5 }), ZodError);
});