DT
Drizzle Team•4w ago
Sparticuz

How to specify types for raw sql statement?

How do I specify the types for a raw sql statement? I have the following code, but I have to override the types to unknown before I can get the types out. 
interface SQLResults {
  numResponses: number;
  opsTeam: number;
}
const csFeedbackMTD = sql<SQLResults>`
  SELECT a.ops_team AS opsTeam, COUNT(DISTINCT(a.customer_fk)) AS numResponses
  FROM (
    SELECT ${eventsTable.customer}, ${eventsTable.opsTeam}
    FROM ${eventsTable}
    WHERE
      ${eventsTable.type} IN(8, 14) AND
      ${eventsTable.effectiveDate} BETWEEN ${format(startDate, "yyyy-MM-dd")} AND ${format(endDate, "yyyy-MM-dd")}
    UNION ALL
    SELECT ${eventsTable.customer}, ${eventsTable.opsTeam}
    FROM ${eventsTable}
    WHERE
      ${eventsTable.type} = 6 AND
      ${eventsTable.scoredByEntity} = 1 AND
      ${eventsTable.effectiveDate} BETWEEN ${format(startDate, "yyyy-MM-dd")} AND ${format(endDate, "yyyy-MM-dd")}
    ) a
  LEFT OUTER JOIN ${customersTable} ON a.customer_fk = ${customersTable.id}
  WHERE
    ${customersTable.startDate} < DATE_SUB(CURDATE(),INTERVAL 10 DAY)
  AND
    a.ops_team IS NOT NULL
  GROUP BY a.ops_team`;

  // This type override is wrong, this is because the database.execute type is wrong
  const [result] = (await database.execute(csFeedbackMTD)) as unknown as [
    SQLResults[],
  ];
interface SQLResults {
  numResponses: number;
  opsTeam: number;
}
const csFeedbackMTD = sql<SQLResults>`
  SELECT a.ops_team AS opsTeam, COUNT(DISTINCT(a.customer_fk)) AS numResponses
  FROM (
    SELECT ${eventsTable.customer}, ${eventsTable.opsTeam}
    FROM ${eventsTable}
    WHERE
      ${eventsTable.type} IN(8, 14) AND
      ${eventsTable.effectiveDate} BETWEEN ${format(startDate, "yyyy-MM-dd")} AND ${format(endDate, "yyyy-MM-dd")}
    UNION ALL
    SELECT ${eventsTable.customer}, ${eventsTable.opsTeam}
    FROM ${eventsTable}
    WHERE
      ${eventsTable.type} = 6 AND
      ${eventsTable.scoredByEntity} = 1 AND
      ${eventsTable.effectiveDate} BETWEEN ${format(startDate, "yyyy-MM-dd")} AND ${format(endDate, "yyyy-MM-dd")}
    ) a
  LEFT OUTER JOIN ${customersTable} ON a.customer_fk = ${customersTable.id}
  WHERE
    ${customersTable.startDate} < DATE_SUB(CURDATE(),INTERVAL 10 DAY)
  AND
    a.ops_team IS NOT NULL
  GROUP BY a.ops_team`;

  // This type override is wrong, this is because the database.execute type is wrong
  const [result] = (await database.execute(csFeedbackMTD)) as unknown as [
    SQLResults[],
  ];
I've tried putting the <SQLResults> as database.execute<SQLResults>(csFeedbackMTD) as well, but the types don't seem to be what I'm getting back.
1 Reply
Sparticuz
SparticuzOP•3w ago
I posted a github issue https://github.com/drizzle-team/drizzle-orm/issues/4087
GitHub
[FEATURE]: Allow specifying types for sql raw statements · Issue #4...
Feature hasn't been suggested before. I have verified this feature I'm about to request hasn't been suggested before. Describe the enhancement you want to request How do I specify the t...

Did you find this page helpful?