Fix Drizzle Relational Query – Relations Not Resolving

// src/db/schema.ts
import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name'),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title'),
  userId: integer('user_id'),
});

// ❌ Relations defined but not exported properly
const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

```<a href="/programming-languages/typescript/">typescript</a>
// query.ts
import { db } from './db';
import { users } from './schema';

const result = await db.query.users.findMany({
  with: {
    posts: true,          // returns undefined
  },
});

The `with` clause doesn't work because `usersRelations` isn't imported.

## Right ✅

```typescript
// src/db/schema.ts
import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name'),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title'),
  userId: integer('user_id'),
});

// ✅ Export relations
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

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

```<a href="/programming-languages/typescript/">typescript</a>
// db/index.ts — re-export everything
export * from './schema';

```typescript
// query.ts
import { db } from './db';
import { users, usersRelations, postsRelations } from './db';

// ✅ Relations imported — findMany works
const result = await db.query.users.findMany({
  with: {
    posts: true,
  },
});

console.log(result[0].posts);   // [{ id: 1, title: '...', userId: 1 }]

**For nested relations:**

```<a href="/programming-languages/typescript/">typescript</a>
const result = await db.query.users.findMany({
  with: {
    posts: {
      with: {
        author: true,          // nested — the post's author (back to user)
      },
    },
  },
});

**Debugging — verify the relation exists:**

```<a href="/programming-languages/typescript/">typescript</a>
import { getTableRelations } from 'drizzle-orm';
console.log(getTableRelations(users));
// Should list 'posts'

**If using the query API without relations imported:**

The `db.query` API only works if all relations are imported into the schema module or the db instance. Ensure the schema module that you pass to `drizzle()` includes the relations.

## Root Cause

Drizzle's relational query API (`db.query.users.findMany`) requires the `relations` to be imported in the schema module that was passed to the `drizzle()` instance. If relations are defined but not exported (or not imported when creating the db instance), the `with` clause resolves to `undefined`.

## Prevention

- Always export relations from the same module as the table definitions.
- Re‑export everything from a central `db/index.ts`.
- Use <a href="/programming-languages/typescript/">TypeScript</a> — it catches relation type mismatches.
- Test with `findFirst` before `findMany` to validate relations.


## Common Mistakes with relational query

1. **Misunderstanding that `String` is `[Char]` with poor performance for large text operations**
2. **Using `foldl` instead of `foldl'` causing stack overflow on large lists**
3. **Forgetting `deriving (Show, Eq)` on custom data types needed for debugging**

These mistakes appear frequently in real-world DRIZZLE code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

## Practice Exercise

**Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.**

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

## FAQ

<details style="margin-bottom:12px;border:1px solid #e2e8f0;border-radius:10px;overflow:hidden"><summary style="cursor:pointer;padding:14px 18px;font-weight:600;font-size:1.05rem;background:#f8fafc;border-bottom:1px solid #e2e8f0;color:#1e293b">**Q: Can I define relations across different files?**</summary><div style="padding:14px 18px;color:#475569;line-height:1.7;background:#fff"><p>A: Yes — but import all relation files in the schema module passed to <code>drizzle()</code>.</p>
</div></details><details style="margin-bottom:12px;border:1px solid #e2e8f0;border-radius:10px;overflow:hidden"><summary style="cursor:pointer;padding:14px 18px;font-weight:600;font-size:1.05rem;background:#f8fafc;border-bottom:1px solid #e2e8f0;color:#1e293b">**Q: What's the difference between `one` and `many`?**</summary><div style="padding:14px 18px;color:#475569;line-height:1.7;background:#fff"><p>A: <code>one</code> (hasOne/belongsTo) for single related row. <code>many</code> (hasMany) for multiple related rows.</p>
</div></details><details style="margin-bottom:12px;border:1px solid #e2e8f0;border-radius:10px;overflow:hidden"><summary style="cursor:pointer;padding:14px 18px;font-weight:600;font-size:1.05rem;background:#f8fafc;border-bottom:1px solid #e2e8f0;color:#1e293b">**Q: Why does my relation need `fields` and `references`?**</summary><div style="padding:14px 18px;color:#475569;line-height:1.7;background:#fff"><p>A: Like Prisma/TypeORM, you must specify which foreign key maps to which primary key.</p>
</div></details><details style="margin-bottom:12px;border:1px solid #e2e8f0;border-radius:10px;overflow:hidden"><summary style="cursor:pointer;padding:14px 18px;font-weight:600;font-size:1.05rem;background:#f8fafc;border-bottom:1px solid #e2e8f0;color:#1e293b">**Q: Can I use `with` with raw SQL queries?**</summary><div style="padding:14px 18px;color:#475569;line-height:1.7;background:#fff"><p>A: No — <code>with</code> is for the query <a href="/design-patterns/builder/">builder</a> API only. Use SQL joins for raw queries.</p>
</div></details>

---

*Relational queries are covered in the [DodaTech Drizzle ORM course](https://dodatech.com/courses/drizzle).*