BUG: TripRepository.findByUserId uses invalid Drizzle ORM cross-table query #2

New issue

Closed

opened 2026-05-09 00:26:19 +00:00 by Mark · 2 comments

Mark commented

2026-05-09 00:26:19 +00:00

Owner

Description

TripRepository.findByUserId() in src/repositories/trip-repository.ts (line 56-74) uses an invalid Drizzle ORM query that will cause a runtime SQL error.

The Problem

const results = await db.query.trips.findMany({
  where: or(
    eq(trips.ownerId, userId),
    eq(tripMembers.userId, userId)  // BUG: tripMembers column on trips table
  ),
  with: {
    members: { ... },
  },
});

The where clause is applied to the trips table in Drizzle, but the second condition references tripMembers.userId — a column from a different table. This will produce invalid SQL at runtime because trip_members.user_id does not exist on the trips table.

Impact

When a user loads their trips list (GET /api/trips), this query runs. It will fail with a PostgreSQL error like "column trip_members.user_id does not exist" or similar, depending on how Drizzle translates it.

Fix

Use a subquery or raw SQL to find trips where the user is either the owner OR a member. For example:

// Option 1: Use a subquery
const memberTripIds = db.select({ tripId: tripMembers.tripId })
  .from(tripMembers)
  .where(eq(tripMembers.userId, userId));

const results = await db.query.trips.findMany({
  where: or(
    eq(trips.ownerId, userId),
    inArray(trips.id, memberTripIds)
  ),
  with: { ... },
});

// Option 2: Use raw SQL via sql`` template literal

## Description `TripRepository.findByUserId()` in `src/repositories/trip-repository.ts` (line 56-74) uses an invalid Drizzle ORM query that will cause a runtime SQL error. ## The Problem ```typescript const results = await db.query.trips.findMany({ where: or( eq(trips.ownerId, userId), eq(tripMembers.userId, userId) // BUG: tripMembers column on trips table ), with: { members: { ... }, }, }); ``` The `where` clause is applied to the `trips` table in Drizzle, but the second condition references `tripMembers.userId` — a column from a *different* table. This will produce invalid SQL at runtime because `trip_members.user_id` does not exist on the `trips` table. ## Impact When a user loads their trips list (`GET /api/trips`), this query runs. It will fail with a PostgreSQL error like "column trip_members.user_id does not exist" or similar, depending on how Drizzle translates it. ## Fix Use a subquery or raw SQL to find trips where the user is either the owner OR a member. For example: ```typescript // Option 1: Use a subquery const memberTripIds = db.select({ tripId: tripMembers.tripId }) .from(tripMembers) .where(eq(tripMembers.userId, userId)); const results = await db.query.trips.findMany({ where: or( eq(trips.ownerId, userId), inArray(trips.id, memberTripIds) ), with: { ... }, }); // Option 2: Use raw SQL via sql`` template literal ```

Mark added the

security/medium

label

2026-07-27 19:19:31 +00:00

Mark referenced this issue from a commit

2026-08-12 03:46:36 +00:00

fix(api): validate lat/lng and item type, owner-only member/share authz, timezone-aware shared day plans, sanitize share secrets

Mark commented

2026-08-12 03:47:09 +00:00

Author

Owner

Fixed in commit a5510ba: TripRepository.findByUserId used inside a where clause — Drizzle relational queries cannot reference another table without a join, so this crashed at runtime on GET /api/trips. Now resolved via subquery + inArray.

Fixed in commit a5510ba: TripRepository.findByUserId used inside a where clause — Drizzle relational queries cannot reference another table without a join, so this crashed at runtime on GET /api/trips. Now resolved via subquery + inArray.

Mark closed this issue

2026-08-12 03:47:10 +00:00

Mark commented

2026-08-12 03:47:28 +00:00

Author

Owner

Fixed in commit a5510ba: TripRepository.findByUserId used eq(tripMembers.userId, ...) inside a db.query.trips.findMany where clause — Drizzle relational queries cannot reference another table without a join, so this crashed at runtime on GET /api/trips. Now resolved via subquery + inArray.

Fixed in commit a5510ba: TripRepository.findByUserId used `eq(tripMembers.userId, ...)` inside a `db.query.trips.findMany` where clause — Drizzle relational queries cannot reference another table without a join, so this crashed at runtime on GET /api/trips. Now resolved via subquery + inArray.

Mark referenced this issue

2026-09-14 15:55:02 +00:00

[Security] HIGH: 5 critical/high dependency vulns #11