DT
Drizzle Team•2w ago
Mike

How do I make Drizzle infer the correct return type when dynamically adding a custom select field

I'm building a query using a helper like this: 
export function createBaseVideoQuery<T extends Record<string, any> = {}>(
  customFields?: T
) {
  const baseFields = getTableColumns(videosTable);

  const query = db
    .select({ ...baseFields, ...customFields })
    .from(videosTable)
    .leftJoin(channelsTable, eq(videosTable.channel_id, channelsTable.id))
    .$dynamic();

  return query;
}

export function withQuestionCount() {
  return {
    question_count: sql`COALESCE((
      SELECT COUNT(*) 
      FROM questions q
      WHERE ...
    ), 0)`.as("question_count"),
  };
}
export function createBaseVideoQuery<T extends Record<string, any> = {}>(
  customFields?: T
) {
  const baseFields = getTableColumns(videosTable);

  const query = db
    .select({ ...baseFields, ...customFields })
    .from(videosTable)
    .leftJoin(channelsTable, eq(videosTable.channel_id, channelsTable.id))
    .$dynamic();

  return query;
}

export function withQuestionCount() {
  return {
    question_count: sql`COALESCE((
      SELECT COUNT(*) 
      FROM questions q
      WHERE ...
    ), 0)`.as("question_count"),
  };
}
Then I pass it into my base query builder: 
const query = createBaseVideoQuery({ ...withQuestionCount() })
  .$dynamic();
const query = createBaseVideoQuery({ ...withQuestionCount() })
  .$dynamic();
When execute the query, the response does have the question_count field BUT TypeScript says question_count doesn't exist. What am I doing wrong here?
4 Replies
Sillvva
Sillvva•2w ago
I think the following changes fix the return type. See the 4 comments: 
import { type SelectedFields } from "drizzle-orm/pg-core";

// changed Record<...> to SelectedFields
export function createBaseVideoQuery<T extends SelectedFields = {}>(
  // Change the optional param to a default value instead
  customFields: T = {} as T
) {
  const baseFields = getTableColumns(videosTable);

  const query = db
    .select({ ...baseFields, ...customFields })
    .from(videosTable)
    .leftJoin(channelsTable, eq(videosTable.channel_id, channelsTable.id))
    .$dynamic();

  return query;
}

export function withQuestionCount() {
  return {
    // Add explicit number type
    question_count: sql<number>`COALESCE((
      SELECT COUNT(*) 
      FROM questions q
      WHERE ...
    ), 0)`.as("question_count"),
  } satisfies SelectedFields; // adds type safety here too
}
import { type SelectedFields } from "drizzle-orm/pg-core";

// changed Record<...> to SelectedFields
export function createBaseVideoQuery<T extends SelectedFields = {}>(
  // Change the optional param to a default value instead
  customFields: T = {} as T
) {
  const baseFields = getTableColumns(videosTable);

  const query = db
    .select({ ...baseFields, ...customFields })
    .from(videosTable)
    .leftJoin(channelsTable, eq(videosTable.channel_id, channelsTable.id))
    .$dynamic();

  return query;
}

export function withQuestionCount() {
  return {
    // Add explicit number type
    question_count: sql<number>`COALESCE((
      SELECT COUNT(*) 
      FROM questions q
      WHERE ...
    ), 0)`.as("question_count"),
  } satisfies SelectedFields; // adds type safety here too
}
Ping for notification
Mike
MikeOP•2w ago
WOW this is amazing! How do you know about SelectedFields and what does it do? I couldn't find it anywhere in the docs
Sillvva
Sillvva•2w ago
In Cursor, I right clicked on the select method and opened its reference. SelectedFields is the select method's parameter type.
Mike
MikeOP•2w ago
I see. thank you so much 😊

Did you find this page helpful?