Drizzle Team•6mo ago

Effectively mixing QueryBuilder and sql`` using CTEs

Is it possible to amend Drizzle QueryBuilder queries with custom SQL? For example, I have a query composing of an update, a CTE and a returning clause. The query however cannot execute because its missing a FROM clause that actual "connects" the CTE and the update query. Drizzle does not support from on its update or insert query builders. I'd like to "hack" that from clause in the otherwise QueryBuilder constructed query. Like sql``.append, but for QueryBuilder. Is there a way to do that? Example code:

const q = await db
    .with(myCte)
    .update(myTable)
    .set({ myCol: 'val' })
    // Eample 1: This API doesnt exist
    //.from(myCte)
    // Example 2: This API doesnt exist
    //.appendSQL(sql`from ${myCte}`)
    .where(eq(myTable.myCol. myCte.myCol))
    .returning({col: myCte.myCol});

const q = await db
    .with(myCte)
    .update(myTable)
    .set({ myCol: 'val' })
    // Eample 1: This API doesnt exist
    //.from(myCte)
    // Example 2: This API doesnt exist
    //.appendSQL(sql`from ${myCte}`)
    .where(eq(myTable.myCol. myCte.myCol))
    .returning({col: myCte.myCol});

If not, how do I obtain the SQL for a CTE? I could construct the update query manually and just pull in the CTE "pre-built" into the custom sql. For example:

  const customSql = sql`with ${myCte} update ${myTable} set ${myTable.myCol} = ${'val'} from ${myCte} where ${myTable.myCol} = ${myCte.myCol} returning ${myCte.myCol}`

  const customSql = sql`with ${myCte} update ${myTable} set ${myTable.myCol} = ${'val'} from ${myCte} where ${myTable.myCol} = ${myCte.myCol} returning ${myCte.myCol}`

The ${myCte} use just inserts the CTE's alias, which is the required behavior in the second case, but not the first where I need the actual CTE table sql. How do I get that so I can construct my own custom update using CTEs?

2 Replies

dev0OP•6mo ago

I think I'm onto two possible approaches: 1) It appears I can create a "partial" query up to "set" and obtain the query string using toSQL. I assume I might be able to get the SQL, too using getSQL and then append the remaining parts of the query from from ${myCte} onwards. 2) I am also able to get the SQL from the complete query using getSQL. I could look into somehow inserting the sqlfrom ${myCte} into that before execution. Any additional recommendations or advice greatly appreciated. I enjoy working with Drizzle when I can stay fully within it's APIs. But sadly some very basic API surfaces are dearly missing, especially on the write side of things. I like that it has escape hatches, but they often feel too much "all or nothing". Would be great if some of those escape hatches where a little more "topical". I tried the first approach and it appears to be working great. I'll try some more approaches just to see what the cleanest way is. Also tried the second approach. Haven't tried executing the query yet, but the resulting query string looks good. It's a little tricky to find the right spot to insert the addtional chunk, but outside of that it's pretty straight forward, too. I think what would be really useful was some form of escape hatch on all query builders that would allow for inserting arbitrary SQL chunks into the generated QueryBuilder chunk list. That's a powerful feature and a potential foot gun obviously, but it would make these kinds of use-cases so much easier to deal with.

Eli•6mo ago

Did you replaces all the escapeParam (like questionmarks ? in the getSQL().sql) yourself to get the raw SQL string?

Gaming

Programming

Effectively mixing QueryBuilder and sql`` using CTEs