Drizzle Orm

Drizzle ORM Skill

Version Verification (MANDATORY)

Before writing any Drizzle code, verify current versions. The beta evolves rapidly.

Run these checks at the start of every Drizzle task:

# Check latest stable and beta versions
npm view drizzle-orm dist-tags --json
npm view drizzle-kit dist-tags --json

Also check GitHub releases for breaking changes:

• https://github.com/drizzle-team/drizzle-orm/releases
• https://orm.drizzle.team/docs/latest-releases

Last verified: March 2026 — drizzle-orm@beta = 1.0.0-beta.18, drizzle-orm@latest = 0.45.1

If the user is on the beta, install with:

pnpm add drizzle-orm@beta
pnpm add -D drizzle-kit@beta

Dialect Preference

Default to SQLite (especially with node:sqlite) unless the user specifies otherwise. All three major dialects are supported: SQLite, PostgreSQL, MySQL, plus MSSQL in the v1 beta.

Node 24 + node:sqlite (Preferred SQLite Driver)

Node.js 22+ ships a built-in node:sqlite module (release candidate as of Node 25). No native addons needed — zero-dependency SQLite.

import { drizzle } from "drizzle-orm/node-sqlite";

// Simple — pass a file path
const db = drizzle("sqlite.db");

// Explicit — use DatabaseSync directly
import { DatabaseSync } from "node:sqlite";
const sqlite = new DatabaseSync("sqlite.db");
const db = drizzle({ client: sqlite });

// In-memory
const db = drizzle(":memory:");

// With relations (v1 beta)
import { relations } from "./relations";
const db = drizzle("sqlite.db", { relations });

node:sqlite API Essentials

import { DatabaseSync } from "node:sqlite";

const database = new DatabaseSync("app.db");

// Execute raw SQL (no return value)
database.exec("PRAGMA journal_mode = WAL");
database.exec("PRAGMA foreign_keys = ON");

// Prepared statements
const stmt = database.prepare("SELECT * FROM users WHERE id = ?");
const row = stmt.get(1);        // Single row
const rows = stmt.all();        // All rows
stmt.run(params);               // INSERT/UPDATE/DELETE

// Iterate (memory-efficient for large result sets)
for (const row of stmt.iterate()) { /* ... */ }

// Custom functions
database.function("upper_custom", (str) => str.toUpperCase());

// Aggregate functions (Node 24+)
database.aggregate("sum_custom", {
  start: 0,
  step: (acc, val) => acc + val,
});

// Transaction check (Node 24+)
database.isTransaction; // boolean

// Tagged template queries (Node 24.9+, SQLTagStore)
const sql = database.createTagStore();
const user = sql.get`SELECT * FROM users WHERE id = ${1}`;
const allUsers = sql.all`SELECT * FROM users`;
sql.run`INSERT INTO users (name) VALUES (${"Alice"})`;

// Async backup
import sqlite from "node:sqlite";
await sqlite.backup(database, "backup.db");

// Cleanup (also supports `using` keyword)
database.close();

drizzle-kit + node:sqlite

As of beta.18, drizzle-kit fully supports node:sqlite for migrations, push, and studio. It auto-detects node:sqlite at runtime — no extra driver install needed.

drizzle.config.ts for node:sqlite

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "sqlite",
  schema: "./src/schema.ts",
  out: "./drizzle",
  dbCredentials: {
    url: "sqlite.db",
  },
});

When to Use node:sqlite vs better-sqlite3

Prefer node:sqlite for new projects on Node 24+. Use better-sqlite3 if you need maximum perf, older Node versions, or db.transaction() helper (node:sqlite requires manual BEGIN/COMMIT).

Recommended SQLite Pragmas

const database = new DatabaseSync("app.db");
database.exec("PRAGMA journal_mode = WAL");
database.exec("PRAGMA synchronous = NORMAL");
database.exec("PRAGMA foreign_keys = ON");
database.exec("PRAGMA cache_size = -64000");     // 64MB
database.exec("PRAGMA temp_store = MEMORY");

v1 Beta: Breaking Changes from 0.x

See references/v1-migration.md for the complete migration guide.

Key Changes Summary

Relations v2 (RQBv2)

import { defineRelations } from "drizzle-orm";
import * as schema from "./schema";

export const relations = defineRelations(schema, (r) => ({
  users: {
    posts: r.many.posts(),
    profile: r.one.profiles({
      from: r.users.id,
      to: r.profiles.userId,
    }),
  },
  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
    }),
  },
}));

// Many-to-many via junction table
export const relations = defineRelations(schema, (r) => ({
  users: {
    groups: r.many.groups({
      from: r.users.id.through(r.usersToGroups.userId),
      to: r.groups.id.through(r.usersToGroups.groupId),
    }),
  },
}));

Split across files with defineRelationsPart:

import { defineRelationsPart } from "drizzle-orm";
const usersRelations = defineRelationsPart(schema, (r) => ({
  users: { posts: r.many.posts() },
}));

Object-Based Where (v1)

// Simple equality
db.query.users.findMany({ where: { id: 1 } });

// Complex filters
db.query.users.findMany({
  where: {
    OR: [
      { name: { like: "John%" } },
      { name: { ilike: "jane%" } },
    ],
    age: { gte: 18 },
  },
});

// Raw SQL in where
db.query.users.findMany({
  where: { RAW: (table) => sql`LOWER(${table.name}) = 'admin'` },
});

Backward Compatibility

You do NOT have to migrate everything at once:

• Old relations: import { relations } from "drizzle-orm/_relations"
• Old queries: db._query.table.findMany() (callback-based where still works)

Schema Design

SQLite (Preferred)

import { sqliteTable, text, integer, real, blob } from "drizzle-orm/sqlite-core";

export const users = sqliteTable("users", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  age: integer("age"),
  active: integer("active", { mode: "boolean" }).default(true),
  createdAt: integer("created_at", { mode: "timestamp" }).notNull()
    .$defaultFn(() => new Date()),
});

export const posts = sqliteTable("posts", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  content: text("content"),
  userId: integer("user_id").references(() => users.id),
});

PostgreSQL

import { pgTable, serial, text, integer, timestamp, pgEnum } from "drizzle-orm/pg-core";

export const roleEnum = pgEnum("role", ["admin", "user"]);

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  role: roleEnum("role").default("user"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

Multiple Schema Files

// drizzle.config.ts
export default defineConfig({
  schema: "./src/schema/**/*.ts",
});

Zod Validation (drizzle-orm/zod)

v1 beta: import { ... } from "drizzle-orm/zod" (built-in, drizzle-zod deprecated) Stable 0.x: import { ... } from "drizzle-zod" (separate package, drizzle-orm@0.36.0+, zod@3.25.1+)

Generate Schemas

import { createInsertSchema, createSelectSchema, createUpdateSchema } from "drizzle-orm/zod";

const userInsertSchema = createInsertSchema(users);   // For INSERT validation
const userSelectSchema = createSelectSchema(users);   // For SELECT result validation
const userUpdateSchema = createUpdateSchema(users);   // For UPDATE (all fields optional)

Works with tables, views (createSelectSchema only), and enums (createSelectSchema only).

Refinements

import { z } from "zod";

const userInsertSchema = createInsertSchema(users, {
  email: (schema) => schema.email(),           // Callback: extend
  name: (schema) => schema.min(1).max(100),    // Callback: extend
  preferences: z.object({ theme: z.string() }), // Direct: overwrite entirely
});

Custom types generate z.any() — always override them:

const selectSchema = createSelectSchema(team, {
  customCol: z.string(), // Override z.any() for custom column types
});

Schema Factory (Advanced)

import { createSchemaFactory } from "drizzle-orm/zod";
import { z } from "@hono/zod-openapi";

// Custom Zod instance (e.g., OpenAPI)
const { createInsertSchema } = createSchemaFactory({ zodInstance: z });

// Type coercion
const { createInsertSchema } = createSchemaFactory({
  coerce: { date: true }, // or coerce: true for all types
});

Type Inference

import { z } from "zod";
type UserInsert = z.infer<typeof userInsertSchema>;
type UserSelect = z.infer<typeof userSelectSchema>;

// Drizzle's native inference (complementary)
type User = typeof users.$inferSelect;
type NewUser = typeof users.$inferInsert;

Common Patterns

// Single source of truth: schema -> validation -> types
export const users = sqliteTable("users", { /* ... */ });
export const userInsertSchema = createInsertSchema(users, {
  email: (s) => s.email(),
});
export type UserInsert = z.infer<typeof userInsertSchema>;

// Form validation (omit server-managed fields)
export const userFormSchema = userInsertSchema.omit({ id: true, createdAt: true });

// PATCH endpoint (partial update)
export const userPatchSchema = userUpdateSchema;

// Pick specific fields
export const loginSchema = userInsertSchema.pick({ email: true });

For complete Zod column type mappings, see references/zod-mappings.md.

Drizzle Kit Commands

Migration Philosophy

Always use generate + migrate for everything except throwaway local prototyping.

Generate Migrations

npx drizzle-kit generate

Creates SQL migration files by diffing current schema against last snapshot.

v1 beta folder structure:

drizzle/
  20250220153045_brave_wolverine/
    migration.sql
    snapshot.json

0.x folder structure:

drizzle/
  _meta/journal.json
  0000_init.sql
  0001_add_email.sql

Apply Migrations

npx drizzle-kit migrate

Or at runtime:

import { drizzle } from "drizzle-orm/node-sqlite";
import { migrate } from "drizzle-orm/node-sqlite/migrator";

const db = drizzle("sqlite.db");
await migrate(db, { migrationsFolder: "./drizzle" });

Push (Prototyping Only)

npx drizzle-kit push  # ONLY for throwaway local databases

Other Commands

Configuration

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  out: "./drizzle",
  dialect: "sqlite",                   // "sqlite" | "postgresql" | "mysql" | "mssql"
  schema: "./src/schema.ts",
  dbCredentials: {
    url: "sqlite.db",                  // SQLite: file path
    // url: process.env.DATABASE_URL,  // PG/MySQL: connection string
  },
  migrations: {
    prefix: "timestamp",               // "timestamp" | "unix" | "supabase" | "none"
    table: "__drizzle_migrations__",
  },
  verbose: true,
});

Seeding

See references/seeding.md for drizzle-seed generators, versioning, and refinements.

import { seed, reset } from "drizzle-seed";
import * as schema from "./schema";

await reset(db, schema);
await seed(db, schema, { count: 100, seed: 12345 });

Best Practices

1. Verify versions before writing Drizzle code (npm view drizzle-orm dist-tags)
2. Always use migrations for production (generate + migrate)
3. Prefer node:sqlite on Node 24+ for zero-dependency SQLite
4. Use drizzle-orm/zod on v1 beta (not the separate drizzle-zod package)
5. Review generated SQL before applying migrations
6. Commit migration files to version control
7. Use defineRelations on v1 beta (not per-table relations())
8. Pin seed version for deterministic test data
9. Set WAL mode for SQLite: database.exec("PRAGMA journal_mode = WAL")
10. Enable foreign keys for SQLite: database.exec("PRAGMA foreign_keys = ON")