Conditional logic within template strings not supported on raw MySQL query

Jjivan5/19/2023
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}` : ``};
`);
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
?
?
?
?;
SELECT
COUNT(*) as total FROM
te_tenants WHERE
1 = 1
?
?
?
?;
Params:
["", "", "", ""]
["", "", "", ""]
ASAndrii Sherman5/19/2023
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
Jjivan5/19/2023
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.
ASAndrii Sherman5/19/2023
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))
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
Jjivan5/19/2023
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}`);
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.
ASAndrii Sherman5/19/2023
yeah, you need to write
const sqlQuery = sql`SELECT COUNT(*) as total FROM te_tenants WHERE 1 = 1;`;
const [res] = await db.execute(sqlQuery);
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
Jjivan5/19/2023
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.
Aadoublef8/1/2023
@a_sherman I wanted to ask if those functions from the drizzle-orm package can work outside of Node/Drizzle? I am a Deno user that is looking to try and experiement with Turso and was thinking if I may be able to add this
ASAndrii Sherman8/2/2023
It should work there. We didn't have a chance to try out Deno, but you can give it a shot
Aadoublef8/2/2023
@Andrew Sherman ah I gave it ago and while I can import from Esm.sh looks like there's some node specific code that when imported breaks Deno. I think it's still something I'd like to dive into more this week so if I gain any traction will notify through an issue

Looking for more? Join the community!

Want results from more Discord servers?
Add your server
Recommended Posts
executing an `update` statement does not use `.onUpdateNow()` defined in schemaHello everyone 🙂 I have the following schema for my `USERS` table: ```ts export const users = mysqHelp with raw queryThe following query is all raw. Is there a way to make SQL raw only the part of the `where` clause? Create GIN index in PostgresI need to create this index in postgres: ```sql CREATE INDEX users_name_gin_trgm_idx ON users USINGHow to plus 1 to the current value of a column when updateHow to plus 1 to the current value of a column when updating a recordOrdering by a string column but coercing it to a number for orderingI have a column that mostly contains numbers with only a very few exceptions and want to apply ordertype union on entire rowi can think of workarounds for this but figured i'd ask if i have a table that's like [id, type, daProper query results inferencePosting my GitHub discussion here for more visibility: https://github.com/drizzle-team/drizzle-orm/dDrizzle with Next 13.4.2 & Vercel Postgres TimeoutHi all. I'm trying out NextJS server actions and Drizzle for the first time. I have it hooked up tI think I don't really understand migrations local sqlite.It's a local sqlite db (`DB_URL=file:dev.db`) in a nextjs & trpc setup. I'm trying drizzle for the fConnecting to Vercel PostgresI'm trying to connect to Vercel Postgres like this: ```ts import { drizzle } from "drizzle-orm/verceinsertID not returned correctly when using custom UUID```export const users = mysqlTable("users", { id: varchar("id", { length: 36 }) .primaryKey() weird ilike postgres behaviorhi, I have a very weird issue when trying to filter using ilike in postgres, both my local instance Cannot make a .all() query on an empty table (Bun SQLITE)I am new to SQLITE but i'm unable to make a .all() query on an empty table in a Bun with Drizzle proSelf referencing nullable ID field not assigneable in a `eq` statementI am not sure if I encountered another edge case but basically I have a self-referencing table whereCustom `Select` object returns type `any`Databases like Planetscale do not support FK constraints. As I understand I can manipulate the returschema generates wrong indexI have this schema: ```js export const historyFiles = pgTable('history_files', { id: serial('id'MySQL (Planetscale): Cannot read properties of undefined (reading 'name')Hi Hopefully someone can help me. I'm getting the above error in my insert query. ```ts try { cDeploying Next.js w/ Drizzle on Vercel with TurborepoI have a turborepo with Next.js using `drizzle-orm` as a shared package. A bit of context - I'm usiuuid missing in drizzle-orm/mysql-core?There is a uuid method in the drizzle-orm/pg-core package, but none in the mysql-core package. What DatabaseError: Duplicate column name 'id'hey guys, i have a problem in my query but cannot figure out what is causing it. ```ts const cour