Wrapping Drizzle queries with raw SQL?

[spiffytech]

I'm looking for an escape hatch for features Drizzle doesn't support yet, like ON CONFLICT or UPDATE ... ORDER BY.

I'd like to do something like this:

db.run(sql`
  ${db.update(...)}
  ORDER BY foo
`)

I.e., getting Drizzle's great query-building properties where I can, and supplementing it with raw SQL when necessary.

Right now this doesn't work - Drizzle generates SQL that shaped like (update ...) ORDER BY foo, which isn't valid SQL.

Is there a way to wrap Drizzle queries with raw SQL? Right now I'm falling back to queries written as 100% SQL.

[205g0]

I think you can add partial raw sql to, e.g., .select() and others. Out of interest, what's wrong for using a raw, entire sql statement from time to time and where type-safety is not needed?

[spiffytech]

What I'm finding is it's my most complicated queries that need SQL features that Drizzle doesn't support, and that's where giving up type safety hurts the most. Even if Drizzle will only handled the simpler parts of the query, it'd make it easier to manage the query as a whole.

[205g0]

Mind to share an example query in SQL we can look at? Not your most complicated, edge use case query no one will ever use but a typical complicated one where you think Drizzle would struggle...

[spiffytech]

Sure! One example is needing to specify a conflict target clause with ON CONFLICT - right now Drizzle seems to only support unconditional ON CONFLICT.

In the example below, I only want the clause to trigger for a specific UNIQUE index, but want the query to fail if I have a PK violation. In past versions of this query, I've had multiple ON CONFLICT clauses that handled more possible violations.

Drizzle can't express this query right now. I suppose I could mimic the behavior by runing some SELECTs in a transaction instead...

The query could be expressed as sql`${myDrizzleInsertQuery} SOME SQL` if the sql tagged template literal supported it. Right now, the entire query has to fall back to raw SQL. I've previously filed an issue for UPDATE ... ORDER BY that could benefit from the same workaround.

And it is a workaround, to be clear - a stop-gap for handling cases where I need an SQL feature that Drizzle doesn't support yet.

  db.get<InferModel<typeof linksTable>>(
        sql/*sql*/ `
        insert into links (
            link_id,
            user_id,
            domain,
            url,
            original_url,
            title,
            description,
            emoji,
            is_latest_deleted,
            deleted_at
        ) values (
            ${link.link_id},
            ${link.user_id},
            ${link.domain},
            ${link.url},
            ${link.original_url},
            ${link.title},
            ${link.description},
            ${link.emoji},
            0,
            null
        )
        on conflict (user_id, original_url) do update set
            deleted_at = null,
            is_latest_deleted = 0,
            created_at = ${new Date().toISOString()}
        returning *
      `
    )

[spiffytech]

Wait - double-checking the current Drizzle docs, I do see a conflict target option.

Has this been there the whole time and I'm just dense and didn't read closely enough? 🙃

Well the UPDATE ... ORDER BY one still seems valid. I think I've seen others, I'll have to skim my codebase and check.

[spiffytech]

Looks like Drizzle produces invalid conflict target syntax when specifying multiple column targets. I'll file an issue for that, and work around that with sql in the meantime. EDIT: There's already an issue for that. It got fixed, but I'm not sure the fact of that fix got documented.

I might have seen other examples in the past, but for now the UPDATE ... ORDER BY example looks like the only query in my codebase that would benefit from this.

[AndriiSherman]

You can check: https://orm.drizzle.team/docs/sql#sql-select
and: https://orm.drizzle.team/docs/sql

[spiffytech]

Could you please clarify how I should use sql to handle this?

sql-select doesn't seem like what I'm after - it lets me put raw SQL inside a Drizzle query. I'm looking for the opposite - putting a Drizzle query inside raw SQL, so I can use SQL features Drizzle doesn't have support for.

I tried sql.raw, which seemed promising, but it doesn't SQL-ify the Drizzle query, so I just wind up with [object Object] in my processed SQL.