Conditional logic within template strings not supported on raw MySQL query

I am trying to execute raw SQL query with some conditional logic but it always fails. It's working fine with mysql2 driver.

    const [res] = await db.execute(sql
`
SELECT 
  COUNT(*) as total
FROM
  te_tenants
WHERE
  1 = 1
  ${query ? `AND (te_name LIKE '%${query}%' OR te_code LIKE '%${query}%') ` : ``}
  ${filterTenantIds ? `AND te_id IN (${allowedTenantIds.join(", ")}) ` : ``}
  ${isConduitType ? `AND (te_conduit_prefix != '' OR te_conduit_prefix != NULL) `: ``}
  ${isPartnerAdmin ? `AND te_re_id = ${partnerId}` : ``};
`);

Generated Query:

SELECT 
  COUNT(*) as total                                                                                                    FROM
  te_tenants                                                                                                           WHERE
  1 = 1
  ?
  ?
  ?
  ?;

Params:

["", "", "", ""]

First question, are you using raw query on purpose or you just didn't find how to make this query in drizzle?

maybe I can help you to make it work with drizzle

That would be great. I didn't find a way to convert the above query into drizzle.
Could you also please tell me why the above fails as I will be doing a lot of raw queries in the future.

Really appreciate your quick reply. Thanks, man.

Don't look and logic, just a small example on how to combine filter and pass them to .where + how to setup count()

We didn't have all of that in docs yet, but it will be very soon

Ping me if everything worked or not

import { and, eq, lt, or, SQL, sql } from "drizzle-orm";

const where: SQL[] = []

const id = ...
if (id > 3) {
  where.push(eq(schema.users.id, id))
}

if (id > 3) {
  where.push(or(lt(schema.users.id, 3), eq(schema.users.id, 3))!)
}

  await db.select({ count: sql<number>`count(*)`.mapWith(Number)}).from(schema.users).where(and(...where))

I don't know why it;s not formatting

worked

Will try it out and let you know.

const sqlQuery = `SELECT COUNT(*) as total FROM te_tenants WHERE 1 = 1;`;
const [res] = await db.execute(sql`${sqlQuery}`);

Something this simple is also failing. I am using deno, so maybe something to do with mysql driver.
But, I haven't tested it with node.

yeah, you need to write

const sqlQuery = sql`SELECT COUNT(*) as total FROM te_tenants WHERE 1 = 1;`;
const [res] = await db.execute(sqlQuery);

in this case it will work well

Yeah, it works, but fails with any conditional logic in it.

I was able to convert the above query to drizzle with your example.
Thank you for your help.