Drizzle: Relations, Joins & Transactions

Defining Relations

// src/db/schema.ts — define relations for the relational query API
import { relations } from 'drizzle-orm';

export const usersRelations = relations(users, ({ many }) => ({
  folders: many(folders),
}));

export const foldersRelations = relations(folders, ({ one, many }) => ({
  user: one(users, {
    fields: [folders.userId],
    references: [users.id],
  }),
  parent: one(folders, {
    fields: [folders.parentId],
    references: [folders.id],
    relationName: 'parentFolder',
  }),
  children: many(folders, { relationName: 'parentFolder' }),
  pages: many(pages),
}));

export const pagesRelations = relations(pages, ({ one }) => ({
  folder: one(folders, {
    fields: [pages.folderId],
    references: [folders.id],
  }),
}));

Relational Query API (with relations)

// Requires schema with relations passed to drizzle()
// const db = drizzle(sql, { schema });

// Get user with all their folders
const userWithFolders = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: {
    folders: {
      orderBy: [asc(folders.name)],
    },
  },
});

// Nested relations
const folderWithPages = await db.query.folders.findFirst({
  where: eq(folders.id, folderId),
  with: {
    pages: {
      where: eq(pages.status, 'proficient'),
      orderBy: [desc(pages.updatedAt)],
      limit: 10,
      columns: {
        id: true,
        title: true,
        status: true,
        updatedAt: true,
        // Exclude heavy content field
        content: false,
      },
    },
    user: {
      columns: { id: true, name: true },
    },
  },
});

// Find many with relations
const allFolders = await db.query.folders.findMany({
  where: eq(folders.userId, userId),
  with: { pages: true },
});

Manual Joins

// Left join — includes rows without matching pages
const foldersWithPageCount = await db.select({
  folder: folders,
  pageCount: count(pages.id),
})
  .from(folders)
  .leftJoin(pages, eq(pages.folderId, folders.id))
  .where(eq(folders.userId, userId))
  .groupBy(folders.id)
  .orderBy(desc(count(pages.id)));

// Inner join — only rows with matching data
const pagesWithFolder = await db.select({
  page: pages,
  folderName: folders.name,
  folderSlug: folders.slug,
})
  .from(pages)
  .innerJoin(folders, eq(pages.folderId, folders.id))
  .where(eq(folders.userId, userId));

// Multiple joins
const fullData = await db.select()
  .from(pages)
  .innerJoin(folders, eq(pages.folderId, folders.id))
  .innerJoin(users, eq(folders.userId, users.id))
  .where(eq(users.clerkId, clerkId));

Transactions

// All operations in transaction are atomic
const result = await db.transaction(async (tx) => {
  // Create folder
  const [folder] = await tx.insert(folders)
    .values({ userId, name: 'New Project', slug: 'new-project' })
    .returning();

  // Create initial pages inside it
  const createdPages = await tx.insert(pages)
    .values([
      { folderId: folder.id, title: 'Overview', slug: 'overview' },
      { folderId: folder.id, title: 'Notes', slug: 'notes' },
    ])
    .returning();

  return { folder, pages: createdPages };
});

// Rollback by throwing inside the callback
const safeTransfer = await db.transaction(async (tx) => {
  const [source] = await tx.select().from(folders).where(eq(folders.id, sourceId)).for('update');

  if (!source) {
    tx.rollback(); // explicit rollback
    return;
  }

  await tx.update(folders).set({ parentId: targetId }).where(eq(folders.id, sourceId));
  return source;
});

Batch Operations

// Execute multiple queries in a single round-trip (Neon/SQLite)
const [userResult, foldersResult, pagesResult] = await db.batch([
  db.select().from(users).where(eq(users.id, userId)),
  db.select().from(folders).where(eq(folders.userId, userId)),
  db.select().from(pages).where(inArray(pages.folderId, folderIds)),
]);