Drizzle ORM Cheat Sheet — Schema, Queries, and Migrations

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

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  age: integer('age'),
  active: boolean('active').default(true),
  createdAt: timestamp('created_at').defaultNow(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  body: text('body'),
  authorId: integer('author_id').references(() => users.id),
});

import { eq, gt, like, and, or, desc, asc } from 'drizzle-orm';

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

// With conditions
const activeUsers = await db.select().from(users)
  .where(eq(users.active, true));

// Multiple conditions
const results = await db.select().from(users)
  .where(and(
    gt(users.age, 18),
    like(users.name, '%Alice%')
  ));

// Select specific columns
const names = await db.select({ name: users.name, email: users.email })
  .from(users);

// Order, limit, offset
const recent = await db.select().from(users)
  .orderBy(desc(users.createdAt))
  .limit(10)
  .offset(20);

// Single insert
await db.insert(users).values({
  name: 'Alice',
  email: 'alice@example.com',
});

// Insert + return
const [newUser] = await db.insert(users).values({
  name: 'Bob',
  email: 'bob@example.com',
}).returning();

// Bulk insert
await db.insert(users).values([
  { name: 'Alice', email: 'alice@example.com' },
  { name: 'Bob', email: 'bob@example.com' },
]);

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

// Update
await db.update(users)
  .set({ name: 'Alice Smith' })
  .where(eq(users.id, 1));

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

// Delete all
await db.delete(users);

// Inner join
const result = await db.select({
  postTitle: posts.title,
  authorName: users.name,
}).from(posts)
  .innerJoin(users, eq(posts.authorId, users.id));

// Left join
const result = await db.select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

npx drizzle-kit generate    # Generate migration from schema changes
npx drizzle-kit migrate     # Apply migrations
npx drizzle-kit push        # Push schema directly (dev only)
npx drizzle-kit studio      # Visual database browser

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

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});