Typesafe select expression builder helper

[cseickel]

Hello,

Thanks for the great library and taking the time to answer this question. I have a question I am hoping someone can help me with.

I am trying to make a helper function for a repeated formula I am using in a select clause. It works fine but there is no protection against mistyped (or changed) column names. Is there a way to say that the field argument of the weighted function must be specifically a column name of the p CTE in the query?

  const query = withComponentQuery(componentQuery, accounts); // type is QueryCreatorWithCommonTableExpression

  const weighted = (field: string) =>
    sql<number | null>`p."${sql.raw(field)}" * coalesce(c."normalizedWeight", 1)`.as(field);

  return query
  .selectFrom("pos as p")
  .innerJoin("vw_equity as e", (join) => join
    .onRef("e.ticker", "=", "p.ticker")
    .onRef("e.tradingDate", "=", "p.tradingDate")
  )
  .leftJoin("c", (join) => join
    .onRef("c.ticker", "=", sql<string>`coalesce(e."etfProxy", e."etfTicker")`)
  )
  .select([
    "p.ticker",
    "c.weight",
    (b) => b.fn.coalesce("c.normalizedWeight", b.lit(1)).as("normalizedWeight"),
    weighted("posWeightedVega"),
  ...

[koskimas]

This should work

const weighted = <T extends string>(field: T) =>
  sql<number | null>`p.${sql.ref(field)} * coalesce(c."normalizedWeight", 1)`.as(field);

[cseickel]

Thanks for the reply @koskimas, but adding that generic type does not do anything. The function will still accept any string without raising errors.

[cseickel]

I found a way to accomplish my goal. I'm sure there is a better way, but this works:

const query = withComponentQuery(componentQuery, accounts)   
  .selectFrom("pos as p")
  .innerJoin("vw_equity as e", (join) => join
    .onRef("e.ticker", "=", "p.ticker")
    .onRef("e.tradingDate", "=", "p.tradingDate")
  )
  .leftJoin("c", (join) => join
    .onRef("c.ticker", "=", sql<string>`coalesce(e."etfProxy", e."etfTicker")`)
  );

type QueryExpression = Parameters<typeof query["select"]>[0];

const weighted = (field: QueryExpression) =>
  sql<number | null>`${sql.ref(field as string)} * coalesce(c."normalizedWeight", 1)`
    .as((field as string).split(".").pop()!);

return query
  .select([
    "p.ticker",
    "c.weight",
    (b) => b.fn.coalesce("c.normalizedWeight", b.lit(1)).as("normalizedWeight"),
    weighted("posWeightedVega"),
  ...