Drizzle: Queries — Select, Insert, Update, Delete

SELECT Queries

import { db } from '@/db';
import { users, folders, pages } from '@/db/schema';
import { eq, and, or, not, lt, gt, lte, gte, like, ilike,
         inArray, isNull, isNotNull, between, desc, asc, sql } from 'drizzle-orm';

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

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

// Where clause
const user = await db.select()
  .from(users)
  .where(eq(users.clerkId, 'clerk_abc123'))
  .limit(1);

// Multiple conditions
const activePros = await db.select()
  .from(users)
  .where(and(
    eq(users.plan, 'pro'),
    isNotNull(users.nickname),
  ));

// OR conditions
const found = await db.select()
  .from(users)
  .where(or(
    eq(users.email, 'alice@example.com'),
    eq(users.nickname, 'alice'),
  ));

// Pattern matching (case-insensitive)
const searchResults = await db.select()
  .from(users)
  .where(ilike(users.name, '%alice%'));

// IN clause
const specificUsers = await db.select()
  .from(users)
  .where(inArray(users.id, ['uuid1', 'uuid2', 'uuid3']));

// Order, limit, offset
const page1 = await db.select()
  .from(pages)
  .where(eq(pages.folderId, folderId))
  .orderBy(desc(pages.updatedAt))
  .limit(20)
  .offset(0);

Aggregates & GroupBy

import { count, sum, avg, min, max } from 'drizzle-orm';

// Count
const [{ total }] = await db.select({ total: count() }).from(users);

// Count with condition
const [{ proCount }] = await db.select({ proCount: count() })
  .from(users)
  .where(eq(users.plan, 'pro'));

// Group by
const pagesByStatus = await db.select({
  status: pages.status,
  count: count(),
})
  .from(pages)
  .groupBy(pages.status);
// [{ status: 'to_learn', count: 42 }, { status: 'proficient', count: 8 }]

// Having (filter on aggregated value)
const activeFolders = await db.select({
  folderId: pages.folderId,
  pageCount: count(),
})
  .from(pages)
  .groupBy(pages.folderId)
  .having(gt(count(), 5));

INSERT

// Insert single row
const [newUser] = await db.insert(users)
  .values({
    clerkId: 'clerk_abc',
    email: 'alice@example.com',
    name: 'Alice',
    plan: 'free',
  })
  .returning(); // returns the full inserted row

// Insert multiple rows
await db.insert(folders).values([
  { userId: user.id, name: 'React', slug: 'react' },
  { userId: user.id, name: 'TypeScript', slug: 'typescript' },
  { userId: user.id, name: 'Node.js', slug: 'nodejs' },
]);

// Upsert — insert or update on conflict
await db.insert(users)
  .values({ clerkId: 'clerk_abc', email: 'alice@example.com', name: 'Alice' })
  .onConflictDoUpdate({
    target: users.clerkId,            // conflict on this column
    set: {
      name: sql`excluded.name`,      // use the new value
      updatedAt: new Date(),
    },
  });

// Insert or do nothing on conflict
await db.insert(users)
  .values(userData)
  .onConflictDoNothing();

UPDATE & DELETE

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

// Update with SQL expression
await db.update(pages)
  .set({ updatedAt: new Date() })
  .where(eq(pages.folderId, folderId));

// Delete
await db.delete(folders)
  .where(eq(folders.id, folderId));

// Delete and return deleted rows
const [deleted] = await db.delete(users)
  .where(eq(users.clerkId, clerkId))
  .returning();

// Bulk delete
await db.delete(pages)
  .where(inArray(pages.id, pageIds));

Dynamic Queries

import { SQL } from 'drizzle-orm';

// Build conditions dynamically
async function searchPages(opts: {
  folderId?: string;
  status?: string;
  query?: string;
}) {
  const conditions: SQL[] = [];

  if (opts.folderId) conditions.push(eq(pages.folderId, opts.folderId));
  if (opts.status) conditions.push(eq(pages.status, opts.status as any));
  if (opts.query) conditions.push(ilike(pages.title, `%${opts.query}%`));

  return db.select()
    .from(pages)
    .where(conditions.length > 0 ? and(...conditions) : undefined)
    .orderBy(desc(pages.updatedAt));
}