Drizzle ORM Complete Guide | Schema· Queries

Why Drizzle?

// Prisma approach ??separate .prisma schema language
// model User {
//   id    Int    @id @default(autoincrement())
//   email String @unique
// }

// Drizzle approach ??schema in TypeScript
import { pgTable, serial, text, varchar } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: text('name').notNull(),
});

// The schema IS the types ??no generation step needed for type safety

Drizzle advantages:

• Schema in TypeScript ??one language, one file
• SQL-first ??you see exactly what SQL runs
• Zero overhead ??no query engine process
• Works everywhere ??Node.js, serverless, edge, Bun, Deno
• Smallest bundle (~30KB vs Prisma’s ~10MB)

---

Setup

# PostgreSQL
npm install drizzle-orm pg
npm install -D drizzle-kit @types/pg

# Or with postgres.js (no types needed)
npm install drizzle-orm postgres
npm install -D drizzle-kit

// drizzle.config.ts
import type { Config } from 'drizzle-kit';

export default {
  schema: './src/db/schema.ts',    // Your schema file(s)
  out: './drizzle',                // Migration output directory
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config;

---

Schema Definition

// src/db/schema.ts
import {
  pgTable, serial, text, varchar, integer, boolean,
  timestamp, pgEnum, uniqueIndex, index
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

// Enum
export const roleEnum = pgEnum('role', ['admin', 'editor', 'viewer']);

// Users table
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: text('name').notNull(),
  role: roleEnum('role').default('viewer').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  emailIdx: uniqueIndex('users_email_idx').on(table.email),
}));

// Posts table
export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 500 }).notNull(),
  content: text('content'),
  published: boolean('published').default(false).notNull(),
  authorId: integer('author_id').notNull().references(() => users.id, {
    onDelete: 'cascade',
  }),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  authorIdx: index('posts_author_idx').on(table.authorId),
  publishedIdx: index('posts_published_idx').on(table.published, table.createdAt),
}));

// Tags table
export const tags = pgTable('tags', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 100 }).notNull().unique(),
});

// Many-to-many join table
export const postTags = pgTable('post_tags', {
  postId: integer('post_id').notNull().references(() => posts.id, { onDelete: 'cascade' }),
  tagId: integer('tag_id').notNull().references(() => tags.id, { onDelete: 'cascade' }),
});

// Relations (for ORM-style queries with .with())
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
  postTags: many(postTags),
}));

export const postTagsRelations = relations(postTags, ({ one }) => ({
  post: one(posts, { fields: [postTags.postId], references: [posts.id] }),
  tag: one(tags, { fields: [postTags.tagId], references: [tags.id] }),
}));

---

Database Connection

// src/db/index.ts
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';

const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client, { schema });

// Infer types from schema
export type User = typeof schema.users.$inferSelect;
export type NewUser = typeof schema.users.$inferInsert;
export type Post = typeof schema.posts.$inferSelect;
export type NewPost = typeof schema.posts.$inferInsert;

---

CRUD Queries

Insert

import { db } from './db';
import { users, posts } from './db/schema';

// Insert one
const [user] = await db.insert(users).values({
  email: 'alice@example.com',
  name: 'Alice',
  role: 'admin',
}).returning();  // Returns the created row(s)

// Insert many
await db.insert(users).values([
  { email: 'bob@example.com', name: 'Bob' },
  { email: 'carol@example.com', name: 'Carol' },
]);

// Upsert (insert or update on conflict)
await db.insert(users).values({
  email: 'alice@example.com',
  name: 'Alice Updated',
}).onConflictDoUpdate({
  target: users.email,
  set: { name: 'Alice Updated' },
});

// Upsert ??do nothing on conflict
await db.insert(tags).values({ name: 'typescript' })
  .onConflictDoNothing();

Select

import { eq, and, or, gte, lte, like, desc, asc, count, sql } from 'drizzle-orm';

// Select all
const allUsers = await db.select().from(users);

// Select specific fields
const userNames = await db.select({
  id: users.id,
  name: users.name,
}).from(users);

// Where conditions
const adminUsers = await db.select()
  .from(users)
  .where(eq(users.role, 'admin'));

const recentPublishedPosts = await db.select()
  .from(posts)
  .where(
    and(
      eq(posts.published, true),
      gte(posts.createdAt, new Date('2026-01-01'))
    )
  )
  .orderBy(desc(posts.createdAt))
  .limit(10)
  .offset(0);

// Search with LIKE
const searchResults = await db.select()
  .from(posts)
  .where(like(posts.title, '%typescript%'));

// Join
const postsWithAuthors = await db.select({
  postId: posts.id,
  title: posts.title,
  authorName: users.name,
  authorEmail: users.email,
})
.from(posts)
.leftJoin(users, eq(posts.authorId, users.id))
.where(eq(posts.published, true))
.orderBy(desc(posts.createdAt));

Relations (with)

// Use .query for ORM-style nested queries (requires relations config)
const userWithPosts = await db.query.users.findFirst({
  where: eq(users.id, 1),
  with: {
    posts: {
      where: eq(posts.published, true),
      orderBy: desc(posts.createdAt),
      limit: 5,
    },
  },
});

// userWithPosts.posts is typed as Post[]

const allUsersWithPostCount = await db.query.users.findMany({
  with: {
    posts: true,
  },
});

Update

// Update with returning
const [updated] = await db.update(users)
  .set({ name: 'Alice Smith', updatedAt: new Date() })
  .where(eq(users.id, 1))
  .returning();

// Atomic increment using sql template
await db.update(posts)
  .set({ viewCount: sql`${posts.viewCount} + 1` })
  .where(eq(posts.id, 1));

Delete

await db.delete(users).where(eq(users.id, 1));

// Delete with returning
const [deleted] = await db.delete(posts)
  .where(eq(posts.id, 1))
  .returning();

---

Transactions

// Atomic transaction
const result = await db.transaction(async (tx) => {
  // All operations use tx instead of db
  const [newUser] = await tx.insert(users).values({
    email: 'alice@example.com',
    name: 'Alice',
  }).returning();

  await tx.insert(posts).values({
    title: 'First Post',
    content: 'Hello!',
    authorId: newUser.id,
  });

  return newUser;
});
// If any operation throws, the entire transaction rolls back

---

Raw SQL

import { sql } from 'drizzle-orm';

// Type-safe raw query
const result = await db.execute<{ id: number; name: string }>(
  sql`SELECT id, name FROM users WHERE created_at > ${new Date('2026-01-01')}`
);

// Use raw SQL in queries
const usersWithCount = await db.select({
  user: users,
  postCount: sql<number>`count(${posts.id})`,
})
.from(users)
.leftJoin(posts, eq(users.id, posts.authorId))
.groupBy(users.id);

---

Migrations with drizzle-kit

# Generate migration from schema changes
npx drizzle-kit generate

# Output: drizzle/0001_add_posts_view_count.sql
# ALTER TABLE posts ADD COLUMN view_count INTEGER DEFAULT 0 NOT NULL;

# Apply migrations (development)
npx drizzle-kit migrate

# Push schema directly (development only ??no migration files)
npx drizzle-kit push

# Open Drizzle Studio (visual DB browser)
npx drizzle-kit studio

---

Edge / Serverless (Neon + Cloudflare)

// Neon (PostgreSQL serverless) ??works in edge environments
import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql);

// Cloudflare D1 (SQLite at the edge)
import { drizzle } from 'drizzle-orm/d1';

export function createDB(d1: D1Database) {
  return drizzle(d1);
}

// Cloudflare Workers handler
export default {
  async fetch(request: Request, env: Env) {
    const db = createDB(env.DB);
    const users = await db.select().from(usersTable);
    return Response.json(users);
  }
};

---

Type Inference

// Infer types directly from schema ??no duplication
import { InferSelectModel, InferInsertModel } from 'drizzle-orm';

type User = InferSelectModel<typeof users>;
// { id: number; email: string; name: string; role: "admin" | "editor" | "viewer"; createdAt: Date; }

type NewUser = InferInsertModel<typeof users>;
// { email: string; name: string; role?: "admin" | "editor" | "viewer"; createdAt?: Date; }

// Or use the $ shorthand
type User = typeof users.$inferSelect;
type NewUser = typeof users.$inferInsert;

---

Drizzle vs Prisma Quick Comparison

Feature	Drizzle	Prisma
Schema language	TypeScript	Prisma DSL (.prisma)
Bundle size	~30KB	~10MB
Edge support	??Native	—️ Limited (Data Proxy)
Migration tool	drizzle-kit	prisma migrate
Visual Studio	drizzle studio	Prisma Studio
Relations	Explicit joins or .with()	.include(), .select()
Raw SQL	sql template tag	$queryRaw
Type safety	Full	Full

Related posts:

• Prisma Complete Guide
• Database Comparison: SQL vs NoSQL
• Docker Compose Complete Guide

---

Frequently Asked Questions (FAQ)

Q. When would I use this in practice?

A. Drizzle ORM defines your schema in TypeScript (no .prisma files), generates zero-abstraction SQL, and has the smallest bundle size of any TS ORM. This guide covers schema design, queries, relations, and migrations with drizzle-kit.

Q. What should I read before this?

A. Follow the previous article or related articles links at the bottom of each post to learn in sequence.

Q. Where can I study this more deeply?

A. Check cppreference and the relevant library’s official documentation. The reference links at the end of the article are also worth using.

---

Related Articles (Internal Links)

Other articles related to this topic.

• Prisma Complete Guide | Schema· Queries
• SQL vs NoSQL Database Guide | MySQL· PostgreSQL
• Docker Compose Complete Guide | Multi-Container Apps

---

Keywords Covered in This Article (Related Search Terms)

This article covers Drizzle, ORM, TypeScript, PostgreSQL, Node.js, Database, Backend.