DT
Drizzle Team•2w ago
TE Johan

Column reference is ambiguous

Context Framework: Next.js Database: Postgresql "drizzle-orm": "^0.38.3" Error 
Error: column reference "id" is ambiguous
    at async Object.getExams (rsc://React/Server/D:%5CDev%5Cjecert%5C.next%5Cserver%5Cchunks%5Cssr%5C%5Broot%20of%20the%20server%5D__fa4e8b._.js?0:171:24)
    at async AppPage (rsc://React/Server/D:%5CDev%5Cjecert%5C.next%5Cserver%5Cchunks%5Cssr%5C%5Broot%20of%20the%20server%5D__fa4e8b._.js?1:424:19)
    at resolveErrorDev (http://localhost:3000/_next/static/chunks/node_modules_next_dist_compiled_107ce8._.js:3662:65)
    at processFullStringRow (http://localhost:3000/_next/static/chunks/node_modules_next_dist_compiled_107ce8._.js:3824:23)
    at processFullBinaryRow (http://localhost:3000/_next/static/chunks/node_modules_next_dist_compiled_107ce8._.js:3812:9)
    at progress (http://localhost:3000/_next/static/chunks/node_modules_next_dist_compiled_107ce8._.js:3932:102)
Error: column reference "id" is ambiguous
    at async Object.getExams (rsc://React/Server/D:%5CDev%5Cjecert%5C.next%5Cserver%5Cchunks%5Cssr%5C%5Broot%20of%20the%20server%5D__fa4e8b._.js?0:171:24)
    at async AppPage (rsc://React/Server/D:%5CDev%5Cjecert%5C.next%5Cserver%5Cchunks%5Cssr%5C%5Broot%20of%20the%20server%5D__fa4e8b._.js?1:424:19)
    at resolveErrorDev (http://localhost:3000/_next/static/chunks/node_modules_next_dist_compiled_107ce8._.js:3662:65)
    at processFullStringRow (http://localhost:3000/_next/static/chunks/node_modules_next_dist_compiled_107ce8._.js:3824:23)
    at processFullBinaryRow (http://localhost:3000/_next/static/chunks/node_modules_next_dist_compiled_107ce8._.js:3812:9)
    at progress (http://localhost:3000/_next/static/chunks/node_modules_next_dist_compiled_107ce8._.js:3932:102)
Query 
import { db } from "@/db"
import { exam, examProvider } from "@/db/schema"
import { ExamWithProvider } from "@/types"
import { desc, eq } from "drizzle-orm"
import { sql } from "drizzle-orm"

export const ExamService = {
  getExams: async (): Promise<ExamWithProvider[]> => {
    const rankedExams = db
      .select({
        examId: exam.id, // Renamed to be explicit
        name: exam.name,
        slug: exam.slug,
        description: exam.description,
        image: exam.image,
        createdAt: exam.createdAt,
        updatedAt: exam.updatedAt,
        provider: {
          providerId: examProvider.id, // Renamed to be explicit
          name: examProvider.name,
          displayName: examProvider.displayName,
          slug: examProvider.slug,
        },
        rowNumber: sql<number>`ROW_NUMBER() OVER (
          PARTITION BY ${examProvider.id}
          ORDER BY ${exam.updatedAt} DESC
        )`.as("row_number"),
      })
      .from(exam)
      .innerJoin(examProvider, eq(exam.providerId, examProvider.id))

    const result = await db
      .select()
      .from(rankedExams.as("ranked"))
      .where(sql`row_number <= 6`)

    // Transform back to match ExamWithProvider type
    return result.map(
      ({
        rowNumber,
        examId,
        provider: { providerId, ...providerRest },
        ...rest
      }) => ({
        id: examId,
        ...rest,
        provider: {
          id: providerId,
          ...providerRest,
        },
      })
    )
  },
}
import { db } from "@/db"
import { exam, examProvider } from "@/db/schema"
import { ExamWithProvider } from "@/types"
import { desc, eq } from "drizzle-orm"
import { sql } from "drizzle-orm"

export const ExamService = {
  getExams: async (): Promise<ExamWithProvider[]> => {
    const rankedExams = db
      .select({
        examId: exam.id, // Renamed to be explicit
        name: exam.name,
        slug: exam.slug,
        description: exam.description,
        image: exam.image,
        createdAt: exam.createdAt,
        updatedAt: exam.updatedAt,
        provider: {
          providerId: examProvider.id, // Renamed to be explicit
          name: examProvider.name,
          displayName: examProvider.displayName,
          slug: examProvider.slug,
        },
        rowNumber: sql<number>`ROW_NUMBER() OVER (
          PARTITION BY ${examProvider.id}
          ORDER BY ${exam.updatedAt} DESC
        )`.as("row_number"),
      })
      .from(exam)
      .innerJoin(examProvider, eq(exam.providerId, examProvider.id))

    const result = await db
      .select()
      .from(rankedExams.as("ranked"))
      .where(sql`row_number <= 6`)

    // Transform back to match ExamWithProvider type
    return result.map(
      ({
        rowNumber,
        examId,
        provider: { providerId, ...providerRest },
        ...rest
      }) => ({
        id: examId,
        ...rest,
        provider: {
          id: providerId,
          ...providerRest,
        },
      })
    )
  },
}
Schema 
export const examProvider = pgTable("exam_provider", {
  id: uuid("id").defaultRandom().primaryKey(),
  name: varchar("name").notNull(),
  displayName: varchar("displayName").notNull(),
  slug: varchar("slug").notNull().unique(),
  createdAt: timestamp("createdAt").notNull().defaultNow(),
  updatedAt: timestamp("updatedAt")
    .notNull()
    .defaultNow()
    .$onUpdate(() => sql`CURRENT_TIMESTAMP`),
})

export const exam = pgTable("exam", {
  id: uuid("id").defaultRandom().primaryKey(),
  name: varchar("name").notNull(),
  displayName: varchar("displayName").notNull(),
  slug: varchar("slug").notNull().unique(),
  description: text("description"),
  image: varchar("image"),
  providerId: uuid("providerId")
    .notNull()
    .references(() => examProvider.id),
  createdAt: timestamp("createdAt").notNull().defaultNow(),
  updatedAt: timestamp("updatedAt")
    .notNull()
    .defaultNow()
    .$onUpdate(() => sql`CURRENT_TIMESTAMP`),
})
export const examProvider = pgTable("exam_provider", {
  id: uuid("id").defaultRandom().primaryKey(),
  name: varchar("name").notNull(),
  displayName: varchar("displayName").notNull(),
  slug: varchar("slug").notNull().unique(),
  createdAt: timestamp("createdAt").notNull().defaultNow(),
  updatedAt: timestamp("updatedAt")
    .notNull()
    .defaultNow()
    .$onUpdate(() => sql`CURRENT_TIMESTAMP`),
})

export const exam = pgTable("exam", {
  id: uuid("id").defaultRandom().primaryKey(),
  name: varchar("name").notNull(),
  displayName: varchar("displayName").notNull(),
  slug: varchar("slug").notNull().unique(),
  description: text("description"),
  image: varchar("image"),
  providerId: uuid("providerId")
    .notNull()
    .references(() => examProvider.id),
  createdAt: timestamp("createdAt").notNull().defaultNow(),
  updatedAt: timestamp("updatedAt")
    .notNull()
    .defaultNow()
    .$onUpdate(() => sql`CURRENT_TIMESTAMP`),
})
2 Replies
TE Johan
TE JohanOP•2w ago
GET / 500 in 349ms
 ✓ Compiled / in 26ms
 ⨯ error: column reference "id" is ambiguous
    at async Object.getExams (file://D%3A/Dev/jecert/services/exam.ts:34:19)
    at async AppPage (file://D%3A/Dev/jecert/app/%28app%29/page.tsx:6:16)
  32 |
  33 |     // Wrap it as a subquery and filter for top 6 per provider
> 34 |     const result = await db
     |                   ^
  35 |       .select()
  36 |       .from(rankedExams.as("ranked"))
  37 |       .where(sql`row_number <= 6`) {
  length: 106,
  severity: 'ERROR',
  code: '42702',
  detail: undefined,
  hint: undefined,
  position: '8',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '816',
  routine: 'scanRTEForColumn',
  digest: '2677048155'
}
GET / 500 in 349ms
 ✓ Compiled / in 26ms
 ⨯ error: column reference "id" is ambiguous
    at async Object.getExams (file://D%3A/Dev/jecert/services/exam.ts:34:19)
    at async AppPage (file://D%3A/Dev/jecert/app/%28app%29/page.tsx:6:16)
  32 |
  33 |     // Wrap it as a subquery and filter for top 6 per provider
> 34 |     const result = await db
     |                   ^
  35 |       .select()
  36 |       .from(rankedExams.as("ranked"))
  37 |       .where(sql`row_number <= 6`) {
  length: 106,
  severity: 'ERROR',
  code: '42702',
  detail: undefined,
  hint: undefined,
  position: '8',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '816',
  routine: 'scanRTEForColumn',
  digest: '2677048155'
}
Mario564
Mario564•6d ago
The issue is with examId: exam.id and providerId: examProvider.id. Since both columns are named "id" and no SQL alias is provided, it can't resolve which column it's referring to. We have a PR where we can alias columns easier, but for now, you'll have to do this: 
{
  examId: sql`${exam.id}`.as('exam_id'),
  ...,
  providerId: sql`${examProvider.id}`.as('provider_id'),
  ...,
}
{
  examId: sql`${exam.id}`.as('exam_id'),
  ...,
  providerId: sql`${examProvider.id}`.as('provider_id'),
  ...,
}

Did you find this page helpful?