Drizzle ORM Joins

Generate type-safe SQL joins using Drizzle ORM. Supports all join types (inner, left, right, full, lateral, cross) with automatic result type inference and null safety.

Instructions

When the user asks you to create database queries with joins using Drizzle ORM, follow these steps:

1. Understand the Join Type

Identify which join type is needed:

2. Define Schema Context

Ensure the Drizzle schema is defined with proper relations:

```typescript

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

import { eq } from 'drizzle-orm';

export const users = pgTable('users', {

id: serial('id').primaryKey(),

name: text('name').notNull(),

});

export const pets = pgTable('pets', {

id: serial('id').primaryKey(),

name: text('name').notNull(),

ownerId: integer('owner_id').notNull().references(() => users.id),

});

```

3. Generate the Join Query

Use the appropriate join method based on requirements:

**Left Join:**

```typescript

const result = await db

.select()

.from(users)

.leftJoin(pets, eq(users.id, pets.ownerId));

// Result type: { user: User; pets: Pet | null }[]

```

**Inner Join:**

```typescript

const result = await db

.select()

.from(users)

.innerJoin(pets, eq(users.id, pets.ownerId));

// Result type: { user: User; pets: Pet }[]

```

**Right Join:**

```typescript

const result = await db

.select()

.from(users)

.rightJoin(pets, eq(users.id, pets.ownerId));

// Result type: { user: User | null; pets: Pet }[]

```

**Full Join:**

```typescript

const result = await db

.select()

.from(users)

.fullJoin(pets, eq(users.id, pets.ownerId));

// Result type: { user: User | null; pets: Pet | null }[]

```

**Cross Join:**

```typescript

const result = await db

.select()

.from(users)

.crossJoin(pets);

// Result type: { user: User; pets: Pet }[]

```

**Left Join Lateral (subquery):**

```typescript

const subquery = db

.select()

.from(pets)

.where(gte(users.age, 16))

.as('userPets');

const result = await db

.select()

.from(users)

.leftJoinLateral(subquery, sql`true`);

```

4. Handle Partial Selects

When only specific fields are needed, use partial select with explicit field mapping:

```typescript

const result = await db

.select({

userId: users.id,

userName: users.name,

petId: pets.id,

petName: pets.name,

})

.from(users)

.leftJoin(pets, eq(users.id, pets.ownerId));

// Result type: { userId: number; userName: string; petId: number | null; petName: string | null }[]

```

**Important:** When using `sql` operator in partial selects with nullable joins, explicitly declare null types:

```typescript

const result = await db

.select({

userId: users.id,

petId: pets.id,

upperName: sql<string | null>`upper(${pets.name})`, // Explicit null type

})

.from(users)

.leftJoin(pets, eq(users.id, pets.ownerId));

```

5. Use Nested Select for Grouped Objects

To avoid numerous nullable fields, nest related data into objects:

```typescript

const result = await db

.select({

userId: users.id,

userName: users.name,

pet: {

id: pets.id,

name: pets.name,

upperName: sql<string>`upper(${pets.name})`,

},

})

.from(users)

.fullJoin(pets, eq(users.id, pets.ownerId));

// Result type: { userId: number | null; userName: string | null; pet: { id: number; name: string; upperName: string } | null }[]

```

6. Handle Self-Joins with Aliases

For self-referencing tables, use `alias()`:

```typescript

import { alias } from 'drizzle-orm/pg-core';

const parent = alias(users, 'parent');

const result = await db

.select()

.from(users)

.leftJoin(parent, eq(parent.id, users.parentId));

// Result type: { users: User; parent: User | null }[]

```

7. Aggregate Results for One-to-Many

For one-to-many relationships, aggregate results into nested structures:

```typescript

type User = typeof users.$inferSelect;

type Pet = typeof pets.$inferSelect;

const rows = await db

.select({

user: users,

pet: pets,

})

.from(users)

.leftJoin(pets, eq(users.id, pets.ownerId));

const result = rows.reduce<Record<number, { user: User; pets: Pet[] }>>(

(acc, row) => {

const user = row.user;

const pet = row.pet;

if (!acc[user.id]) {

acc[user.id] = { user, pets: [] };

}

if (pet) {

acc[user.id].pets.push(pet);

}

return acc;

},

{}

);

```

Examples

Many-to-One (City ↔ Users)

```typescript

const result = await db

.select()

.from(cities)

.leftJoin(users, eq(cities.id, users.cityId));

```

Many-to-Many (Users ↔ ChatGroups)

```typescript

const result = await db

.select()

.from(usersToChatGroups)

.leftJoin(users, eq(usersToChatGroups.userId, users.id))

.leftJoin(chatGroups, eq(usersToChatGroups.groupId, chatGroups.id))

.where(eq(chatGroups.id, 1));

```

Key Principles

1. **Type Safety**: Drizzle automatically infers nullable fields based on join type

2. **Null Handling**: Left/right/full joins produce nullable fields — always handle nulls in application logic

3. **Explicit Typing**: When using `sql` operator with nullable joins, declare `sql<Type | null>`

4. **Nested Objects**: Group related nullable fields into objects to simplify type signatures

5. **Performance**: Use partial selects to fetch only needed columns

6. **Aliases**: Required for self-joins to avoid table name conflicts

Constraints

Reference

Generated from: https://orm.drizzle.team/docs/joins