patternjavascriptdrizzleModerate
Drizzle ORM: relations API vs SQL joins and when to use each
Viewed 0 times
drizzlerelationsfindManywithleftJoinrelational APIN+1Cartesian productschema relations
Error Messages
TypeError: Cannot read properties of undefined (reading 'referencedTable')Problem
Drizzle's relational query API (db.query.users.findMany with with:) and manual SQL joins both fetch related data but behave differently. Using the wrong one leads to either N+1 queries or unexpectedly wide Cartesian products.
Solution
Use the relational query API (db.query) for application-level data fetching — it issues separate optimized queries per relation (not JOINs) and returns typed nested objects. Use explicit joins (db.select().from().leftJoin()) for aggregate queries, filtering across tables, or when you need a single flat result set.
Why
Drizzle's relational API with findMany + with issues one query per relation using IN clauses, avoiding the duplicate rows problem of JOIN on one-to-many. Manual joins give full SQL control but return flat rows that require manual grouping in application code.
Gotchas
- The relational query API requires defining relations in the schema with relations() — missing this causes TypeScript errors on with: fields
- db.query uses separate queries internally — for read-consistency across tables, wrap in a transaction
- Joining a one-to-many relation with db.select().leftJoin() returns one row per child — group results manually
- Drizzle does not yet support polymorphic relations — use raw SQL or a discriminated union pattern instead
Code Snippets
Relational API vs explicit join in Drizzle
// schema.ts: define relations
import { relations } from 'drizzle-orm';
export const usersRelations = relations(users, ({ many }) => ({
posts: many(posts)
}));
// Relational API: separate IN queries, typed nested result
const usersWithPosts = await db.query.users.findMany({
with: { posts: { columns: { id: true, title: true } } }
});
// result: [{ id, name, posts: [{ id, title }] }]
// Manual join: single query, flat rows — need manual grouping
const rows = await db
.select({ userId: users.id, postTitle: posts.title })
.from(users)
.leftJoin(posts, eq(posts.authorId, users.id))
.where(gt(users.createdAt, cutoffDate));Revisions (0)
No revisions yet.